v0.17.0

sqlfmt CHANGELOG

All notable changes to this project will be documented in this file.

[Unreleased]

[0.17.0] - 2023-02-24

• sqlfmt now defaults to reading and writing files using the utf-8 encoding. Previously, we used Python's default behavior of using the encoding from the host machine's locale. However, as utf-8 becomes a de-facto standard, this was causing issues for some Windows users, whose locale was set to use older encodings. You can use the --encoding option to specify a different encoding. Setting encoding to inherit, e.g., sqlfmt --encoding inherit foo.sql will revert to the old behavior of using the host's locale. sqlfmt will detect and preserve a UTF BOM if it is present. If you specify --encoding utf-8-sig, sqlfmt will always write a UTF-8 BOM in the formatted file. (#350, #381, #383 - thank you @profesia-company, @cmcnicoll, @aersam, and @ryanmeekins!)

[0.16.0] - 2023-01-27

Formatting Changes + Bug Fixes

• sqlfmt no longer merges lines that contain comments, unless the position of those comments can be preserved (#348 - thank you, @rileyschack and @IanEdington!). Accordingly, comments that are inline will stay inline, even if they are too long to fit.
• sqlfmt no longer merges together lines containing multiline jinja blocks unless those lines start with an operator or comma (#365 - thank you, @gavlt!).
• fixed a bug where adding a jinja end tag (e.g., {% endif %}) to a line could cause bad formatting of everything on that line

[0.15.2] - 2023-01-23

Features

• adds support for ARM-based platforms using Docker.

[0.15.1] - 2023-01-20

Features

• added a Dockerfile for running sqlfmt in a container. New versions of sqlfmt will include Docker builds pushed to the GitHub Container Registry (thank you @ysmilda!).

[0.15.0] - 2023-01-18

Formatting Changes + Bug Fixes

• sqlfmt now removes extra blank lines (#249 - thank you, @nfcampos!). Basically, no more than 1 blank line inside queries or blocks; no more than 2 between queries or blocks.
• sqlfmt now supports create <object> ... clone statements (#313).
• sqlfmt will now format all files that end with *.sql and *.sql.jinja, even those with other dots in their filenames (#354 - thank you @ysmilda!).
• fixed a bug where {% call %} blocks with arguments like {% call(foo) bar(baz) %} would cause a parsing error (#353 - thank you @IgnorantWalking!).
• sqlfmt now supports bun placeholders (#356 - thank you @ysmilda!)

Features

• by default, sqlfmt now runs an additional safety check that parses the formatted output to ensure it contains all of the same content as the raw input. This incurs a slight (~20%) performance penalty. To bypass this safety check, you can use the command line option --fast, the corresponding TOML or environment variable config, or pass Mode(fast=True) to any API method. The safety check is automatically bypassed if sqlfmt is run with the --check or --diff options. If the safety check fails, the CLI will include an error in the report, and the format_string API will raise a SqlfmtEquivalenceError, which is a subclass of SqlfmtError.

[0.14.3] - 2023-01-05

Formatting Changes + Bug Fixes

• fixed a bug where very long lines could raise RecursionError (#343 - thank you @kcem-flyr!).

[0.14.2] - 2022-12-12

Formatting Changes + Bug Fixes

• fixed a bug where nested {% set %} and {% call %} blocks would cause a parsing error (#338 - thank you @AndrewLane!).

[0.14.1] - 2022-12-06

Formatting Changes + Bug Fixes

• sqlfmt now supports is [not] distinct from as a word operator (#327 - thank you @IgnorantWalking, @kadekillary!).
• fixed a bug where jinja {% call %} blocks that called a macro that wasn't statement caused a parsing error (#335 - thank you @AndrewLane!).

Performance

• sqlfmt runs finish in 20% less time due to algorithmic improvements

[0.14.0] - 2022-11-30

Formatting Changes + Bug Fixes

• sqlfmt now supports {% materialization ... %} and {% call statement(...) %} blocks (#309).
• sqlfmt now resets the SQL depth of a query after encountering an {% endmacro %}, {% endtest %}, {% endcall %}, or {% endmaterialization %} tag.
• sqlfmt now supports create warehouse and alter warehouse statements (#312, #299).
• sqlfmt now supports alter function and drop function statements (#310, #311), and Snowflake's create external function statements (#322).
• sqlfmt better supports numeric constants (number literals), including those using scientific notation (e.g., 1.5e-9) and the unary + or - operators (e.g., +3), and is now smarter about when the - symbol is the unary negative or binary subtraction operator. (#321 - thank you @liaopeiyuan!).
• fixed a bug where we added extra whitespace to the end of empty comment lines (#319 - thank you @eherde!).
• fixed an bug where wrapping unsupported DDL in jinja would cause a parsing error (#326 - thank you @ETG-msimons!). Also improved parsing of unsupported DDL and made false positives less likely.
• fixed a bug where we could have unsafely run black against jinja that contained Python keywords and their safe alternatives (e.g., return(return_())).
• fixed a bug where we deleted some extra whitespace lines (and in very rare cases, nonblank lines)
• fixed a bug where Python recursion limits could cause incorrect formatting in rare cases

[0.13.0] - 2022-11-01

Formatting Changes + Bug Fixes

• sqlfmt now supports delete statements and the associated keywords using and returning (#281).
• sqlfmt now supports grant and revoke statements and all associated keywords (#283).
• sqlfmt now supports create function statements and all associated keywords (#282).
• sqlfmt now supports the explain keyword (#280).
• sqlfmt now supports BigQuery typed table and struct definitions and literals, like table<a int64, b bytes(5), c string>.
• sqlfmt now supports variables like $foo as ordinary identifiers.

Features

• sqlfmt is now tested against Python 3.11 (#242). Previous versions of sqlfmt are also compatible.
  with Python 3.11. When installed in 3.11, sqlfmt no longer requires the tomli dependency.

[0.12.0] - 2022-10-14

Formatting Changes + Bug Fixes

• DDL and DML statements (create, insert, grant, etc.) will no longer be formatted (#243).
  These statements were never supported by sqlfmt, and the existing algorithm produced bad formatting. Support for DDL and DML statements will be gradually added back in in future versions.
  For more information, see the tracking issue for DDL support.
• BigQuery typed array literals like array<float64>[1, 2] are now supported, and spaces will no longer be inserted around < and > (#212).
• SparkSQL-specific keywords tablesample, cluster by, distribute by, sort by, and lateral view are now supported by the polyglot dialect (#264).
• pivot and unpivot are now supported as word operators, and will have a space between the keyword and the following parentheses.
• values is now supported as an unterminated keyword; tuples of values will be indented from the values keyword if they span more than one line (#263).

[0.11.1] - 2022-09-17

Features

• Any CLI option can now be configured using environment variables. Variable names are prefixed by SQLFMT and are the SHOUTING_CASE spelling of the options. For example, sqlfmt . --line-length 100 is equivalent to SQLFMT_LINE_LENGTH=100 sqlfmt . (#251).

D...

v0.16.0

sqlfmt CHANGELOG

All notable changes to this project will be documented in this file.

[Unreleased]

[0.16.0] - 2023-01-27

Formatting Changes + Bug Fixes

• sqlfmt no longer merges lines that contain comments, unless the position of those comments can be preserved (#348 - thank you, @rileyschack and @IanEdington!). Accordingly, comments that are inline will stay inline, even if they are too long to fit.
• sqlfmt no longer merges together lines containing multiline jinja blocks unless those lines start with an operator or comma (#365 - thank you, @gavlt!).
• fixed a bug where adding a jinja end tag (e.g., {% endif %}) to a line could cause bad formatting of everything on that line

[0.15.2] - 2023-01-23

Features

• adds support for ARM-based platforms using Docker.

[0.15.1] - 2023-01-20

Features

• added a Dockerfile for running sqlfmt in a container. New versions of sqlfmt will include Docker builds pushed to the GitHub Container Registry (thank you @ysmilda!).

[0.15.0] - 2023-01-18

Formatting Changes + Bug Fixes

• sqlfmt now removes extra blank lines (#249 - thank you, @nfcampos!). Basically, no more than 1 blank line inside queries or blocks; no more than 2 between queries or blocks.
• sqlfmt now supports create <object> ... clone statements (#313).
• sqlfmt will now format all files that end with *.sql and *.sql.jinja, even those with other dots in their filenames (#354 - thank you @ysmilda!).
• fixed a bug where {% call %} blocks with arguments like {% call(foo) bar(baz) %} would cause a parsing error (#353 - thank you @IgnorantWalking!).
• sqlfmt now supports bun placeholders (#356 - thank you @ysmilda!)

Features

• by default, sqlfmt now runs an additional safety check that parses the formatted output to ensure it contains all of the same content as the raw input. This incurs a slight (~20%) performance penalty. To bypass this safety check, you can use the command line option --fast, the corresponding TOML or environment variable config, or pass Mode(fast=True) to any API method. The safety check is automatically bypassed if sqlfmt is run with the --check or --diff options. If the safety check fails, the CLI will include an error in the report, and the format_string API will raise a SqlfmtEquivalenceError, which is a subclass of SqlfmtError.

[0.14.3] - 2023-01-05

Formatting Changes + Bug Fixes

• fixed a bug where very long lines could raise RecursionError (#343 - thank you @kcem-flyr!).

[0.14.2] - 2022-12-12

Formatting Changes + Bug Fixes

• fixed a bug where nested {% set %} and {% call %} blocks would cause a parsing error (#338 - thank you @AndrewLane!).

[0.14.1] - 2022-12-06

Formatting Changes + Bug Fixes

• sqlfmt now supports is [not] distinct from as a word operator (#327 - thank you @IgnorantWalking, @kadekillary!).
• fixed a bug where jinja {% call %} blocks that called a macro that wasn't statement caused a parsing error (#335 - thank you @AndrewLane!).

Performance

• sqlfmt runs finish in 20% less time due to algorithmic improvements

[0.14.0] - 2022-11-30

Formatting Changes + Bug Fixes

• sqlfmt now supports {% materialization ... %} and {% call statement(...) %} blocks (#309).
• sqlfmt now resets the SQL depth of a query after encountering an {% endmacro %}, {% endtest %}, {% endcall %}, or {% endmaterialization %} tag.
• sqlfmt now supports create warehouse and alter warehouse statements (#312, #299).
• sqlfmt now supports alter function and drop function statements (#310, #311), and Snowflake's create external function statements (#322).
• sqlfmt better supports numeric constants (number literals), including those using scientific notation (e.g., 1.5e-9) and the unary + or - operators (e.g., +3), and is now smarter about when the - symbol is the unary negative or binary subtraction operator. (#321 - thank you @liaopeiyuan!).
• fixed a bug where we added extra whitespace to the end of empty comment lines (#319 - thank you @eherde!).
• fixed an bug where wrapping unsupported DDL in jinja would cause a parsing error (#326 - thank you @ETG-msimons!). Also improved parsing of unsupported DDL and made false positives less likely.
• fixed a bug where we could have unsafely run black against jinja that contained Python keywords and their safe alternatives (e.g., return(return_())).
• fixed a bug where we deleted some extra whitespace lines (and in very rare cases, nonblank lines)
• fixed a bug where Python recursion limits could cause incorrect formatting in rare cases

[0.13.0] - 2022-11-01

Formatting Changes + Bug Fixes

• sqlfmt now supports delete statements and the associated keywords using and returning (#281).
• sqlfmt now supports grant and revoke statements and all associated keywords (#283).
• sqlfmt now supports create function statements and all associated keywords (#282).
• sqlfmt now supports the explain keyword (#280).
• sqlfmt now supports BigQuery typed table and struct definitions and literals, like table<a int64, b bytes(5), c string>.
• sqlfmt now supports variables like $foo as ordinary identifiers.

Features

• sqlfmt is now tested against Python 3.11 (#242). Previous versions of sqlfmt are also compatible.
  with Python 3.11. When installed in 3.11, sqlfmt no longer requires the tomli dependency.

[0.12.0] - 2022-10-14

Formatting Changes + Bug Fixes

• DDL and DML statements (create, insert, grant, etc.) will no longer be formatted (#243).
  These statements were never supported by sqlfmt, and the existing algorithm produced bad formatting. Support for DDL and DML statements will be gradually added back in in future versions.
  For more information, see the tracking issue for DDL support.
• BigQuery typed array literals like array<float64>[1, 2] are now supported, and spaces will no longer be inserted around < and > (#212).
• SparkSQL-specific keywords tablesample, cluster by, distribute by, sort by, and lateral view are now supported by the polyglot dialect (#264).
• pivot and unpivot are now supported as word operators, and will have a space between the keyword and the following parentheses.
• values is now supported as an unterminated keyword; tuples of values will be indented from the values keyword if they span more than one line (#263).

[0.11.1] - 2022-09-17

Features

• Any CLI option can now be configured using environment variables. Variable names are prefixed by SQLFMT and are the SHOUTING_CASE spelling of the options. For example, sqlfmt . --line-length 100 is equivalent to SQLFMT_LINE_LENGTH=100 sqlfmt . (#251).

Documentation

• The README has been shortened and now links to docs.sqlfmt.com.

[0.11.0] - 2022-08-21

Breaking API Changes

• The files argument of api.run is now a Collection[pathlib.Path] that represents an exact collection of files to be formatted, instead of a list of paths to search for files. Use api.get_matching_paths(paths, mode) to return the set of exact paths expected by api.run.

Features

• sqlfmt will now display a progress bar for long runs (#231). You can disable this with the --no-progressbar option.
• api.run now accepts an optional callback argument, which must be a Callable[[Awaitable[SqlFormatResult]], None]. Unless the --single-process option is used, the callback is executed after each file is formatted.
• sqlfmt can now be called as a python module, with python -m sqlfmt.

Formatting Changes + Bug Fixes

• adds more granularity to operator precedence and will merge lines more aggressively that start with high-precedence op...

v0.15.2

sqlfmt CHANGELOG

All notable changes to this project will be documented in this file.

[Unreleased]

[0.15.2] - 2023-01-23

• adds support for ARM-based platforms using Docker.

[0.15.1] - 2023-01-20

Features

• added a Dockerfile for running sqlfmt in a container. New versions of sqlfmt will include Docker builds pushed to the GitHub Container Registry (thank you @ysmilda!).

[0.15.0] - 2023-01-18

Formatting Changes + Bug Fixes

• sqlfmt now removes extra blank lines (#249 - thank you, @nfcampos!). Basically, no more than 1 blank line inside queries or blocks; no more than 2 between queries or blocks.
• sqlfmt now supports create <object> ... clone statements (#313).
• sqlfmt will now format all files that end with *.sql and *.sql.jinja, even those with other dots in their filenames (#354 - thank you @ysmilda!).
• fixed a bug where {% call %} blocks with arguments like {% call(foo) bar(baz) %} would cause a parsing error (#353 - thank you @IgnorantWalking!).
• sqlfmt now supports bun placeholders (#356 - thank you @ysmilda!)

Features

• by default, sqlfmt now runs an additional safety check that parses the formatted output to ensure it contains all of the same content as the raw input. This incurs a slight (~20%) performance penalty. To bypass this safety check, you can use the command line option --fast, the corresponding TOML or environment variable config, or pass Mode(fast=True) to any API method. The safety check is automatically bypassed if sqlfmt is run with the --check or --diff options. If the safety check fails, the CLI will include an error in the report, and the format_string API will raise a SqlfmtEquivalenceError, which is a subclass of SqlfmtError.

[0.14.3] - 2023-01-05

Formatting Changes + Bug Fixes

• fixed a bug where very long lines could raise RecursionError (#343 - thank you @kcem-flyr!).

[0.14.2] - 2022-12-12

Formatting Changes + Bug Fixes

• fixed a bug where nested {% set %} and {% call %} blocks would cause a parsing error (#338 - thank you @AndrewLane!).

[0.14.1] - 2022-12-06

Formatting Changes + Bug Fixes

• sqlfmt now supports is [not] distinct from as a word operator (#327 - thank you @IgnorantWalking, @kadekillary!).
• fixed a bug where jinja {% call %} blocks that called a macro that wasn't statement caused a parsing error (#335 - thank you @AndrewLane!).

Performance

• sqlfmt runs finish in 20% less time due to algorithmic improvements

[0.14.0] - 2022-11-30

Formatting Changes + Bug Fixes

• sqlfmt now supports {% materialization ... %} and {% call statement(...) %} blocks (#309).
• sqlfmt now resets the SQL depth of a query after encountering an {% endmacro %}, {% endtest %}, {% endcall %}, or {% endmaterialization %} tag.
• sqlfmt now supports create warehouse and alter warehouse statements (#312, #299).
• sqlfmt now supports alter function and drop function statements (#310, #311), and Snowflake's create external function statements (#322).
• sqlfmt better supports numeric constants (number literals), including those using scientific notation (e.g., 1.5e-9) and the unary + or - operators (e.g., +3), and is now smarter about when the - symbol is the unary negative or binary subtraction operator. (#321 - thank you @liaopeiyuan!).
• fixed a bug where we added extra whitespace to the end of empty comment lines (#319 - thank you @eherde!).
• fixed an bug where wrapping unsupported DDL in jinja would cause a parsing error (#326 - thank you @ETG-msimons!). Also improved parsing of unsupported DDL and made false positives less likely.
• fixed a bug where we could have unsafely run black against jinja that contained Python keywords and their safe alternatives (e.g., return(return_())).
• fixed a bug where we deleted some extra whitespace lines (and in very rare cases, nonblank lines)
• fixed a bug where Python recursion limits could cause incorrect formatting in rare cases

[0.13.0] - 2022-11-01

Formatting Changes + Bug Fixes

• sqlfmt now supports delete statements and the associated keywords using and returning (#281).
• sqlfmt now supports grant and revoke statements and all associated keywords (#283).
• sqlfmt now supports create function statements and all associated keywords (#282).
• sqlfmt now supports the explain keyword (#280).
• sqlfmt now supports BigQuery typed table and struct definitions and literals, like table<a int64, b bytes(5), c string>.
• sqlfmt now supports variables like $foo as ordinary identifiers.

Features

• sqlfmt is now tested against Python 3.11 (#242). Previous versions of sqlfmt are also compatible.
  with Python 3.11. When installed in 3.11, sqlfmt no longer requires the tomli dependency.

[0.12.0] - 2022-10-14

Formatting Changes + Bug Fixes

• DDL and DML statements (create, insert, grant, etc.) will no longer be formatted (#243).
  These statements were never supported by sqlfmt, and the existing algorithm produced bad formatting. Support for DDL and DML statements will be gradually added back in in future versions.
  For more information, see the tracking issue for DDL support.
• BigQuery typed array literals like array<float64>[1, 2] are now supported, and spaces will no longer be inserted around < and > (#212).
• SparkSQL-specific keywords tablesample, cluster by, distribute by, sort by, and lateral view are now supported by the polyglot dialect (#264).
• pivot and unpivot are now supported as word operators, and will have a space between the keyword and the following parentheses.
• values is now supported as an unterminated keyword; tuples of values will be indented from the values keyword if they span more than one line (#263).

[0.11.1] - 2022-09-17

Features

• Any CLI option can now be configured using environment variables. Variable names are prefixed by SQLFMT and are the SHOUTING_CASE spelling of the options. For example, sqlfmt . --line-length 100 is equivalent to SQLFMT_LINE_LENGTH=100 sqlfmt . (#251).

Documentation

• The README has been shortened and now links to docs.sqlfmt.com.

[0.11.0] - 2022-08-21

Breaking API Changes

• The files argument of api.run is now a Collection[pathlib.Path] that represents an exact collection of files to be formatted, instead of a list of paths to search for files. Use api.get_matching_paths(paths, mode) to return the set of exact paths expected by api.run.

Features

• sqlfmt will now display a progress bar for long runs (#231). You can disable this with the --no-progressbar option.
• api.run now accepts an optional callback argument, which must be a Callable[[Awaitable[SqlFormatResult]], None]. Unless the --single-process option is used, the callback is executed after each file is formatted.
• sqlfmt can now be called as a python module, with python -m sqlfmt.

Formatting Changes + Bug Fixes

• adds more granularity to operator precedence and will merge lines more aggressively that start with high-precedence operators (#200).
• improves the formatting of between ... and ..., especially in situations where the source includes a line break (#207).
• improves the consistency of formatting long chains of operators that include parentheses (#214).
• fixes a bug that caused unnecessary copying of the cache when using multiprocessing. Large projects should see dramatically faster (near-instant) runs once the cache is warm.
• fixes a bug that could cause lines with long jinja tags to be one character over the line length limit, and could result in unstable formatting (#237 - thank you [@nfcampos](https://github.co...

v0.15.1

sqlfmt CHANGELOG

All notable changes to this project will be documented in this file.

[Unreleased]

[0.15.1] - 2023-01-20

Features

• added a Dockerfile for running sqlfmt in a container. New versions of sqlfmt will include Docker builds pushed to the GitHub Container Registry (thank you @ysmilda!).

[0.15.0] - 2023-01-18

Formatting Changes + Bug Fixes

• sqlfmt now removes extra blank lines (#249 - thank you, @nfcampos!). Basically, no more than 1 blank line inside queries or blocks; no more than 2 between queries or blocks.
• sqlfmt now supports create <object> ... clone statements (#313).
• sqlfmt will now format all files that end with *.sql and *.sql.jinja, even those with other dots in their filenames (#354 - thank you @ysmilda!).
• fixed a bug where {% call %} blocks with arguments like {% call(foo) bar(baz) %} would cause a parsing error (#353 - thank you @IgnorantWalking!).
• sqlfmt now supports bun placeholders (#356 - thank you @ysmilda!)

Features

• by default, sqlfmt now runs an additional safety check that parses the formatted output to ensure it contains all of the same content as the raw input. This incurs a slight (~20%) performance penalty. To bypass this safety check, you can use the command line option --fast, the corresponding TOML or environment variable config, or pass Mode(fast=True) to any API method. The safety check is automatically bypassed if sqlfmt is run with the --check or --diff options. If the safety check fails, the CLI will include an error in the report, and the format_string API will raise a SqlfmtEquivalenceError, which is a subclass of SqlfmtError.

[0.14.3] - 2023-01-05

Formatting Changes + Bug Fixes

• fixed a bug where very long lines could raise RecursionError (#343 - thank you @kcem-flyr!).

[0.14.2] - 2022-12-12

Formatting Changes + Bug Fixes

• fixed a bug where nested {% set %} and {% call %} blocks would cause a parsing error (#338 - thank you @AndrewLane!).

[0.14.1] - 2022-12-06

Formatting Changes + Bug Fixes

• sqlfmt now supports is [not] distinct from as a word operator (#327 - thank you @IgnorantWalking, @kadekillary!).
• fixed a bug where jinja {% call %} blocks that called a macro that wasn't statement caused a parsing error (#335 - thank you @AndrewLane!).

Performance

• sqlfmt runs finish in 20% less time due to algorithmic improvements

[0.14.0] - 2022-11-30

Formatting Changes + Bug Fixes

• sqlfmt now supports {% materialization ... %} and {% call statement(...) %} blocks (#309).
• sqlfmt now resets the SQL depth of a query after encountering an {% endmacro %}, {% endtest %}, {% endcall %}, or {% endmaterialization %} tag.
• sqlfmt now supports create warehouse and alter warehouse statements (#312, #299).
• sqlfmt now supports alter function and drop function statements (#310, #311), and Snowflake's create external function statements (#322).
• sqlfmt better supports numeric constants (number literals), including those using scientific notation (e.g., 1.5e-9) and the unary + or - operators (e.g., +3), and is now smarter about when the - symbol is the unary negative or binary subtraction operator. (#321 - thank you @liaopeiyuan!).
• fixed a bug where we added extra whitespace to the end of empty comment lines (#319 - thank you @eherde!).
• fixed an bug where wrapping unsupported DDL in jinja would cause a parsing error (#326 - thank you @ETG-msimons!). Also improved parsing of unsupported DDL and made false positives less likely.
• fixed a bug where we could have unsafely run black against jinja that contained Python keywords and their safe alternatives (e.g., return(return_())).
• fixed a bug where we deleted some extra whitespace lines (and in very rare cases, nonblank lines)
• fixed a bug where Python recursion limits could cause incorrect formatting in rare cases

[0.13.0] - 2022-11-01

Formatting Changes + Bug Fixes

• sqlfmt now supports delete statements and the associated keywords using and returning (#281).
• sqlfmt now supports grant and revoke statements and all associated keywords (#283).
• sqlfmt now supports create function statements and all associated keywords (#282).
• sqlfmt now supports the explain keyword (#280).
• sqlfmt now supports BigQuery typed table and struct definitions and literals, like table<a int64, b bytes(5), c string>.
• sqlfmt now supports variables like $foo as ordinary identifiers.

Features

• sqlfmt is now tested against Python 3.11 (#242). Previous versions of sqlfmt are also compatible.
  with Python 3.11. When installed in 3.11, sqlfmt no longer requires the tomli dependency.

[0.12.0] - 2022-10-14

Formatting Changes + Bug Fixes

• DDL and DML statements (create, insert, grant, etc.) will no longer be formatted (#243).
  These statements were never supported by sqlfmt, and the existing algorithm produced bad formatting. Support for DDL and DML statements will be gradually added back in in future versions.
  For more information, see the tracking issue for DDL support.
• BigQuery typed array literals like array<float64>[1, 2] are now supported, and spaces will no longer be inserted around < and > (#212).
• SparkSQL-specific keywords tablesample, cluster by, distribute by, sort by, and lateral view are now supported by the polyglot dialect (#264).
• pivot and unpivot are now supported as word operators, and will have a space between the keyword and the following parentheses.
• values is now supported as an unterminated keyword; tuples of values will be indented from the values keyword if they span more than one line (#263).

[0.11.1] - 2022-09-17

Features

• Any CLI option can now be configured using environment variables. Variable names are prefixed by SQLFMT and are the SHOUTING_CASE spelling of the options. For example, sqlfmt . --line-length 100 is equivalent to SQLFMT_LINE_LENGTH=100 sqlfmt . (#251).

Documentation

• The README has been shortened and now links to docs.sqlfmt.com.

[0.11.0] - 2022-08-21

Breaking API Changes

• The files argument of api.run is now a Collection[pathlib.Path] that represents an exact collection of files to be formatted, instead of a list of paths to search for files. Use api.get_matching_paths(paths, mode) to return the set of exact paths expected by api.run.

Features

• sqlfmt will now display a progress bar for long runs (#231). You can disable this with the --no-progressbar option.
• api.run now accepts an optional callback argument, which must be a Callable[[Awaitable[SqlFormatResult]], None]. Unless the --single-process option is used, the callback is executed after each file is formatted.
• sqlfmt can now be called as a python module, with python -m sqlfmt.

Formatting Changes + Bug Fixes

• adds more granularity to operator precedence and will merge lines more aggressively that start with high-precedence operators (#200).
• improves the formatting of between ... and ..., especially in situations where the source includes a line break (#207).
• improves the consistency of formatting long chains of operators that include parentheses (#214).
• fixes a bug that caused unnecessary copying of the cache when using multiprocessing. Large projects should see dramatically faster (near-instant) runs once the cache is warm.
• fixes a bug that could cause lines with long jinja tags to be one character over the line length limit, and could result in unstable formatting (#237 - thank you @nfcampos!).
• fixes a bug that formatted array literals like they were indexi...

v0.15.0

sqlfmt CHANGELOG

All notable changes to this project will be documented in this file.

[Unreleased]

[0.15.0] - 2023-01-18

Formatting Changes + Bug Fixes

• sqlfmt now removes extra blank lines (#249 - thank you, @nfcampos!). Basically, no more than 1 blank line inside queries or blocks; no more than 2 between queries or blocks.
• sqlfmt now supports create <object> ... clone statements (#313).
• sqlfmt will now format all files that end with *.sql and *.sql.jinja, even those with other dots in their filenames (#354 - thank you @ysmilda!).
• fixed a bug where {% call %} blocks with arguments like {% call(foo) bar(baz) %} would cause a parsing error (#353 - thank you @IgnorantWalking!).
• sqlfmt now supports bun placeholders (#356 - thank you @ysmilda!)

Features

• by default, sqlfmt now runs an additional safety check that parses the formatted output to ensure it contains all of the same content as the raw input. This incurs a slight (~20%) performance penalty. To bypass this safety check, you can use the command line option --fast, the corresponding TOML or environment variable config, or pass Mode(fast=True) to any API method. The safety check is automatically bypassed if sqlfmt is run with the --check or --diff options. If the safety check fails, the CLI will include an error in the report, and the format_string API will raise a SqlfmtEquivalenceError, which is a subclass of SqlfmtError.

[0.14.3] - 2023-01-05

Formatting Changes + Bug Fixes

• fixed a bug where very long lines could raise RecursionError (#343 - thank you @kcem-flyr!).

[0.14.2] - 2022-12-12

Formatting Changes + Bug Fixes

• fixed a bug where nested {% set %} and {% call %} blocks would cause a parsing error (#338 - thank you @AndrewLane!).

[0.14.1] - 2022-12-06

Formatting Changes + Bug Fixes

• sqlfmt now supports is [not] distinct from as a word operator (#327 - thank you @IgnorantWalking, @kadekillary!).
• fixed a bug where jinja {% call %} blocks that called a macro that wasn't statement caused a parsing error (#335 - thank you @AndrewLane!).

Performance

• sqlfmt runs finish in 20% less time due to algorithmic improvements

[0.14.0] - 2022-11-30

Formatting Changes + Bug Fixes

• sqlfmt now supports {% materialization ... %} and {% call statement(...) %} blocks (#309).
• sqlfmt now resets the SQL depth of a query after encountering an {% endmacro %}, {% endtest %}, {% endcall %}, or {% endmaterialization %} tag.
• sqlfmt now supports create warehouse and alter warehouse statements (#312, #299).
• sqlfmt now supports alter function and drop function statements (#310, #311), and Snowflake's create external function statements (#322).
• sqlfmt better supports numeric constants (number literals), including those using scientific notation (e.g., 1.5e-9) and the unary + or - operators (e.g., +3), and is now smarter about when the - symbol is the unary negative or binary subtraction operator. (#321 - thank you @liaopeiyuan!).
• fixed a bug where we added extra whitespace to the end of empty comment lines (#319 - thank you @eherde!).
• fixed an bug where wrapping unsupported DDL in jinja would cause a parsing error (#326 - thank you @ETG-msimons!). Also improved parsing of unsupported DDL and made false positives less likely.
• fixed a bug where we could have unsafely run black against jinja that contained Python keywords and their safe alternatives (e.g., return(return_())).
• fixed a bug where we deleted some extra whitespace lines (and in very rare cases, nonblank lines)
• fixed a bug where Python recursion limits could cause incorrect formatting in rare cases

[0.13.0] - 2022-11-01

Formatting Changes + Bug Fixes

• sqlfmt now supports delete statements and the associated keywords using and returning (#281).
• sqlfmt now supports grant and revoke statements and all associated keywords (#283).
• sqlfmt now supports create function statements and all associated keywords (#282).
• sqlfmt now supports the explain keyword (#280).
• sqlfmt now supports BigQuery typed table and struct definitions and literals, like table<a int64, b bytes(5), c string>.
• sqlfmt now supports variables like $foo as ordinary identifiers.

Features

• sqlfmt is now tested against Python 3.11 (#242). Previous versions of sqlfmt are also compatible.
  with Python 3.11. When installed in 3.11, sqlfmt no longer requires the tomli dependency.

[0.12.0] - 2022-10-14

Formatting Changes + Bug Fixes

• DDL and DML statements (create, insert, grant, etc.) will no longer be formatted (#243).
  These statements were never supported by sqlfmt, and the existing algorithm produced bad formatting. Support for DDL and DML statements will be gradually added back in in future versions.
  For more information, see the tracking issue for DDL support.
• BigQuery typed array literals like array<float64>[1, 2] are now supported, and spaces will no longer be inserted around < and > (#212).
• SparkSQL-specific keywords tablesample, cluster by, distribute by, sort by, and lateral view are now supported by the polyglot dialect (#264).
• pivot and unpivot are now supported as word operators, and will have a space between the keyword and the following parentheses.
• values is now supported as an unterminated keyword; tuples of values will be indented from the values keyword if they span more than one line (#263).

[0.11.1] - 2022-09-17

Features

• Any CLI option can now be configured using environment variables. Variable names are prefixed by SQLFMT and are the SHOUTING_CASE spelling of the options. For example, sqlfmt . --line-length 100 is equivalent to SQLFMT_LINE_LENGTH=100 sqlfmt . (#251).

Documentation

• The README has been shortened and now links to docs.sqlfmt.com.

[0.11.0] - 2022-08-21

Breaking API Changes

• The files argument of api.run is now a Collection[pathlib.Path] that represents an exact collection of files to be formatted, instead of a list of paths to search for files. Use api.get_matching_paths(paths, mode) to return the set of exact paths expected by api.run.

Features

• sqlfmt will now display a progress bar for long runs (#231). You can disable this with the --no-progressbar option.
• api.run now accepts an optional callback argument, which must be a Callable[[Awaitable[SqlFormatResult]], None]. Unless the --single-process option is used, the callback is executed after each file is formatted.
• sqlfmt can now be called as a python module, with python -m sqlfmt.

Formatting Changes + Bug Fixes

• adds more granularity to operator precedence and will merge lines more aggressively that start with high-precedence operators (#200).
• improves the formatting of between ... and ..., especially in situations where the source includes a line break (#207).
• improves the consistency of formatting long chains of operators that include parentheses (#214).
• fixes a bug that caused unnecessary copying of the cache when using multiprocessing. Large projects should see dramatically faster (near-instant) runs once the cache is warm.
• fixes a bug that could cause lines with long jinja tags to be one character over the line length limit, and could result in unstable formatting (#237 - thank you @nfcampos!).
• fixes a bug that formatted array literals like they were indexing operations (#235 - thank you @nfcampos!).

[0.10.1] - 2022-08-05

Features

• sqlfmt now supports the psycopg placeholders %s and %(name)s ([#198]...

v0.14.3

sqlfmt CHANGELOG

All notable changes to this project will be documented in this file.

[Unreleased]

[0.14.3] - 2023-01-05

• fixed a bug where very long lines could raise RecursionError (#343 - thank you @kcem-flyr!).

[0.14.2] - 2022-12-12

Formatting Changes + Bug Fixes

• fixed a bug where nested {% set %} and {% call %} blocks would cause a parsing error (#338 - thank you @AndrewLane!).

[0.14.1] - 2022-12-06

Formatting Changes + Bug Fixes

• sqlfmt now supports is [not] distinct from as a word operator (#327 - thank you @IgnorantWalking, @kadekillary!).
• fixed a bug where jinja {% call %} blocks that called a macro that wasn't statement caused a parsing error (#335 - thank you @AndrewLane!).

Performance

• sqlfmt runs finish in 20% less time due to algorithmic improvements

[0.14.0] - 2022-11-30

Formatting Changes + Bug Fixes

• sqlfmt now supports {% materialization ... %} and {% call statement(...) %} blocks (#309).
• sqlfmt now resets the SQL depth of a query after encountering an {% endmacro %}, {% endtest %}, {% endcall %}, or {% endmaterialization %} tag.
• sqlfmt now supports create warehouse and alter warehouse statements (#312, #299).
• sqlfmt now supports alter function and drop function statements (#310, #311), and Snowflake's create external function statements (#322).
• sqlfmt better supports numeric constants (number literals), including those using scientific notation (e.g., 1.5e-9) and the unary + or - operators (e.g., +3), and is now smarter about when the - symbol is the unary negative or binary subtraction operator. (#321 - thank you @liaopeiyuan!).
• fixed a bug where we added extra whitespace to the end of empty comment lines (#319 - thank you @eherde!).
• fixed an bug where wrapping unsupported DDL in jinja would cause a parsing error (#326 - thank you @ETG-msimons!). Also improved parsing of unsupported DDL and made false positives less likely.
• fixed a bug where we could have unsafely run black against jinja that contained Python keywords and their safe alternatives (e.g., return(return_())).
• fixed a bug where we deleted some extra whitespace lines (and in very rare cases, nonblank lines)
• fixed a bug where Python recursion limits could cause incorrect formatting in rare cases

[0.13.0] - 2022-11-01

Formatting Changes + Bug Fixes

• sqlfmt now supports delete statements and the associated keywords using and returning (#281).
• sqlfmt now supports grant and revoke statements and all associated keywords (#283).
• sqlfmt now supports create function statements and all associated keywords (#282).
• sqlfmt now supports the explain keyword (#280).
• sqlfmt now supports BigQuery typed table and struct definitions and literals, like table<a int64, b bytes(5), c string>.
• sqlfmt now supports variables like $foo as ordinary identifiers.

Features

• sqlfmt is now tested against Python 3.11 (#242). Previous versions of sqlfmt are also compatible.
  with Python 3.11. When installed in 3.11, sqlfmt no longer requires the tomli dependency.

[0.12.0] - 2022-10-14

Formatting Changes + Bug Fixes

• DDL and DML statements (create, insert, grant, etc.) will no longer be formatted (#243).
  These statements were never supported by sqlfmt, and the existing algorithm produced bad formatting. Support for DDL and DML statements will be gradually added back in in future versions.
  For more information, see the tracking issue for DDL support.
• BigQuery typed array literals like array<float64>[1, 2] are now supported, and spaces will no longer be inserted around < and > (#212).
• SparkSQL-specific keywords tablesample, cluster by, distribute by, sort by, and lateral view are now supported by the polyglot dialect (#264).
• pivot and unpivot are now supported as word operators, and will have a space between the keyword and the following parentheses.
• values is now supported as an unterminated keyword; tuples of values will be indented from the values keyword if they span more than one line (#263).

[0.11.1] - 2022-09-17

Features

• Any CLI option can now be configured using environment variables. Variable names are prefixed by SQLFMT and are the SHOUTING_CASE spelling of the options. For example, sqlfmt . --line-length 100 is equivalent to SQLFMT_LINE_LENGTH=100 sqlfmt . (#251).

Documentation

• The README has been shortened and now links to docs.sqlfmt.com.

[0.11.0] - 2022-08-21

Breaking API Changes

• The files argument of api.run is now a Collection[pathlib.Path] that represents an exact collection of files to be formatted, instead of a list of paths to search for files. Use api.get_matching_paths(paths, mode) to return the set of exact paths expected by api.run.

Features

• sqlfmt will now display a progress bar for long runs (#231). You can disable this with the --no-progressbar option.
• api.run now accepts an optional callback argument, which must be a Callable[[Awaitable[SqlFormatResult]], None]. Unless the --single-process option is used, the callback is executed after each file is formatted.
• sqlfmt can now be called as a python module, with python -m sqlfmt.

Formatting Changes + Bug Fixes

• adds more granularity to operator precedence and will merge lines more aggressively that start with high-precedence operators (#200).
• improves the formatting of between ... and ..., especially in situations where the source includes a line break (#207).
• improves the consistency of formatting long chains of operators that include parentheses (#214).
• fixes a bug that caused unnecessary copying of the cache when using multiprocessing. Large projects should see dramatically faster (near-instant) runs once the cache is warm.
• fixes a bug that could cause lines with long jinja tags to be one character over the line length limit, and could result in unstable formatting (#237 - thank you @nfcampos!).
• fixes a bug that formatted array literals like they were indexing operations (#235 - thank you @nfcampos!).

[0.10.1] - 2022-08-05

Features

• sqlfmt now supports the psycopg placeholders %s and %(name)s (#198 - thank you @snorkysnark!).

Formatting Changes + Bug Fixes

• sqlfmt now standardizes whitespace inside word tokens (#201).
• using is now treated as a word operator. It gets a space before its brackets and merging with surrounding lines is now much improved (#218 - thank you @nfcampos!).
• within group and filter are now treated like over, and the formatting of those aggregate clauses is improved (#205).

[0.10.0] - 2022-08-02

Features

• sqlfmt now supports ClickHouse. When run with the --dialect clickhouse option, sqlfmt will not lowercase names that could be case-sensitive in ClickHouse, like function names, aliases, etc. (#193 - thank you @Shlomixg!).

Formatting Changes + Bug Fixes

• formatting for chained boolean operators with complex expressions is now significantly improved (#189 - thank you @Rainymood!).
• formatting for array indexing is now significantly improved (#209) and sqlfmt no longer inserts spaces between the offset() function and its brackets.
• set operators (like union) are now formatted differently. They must be on their own line, and will not cause subsequent blocks to be indented (#188 - thank you @Rainymood!).
• select * except (...) syntax is now explicitly supported, and for...

v0.14.2

sqlfmt CHANGELOG

All notable changes to this project will be documented in this file.

[Unreleased]

[0.14.2] - 2022-12-12

Formatting Changes + Bug Fixes

• fixed a bug where nested {% set %} and {% call %} blocks would cause a parsing error (#338 - thank you @AndrewLane!).

[0.14.1] - 2022-12-06

Formatting Changes + Bug Fixes

• sqlfmt now supports is [not] distinct from as a word operator (#327 - thank you @IgnorantWalking, @kadekillary!).
• fixed a bug where jinja {% call %} blocks that called a macro that wasn't statement caused a parsing error (#335 - thank you @AndrewLane!).

Performance

• sqlfmt runs finish in 20% less time due to algorithmic improvements

[0.14.0] - 2022-11-30

Formatting Changes + Bug Fixes

• sqlfmt now supports {% materialization ... %} and {% call statement(...) %} blocks (#309).
• sqlfmt now resets the SQL depth of a query after encountering an {% endmacro %}, {% endtest %}, {% endcall %}, or {% endmaterialization %} tag.
• sqlfmt now supports create warehouse and alter warehouse statements (#312, #299).
• sqlfmt now supports alter function and drop function statements (#310, #311), and Snowflake's create external function statements (#322).
• sqlfmt better supports numeric constants (number literals), including those using scientific notation (e.g., 1.5e-9) and the unary + or - operators (e.g., +3), and is now smarter about when the - symbol is the unary negative or binary subtraction operator. (#321 - thank you @liaopeiyuan!).
• fixed a bug where we added extra whitespace to the end of empty comment lines (#319 - thank you @eherde!).
• fixed an bug where wrapping unsupported DDL in jinja would cause a parsing error (#326 - thank you @ETG-msimons!). Also improved parsing of unsupported DDL and made false positives less likely.
• fixed a bug where we could have unsafely run black against jinja that contained Python keywords and their safe alternatives (e.g., return(return_())).
• fixed a bug where we deleted some extra whitespace lines (and in very rare cases, nonblank lines)
• fixed a bug where Python recursion limits could cause incorrect formatting in rare cases

[0.13.0] - 2022-11-01

Formatting Changes + Bug Fixes

• sqlfmt now supports delete statements and the associated keywords using and returning (#281).
• sqlfmt now supports grant and revoke statements and all associated keywords (#283).
• sqlfmt now supports create function statements and all associated keywords (#282).
• sqlfmt now supports the explain keyword (#280).
• sqlfmt now supports BigQuery typed table and struct definitions and literals, like table<a int64, b bytes(5), c string>.
• sqlfmt now supports variables like $foo as ordinary identifiers.

Features

• sqlfmt is now tested against Python 3.11 (#242). Previous versions of sqlfmt are also compatible.
  with Python 3.11. When installed in 3.11, sqlfmt no longer requires the tomli dependency.

[0.12.0] - 2022-10-14

Formatting Changes + Bug Fixes

• DDL and DML statements (create, insert, grant, etc.) will no longer be formatted (#243).
  These statements were never supported by sqlfmt, and the existing algorithm produced bad formatting. Support for DDL and DML statements will be gradually added back in in future versions.
  For more information, see the tracking issue for DDL support.
• BigQuery typed array literals like array<float64>[1, 2] are now supported, and spaces will no longer be inserted around < and > (#212).
• SparkSQL-specific keywords tablesample, cluster by, distribute by, sort by, and lateral view are now supported by the polyglot dialect (#264).
• pivot and unpivot are now supported as word operators, and will have a space between the keyword and the following parentheses.
• values is now supported as an unterminated keyword; tuples of values will be indented from the values keyword if they span more than one line (#263).

[0.11.1] - 2022-09-17

Features

• Any CLI option can now be configured using environment variables. Variable names are prefixed by SQLFMT and are the SHOUTING_CASE spelling of the options. For example, sqlfmt . --line-length 100 is equivalent to SQLFMT_LINE_LENGTH=100 sqlfmt . (#251).

Documentation

• The README has been shortened and now links to docs.sqlfmt.com.

[0.11.0] - 2022-08-21

Breaking API Changes

• The files argument of api.run is now a Collection[pathlib.Path] that represents an exact collection of files to be formatted, instead of a list of paths to search for files. Use api.get_matching_paths(paths, mode) to return the set of exact paths expected by api.run.

Features

• sqlfmt will now display a progress bar for long runs (#231). You can disable this with the --no-progressbar option.
• api.run now accepts an optional callback argument, which must be a Callable[[Awaitable[SqlFormatResult]], None]. Unless the --single-process option is used, the callback is executed after each file is formatted.
• sqlfmt can now be called as a python module, with python -m sqlfmt.

Formatting Changes + Bug Fixes

• adds more granularity to operator precedence and will merge lines more aggressively that start with high-precedence operators (#200).
• improves the formatting of between ... and ..., especially in situations where the source includes a line break (#207).
• improves the consistency of formatting long chains of operators that include parentheses (#214).
• fixes a bug that caused unnecessary copying of the cache when using multiprocessing. Large projects should see dramatically faster (near-instant) runs once the cache is warm.
• fixes a bug that could cause lines with long jinja tags to be one character over the line length limit, and could result in unstable formatting (#237 - thank you @nfcampos!).
• fixes a bug that formatted array literals like they were indexing operations (#235 - thank you @nfcampos!).

[0.10.1] - 2022-08-05

Features

• sqlfmt now supports the psycopg placeholders %s and %(name)s (#198 - thank you @snorkysnark!).

Formatting Changes + Bug Fixes

• sqlfmt now standardizes whitespace inside word tokens (#201).
• using is now treated as a word operator. It gets a space before its brackets and merging with surrounding lines is now much improved (#218 - thank you @nfcampos!).
• within group and filter are now treated like over, and the formatting of those aggregate clauses is improved (#205).

[0.10.0] - 2022-08-02

Features

• sqlfmt now supports ClickHouse. When run with the --dialect clickhouse option, sqlfmt will not lowercase names that could be case-sensitive in ClickHouse, like function names, aliases, etc. (#193 - thank you @Shlomixg!).

Formatting Changes + Bug Fixes

• formatting for chained boolean operators with complex expressions is now significantly improved (#189 - thank you @Rainymood!).
• formatting for array indexing is now significantly improved (#209) and sqlfmt no longer inserts spaces between the offset() function and its brackets.
• set operators (like union) are now formatted differently. They must be on their own line, and will not cause subsequent blocks to be indented (#188 - thank you @Rainymood!).
• select * except (...) syntax is now explicitly supported, and formatting is improved. Support added for BigQuery and DuckDB star options: except, exclude, replace.
• sqlfmt no longer inserts spaces between nested or repeated brackets, like (()) or ()[].
• a ...

v0.14.1

sqlfmt CHANGELOG

All notable changes to this project will be documented in this file.

[Unreleased]

[0.14.1] - 2022-12-06

Formatting Changes + Bug Fixes

• sqlfmt now supports is [not] distinct from as a word operator (#327 - thank you @IgnorantWalking, @kadekillary!).
• fixed a bug where jinja {% call %} blocks that called a macro that wasn't statement caused a parsing error (#335 - thank you @AndrewLane!).

Performance

• sqlfmt runs finish in 20% less time due to algorithmic improvements

[0.14.0] - 2022-11-30

Formatting Changes + Bug Fixes

• sqlfmt now supports {% materialization ... %} and {% call statement(...) %} blocks (#309).
• sqlfmt now resets the SQL depth of a query after encountering an {% endmacro %}, {% endtest %}, {% endcall %}, or {% endmaterialization %} tag.
• sqlfmt now supports create warehouse and alter warehouse statements (#312, #299).
• sqlfmt now supports alter function and drop function statements (#310, #311), and Snowflake's create external function statements (#322).
• sqlfmt better supports numeric constants (number literals), including those using scientific notation (e.g., 1.5e-9) and the unary + or - operators (e.g., +3), and is now smarter about when the - symbol is the unary negative or binary subtraction operator. (#321 - thank you @liaopeiyuan!).
• fixed a bug where we added extra whitespace to the end of empty comment lines (#319 - thank you @eherde!).
• fixed an bug where wrapping unsupported DDL in jinja would cause a parsing error (#326 - thank you @ETG-msimons!). Also improved parsing of unsupported DDL and made false positives less likely.
• fixed a bug where we could have unsafely run black against jinja that contained Python keywords and their safe alternatives (e.g., return(return_())).
• fixed a bug where we deleted some extra whitespace lines (and in very rare cases, nonblank lines)
• fixed a bug where Python recursion limits could cause incorrect formatting in rare cases

[0.13.0] - 2022-11-01

Formatting Changes + Bug Fixes

• sqlfmt now supports delete statements and the associated keywords using and returning (#281).
• sqlfmt now supports grant and revoke statements and all associated keywords (#283).
• sqlfmt now supports create function statements and all associated keywords (#282).
• sqlfmt now supports the explain keyword (#280).
• sqlfmt now supports BigQuery typed table and struct definitions and literals, like table<a int64, b bytes(5), c string>.
• sqlfmt now supports variables like $foo as ordinary identifiers.

Features

• sqlfmt is now tested against Python 3.11 (#242). Previous versions of sqlfmt are also compatible.
  with Python 3.11. When installed in 3.11, sqlfmt no longer requires the tomli dependency.

[0.12.0] - 2022-10-14

Formatting Changes + Bug Fixes

• DDL and DML statements (create, insert, grant, etc.) will no longer be formatted (#243).
  These statements were never supported by sqlfmt, and the existing algorithm produced bad formatting. Support for DDL and DML statements will be gradually added back in in future versions.
  For more information, see the tracking issue for DDL support.
• BigQuery typed array literals like array<float64>[1, 2] are now supported, and spaces will no longer be inserted around < and > (#212).
• SparkSQL-specific keywords tablesample, cluster by, distribute by, sort by, and lateral view are now supported by the polyglot dialect (#264).
• pivot and unpivot are now supported as word operators, and will have a space between the keyword and the following parentheses.
• values is now supported as an unterminated keyword; tuples of values will be indented from the values keyword if they span more than one line (#263).

[0.11.1] - 2022-09-17

Features

• Any CLI option can now be configured using environment variables. Variable names are prefixed by SQLFMT and are the SHOUTING_CASE spelling of the options. For example, sqlfmt . --line-length 100 is equivalent to SQLFMT_LINE_LENGTH=100 sqlfmt . (#251).

Documentation

• The README has been shortened and now links to docs.sqlfmt.com.

[0.11.0] - 2022-08-21

Breaking API Changes

• The files argument of api.run is now a Collection[pathlib.Path] that represents an exact collection of files to be formatted, instead of a list of paths to search for files. Use api.get_matching_paths(paths, mode) to return the set of exact paths expected by api.run.

Features

• sqlfmt will now display a progress bar for long runs (#231). You can disable this with the --no-progressbar option.
• api.run now accepts an optional callback argument, which must be a Callable[[Awaitable[SqlFormatResult]], None]. Unless the --single-process option is used, the callback is executed after each file is formatted.
• sqlfmt can now be called as a python module, with python -m sqlfmt.

Formatting Changes + Bug Fixes

• adds more granularity to operator precedence and will merge lines more aggressively that start with high-precedence operators (#200).
• improves the formatting of between ... and ..., especially in situations where the source includes a line break (#207).
• improves the consistency of formatting long chains of operators that include parentheses (#214).
• fixes a bug that caused unnecessary copying of the cache when using multiprocessing. Large projects should see dramatically faster (near-instant) runs once the cache is warm.
• fixes a bug that could cause lines with long jinja tags to be one character over the line length limit, and could result in unstable formatting (#237 - thank you @nfcampos!).
• fixes a bug that formatted array literals like they were indexing operations (#235 - thank you @nfcampos!).

[0.10.1] - 2022-08-05

Features

• sqlfmt now supports the psycopg placeholders %s and %(name)s (#198 - thank you @snorkysnark!).

Formatting Changes + Bug Fixes

• sqlfmt now standardizes whitespace inside word tokens (#201).
• using is now treated as a word operator. It gets a space before its brackets and merging with surrounding lines is now much improved (#218 - thank you @nfcampos!).
• within group and filter are now treated like over, and the formatting of those aggregate clauses is improved (#205).

[0.10.0] - 2022-08-02

Features

• sqlfmt now supports ClickHouse. When run with the --dialect clickhouse option, sqlfmt will not lowercase names that could be case-sensitive in ClickHouse, like function names, aliases, etc. (#193 - thank you @Shlomixg!).

Formatting Changes + Bug Fixes

• formatting for chained boolean operators with complex expressions is now significantly improved (#189 - thank you @Rainymood!).
• formatting for array indexing is now significantly improved (#209) and sqlfmt no longer inserts spaces between the offset() function and its brackets.
• set operators (like union) are now formatted differently. They must be on their own line, and will not cause subsequent blocks to be indented (#188 - thank you @Rainymood!).
• select * except (...) syntax is now explicitly supported, and formatting is improved. Support added for BigQuery and DuckDB star options: except, exclude, replace.
• sqlfmt no longer inserts spaces between nested or repeated brackets, like (()) or ()[].
• a bug causing unstable formatting with long/multiline jinja tags has been fixed (#175).

[0.9.0] - 2022-06-02

Features

• jinjafmt is now able to format jinja that contains functions and variables that are reserved...

v0.14.0

sqlfmt CHANGELOG

All notable changes to this project will be documented in this file.

[Unreleased]

[0.14.0] - 2022-11-30

Formatting Changes + Bug Fixes

• sqlfmt now supports {% materialization ... %} and {% call statement(...) %} blocks (#309).
• sqlfmt now resets the SQL depth of a query after encountering an {% endmacro %}, {% endtest %}, {% endcall %}, or {% endmaterialization %} tag.
• sqlfmt now supports create warehouse and alter warehouse statements (#312, #299).
• sqlfmt now supports alter function and drop function statements (#310, #311), and Snowflake's create external function statements (#322).
• sqlfmt better supports numeric constants (number literals), including those using scientific notation (e.g., 1.5e-9) and the unary + or - operators (e.g., +3), and is now smarter about when the - symbol is the unary negative or binary subtraction operator. (#321 - thank you @liaopeiyuan!).
• fixed a bug where we added extra whitespace to the end of empty comment lines (#319 - thank you @eherde!).
• fixed an bug where wrapping unsupported DDL in jinja would cause a parsing error (#326 - thank you @ETG-msimons!). Also improved parsing of unsupported DDL and made false positives less likely.
• fixed a bug where we could have unsafely run black against jinja that contained Python keywords and their safe alternatives (e.g., return(return_())).
• fixed a bug where we deleted some extra whitespace lines (and in very rare cases, nonblank lines)
• fixed a bug where Python recursion limits could cause incorrect formatting in rare cases

[0.13.0] - 2022-11-01

Formatting Changes + Bug Fixes

• sqlfmt now supports delete statements and the associated keywords using and returning (#281).
• sqlfmt now supports grant and revoke statements and all associated keywords (#283).
• sqlfmt now supports create function statements and all associated keywords (#282).
• sqlfmt now supports the explain keyword (#280).
• sqlfmt now supports BigQuery typed table and struct definitions and literals, like table<a int64, b bytes(5), c string>.
• sqlfmt now supports variables like $foo as ordinary identifiers.

Features

• sqlfmt is now tested against Python 3.11 (#242). Previous versions of sqlfmt are also compatible.
  with Python 3.11. When installed in 3.11, sqlfmt no longer requires the tomli dependency.

[0.12.0] - 2022-10-14

Formatting Changes + Bug Fixes

• DDL and DML statements (create, insert, grant, etc.) will no longer be formatted (#243).
  These statements were never supported by sqlfmt, and the existing algorithm produced bad formatting. Support for DDL and DML statements will be gradually added back in in future versions.
  For more information, see the tracking issue for DDL support.
• BigQuery typed array literals like array<float64>[1, 2] are now supported, and spaces will no longer be inserted around < and > (#212).
• SparkSQL-specific keywords tablesample, cluster by, distribute by, sort by, and lateral view are now supported by the polyglot dialect (#264).
• pivot and unpivot are now supported as word operators, and will have a space between the keyword and the following parentheses.
• values is now supported as an unterminated keyword; tuples of values will be indented from the values keyword if they span more than one line (#263).

[0.11.1] - 2022-09-17

Features

• Any CLI option can now be configured using environment variables. Variable names are prefixed by SQLFMT and are the SHOUTING_CASE spelling of the options. For example, sqlfmt . --line-length 100 is equivalent to SQLFMT_LINE_LENGTH=100 sqlfmt . (#251).

Documentation

• The README has been shortened and now links to docs.sqlfmt.com.

[0.11.0] - 2022-08-21

Breaking API Changes

• The files argument of api.run is now a Collection[pathlib.Path] that represents an exact collection of files to be formatted, instead of a list of paths to search for files. Use api.get_matching_paths(paths, mode) to return the set of exact paths expected by api.run.

Features

• sqlfmt will now display a progress bar for long runs (#231). You can disable this with the --no-progressbar option.
• api.run now accepts an optional callback argument, which must be a Callable[[Awaitable[SqlFormatResult]], None]. Unless the --single-process option is used, the callback is executed after each file is formatted.
• sqlfmt can now be called as a python module, with python -m sqlfmt.

Formatting Changes + Bug Fixes

• adds more granularity to operator precedence and will merge lines more aggressively that start with high-precedence operators (#200).
• improves the formatting of between ... and ..., especially in situations where the source includes a line break (#207).
• improves the consistency of formatting long chains of operators that include parentheses (#214).
• fixes a bug that caused unnecessary copying of the cache when using multiprocessing. Large projects should see dramatically faster (near-instant) runs once the cache is warm.
• fixes a bug that could cause lines with long jinja tags to be one character over the line length limit, and could result in unstable formatting (#237 - thank you @nfcampos!).
• fixes a bug that formatted array literals like they were indexing operations (#235 - thank you @nfcampos!).

[0.10.1] - 2022-08-05

Features

• sqlfmt now supports the psycopg placeholders %s and %(name)s (#198 - thank you @snorkysnark!).

Formatting Changes + Bug Fixes

• sqlfmt now standardizes whitespace inside word tokens (#201).
• using is now treated as a word operator. It gets a space before its brackets and merging with surrounding lines is now much improved (#218 - thank you @nfcampos!).
• within group and filter are now treated like over, and the formatting of those aggregate clauses is improved (#205).

[0.10.0] - 2022-08-02

Features

• sqlfmt now supports ClickHouse. When run with the --dialect clickhouse option, sqlfmt will not lowercase names that could be case-sensitive in ClickHouse, like function names, aliases, etc. (#193 - thank you @Shlomixg!).

Formatting Changes + Bug Fixes

• formatting for chained boolean operators with complex expressions is now significantly improved (#189 - thank you @Rainymood!).
• formatting for array indexing is now significantly improved (#209) and sqlfmt no longer inserts spaces between the offset() function and its brackets.
• set operators (like union) are now formatted differently. They must be on their own line, and will not cause subsequent blocks to be indented (#188 - thank you @Rainymood!).
• select * except (...) syntax is now explicitly supported, and formatting is improved. Support added for BigQuery and DuckDB star options: except, exclude, replace.
• sqlfmt no longer inserts spaces between nested or repeated brackets, like (()) or ()[].
• a bug causing unstable formatting with long/multiline jinja tags has been fixed (#175).

[0.9.0] - 2022-06-02

Features

• jinjafmt is now able to format jinja that contains functions and variables that are reserved python words (e.g., return, except, from) (#177, #155), and ~, the jinja string concatenation operator (#182)
• adds a new command-line option to reset the sqlfmt cache (#184)

Fixes

• fixes issue where jinjafmt would insert a trailing comma into multiline macro definitions, causing dbt compiling errors (#156)
• fixes issue causing unstable formatting of multilin...

v0.13.0

sqlfmt CHANGELOG

All notable changes to this project will be documented in this file.

[Unreleased]

[0.13.0] - 2022-11-01

Formatting Changes + Bug Fixes

• sqlfmt now supports delete statements and the associated keywords using and returning (#281)
• sqlfmt now supports grant and revoke statements and all associated keywords (#283)
• sqlfmt now supports create function statements and all associated keywords (#282)
• sqlfmt now supports the explain keyword (#280)
• sqlfmt now supports BigQuery typed table and struct definitions and literals, like table<a int64, b bytes(5), c string>
• sqlfmt now supports variables like $foo as ordinary identifiers

Features

• sqlfmt is now tested against Python 3.11 (#242). Previous versions of sqlfmt are also compatible
  with Python 3.11. When installed in 3.11, sqlfmt no longer requires the tomli dependency.

[0.12.0] - 2022-10-14

Formatting Changes + Bug Fixes

• DDL and DML statements (create, insert, grant, etc.) will no longer be formatted (#243).
  These statements were never supported by sqlfmt, and the existing algorithm produced bad formatting. Support for DDL and DML statements will be gradually added back in in future versions.
  For more information, see the tracking issue for DDL support.
• BigQuery typed array literals like array<float64>[1, 2] are now supported, and spaces will no longer be inserted around < and > (#212)
• SparkSQL-specific keywords tablesample, cluster by, distribute by, sort by, and lateral view are now supported by the polyglot dialect (#264)
• pivot and unpivot are now supported as word operators, and will have a space between the keyword and the following parentheses
• values is now supported as an unterminated keyword; tuples of values will be indented from the values keyword if they span more than one line (#263)

[0.11.1] - 2022-09-17

Features

• Any CLI option can now be configured using environment variables. Variable names are prefixed by SQLFMT and are the SHOUTING_CASE spelling of the options. For example, sqlfmt . --line-length 100 is equivalent to SQLFMT_LINE_LENGTH=100 sqlfmt . (#251).

Documentation

• The README has been shortened and now links to docs.sqlfmt.com.

[0.11.0] - 2022-08-21

Breaking API Changes

• The files argument of api.run is now a Collection[pathlib.Path] that represents an exact collection of files to be formatted, instead of a list of paths to search for files. Use api.get_matching_paths(paths, mode) to return the set of exact paths expected by api.run

Features

• sqlfmt will now display a progress bar for long runs (#231). You can disable this with the --no-progressbar option
• api.run now accepts an optional callback argument, which must be a Callable[[Awaitable[SqlFormatResult]], None]. Unless the --single-process option is used, the callback is executed after each file is formatted.
• sqlfmt can now be called as a python module, with python -m sqlfmt

Formatting Changes + Bug Fixes

• adds more granularity to operator precedence and will merge lines more aggressively that start with high-precedence operators (#200)
• improves the formatting of between ... and ..., especially in situations where the source includes a line break (#207)
• improves the consistency of formatting long chains of operators that include parentheses (#214)
• fixes a bug that caused unnecessary copying of the cache when using multiprocessing. Large projects should see dramatically faster (near-instant) runs once the cache is warm
• fixes a bug that could cause lines with long jinja tags to be one character over the line length limit, and could result in unstable formatting (#237 - thank you @nfcampos!)
• fixes a bug that formatted array literals like they were indexing operations (#235 - thank you @nfcampos!)

[0.10.1] - 2022-08-05

Features

• sqlfmt now supports the psycopg placeholders %s and %(name)s (#198 - thank you @snorkysnark!)

Formatting Changes + Bug Fixes

• sqlfmt now standardizes whitespace inside word tokens (#201)
• using is now treated as a word operator. It gets a space before its brackets and merging with surrounding lines is now much improved (#218 - thank you @nfcampos!)
• within group and filter are now treated like over, and the formatting of those aggregate clauses is improved (#205)

[0.10.0] - 2022-08-02

Features

• sqlfmt now supports ClickHouse. When run with the --dialect clickhouse option, sqlfmt will not lowercase names that could be case-sensitive in ClickHouse, like function names, aliases, etc. (#193 - thank you @Shlomixg!)

Formatting Changes + Bug Fixes

• formatting for chained boolean operators with complex expressions is now significantly improved (#189 - thank you @Rainymood!)
• formatting for array indexing is now significantly improved (#209) and sqlfmt no longer inserts spaces between the offset() function and its brackets
• set operators (like union) are now formatted differently. They must be on their own line, and will not cause subsequent blocks to be indented (#188 - thank you @Rainymood!)
• select * except (...) syntax is now explicitly supported, and formatting is improved. Support added for BigQuery and DuckDB star options: except, exclude, replace
• sqlfmt no longer inserts spaces between nested or repeated brackets, like (()) or ()[]
• a bug causing unstable formatting with long/multiline jinja tags has been fixed (#175)

[0.9.0] - 2022-06-02

Features

• jinjafmt is now able to format jinja that contains functions and variables that are reserved python words (e.g., return, except, from) (#177, #155), and ~, the jinja string concatenation operator (#182)
• adds a new command-line option to reset the sqlfmt cache (#184)

Fixes

• fixes issue where jinjafmt would insert a trailing comma into multiline macro definitions, causing dbt compiling errors (#156)
• fixes issue causing unstable formatting of multiline jinja tags when black is unable to parse the tag (#176)
• fixes issue for developers where pre-commit hooks would not install

Primer

• sqlfmt_primer now runs against forked (formatted) repos to make changes easier to detect

[0.8.0] - 2022-05-04

Formatting Changes

• sqlfmt is now more conservative about preserving whitespace around jinja expressions when we remove newlines (#162, #165 - thank you @rcaddell and @rjay98!)
• jinja blocks are now dedented before line merging, instead of after. This results in small changes to formatted output in some cases where jinja blocks are used
• fixes an issue where jinja else and elif statements could cause unstable formatting. May impact whitespace for the tokens following {% else %} and {% elif %} statements

[0.7.0] - 2022-04-24

Breaking Changes

• api.run now accepts files as a List[pathlib.Path] instead of a List[str]

Features

• any command line option can now be set in a pyproject.toml file. See README for more information (#90)
• sqlfmt now accepts an --exclude option to specify a glob of files to exclude from formatting (#131)

[0.6.0] - 2022-03-21

Formatting Fixes

• adds support for snapshot blocks, so the contents of those blocks are now properly formatted (#137)
• fixes issue causing unstable formatting of multiline jinja tags when black is not installed (#138)
• fixes formatting of semicolons and semicolon-delimited queries (#132)

[0.5.1] - 2022-02-08

Fixes

• adds support for numbered field references (e.g., $1) and snowflake stages (@my_stage) as identifiers
• do not spl...