NewYour coding agent can read the release notes before it upgrades.Set up the MCP server →
PyPI · #4022 most downloaded on PyPI
sqlfmt formats your dbt SQL files so you don't have to.
Last release 1 months ago
10 Aug 2026
Ships fairly regularly
a new release about every 3 months
Nearly every release is documented
notes for 59 of 59 stable releases
1 version withdrawn
withdrawn after publishing
5 years old
62 releases · first in 2021
One column per quarter.
All notable changes to this project will be documented in this file.
All notable changes to this project will be documented in this file.
fmt: off region (#673 - thank you @jmskarda!).select '''' || 'quoted_text' || ''''; it also no longer silently splits longer quote runs such as '''''''' into two tokens (#553 - thank you @albertsgrc and @ifmateus!).METADATA$FILENAME as single tokens, rather than splitting on the $.sqlfmt --quiet has been updated to suppress all report output, except error messages (#752, thank you @tuckerrc!). This makes it more suitable for shell scripts and bash pipelines.sqlfmt --diff now includes the filename in the diff, instead of the placeholders, source_query and formatted_query; it is now compatible with --quiet.any left join as a join keyword; in 0.28.0 we only supported the other syntax, left any join (#719 - thank you @haild-metricvn; see also #713)._), like 1_000_000 (#716 - thank you @hai-ld!).0 and x (#696 - thank you @KMontag42 and @snrsw!).?& and ?| as single operator tokens (#690 - thank you @thiemonipro!).paste join, any left join, array join, and more (#706 - thank you @camerondavison!).left outer union all by name, intersect distinct strict corresponding, except corresponding by and others ().pyproject.toml file as an argument. To leverage this pass --config <path to file>. If passed, sqlfmt will not attempt to find a config file. The file must exist or an exception will be thrown. Note that other options pased at the command line will override the settings within this file.pragma, set, and call statements(#656 - thank you @aersam!)* and columns in DuckDB *columns expressions (#657 - thank you @aersam!): and ['name'] in a DataBricks escaped variant expression like foo:['bar.baz'] (#637 - thank you @aersam!)#>, #>>, #-, and ## as comments (#461 - thank you @pauljz and many others!)".1234567E+2BD (#645).filter(), isnull(), and rlike('foo', 'bar') (but it also permits filter (), isnull (), and rlike ('foo') to support dialects where those are operators, not function names) (#641, #478 - thank you @williamscs, @hongtron, and @chwiese!).32y and +3.2e6bd and will not introduce a space between the digits and their type suffix (#640 - thank you @ShaneMazur!)./*+ COALESCE(3) */ (#639 - thank you @wr-atlas!).create row access policy statements with grant sub-statements (it also generally more robustly handles unsupported DDL) (#633).foo.select or foo.case (#599 - thank you @matthieucan!).union [all] by name is now supported (#611 - thank you @aersam!).interval is now parsed as a word operator. Parenthesized expressions like interval (10) days will maintain the space after interval.left anti & right anti joins are now supported.(NOT) (I)LIKE, ~~, ~~*, !~~, !~~*, are now supported (these use two tildes where the posix version of these operators use a single tilde) (#576 - thank you @tuckerrc!).{% for %}...{% else %}...{% endfor %} loops are now supported. Previously, a BracketError was raised if a for loop included an else tag (#549 - thank you, @yassun7010!).map<...> type declaration syntax from Athena. (#500 - thank you for the issue and fix, @benjamin-awd!){{ {'a': {'b': 1}} }}) could cause parsing errors (#471 - thank you @rparvathaneni-sc and @benjamin-awd!). This fix introduces a dependency on jinja2 > v3.0.:=) from being lexed as a single token (#502 - thank you @federico-hero!).any() and all() will no longer get spaces between the function name and the parenthesis, unless they are a part of a like any () or like all () operator (#483 - thank you @damirbk!).// comment markers are now parsed as comments and rewritten to -- on formatting (#468 - thank you @nilsonavp!).semi, anti, positional, and asof joins are now supported. (#482).--exclude would not follow symlinks when globbing
(#457 - thank you @jeancochrane!).--fmt: off comments could cause an error in formatting a file
(#447 - thank you @ramonvermeulen!).--fmt: off blocks.--fmt: off would still be formatted.
(#136).exclude paths defined in pyproject.toml files are now evaluated relative to the location of the file, not the current working directory.
Relative paths provided to the --exclude option (or env var) are evaluated relative to the current working directory. Files and exclude paths
are now compared as resolved, absolute paths. (Fixes #431 - thank you @cmcnicoll!){#-- comment --#} would cause a false positive for the
comment safety check. (#434)<=> operator (#432 - thank you @kathykwon!)/* comment */) on a single line would cause sqlfmt
to not include all comments in formatted output (#419 - thank you @aersam!)union distinct tokens (#417 - thank you, @paschmaria!)the contents of jinja blocks are now indented if the block wraps onto multiple rows (#403). This is now the proper sqlfmt style:
select
some_field,
{% for some_item in some_sequence %}
some_function({{ some_item }}){% if not loop.last %}, {% endif %}
{% endfor %}
While in this simple example the new style makes it less clear
that some_field and some_function are at the
same SQL depth, the formatting of complex files with nested jinja blocks is much improved.
For example:
{%- for col in cols -%}
{%- if col.column.lower() not in remove | map(
"lower"
) and col.column.lower() not in exclude | map("lower") -%}
{% do include_cols.append(col) %}
{%- endif %}
{%- endfor %}
See also this discussion. Thank you @dave-connors-3 and @alrocar!
sqlfmt now supports all Postgres frame clauses, not just those that start with rows between. (#404)
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!){% endif %}) to a line could cause bad formatting of everything on that linecreate <object> ... clone statements (#313).*.sql and *.sql.jinja, even those with other dots in their filenames (#354 - thank you @ysmilda!).{% call %} blocks with arguments like {% call(foo) bar(baz) %} would cause a parsing error (#353 - thank you @IgnorantWalking!).--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.RecursionError (#343 - thank you @kcem-flyr!).{% set %} and {% call %} blocks would cause a parsing error (#338 - thank you @AndrewLane!).is [not] distinct from as a word operator (#327 - thank you @IgnorantWalking, @kadekillary!).{% call %} blocks that called a macro that wasn't statement caused a parsing error (#335 - thank you @AndrewLane!).{% materialization ... %} and {% call statement(...) %} blocks (#309).{% endmacro %}, {% endtest %}, {% endcall %}, or {% endmaterialization %} tag.create warehouse and alter warehouse statements (#312, #299).alter function and drop function statements (#310, #311), and Snowflake's create external function statements (#322).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!).return(return_())).delete statements and the associated keywords using and returning (#281).grant and revoke statements and all associated keywords (#283).create function statements and all associated keywords (#282).explain keyword (#280).table<a int64, b bytes(5), c string>.$foo as ordinary identifiers.tomli dependency.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.array<float64>[1, 2] are now supported, and spaces will no longer be inserted around < and > (#212).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).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).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.--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.python -m sqlfmt.between ... and ..., especially in situations where the source includes a line break (#207).%s and %(name)s (#198 - thank you @snorkysnark!).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).--dialect clickhouse option, sqlfmt will not lowercase names that could be case-sensitive in ClickHouse, like function names, aliases, etc. (#193 - thank you @Shlomixg!).offset() function and its brackets.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.(()) or ()[].~, the jinja string concatenation operator (#182){% else %} and {% elif %} statementsfiles as a List[pathlib.Path] instead of a List[str]pyproject.toml file. See README for more information (#90)--exclude option to specify a glob of files to exclude from formatting (#131)$1) and snowflake stages (@my_stage) as identifiersbetween operator's and keyword (#124 - thank you @WestComputing!)pipx install sqlfmt[jinjafmt]). If black is installed, but you do not want to use this feature, you can disable it with the command-line option --no-jinjafmt@>, ||/, ?-| (#105)--single-process, to force single-processing, even when formatting many filesmake profiling- as the files argument--quiet option-- fmt: off and -- fmt: on comments in sql fileswindow and qualify# comment)All notable changes to this project will be documented in this file.
All notable changes to this project will be documented in this file.
METADATA$FILENAME as single tokens, rather than splitting on the $.sqlfmt --quiet has been updated to suppress all report output, except error messages (#752, thank you @tuckerrc!). This makes it more suitable for shell scripts and bash pipelines.sqlfmt --diff now includes the filename in the diff, instead of the placeholders, source_query and formatted_query; it is now compatible with --quiet.any left join as a join keyword; in 0.28.0 we only supported the other syntax, left any join (#719 - thank you @haild-metricvn; see also #713)._), like 1_000_000 (#716 - thank you @hai-ld!).0 and x (#696 - thank you @KMontag42 and @snrsw!).?& and ?| as single operator tokens (#690 - thank you @thiemonipro!).paste join, any left join, array join, and more (#706 - thank you @camerondavison!).left outer union all by name, intersect distinct strict corresponding, except corresponding by and others ().pyproject.toml file as an argument. To leverage this pass --config <path to file>. If passed, sqlfmt will not attempt to find a config file. The file must exist or an exception will be thrown. Note that other options pased at the command line will override the settings within this file.pragma, set, and call statements(#656 - thank you @aersam!)* and columns in DuckDB *columns expressions (#657 - thank you @aersam!): and ['name'] in a DataBricks escaped variant expression like foo:['bar.baz'] (#637 - thank you @aersam!)#>, #>>, #-, and ## as comments (#461 - thank you @pauljz and many others!)".1234567E+2BD (#645).filter(), isnull(), and rlike('foo', 'bar') (but it also permits filter (), isnull (), and rlike ('foo') to support dialects where those are operators, not function names) (#641, #478 - thank you @williamscs, @hongtron, and @chwiese!).32y and +3.2e6bd and will not introduce a space between the digits and their type suffix (#640 - thank you @ShaneMazur!)./*+ COALESCE(3) */ (#639 - thank you @wr-atlas!).create row access policy statements with grant sub-statements (it also generally more robustly handles unsupported DDL) (#633).foo.select or foo.case (#599 - thank you @matthieucan!).union [all] by name is now supported (#611 - thank you @aersam!).interval is now parsed as a word operator. Parenthesized expressions like interval (10) days will maintain the space after interval.left anti & right anti joins are now supported.(NOT) (I)LIKE, ~~, ~~*, !~~, !~~*, are now supported (these use two tildes where the posix version of these operators use a single tilde) (#576 - thank you @tuckerrc!).{% for %}...{% else %}...{% endfor %} loops are now supported. Previously, a BracketError was raised if a for loop included an else tag (#549 - thank you, @yassun7010!).map<...> type declaration syntax from Athena. (#500 - thank you for the issue and fix, @benjamin-awd!){{ {'a': {'b': 1}} }}) could cause parsing errors (#471 - thank you @rparvathaneni-sc and @benjamin-awd!). This fix introduces a dependency on jinja2 > v3.0.:=) from being lexed as a single token (#502 - thank you @federico-hero!).any() and all() will no longer get spaces between the function name and the parenthesis, unless they are a part of a like any () or like all () operator (#483 - thank you @damirbk!).// comment markers are now parsed as comments and rewritten to -- on formatting (#468 - thank you @nilsonavp!).semi, anti, positional, and asof joins are now supported. (#482).--exclude would not follow symlinks when globbing
(#457 - thank you @jeancochrane!).--fmt: off comments could cause an error in formatting a file
(#447 - thank you @ramonvermeulen!).--fmt: off blocks.--fmt: off would still be formatted.
(#136).exclude paths defined in pyproject.toml files are now evaluated relative to the location of the file, not the current working directory.
Relative paths provided to the --exclude option (or env var) are evaluated relative to the current working directory. Files and exclude paths
are now compared as resolved, absolute paths. (Fixes #431 - thank you @cmcnicoll!){#-- comment --#} would cause a false positive for the
comment safety check. (#434)<=> operator (#432 - thank you @kathykwon!)/* comment */) on a single line would cause sqlfmt
to not include all comments in formatted output (#419 - thank you @aersam!)union distinct tokens (#417 - thank you, @paschmaria!)the contents of jinja blocks are now indented if the block wraps onto multiple rows (#403). This is now the proper sqlfmt style:
select
some_field,
{% for some_item in some_sequence %}
some_function({{ some_item }}){% if not loop.last %}, {% endif %}
{% endfor %}
While in this simple example the new style makes it less clear
that some_field and some_function are at the
same SQL depth, the formatting of complex files with nested jinja blocks is much improved.
For example:
{%- for col in cols -%}
{%- if col.column.lower() not in remove | map(
"lower"
) and col.column.lower() not in exclude | map("lower") -%}
{% do include_cols.append(col) %}
{%- endif %}
{%- endfor %}
See also this discussion. Thank you @dave-connors-3 and @alrocar!
sqlfmt now supports all Postgres frame clauses, not just those that start with rows between. (#404)
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!){% endif %}) to a line could cause bad formatting of everything on that linecreate <object> ... clone statements (#313).*.sql and *.sql.jinja, even those with other dots in their filenames (#354 - thank you @ysmilda!).{% call %} blocks with arguments like {% call(foo) bar(baz) %} would cause a parsing error (#353 - thank you @IgnorantWalking!).--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.RecursionError (#343 - thank you @kcem-flyr!).{% set %} and {% call %} blocks would cause a parsing error (#338 - thank you @AndrewLane!).is [not] distinct from as a word operator (#327 - thank you @IgnorantWalking, @kadekillary!).{% call %} blocks that called a macro that wasn't statement caused a parsing error (#335 - thank you @AndrewLane!).{% materialization ... %} and {% call statement(...) %} blocks (#309).{% endmacro %}, {% endtest %}, {% endcall %}, or {% endmaterialization %} tag.create warehouse and alter warehouse statements (#312, #299).alter function and drop function statements (#310, #311), and Snowflake's create external function statements (#322).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!).return(return_())).delete statements and the associated keywords using and returning (#281).grant and revoke statements and all associated keywords (#283).create function statements and all associated keywords (#282).explain keyword (#280).table<a int64, b bytes(5), c string>.$foo as ordinary identifiers.tomli dependency.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.array<float64>[1, 2] are now supported, and spaces will no longer be inserted around < and > (#212).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).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).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.--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.python -m sqlfmt.between ... and ..., especially in situations where the source includes a line break (#207).%s and %(name)s (#198 - thank you @snorkysnark!).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).--dialect clickhouse option, sqlfmt will not lowercase names that could be case-sensitive in ClickHouse, like function names, aliases, etc. (#193 - thank you @Shlomixg!).offset() function and its brackets.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.(()) or ()[].~, the jinja string concatenation operator (#182){% else %} and {% elif %} statementsfiles as a List[pathlib.Path] instead of a List[str]pyproject.toml file. See README for more information (#90)--exclude option to specify a glob of files to exclude from formatting (#131)$1) and snowflake stages (@my_stage) as identifiersbetween operator's and keyword (#124 - thank you @WestComputing!)pipx install sqlfmt[jinjafmt]). If black is installed, but you do not want to use this feature, you can disable it with the command-line option --no-jinjafmt@>, ||/, ?-| (#105)--single-process, to force single-processing, even when formatting many filesmake profiling- as the files argument--quiet option-- fmt: off and -- fmt: on comments in sql fileswindow and qualify# comment)All notable changes to this project will be documented in this file.
All notable changes to this project will be documented in this file.
METADATA$FILENAME as single tokens, rather than splitting on the $.sqlfmt --quiet has been updated to suppress all report output, except error messages (#752, thank you @tuckerrc!). This makes it more suitable for shell scripts and bash pipelines.sqlfmt --diff now includes the filename in the diff, instead of the placeholders, source_query and formatted_query; it is now compatible with --quiet.any left join as a join keyword; in 0.28.0 we only supported the other syntax, left any join (#719 - thank you @haild-metricvn; see also #713)._), like 1_000_000 (#716 - thank you @hai-ld!).0 and x (#696 - thank you @KMontag42 and @snrsw!).?& and ?| as single operator tokens (#690 - thank you @thiemonipro!).paste join, any left join, array join, and more (#706 - thank you @camerondavison!).left outer union all by name, intersect distinct strict corresponding, except corresponding by and others ().pyproject.toml file as an argument. To leverage this pass --config <path to file>. If passed, sqlfmt will not attempt to find a config file. The file must exist or an exception will be thrown. Note that other options pased at the command line will override the settings within this file.pragma, set, and call statements(#656 - thank you @aersam!)* and columns in DuckDB *columns expressions (#657 - thank you @aersam!): and ['name'] in a DataBricks escaped variant expression like foo:['bar.baz'] (#637 - thank you @aersam!)#>, #>>, #-, and ## as comments (#461 - thank you @pauljz and many others!)".1234567E+2BD (#645).filter(), isnull(), and rlike('foo', 'bar') (but it also permits filter (), isnull (), and rlike ('foo') to support dialects where those are operators, not function names) (#641, #478 - thank you @williamscs, @hongtron, and @chwiese!).32y and +3.2e6bd and will not introduce a space between the digits and their type suffix (#640 - thank you @ShaneMazur!)./*+ COALESCE(3) */ (#639 - thank you @wr-atlas!).create row access policy statements with grant sub-statements (it also generally more robustly handles unsupported DDL) (#633).foo.select or foo.case (#599 - thank you @matthieucan!).union [all] by name is now supported (#611 - thank you @aersam!).interval is now parsed as a word operator. Parenthesized expressions like interval (10) days will maintain the space after interval.left anti & right anti joins are now supported.(NOT) (I)LIKE, ~~, ~~*, !~~, !~~*, are now supported (these use two tildes where the posix version of these operators use a single tilde) (#576 - thank you @tuckerrc!).{% for %}...{% else %}...{% endfor %} loops are now supported. Previously, a BracketError was raised if a for loop included an else tag (#549 - thank you, @yassun7010!).map<...> type declaration syntax from Athena. (#500 - thank you for the issue and fix, @benjamin-awd!){{ {'a': {'b': 1}} }}) could cause parsing errors (#471 - thank you @rparvathaneni-sc and @benjamin-awd!). This fix introduces a dependency on jinja2 > v3.0.:=) from being lexed as a single token (#502 - thank you @federico-hero!).any() and all() will no longer get spaces between the function name and the parenthesis, unless they are a part of a like any () or like all () operator (#483 - thank you @damirbk!).// comment markers are now parsed as comments and rewritten to -- on formatting (#468 - thank you @nilsonavp!).semi, anti, positional, and asof joins are now supported. (#482).--exclude would not follow symlinks when globbing
(#457 - thank you @jeancochrane!).--fmt: off comments could cause an error in formatting a file
(#447 - thank you @ramonvermeulen!).--fmt: off blocks.--fmt: off would still be formatted.
(#136).exclude paths defined in pyproject.toml files are now evaluated relative to the location of the file, not the current working directory.
Relative paths provided to the --exclude option (or env var) are evaluated relative to the current working directory. Files and exclude paths
are now compared as resolved, absolute paths. (Fixes #431 - thank you @cmcnicoll!){#-- comment --#} would cause a false positive for the
comment safety check. (#434)<=> operator (#432 - thank you @kathykwon!)/* comment */) on a single line would cause sqlfmt
to not include all comments in formatted output (#419 - thank you @aersam!)union distinct tokens (#417 - thank you, @paschmaria!)the contents of jinja blocks are now indented if the block wraps onto multiple rows (#403). This is now the proper sqlfmt style:
select
some_field,
{% for some_item in some_sequence %}
some_function({{ some_item }}){% if not loop.last %}, {% endif %}
{% endfor %}
While in this simple example the new style makes it less clear
that some_field and some_function are at the
same SQL depth, the formatting of complex files with nested jinja blocks is much improved.
For example:
{%- for col in cols -%}
{%- if col.column.lower() not in remove | map(
"lower"
) and col.column.lower() not in exclude | map("lower") -%}
{% do include_cols.append(col) %}
{%- endif %}
{%- endfor %}
See also this discussion. Thank you @dave-connors-3 and @alrocar!
sqlfmt now supports all Postgres frame clauses, not just those that start with rows between. (#404)
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!){% endif %}) to a line could cause bad formatting of everything on that linecreate <object> ... clone statements (#313).*.sql and *.sql.jinja, even those with other dots in their filenames (#354 - thank you @ysmilda!).{% call %} blocks with arguments like {% call(foo) bar(baz) %} would cause a parsing error (#353 - thank you @IgnorantWalking!).--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.RecursionError (#343 - thank you @kcem-flyr!).{% set %} and {% call %} blocks would cause a parsing error (#338 - thank you @AndrewLane!).is [not] distinct from as a word operator (#327 - thank you @IgnorantWalking, @kadekillary!).{% call %} blocks that called a macro that wasn't statement caused a parsing error (#335 - thank you @AndrewLane!).{% materialization ... %} and {% call statement(...) %} blocks (#309).{% endmacro %}, {% endtest %}, {% endcall %}, or {% endmaterialization %} tag.create warehouse and alter warehouse statements (#312, #299).alter function and drop function statements (#310, #311), and Snowflake's create external function statements (#322).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!).return(return_())).delete statements and the associated keywords using and returning (#281).grant and revoke statements and all associated keywords (#283).create function statements and all associated keywords (#282).explain keyword (#280).table<a int64, b bytes(5), c string>.$foo as ordinary identifiers.tomli dependency.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.array<float64>[1, 2] are now supported, and spaces will no longer be inserted around < and > (#212).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).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).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.--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.python -m sqlfmt.between ... and ..., especially in situations where the source includes a line break (#207).%s and %(name)s (#198 - thank you @snorkysnark!).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).--dialect clickhouse option, sqlfmt will not lowercase names that could be case-sensitive in ClickHouse, like function names, aliases, etc. (#193 - thank you @Shlomixg!).offset() function and its brackets.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.(()) or ()[].~, the jinja string concatenation operator (#182){% else %} and {% elif %} statementsfiles as a List[pathlib.Path] instead of a List[str]pyproject.toml file. See README for more information (#90)--exclude option to specify a glob of files to exclude from formatting (#131)$1) and snowflake stages (@my_stage) as identifiersbetween operator's and keyword (#124 - thank you @WestComputing!)pipx install sqlfmt[jinjafmt]). If black is installed, but you do not want to use this feature, you can disable it with the command-line option --no-jinjafmt@>, ||/, ?-| (#105)--single-process, to force single-processing, even when formatting many filesmake profiling- as the files argument--quiet option-- fmt: off and -- fmt: on comments in sql fileswindow and qualify# comment)All notable changes to this project will be documented in this file.
All notable changes to this project will be documented in this file.
any left join as a join keyword; in 0.28.0 we only supported the other syntax, left any join (#719 - thank you @haild-metricvn; see also #713)._), like 1_000_000 (#716 - thank you @hai-ld!).0 and x (#696 - thank you @KMontag42 and @snrsw!).?& and ?| as single operator tokens (#690 - thank you @thiemonipro!).paste join, any left join, array join, and more (#706 - thank you @camerondavison!).left outer union all by name, intersect distinct strict corresponding, except corresponding by and others ().pyproject.toml file as an argument. To leverage this pass --config <path to file>. If passed, sqlfmt will not attempt to find a config file. The file must exist or an exception will be thrown. Note that other options pased at the command line will override the settings within this file.pragma, set, and call statements(#656 - thank you @aersam!)* and columns in DuckDB *columns expressions (#657 - thank you @aersam!): and ['name'] in a DataBricks escaped variant expression like foo:['bar.baz'] (#637 - thank you @aersam!)#>, #>>, #-, and ## as comments (#461 - thank you @pauljz and many others!)".1234567E+2BD (#645).filter(), isnull(), and rlike('foo', 'bar') (but it also permits filter (), isnull (), and rlike ('foo') to support dialects where those are operators, not function names) (#641, #478 - thank you @williamscs, @hongtron, and @chwiese!).32y and +3.2e6bd and will not introduce a space between the digits and their type suffix (#640 - thank you @ShaneMazur!)./*+ COALESCE(3) */ (#639 - thank you @wr-atlas!).create row access policy statements with grant sub-statements (it also generally more robustly handles unsupported DDL) (#633).foo.select or foo.case (#599 - thank you @matthieucan!).union [all] by name is now supported (#611 - thank you @aersam!).interval is now parsed as a word operator. Parenthesized expressions like interval (10) days will maintain the space after interval.left anti & right anti joins are now supported.(NOT) (I)LIKE, ~~, ~~*, !~~, !~~*, are now supported (these use two tildes where the posix version of these operators use a single tilde) (#576 - thank you @tuckerrc!).{% for %}...{% else %}...{% endfor %} loops are now supported. Previously, a BracketError was raised if a for loop included an else tag (#549 - thank you, @yassun7010!).map<...> type declaration syntax from Athena. (#500 - thank you for the issue and fix, @benjamin-awd!){{ {'a': {'b': 1}} }}) could cause parsing errors (#471 - thank you @rparvathaneni-sc and @benjamin-awd!). This fix introduces a dependency on jinja2 > v3.0.:=) from being lexed as a single token (#502 - thank you @federico-hero!).any() and all() will no longer get spaces between the function name and the parenthesis, unless they are a part of a like any () or like all () operator (#483 - thank you @damirbk!).// comment markers are now parsed as comments and rewritten to -- on formatting (#468 - thank you @nilsonavp!).semi, anti, positional, and asof joins are now supported. (#482).--exclude would not follow symlinks when globbing
(#457 - thank you @jeancochrane!).--fmt: off comments could cause an error in formatting a file
(#447 - thank you @ramonvermeulen!).--fmt: off blocks.--fmt: off would still be formatted.
(#136).exclude paths defined in pyproject.toml files are now evaluated relative to the location of the file, not the current working directory.
Relative paths provided to the --exclude option (or env var) are evaluated relative to the current working directory. Files and exclude paths
are now compared as resolved, absolute paths. (Fixes #431 - thank you @cmcnicoll!){#-- comment --#} would cause a false positive for the
comment safety check. (#434)<=> operator (#432 - thank you @kathykwon!)/* comment */) on a single line would cause sqlfmt
to not include all comments in formatted output (#419 - thank you @aersam!)union distinct tokens (#417 - thank you, @paschmaria!)the contents of jinja blocks are now indented if the block wraps onto multiple rows (#403). This is now the proper sqlfmt style:
select
some_field,
{% for some_item in some_sequence %}
some_function({{ some_item }}){% if not loop.last %}, {% endif %}
{% endfor %}
While in this simple example the new style makes it less clear
that some_field and some_function are at the
same SQL depth, the formatting of complex files with nested jinja blocks is much improved.
For example:
{%- for col in cols -%}
{%- if col.column.lower() not in remove | map(
"lower"
) and col.column.lower() not in exclude | map("lower") -%}
{% do include_cols.append(col) %}
{%- endif %}
{%- endfor %}
See also this discussion. Thank you @dave-connors-3 and @alrocar!
sqlfmt now supports all Postgres frame clauses, not just those that start with rows between. (#404)
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!){% endif %}) to a line could cause bad formatting of everything on that linecreate <object> ... clone statements (#313).*.sql and *.sql.jinja, even those with other dots in their filenames (#354 - thank you @ysmilda!).{% call %} blocks with arguments like {% call(foo) bar(baz) %} would cause a parsing error (#353 - thank you @IgnorantWalking!).--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.RecursionError (#343 - thank you @kcem-flyr!).{% set %} and {% call %} blocks would cause a parsing error (#338 - thank you @AndrewLane!).is [not] distinct from as a word operator (#327 - thank you @IgnorantWalking, @kadekillary!).{% call %} blocks that called a macro that wasn't statement caused a parsing error (#335 - thank you @AndrewLane!).{% materialization ... %} and {% call statement(...) %} blocks (#309).{% endmacro %}, {% endtest %}, {% endcall %}, or {% endmaterialization %} tag.create warehouse and alter warehouse statements (#312, #299).alter function and drop function statements (#310, #311), and Snowflake's create external function statements (#322).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!).return(return_())).delete statements and the associated keywords using and returning (#281).grant and revoke statements and all associated keywords (#283).create function statements and all associated keywords (#282).explain keyword (#280).table<a int64, b bytes(5), c string>.$foo as ordinary identifiers.tomli dependency.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.array<float64>[1, 2] are now supported, and spaces will no longer be inserted around < and > (#212).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).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).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.--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.python -m sqlfmt.between ... and ..., especially in situations where the source includes a line break (#207).%s and %(name)s (#198 - thank you @snorkysnark!).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).--dialect clickhouse option, sqlfmt will not lowercase names that could be case-sensitive in ClickHouse, like function names, aliases, etc. (#193 - thank you @Shlomixg!).offset() function and its brackets.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.(()) or ()[].~, the jinja string concatenation operator (#182){% else %} and {% elif %} statementsfiles as a List[pathlib.Path] instead of a List[str]pyproject.toml file. See README for more information (#90)--exclude option to specify a glob of files to exclude from formatting (#131)$1) and snowflake stages (@my_stage) as identifiersbetween operator's and keyword (#124 - thank you @WestComputing!)pipx install sqlfmt[jinjafmt]). If black is installed, but you do not want to use this feature, you can disable it with the command-line option --no-jinjafmt@>, ||/, ?-| (#105)--single-process, to force single-processing, even when formatting many filesmake profiling- as the files argument--quiet option-- fmt: off and -- fmt: on comments in sql fileswindow and qualify# comment)All notable changes to this project will be documented in this file.
All notable changes to this project will be documented in this file.
any left join as a join keyword; in 0.28.0 we only supported the other syntax, left any join (#719 - thank you @haild-metricvn; see also #713)._), like 1_000_000 (#716 - thank you @hai-ld!).0 and x (#696 - thank you @KMontag42 and @snrsw!).?& and ?| as single operator tokens (#690 - thank you @thiemonipro!).paste join, any left join, array join, and more (#706 - thank you @camerondavison!).left outer union all by name, intersect distinct strict corresponding, except corresponding by and others ().pyproject.toml file as an argument. To leverage this pass --config <path to file>. If passed, sqlfmt will not attempt to find a config file. The file must exist or an exception will be thrown. Note that other options pased at the command line will override the settings within this file.pragma, set, and call statements(#656 - thank you @aersam!)* and columns in DuckDB *columns expressions (#657 - thank you @aersam!): and ['name'] in a DataBricks escaped variant expression like foo:['bar.baz'] (#637 - thank you @aersam!)#>, #>>, #-, and ## as comments (#461 - thank you @pauljz and many others!)".1234567E+2BD (#645).filter(), isnull(), and rlike('foo', 'bar') (but it also permits filter (), isnull (), and rlike ('foo') to support dialects where those are operators, not function names) (#641, #478 - thank you @williamscs, @hongtron, and @chwiese!).32y and +3.2e6bd and will not introduce a space between the digits and their type suffix (#640 - thank you @ShaneMazur!)./*+ COALESCE(3) */ (#639 - thank you @wr-atlas!).create row access policy statements with grant sub-statements (it also generally more robustly handles unsupported DDL) (#633).foo.select or foo.case (#599 - thank you @matthieucan!).union [all] by name is now supported (#611 - thank you @aersam!).interval is now parsed as a word operator. Parenthesized expressions like interval (10) days will maintain the space after interval.left anti & right anti joins are now supported.(NOT) (I)LIKE, ~~, ~~*, !~~, !~~*, are now supported (these use two tildes where the posix version of these operators use a single tilde) (#576 - thank you @tuckerrc!).{% for %}...{% else %}...{% endfor %} loops are now supported. Previously, a BracketError was raised if a for loop included an else tag (#549 - thank you, @yassun7010!).map<...> type declaration syntax from Athena. (#500 - thank you for the issue and fix, @benjamin-awd!){{ {'a': {'b': 1}} }}) could cause parsing errors (#471 - thank you @rparvathaneni-sc and @benjamin-awd!). This fix introduces a dependency on jinja2 > v3.0.:=) from being lexed as a single token (#502 - thank you @federico-hero!).any() and all() will no longer get spaces between the function name and the parenthesis, unless they are a part of a like any () or like all () operator (#483 - thank you @damirbk!).// comment markers are now parsed as comments and rewritten to -- on formatting (#468 - thank you @nilsonavp!).semi, anti, positional, and asof joins are now supported. (#482).--exclude would not follow symlinks when globbing
(#457 - thank you @jeancochrane!).--fmt: off comments could cause an error in formatting a file
(#447 - thank you @ramonvermeulen!).--fmt: off blocks.--fmt: off would still be formatted.
(#136).exclude paths defined in pyproject.toml files are now evaluated relative to the location of the file, not the current working directory.
Relative paths provided to the --exclude option (or env var) are evaluated relative to the current working directory. Files and exclude paths
are now compared as resolved, absolute paths. (Fixes #431 - thank you @cmcnicoll!){#-- comment --#} would cause a false positive for the
comment safety check. (#434)<=> operator (#432 - thank you @kathykwon!)/* comment */) on a single line would cause sqlfmt
to not include all comments in formatted output (#419 - thank you @aersam!)union distinct tokens (#417 - thank you, @paschmaria!)the contents of jinja blocks are now indented if the block wraps onto multiple rows (#403). This is now the proper sqlfmt style:
select
some_field,
{% for some_item in some_sequence %}
some_function({{ some_item }}){% if not loop.last %}, {% endif %}
{% endfor %}
While in this simple example the new style makes it less clear
that some_field and some_function are at the
same SQL depth, the formatting of complex files with nested jinja blocks is much improved.
For example:
{%- for col in cols -%}
{%- if col.column.lower() not in remove | map(
"lower"
) and col.column.lower() not in exclude | map("lower") -%}
{% do include_cols.append(col) %}
{%- endif %}
{%- endfor %}
See also this discussion. Thank you @dave-connors-3 and @alrocar!
sqlfmt now supports all Postgres frame clauses, not just those that start with rows between. (#404)
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!){% endif %}) to a line could cause bad formatting of everything on that linecreate <object> ... clone statements (#313).*.sql and *.sql.jinja, even those with other dots in their filenames (#354 - thank you @ysmilda!).{% call %} blocks with arguments like {% call(foo) bar(baz) %} would cause a parsing error (#353 - thank you @IgnorantWalking!).--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.RecursionError (#343 - thank you @kcem-flyr!).{% set %} and {% call %} blocks would cause a parsing error (#338 - thank you @AndrewLane!).is [not] distinct from as a word operator (#327 - thank you @IgnorantWalking, @kadekillary!).{% call %} blocks that called a macro that wasn't statement caused a parsing error (#335 - thank you @AndrewLane!).{% materialization ... %} and {% call statement(...) %} blocks (#309).{% endmacro %}, {% endtest %}, {% endcall %}, or {% endmaterialization %} tag.create warehouse and alter warehouse statements (#312, #299).alter function and drop function statements (#310, #311), and Snowflake's create external function statements (#322).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!).return(return_())).delete statements and the associated keywords using and returning (#281).grant and revoke statements and all associated keywords (#283).create function statements and all associated keywords (#282).explain keyword (#280).table<a int64, b bytes(5), c string>.$foo as ordinary identifiers.tomli dependency.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.array<float64>[1, 2] are now supported, and spaces will no longer be inserted around < and > (#212).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).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).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.--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.python -m sqlfmt.between ... and ..., especially in situations where the source includes a line break (#207).%s and %(name)s (#198 - thank you @snorkysnark!).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).--dialect clickhouse option, sqlfmt will not lowercase names that could be case-sensitive in ClickHouse, like function names, aliases, etc. (#193 - thank you @Shlomixg!).offset() function and its brackets.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.(()) or ()[].~, the jinja string concatenation operator (#182){% else %} and {% elif %} statementsfiles as a List[pathlib.Path] instead of a List[str]pyproject.toml file. See README for more information (#90)--exclude option to specify a glob of files to exclude from formatting (#131)$1) and snowflake stages (@my_stage) as identifiersbetween operator's and keyword (#124 - thank you @WestComputing!)pipx install sqlfmt[jinjafmt]). If black is installed, but you do not want to use this feature, you can disable it with the command-line option --no-jinjafmt@>, ||/, ?-| (#105)--single-process, to force single-processing, even when formatting many filesmake profiling- as the files argument--quiet option-- fmt: off and -- fmt: on comments in sql fileswindow and qualify# comment)All notable changes to this project will be documented in this file.
All notable changes to this project will be documented in this file.
_), like 1_000_000 (#716 - thank you @hai-ld!).0 and x (#696 - thank you @KMontag42 and @snrsw!).?& and ?| as single operator tokens (#690 - thank you @thiemonipro!).paste join, any left join, array join, and more (#706 - thank you @camerondavison!).left outer union all by name, intersect distinct strict corresponding, except corresponding by and others ().pyproject.toml file as an argument. To leverage this pass --config <path to file>. If passed, sqlfmt will not attempt to find a config file. The file must exist or an exception will be thrown. Note that other options pased at the command line will override the settings within this file.pragma, set, and call statements(#656 - thank you @aersam!)* and columns in DuckDB *columns expressions (#657 - thank you @aersam!): and ['name'] in a DataBricks escaped variant expression like foo:['bar.baz'] (#637 - thank you @aersam!)#>, #>>, #-, and ## as comments (#461 - thank you @pauljz and many others!)".1234567E+2BD (#645).filter(), isnull(), and rlike('foo', 'bar') (but it also permits filter (), isnull (), and rlike ('foo') to support dialects where those are operators, not function names) (#641, #478 - thank you @williamscs, @hongtron, and @chwiese!).32y and +3.2e6bd and will not introduce a space between the digits and their type suffix (#640 - thank you @ShaneMazur!)./*+ COALESCE(3) */ (#639 - thank you @wr-atlas!).create row access policy statements with grant sub-statements (it also generally more robustly handles unsupported DDL) (#633).foo.select or foo.case (#599 - thank you @matthieucan!).union [all] by name is now supported (#611 - thank you @aersam!).interval is now parsed as a word operator. Parenthesized expressions like interval (10) days will maintain the space after interval.left anti & right anti joins are now supported.(NOT) (I)LIKE, ~~, ~~*, !~~, !~~*, are now supported (these use two tildes where the posix version of these operators use a single tilde) (#576 - thank you @tuckerrc!).{% for %}...{% else %}...{% endfor %} loops are now supported. Previously, a BracketError was raised if a for loop included an else tag (#549 - thank you, @yassun7010!).map<...> type declaration syntax from Athena. (#500 - thank you for the issue and fix, @benjamin-awd!){{ {'a': {'b': 1}} }}) could cause parsing errors (#471 - thank you @rparvathaneni-sc and @benjamin-awd!). This fix introduces a dependency on jinja2 > v3.0.:=) from being lexed as a single token (#502 - thank you @federico-hero!).any() and all() will no longer get spaces between the function name and the parenthesis, unless they are a part of a like any () or like all () operator (#483 - thank you @damirbk!).// comment markers are now parsed as comments and rewritten to -- on formatting (#468 - thank you @nilsonavp!).semi, anti, positional, and asof joins are now supported. (#482).--exclude would not follow symlinks when globbing
(#457 - thank you @jeancochrane!).--fmt: off comments could cause an error in formatting a file
(#447 - thank you @ramonvermeulen!).--fmt: off blocks.--fmt: off would still be formatted.
(#136).exclude paths defined in pyproject.toml files are now evaluated relative to the location of the file, not the current working directory.
Relative paths provided to the --exclude option (or env var) are evaluated relative to the current working directory. Files and exclude paths
are now compared as resolved, absolute paths. (Fixes #431 - thank you @cmcnicoll!){#-- comment --#} would cause a false positive for the
comment safety check. (#434)<=> operator (#432 - thank you @kathykwon!)/* comment */) on a single line would cause sqlfmt
to not include all comments in formatted output (#419 - thank you @aersam!)union distinct tokens (#417 - thank you, @paschmaria!)the contents of jinja blocks are now indented if the block wraps onto multiple rows (#403). This is now the proper sqlfmt style:
select
some_field,
{% for some_item in some_sequence %}
some_function({{ some_item }}){% if not loop.last %}, {% endif %}
{% endfor %}
While in this simple example the new style makes it less clear
that some_field and some_function are at the
same SQL depth, the formatting of complex files with nested jinja blocks is much improved.
For example:
{%- for col in cols -%}
{%- if col.column.lower() not in remove | map(
"lower"
) and col.column.lower() not in exclude | map("lower") -%}
{% do include_cols.append(col) %}
{%- endif %}
{%- endfor %}
See also this discussion. Thank you @dave-connors-3 and @alrocar!
sqlfmt now supports all Postgres frame clauses, not just those that start with rows between. (#404)
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!){% endif %}) to a line could cause bad formatting of everything on that linecreate <object> ... clone statements (#313).*.sql and *.sql.jinja, even those with other dots in their filenames (#354 - thank you @ysmilda!).{% call %} blocks with arguments like {% call(foo) bar(baz) %} would cause a parsing error (#353 - thank you @IgnorantWalking!).--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.RecursionError (#343 - thank you @kcem-flyr!).{% set %} and {% call %} blocks would cause a parsing error (#338 - thank you @AndrewLane!).is [not] distinct from as a word operator (#327 - thank you @IgnorantWalking, @kadekillary!).{% call %} blocks that called a macro that wasn't statement caused a parsing error (#335 - thank you @AndrewLane!).{% materialization ... %} and {% call statement(...) %} blocks (#309).{% endmacro %}, {% endtest %}, {% endcall %}, or {% endmaterialization %} tag.create warehouse and alter warehouse statements (#312, #299).alter function and drop function statements (#310, #311), and Snowflake's create external function statements (#322).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!).return(return_())).delete statements and the associated keywords using and returning (#281).grant and revoke statements and all associated keywords (#283).create function statements and all associated keywords (#282).explain keyword (#280).table<a int64, b bytes(5), c string>.$foo as ordinary identifiers.tomli dependency.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.array<float64>[1, 2] are now supported, and spaces will no longer be inserted around < and > (#212).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).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).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.--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.python -m sqlfmt.between ... and ..., especially in situations where the source includes a line break (#207).%s and %(name)s (#198 - thank you @snorkysnark!).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).--dialect clickhouse option, sqlfmt will not lowercase names that could be case-sensitive in ClickHouse, like function names, aliases, etc. (#193 - thank you @Shlomixg!).offset() function and its brackets.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.(()) or ()[].~, the jinja string concatenation operator (#182){% else %} and {% elif %} statementsfiles as a List[pathlib.Path] instead of a List[str]pyproject.toml file. See README for more information (#90)--exclude option to specify a glob of files to exclude from formatting (#131)$1) and snowflake stages (@my_stage) as identifiersbetween operator's and keyword (#124 - thank you @WestComputing!)pipx install sqlfmt[jinjafmt]). If black is installed, but you do not want to use this feature, you can disable it with the command-line option --no-jinjafmt@>, ||/, ?-| (#105)--single-process, to force single-processing, even when formatting many filesmake profiling- as the files argument--quiet option-- fmt: off and -- fmt: on comments in sql fileswindow and qualify# comment)All notable changes to this project will be documented in this file.
All notable changes to this project will be documented in this file.
0 and x (#696 - thank you @KMontag42 and @snrsw!).?& and ?| as single operator tokens (#690 - thank you @thiemonipro!).paste join, any left join, array join, and more (#706 - thank you @camerondavison!).left outer union all by name, intersect distinct strict corresponding, except corresponding by and others ().pyproject.toml file as an argument. To leverage this pass --config <path to file>. If passed, sqlfmt will not attempt to find a config file. The file must exist or an exception will be thrown. Note that other options pased at the command line will override the settings within this file.pragma, set, and call statements(#656 - thank you @aersam!)* and columns in DuckDB *columns expressions (#657 - thank you @aersam!): and ['name'] in a DataBricks escaped variant expression like foo:['bar.baz'] (#637 - thank you @aersam!)#>, #>>, #-, and ## as comments (#461 - thank you @pauljz and many others!)".1234567E+2BD (#645).filter(), isnull(), and rlike('foo', 'bar') (but it also permits filter (), isnull (), and rlike ('foo') to support dialects where those are operators, not function names) (#641, #478 - thank you @williamscs, @hongtron, and @chwiese!).32y and +3.2e6bd and will not introduce a space between the digits and their type suffix (#640 - thank you @ShaneMazur!)./*+ COALESCE(3) */ (#639 - thank you @wr-atlas!).create row access policy statements with grant sub-statements (it also generally more robustly handles unsupported DDL) (#633).foo.select or foo.case (#599 - thank you @matthieucan!).union [all] by name is now supported (#611 - thank you @aersam!).interval is now parsed as a word operator. Parenthesized expressions like interval (10) days will maintain the space after interval.left anti & right anti joins are now supported.(NOT) (I)LIKE, ~~, ~~*, !~~, !~~*, are now supported (these use two tildes where the posix version of these operators use a single tilde) (#576 - thank you @tuckerrc!).{% for %}...{% else %}...{% endfor %} loops are now supported. Previously, a BracketError was raised if a for loop included an else tag (#549 - thank you, @yassun7010!).map<...> type declaration syntax from Athena. (#500 - thank you for the issue and fix, @benjamin-awd!){{ {'a': {'b': 1}} }}) could cause parsing errors (#471 - thank you @rparvathaneni-sc and @benjamin-awd!). This fix introduces a dependency on jinja2 > v3.0.:=) from being lexed as a single token (#502 - thank you @federico-hero!).any() and all() will no longer get spaces between the function name and the parenthesis, unless they are a part of a like any () or like all () operator (#483 - thank you @damirbk!).// comment markers are now parsed as comments and rewritten to -- on formatting (#468 - thank you @nilsonavp!).semi, anti, positional, and asof joins are now supported. (#482).--exclude would not follow symlinks when globbing
(#457 - thank you @jeancochrane!).--fmt: off comments could cause an error in formatting a file
(#447 - thank you @ramonvermeulen!).--fmt: off blocks.--fmt: off would still be formatted.
(#136).exclude paths defined in pyproject.toml files are now evaluated relative to the location of the file, not the current working directory.
Relative paths provided to the --exclude option (or env var) are evaluated relative to the current working directory. Files and exclude paths
are now compared as resolved, absolute paths. (Fixes #431 - thank you @cmcnicoll!){#-- comment --#} would cause a false positive for the
comment safety check. (#434)<=> operator (#432 - thank you @kathykwon!)/* comment */) on a single line would cause sqlfmt
to not include all comments in formatted output (#419 - thank you @aersam!)union distinct tokens (#417 - thank you, @paschmaria!)the contents of jinja blocks are now indented if the block wraps onto multiple rows (#403). This is now the proper sqlfmt style:
select
some_field,
{% for some_item in some_sequence %}
some_function({{ some_item }}){% if not loop.last %}, {% endif %}
{% endfor %}
While in this simple example the new style makes it less clear
that some_field and some_function are at the
same SQL depth, the formatting of complex files with nested jinja blocks is much improved.
For example:
{%- for col in cols -%}
{%- if col.column.lower() not in remove | map(
"lower"
) and col.column.lower() not in exclude | map("lower") -%}
{% do include_cols.append(col) %}
{%- endif %}
{%- endfor %}
See also this discussion. Thank you @dave-connors-3 and @alrocar!
sqlfmt now supports all Postgres frame clauses, not just those that start with rows between. (#404)
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!){% endif %}) to a line could cause bad formatting of everything on that linecreate <object> ... clone statements (#313).*.sql and *.sql.jinja, even those with other dots in their filenames (#354 - thank you @ysmilda!).{% call %} blocks with arguments like {% call(foo) bar(baz) %} would cause a parsing error (#353 - thank you @IgnorantWalking!).--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.RecursionError (#343 - thank you @kcem-flyr!).{% set %} and {% call %} blocks would cause a parsing error (#338 - thank you @AndrewLane!).is [not] distinct from as a word operator (#327 - thank you @IgnorantWalking, @kadekillary!).{% call %} blocks that called a macro that wasn't statement caused a parsing error (#335 - thank you @AndrewLane!).{% materialization ... %} and {% call statement(...) %} blocks (#309).{% endmacro %}, {% endtest %}, {% endcall %}, or {% endmaterialization %} tag.create warehouse and alter warehouse statements (#312, #299).alter function and drop function statements (#310, #311), and Snowflake's create external function statements (#322).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!).return(return_())).delete statements and the associated keywords using and returning (#281).grant and revoke statements and all associated keywords (#283).create function statements and all associated keywords (#282).explain keyword (#280).table<a int64, b bytes(5), c string>.$foo as ordinary identifiers.tomli dependency.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.array<float64>[1, 2] are now supported, and spaces will no longer be inserted around < and > (#212).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).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).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.--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.python -m sqlfmt.between ... and ..., especially in situations where the source includes a line break (#207).%s and %(name)s (#198 - thank you @snorkysnark!).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).--dialect clickhouse option, sqlfmt will not lowercase names that could be case-sensitive in ClickHouse, like function names, aliases, etc. (#193 - thank you @Shlomixg!).offset() function and its brackets.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.(()) or ()[].~, the jinja string concatenation operator (#182){% else %} and {% elif %} statementsfiles as a List[pathlib.Path] instead of a List[str]pyproject.toml file. See README for more information (#90)--exclude option to specify a glob of files to exclude from formatting (#131)$1) and snowflake stages (@my_stage) as identifiersbetween operator's and keyword (#124 - thank you @WestComputing!)pipx install sqlfmt[jinjafmt]). If black is installed, but you do not want to use this feature, you can disable it with the command-line option --no-jinjafmt@>, ||/, ?-| (#105)--single-process, to force single-processing, even when formatting many filesmake profiling- as the files argument--quiet option-- fmt: off and -- fmt: on comments in sql fileswindow and qualify# comment)All notable changes to this project will be documented in this file.
All notable changes to this project will be documented in this file.
pyproject.toml file as an argument. To leverage this pass --config <path to file>. If passed, sqlfmt will not attempt to find a config file. The file must exist or an exception will be thrown. Note that other options pased at the command line will override the settings within this file.pragma, set, and call statements(#656 - thank you @aersam!)* and columns in DuckDB *columns expressions (#657 - thank you @aersam!): and ['name'] in a DataBricks escaped variant expression like foo:['bar.baz'] (#637 - thank you @aersam!)#>, #>>, #-, and ## as comments (#461 - thank you @pauljz and many others!)".1234567E+2BD (#645).filter(), isnull(), and rlike('foo', 'bar') (but it also permits filter (), isnull (), and rlike ('foo') to support dialects where those are operators, not function names) (#641, #478 - thank you @williamscs, @hongtron, and @chwiese!).32y and +3.2e6bd and will not introduce a space between the digits and their type suffix (#640 - thank you @ShaneMazur!)./*+ COALESCE(3) */ (#639 - thank you @wr-atlas!).create row access policy statements with grant sub-statements (it also generally more robustly handles unsupported DDL) (#633).foo.select or foo.case (#599 - thank you @matthieucan!).union [all] by name is now supported (#611 - thank you @aersam!).interval is now parsed as a word operator. Parenthesized expressions like interval (10) days will maintain the space after interval.left anti & right anti joins are now supported.(NOT) (I)LIKE, ~~, ~~*, !~~, !~~*, are now supported (these use two tildes where the posix version of these operators use a single tilde) (#576 - thank you @tuckerrc!).{% for %}...{% else %}...{% endfor %} loops are now supported. Previously, a BracketError was raised if a for loop included an else tag (#549 - thank you, @yassun7010!).map<...> type declaration syntax from Athena. (#500 - thank you for the issue and fix, @benjamin-awd!){{ {'a': {'b': 1}} }}) could cause parsing errors (#471 - thank you @rparvathaneni-sc and @benjamin-awd!). This fix introduces a dependency on jinja2 > v3.0.:=) from being lexed as a single token (#502 - thank you @federico-hero!).any() and all() will no longer get spaces between the function name and the parenthesis, unless they are a part of a like any () or like all () operator (#483 - thank you @damirbk!).// comment markers are now parsed as comments and rewritten to -- on formatting (#468 - thank you @nilsonavp!).semi, anti, positional, and asof joins are now supported. (#482).--exclude would not follow symlinks when globbing
(#457 - thank you @jeancochrane!).--fmt: off comments could cause an error in formatting a file
(#447 - thank you @ramonvermeulen!).--fmt: off blocks.--fmt: off would still be formatted.
(#136).exclude paths defined in pyproject.toml files are now evaluated relative to the location of the file, not the current working directory.
Relative paths provided to the --exclude option (or env var) are evaluated relative to the current working directory. Files and exclude paths
are now compared as resolved, absolute paths. (Fixes #431 - thank you @cmcnicoll!){#-- comment --#} would cause a false positive for the
comment safety check. (#434)<=> operator (#432 - thank you @kathykwon!)/* comment */) on a single line would cause sqlfmt
to not include all comments in formatted output (#419 - thank you @aersam!)union distinct tokens (#417 - thank you, @paschmaria!)the contents of jinja blocks are now indented if the block wraps onto multiple rows (#403). This is now the proper sqlfmt style:
select
some_field,
{% for some_item in some_sequence %}
some_function({{ some_item }}){% if not loop.last %}, {% endif %}
{% endfor %}
While in this simple example the new style makes it less clear
that some_field and some_function are at the
same SQL depth, the formatting of complex files with nested jinja blocks is much improved.
For example:
{%- for col in cols -%}
{%- if col.column.lower() not in remove | map(
"lower"
) and col.column.lower() not in exclude | map("lower") -%}
{% do include_cols.append(col) %}
{%- endif %}
{%- endfor %}
See also this discussion. Thank you @dave-connors-3 and @alrocar!
sqlfmt now supports all Postgres frame clauses, not just those that start with rows between. (#404)
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!){% endif %}) to a line could cause bad formatting of everything on that linecreate <object> ... clone statements (#313).*.sql and *.sql.jinja, even those with other dots in their filenames (#354 - thank you @ysmilda!).{% call %} blocks with arguments like {% call(foo) bar(baz) %} would cause a parsing error (#353 - thank you @IgnorantWalking!).--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.RecursionError (#343 - thank you @kcem-flyr!).{% set %} and {% call %} blocks would cause a parsing error (#338 - thank you @AndrewLane!).is [not] distinct from as a word operator (#327 - thank you @IgnorantWalking, @kadekillary!).{% call %} blocks that called a macro that wasn't statement caused a parsing error (#335 - thank you @AndrewLane!).{% materialization ... %} and {% call statement(...) %} blocks (#309).{% endmacro %}, {% endtest %}, {% endcall %}, or {% endmaterialization %} tag.create warehouse and alter warehouse statements (#312, #299).alter function and drop function statements (#310, #311), and Snowflake's create external function statements (#322).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!).return(return_())).delete statements and the associated keywords using and returning (#281).grant and revoke statements and all associated keywords (#283).create function statements and all associated keywords (#282).explain keyword (#280).table<a int64, b bytes(5), c string>.$foo as ordinary identifiers.tomli dependency.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.array<float64>[1, 2] are now supported, and spaces will no longer be inserted around < and > (#212).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).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).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.--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.python -m sqlfmt.between ... and ..., especially in situations where the source includes a line break (#207).%s and %(name)s (#198 - thank you @snorkysnark!).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).--dialect clickhouse option, sqlfmt will not lowercase names that could be case-sensitive in ClickHouse, like function names, aliases, etc. (#193 - thank you @Shlomixg!).offset() function and its brackets.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.(()) or ()[].~, the jinja string concatenation operator (#182){% else %} and {% elif %} statementsfiles as a List[pathlib.Path] instead of a List[str]pyproject.toml file. See README for more information (#90)--exclude option to specify a glob of files to exclude from formatting (#131)$1) and snowflake stages (@my_stage) as identifiersbetween operator's and keyword (#124 - thank you @WestComputing!)pipx install sqlfmt[jinjafmt]). If black is installed, but you do not want to use this feature, you can disable it with the command-line option --no-jinjafmt@>, ||/, ?-| (#105)--single-process, to force single-processing, even when formatting many filesmake profiling- as the files argument--quiet option-- fmt: off and -- fmt: on comments in sql fileswindow and qualify# comment)All notable changes to this project will be documented in this file.
All notable changes to this project will be documented in this file.
pragma, set, and call statements(#656 - thank you @aersam!)* and columns in DuckDB *columns expressions (#657 - thank you @aersam!): and ['name'] in a DataBricks escaped variant expression like foo:['bar.baz'] (#637 - thank you @aersam!)#>, #>>, #-, and ## as comments (#461 - thank you @pauljz and many others!)".1234567E+2BD (#645).filter(), isnull(), and rlike('foo', 'bar') (but it also permits filter (), isnull (), and rlike ('foo') to support dialects where those are operators, not function names) (#641, #478 - thank you @williamscs, @hongtron, and @chwiese!).32y and +3.2e6bd and will not introduce a space between the digits and their type suffix (#640 - thank you @ShaneMazur!)./*+ COALESCE(3) */ (#639 - thank you @wr-atlas!).create row access policy statements with grant sub-statements (it also generally more robustly handles unsupported DDL) (#633).foo.select or foo.case (#599 - thank you @matthieucan!).union [all] by name is now supported (#611 - thank you @aersam!).interval is now parsed as a word operator. Parenthesized expressions like interval (10) days will maintain the space after interval.left anti & right anti joins are now supported.(NOT) (I)LIKE, ~~, ~~*, !~~, !~~*, are now supported (these use two tildes where the posix version of these operators use a single tilde) (#576 - thank you @tuckerrc!).{% for %}...{% else %}...{% endfor %} loops are now supported. Previously, a BracketError was raised if a for loop included an else tag (#549 - thank you, @yassun7010!).map<...> type declaration syntax from Athena. (#500 - thank you for the issue and fix, @benjamin-awd!){{ {'a': {'b': 1}} }}) could cause parsing errors (#471 - thank you @rparvathaneni-sc and @benjamin-awd!). This fix introduces a dependency on jinja2 > v3.0.:=) from being lexed as a single token (#502 - thank you @federico-hero!).any() and all() will no longer get spaces between the function name and the parenthesis, unless they are a part of a like any () or like all () operator (#483 - thank you @damirbk!).// comment markers are now parsed as comments and rewritten to -- on formatting (#468 - thank you @nilsonavp!).semi, anti, positional, and asof joins are now supported. (#482).--exclude would not follow symlinks when globbing
(#457 - thank you @jeancochrane!).--fmt: off comments could cause an error in formatting a file
(#447 - thank you @ramonvermeulen!).--fmt: off blocks.--fmt: off would still be formatted.
(#136).exclude paths defined in pyproject.toml files are now evaluated relative to the location of the file, not the current working directory.
Relative paths provided to the --exclude option (or env var) are evaluated relative to the current working directory. Files and exclude paths
are now compared as resolved, absolute paths. (Fixes #431 - thank you @cmcnicoll!){#-- comment --#} would cause a false positive for the
comment safety check. (#434)<=> operator (#432 - thank you @kathykwon!)/* comment */) on a single line would cause sqlfmt
to not include all comments in formatted output (#419 - thank you @aersam!)union distinct tokens (#417 - thank you, @paschmaria!)the contents of jinja blocks are now indented if the block wraps onto multiple rows (#403). This is now the proper sqlfmt style:
select
some_field,
{% for some_item in some_sequence %}
some_function({{ some_item }}){% if not loop.last %}, {% endif %}
{% endfor %}
While in this simple example the new style makes it less clear
that some_field and some_function are at the
same SQL depth, the formatting of complex files with nested jinja blocks is much improved.
For example:
{%- for col in cols -%}
{%- if col.column.lower() not in remove | map(
"lower"
) and col.column.lower() not in exclude | map("lower") -%}
{% do include_cols.append(col) %}
{%- endif %}
{%- endfor %}
See also this discussion. Thank you @dave-connors-3 and @alrocar!
sqlfmt now supports all Postgres frame clauses, not just those that start with rows between. (#404)
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!){% endif %}) to a line could cause bad formatting of everything on that linecreate <object> ... clone statements (#313).*.sql and *.sql.jinja, even those with other dots in their filenames (#354 - thank you @ysmilda!).{% call %} blocks with arguments like {% call(foo) bar(baz) %} would cause a parsing error (#353 - thank you @IgnorantWalking!).--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.RecursionError (#343 - thank you @kcem-flyr!).{% set %} and {% call %} blocks would cause a parsing error (#338 - thank you @AndrewLane!).is [not] distinct from as a word operator (#327 - thank you @IgnorantWalking, @kadekillary!).{% call %} blocks that called a macro that wasn't statement caused a parsing error (#335 - thank you @AndrewLane!).{% materialization ... %} and {% call statement(...) %} blocks (#309).{% endmacro %}, {% endtest %}, {% endcall %}, or {% endmaterialization %} tag.create warehouse and alter warehouse statements (#312, #299).alter function and drop function statements (#310, #311), and Snowflake's create external function statements (#322).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!).return(return_())).delete statements and the associated keywords using and returning (#281).grant and revoke statements and all associated keywords (#283).create function statements and all associated keywords (#282).explain keyword (#280).table<a int64, b bytes(5), c string>.$foo as ordinary identifiers.tomli dependency.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.array<float64>[1, 2] are now supported, and spaces will no longer be inserted around < and > (#212).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).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).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.--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.python -m sqlfmt.between ... and ..., especially in situations where the source includes a line break (#207).%s and %(name)s (#198 - thank you @snorkysnark!).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).--dialect clickhouse option, sqlfmt will not lowercase names that could be case-sensitive in ClickHouse, like function names, aliases, etc. (#193 - thank you @Shlomixg!).offset() function and its brackets.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.(()) or ()[].~, the jinja string concatenation operator (#182){% else %} and {% elif %} statementsfiles as a List[pathlib.Path] instead of a List[str]pyproject.toml file. See README for more information (#90)--exclude option to specify a glob of files to exclude from formatting (#131)$1) and snowflake stages (@my_stage) as identifiersbetween operator's and keyword (#124 - thank you @WestComputing!)pipx install sqlfmt[jinjafmt]). If black is installed, but you do not want to use this feature, you can disable it with the command-line option --no-jinjafmt@>, ||/, ?-| (#105)--single-process, to force single-processing, even when formatting many filesmake profiling- as the files argument--quiet option-- fmt: off and -- fmt: on comments in sql fileswindow and qualify# comment)All notable changes to this project will be documented in this file.
All notable changes to this project will be documented in this file.
* and columns in DuckDB *columns expressions (#657 - thank you @aersam!): and ['name'] in a DataBricks escaped variant expression like foo:['bar.baz'] (#637 - thank you @aersam!)#>, #>>, #-, and ## as comments (#461 - thank you @pauljz and many others!)".1234567E+2BD (#645).filter(), isnull(), and rlike('foo', 'bar') (but it also permits filter (), isnull (), and rlike ('foo') to support dialects where those are operators, not function names) (#641, #478 - thank you @williamscs, @hongtron, and @chwiese!).32y and +3.2e6bd and will not introduce a space between the digits and their type suffix (#640 - thank you @ShaneMazur!)./*+ COALESCE(3) */ (#639 - thank you @wr-atlas!).create row access policy statements with grant sub-statements (it also generally more robustly handles unsupported DDL) (#633).foo.select or foo.case (#599 - thank you @matthieucan!).union [all] by name is now supported (#611 - thank you @aersam!).interval is now parsed as a word operator. Parenthesized expressions like interval (10) days will maintain the space after interval.left anti & right anti joins are now supported.(NOT) (I)LIKE, ~~, ~~*, !~~, !~~*, are now supported (these use two tildes where the posix version of these operators use a single tilde) (#576 - thank you @tuckerrc!).{% for %}...{% else %}...{% endfor %} loops are now supported. Previously, a BracketError was raised if a for loop included an else tag (#549 - thank you, @yassun7010!).map<...> type declaration syntax from Athena. (#500 - thank you for the issue and fix, @benjamin-awd!){{ {'a': {'b': 1}} }}) could cause parsing errors (#471 - thank you @rparvathaneni-sc and @benjamin-awd!). This fix introduces a dependency on jinja2 > v3.0.:=) from being lexed as a single token (#502 - thank you @federico-hero!).any() and all() will no longer get spaces between the function name and the parenthesis, unless they are a part of a like any () or like all () operator (#483 - thank you @damirbk!).// comment markers are now parsed as comments and rewritten to -- on formatting (#468 - thank you @nilsonavp!).semi, anti, positional, and asof joins are now supported. (#482).--exclude would not follow symlinks when globbing
(#457 - thank you @jeancochrane!).--fmt: off comments could cause an error in formatting a file
(#447 - thank you @ramonvermeulen!).--fmt: off blocks.--fmt: off would still be formatted.
(#136).exclude paths defined in pyproject.toml files are now evaluated relative to the location of the file, not the current working directory.
Relative paths provided to the --exclude option (or env var) are evaluated relative to the current working directory. Files and exclude paths
are now compared as resolved, absolute paths. (Fixes #431 - thank you @cmcnicoll!){#-- comment --#} would cause a false positive for the
comment safety check. (#434)<=> operator (#432 - thank you @kathykwon!)/* comment */) on a single line would cause sqlfmt
to not include all comments in formatted output (#419 - thank you @aersam!)union distinct tokens (#417 - thank you, @paschmaria!)the contents of jinja blocks are now indented if the block wraps onto multiple rows (#403). This is now the proper sqlfmt style:
select
some_field,
{% for some_item in some_sequence %}
some_function({{ some_item }}){% if not loop.last %}, {% endif %}
{% endfor %}
While in this simple example the new style makes it less clear
that some_field and some_function are at the
same SQL depth, the formatting of complex files with nested jinja blocks is much improved.
For example:
{%- for col in cols -%}
{%- if col.column.lower() not in remove | map(
"lower"
) and col.column.lower() not in exclude | map("lower") -%}
{% do include_cols.append(col) %}
{%- endif %}
{%- endfor %}
See also this discussion. Thank you @dave-connors-3 and @alrocar!
sqlfmt now supports all Postgres frame clauses, not just those that start with rows between. (#404)
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!){% endif %}) to a line could cause bad formatting of everything on that linecreate <object> ... clone statements (#313).*.sql and *.sql.jinja, even those with other dots in their filenames (#354 - thank you @ysmilda!).{% call %} blocks with arguments like {% call(foo) bar(baz) %} would cause a parsing error (#353 - thank you @IgnorantWalking!).--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.RecursionError (#343 - thank you @kcem-flyr!).{% set %} and {% call %} blocks would cause a parsing error (#338 - thank you @AndrewLane!).is [not] distinct from as a word operator (#327 - thank you @IgnorantWalking, @kadekillary!).{% call %} blocks that called a macro that wasn't statement caused a parsing error (#335 - thank you @AndrewLane!).{% materialization ... %} and {% call statement(...) %} blocks (#309).{% endmacro %}, {% endtest %}, {% endcall %}, or {% endmaterialization %} tag.create warehouse and alter warehouse statements (#312, #299).alter function and drop function statements (#310, #311), and Snowflake's create external function statements (#322).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!).return(return_())).delete statements and the associated keywords using and returning (#281).grant and revoke statements and all associated keywords (#283).create function statements and all associated keywords (#282).explain keyword (#280).table<a int64, b bytes(5), c string>.$foo as ordinary identifiers.tomli dependency.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.array<float64>[1, 2] are now supported, and spaces will no longer be inserted around < and > (#212).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).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).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.--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.python -m sqlfmt.between ... and ..., especially in situations where the source includes a line break (#207).%s and %(name)s (#198 - thank you @snorkysnark!).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).--dialect clickhouse option, sqlfmt will not lowercase names that could be case-sensitive in ClickHouse, like function names, aliases, etc. (#193 - thank you @Shlomixg!).offset() function and its brackets.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.(()) or ()[].~, the jinja string concatenation operator (#182){% else %} and {% elif %} statementsfiles as a List[pathlib.Path] instead of a List[str]pyproject.toml file. See README for more information (#90)--exclude option to specify a glob of files to exclude from formatting (#131)$1) and snowflake stages (@my_stage) as identifiersbetween operator's and keyword (#124 - thank you @WestComputing!)pipx install sqlfmt[jinjafmt]). If black is installed, but you do not want to use this feature, you can disable it with the command-line option --no-jinjafmt@>, ||/, ?-| (#105)--single-process, to force single-processing, even when formatting many filesmake profiling- as the files argument--quiet option-- fmt: off and -- fmt: on comments in sql fileswindow and qualify# comment)All notable changes to this project will be documented in this file.
All notable changes to this project will be documented in this file.
filter(), isnull(), and rlike('foo', 'bar') (but it also permits filter (), isnull (), and rlike ('foo') to support dialects where those are operators, not function names) (#641, #478 - thank you @williamscs, @hongtron, and @chwiese!).32y and +3.2e6bd and will not introduce a space between the digits and their type suffix (#640 - thank you @ShaneMazur!)./*+ COALESCE(3) */ (#639 - thank you @wr-atlas!).create row access policy statements with grant sub-statements (it also generally more robustly handles unsupported DDL) (#633).foo.select or foo.case (#599 - thank you @matthieucan!).union [all] by name is now supported (#611 - thank you @aersam!).interval is now parsed as a word operator. Parenthesized expressions like interval (10) days will maintain the space after interval.left anti & right anti joins are now supported.(NOT) (I)LIKE, ~~, ~~*, !~~, !~~*, are now supported (these use two tildes where the posix version of these operators use a single tilde) (#576 - thank you @tuckerrc!).{% for %}...{% else %}...{% endfor %} loops are now supported. Previously, a BracketError was raised if a for loop included an else tag (#549 - thank you, @yassun7010!).map<...> type declaration syntax from Athena. (#500 - thank you for the issue and fix, @benjamin-awd!){{ {'a': {'b': 1}} }}) could cause parsing errors (#471 - thank you @rparvathaneni-sc and @benjamin-awd!). This fix introduces a dependency on jinja2 > v3.0.:=) from being lexed as a single token (#502 - thank you @federico-hero!).any() and all() will no longer get spaces between the function name and the parenthesis, unless they are a part of a like any () or like all () operator (#483 - thank you @damirbk!).// comment markers are now parsed as comments and rewritten to -- on formatting (#468 - thank you @nilsonavp!).semi, anti, positional, and asof joins are now supported. (#482).--exclude would not follow symlinks when globbing
(#457 - thank you @jeancochrane!).--fmt: off comments could cause an error in formatting a file
(#447 - thank you @ramonvermeulen!).--fmt: off blocks.--fmt: off would still be formatted.
(#136).exclude paths defined in pyproject.toml files are now evaluated relative to the location of the file, not the current working directory.
Relative paths provided to the --exclude option (or env var) are evaluated relative to the current working directory. Files and exclude paths
are now compared as resolved, absolute paths. (Fixes #431 - thank you @cmcnicoll!){#-- comment --#} would cause a false positive for the
comment safety check. (#434)<=> operator (#432 - thank you @kathykwon!)/* comment */) on a single line would cause sqlfmt
to not include all comments in formatted output (#419 - thank you @aersam!)union distinct tokens (#417 - thank you, @paschmaria!)the contents of jinja blocks are now indented if the block wraps onto multiple rows (#403). This is now the proper sqlfmt style:
select
some_field,
{% for some_item in some_sequence %}
some_function({{ some_item }}){% if not loop.last %}, {% endif %}
{% endfor %}
While in this simple example the new style makes it less clear
that some_field and some_function are at the
same SQL depth, the formatting of complex files with nested jinja blocks is much improved.
For example:
{%- for col in cols -%}
{%- if col.column.lower() not in remove | map(
"lower"
) and col.column.lower() not in exclude | map("lower") -%}
{% do include_cols.append(col) %}
{%- endif %}
{%- endfor %}
See also this discussion. Thank you @dave-connors-3 and @alrocar!
sqlfmt now supports all Postgres frame clauses, not just those that start with rows between. (#404)
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!){% endif %}) to a line could cause bad formatting of everything on that linecreate <object> ... clone statements (#313).*.sql and *.sql.jinja, even those with other dots in their filenames (#354 - thank you @ysmilda!).{% call %} blocks with arguments like {% call(foo) bar(baz) %} would cause a parsing error (#353 - thank you @IgnorantWalking!).--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.RecursionError (#343 - thank you @kcem-flyr!).{% set %} and {% call %} blocks would cause a parsing error (#338 - thank you @AndrewLane!).is [not] distinct from as a word operator (#327 - thank you @IgnorantWalking, @kadekillary!).{% call %} blocks that called a macro that wasn't statement caused a parsing error (#335 - thank you @AndrewLane!).{% materialization ... %} and {% call statement(...) %} blocks (#309).{% endmacro %}, {% endtest %}, {% endcall %}, or {% endmaterialization %} tag.create warehouse and alter warehouse statements (#312, #299).alter function and drop function statements (#310, #311), and Snowflake's create external function statements (#322).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!).return(return_())).delete statements and the associated keywords using and returning (#281).grant and revoke statements and all associated keywords (#283).create function statements and all associated keywords (#282).explain keyword (#280).table<a int64, b bytes(5), c string>.$foo as ordinary identifiers.tomli dependency.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.array<float64>[1, 2] are now supported, and spaces will no longer be inserted around < and > (#212).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).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).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.--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.python -m sqlfmt.between ... and ..., especially in situations where the source includes a line break (#207).%s and %(name)s (#198 - thank you @snorkysnark!).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).--dialect clickhouse option, sqlfmt will not lowercase names that could be case-sensitive in ClickHouse, like function names, aliases, etc. (#193 - thank you @Shlomixg!).offset() function and its brackets.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.(()) or ()[].~, the jinja string concatenation operator (#182){% else %} and {% elif %} statementsfiles as a List[pathlib.Path] instead of a List[str]pyproject.toml file. See README for more information (#90)--exclude option to specify a glob of files to exclude from formatting (#131)$1) and snowflake stages (@my_stage) as identifiersbetween operator's and keyword (#124 - thank you @WestComputing!)pipx install sqlfmt[jinjafmt]). If black is installed, but you do not want to use this feature, you can disable it with the command-line option --no-jinjafmt@>, ||/, ?-| (#105)--single-process, to force single-processing, even when formatting many filesmake profiling- as the files argument--quiet option-- fmt: off and -- fmt: on comments in sql fileswindow and qualify# comment)All notable changes to this project will be documented in this file.
All notable changes to this project will be documented in this file.
foo.select or foo.case (#599 - thank you @matthieucan!).union [all] by name is now supported (#611 - thank you @aersam!).interval is now parsed as a word operator. Parenthesized expressions like interval (10) days will maintain the space after interval.left anti & right anti joins are now supported.(NOT) (I)LIKE, ~~, ~~*, !~~, !~~*, are now supported (these use two tildes where the posix version of these operators use a single tilde) (#576 - thank you @tuckerrc!).{% for %}...{% else %}...{% endfor %} loops are now supported. Previously, a BracketError was raised if a for loop included an else tag (#549 - thank you, @yassun7010!).map<...> type declaration syntax from Athena. (#500 - thank you for the issue and fix, @benjamin-awd!){{ {'a': {'b': 1}} }}) could cause parsing errors (#471 - thank you @rparvathaneni-sc and @benjamin-awd!). This fix introduces a dependency on jinja2 > v3.0.:=) from being lexed as a single token (#502 - thank you @federico-hero!).any() and all() will no longer get spaces between the function name and the parenthesis, unless they are a part of a like any () or like all () operator (#483 - thank you @damirbk!).// comment markers are now parsed as comments and rewritten to -- on formatting (#468 - thank you @nilsonavp!).semi, anti, positional, and asof joins are now supported. (#482).--exclude would not follow symlinks when globbing
(#457 - thank you @jeancochrane!).--fmt: off comments could cause an error in formatting a file
(#447 - thank you @ramonvermeulen!).--fmt: off blocks.--fmt: off would still be formatted.
(#136).exclude paths defined in pyproject.toml files are now evaluated relative to the location of the file, not the current working directory.
Relative paths provided to the --exclude option (or env var) are evaluated relative to the current working directory. Files and exclude paths
are now compared as resolved, absolute paths. (Fixes #431 - thank you @cmcnicoll!){#-- comment --#} would cause a false positive for the
comment safety check. (#434)<=> operator (#432 - thank you @kathykwon!)/* comment */) on a single line would cause sqlfmt
to not include all comments in formatted output (#419 - thank you @aersam!)union distinct tokens (#417 - thank you, @paschmaria!)the contents of jinja blocks are now indented if the block wraps onto multiple rows (#403). This is now the proper sqlfmt style:
select
some_field,
{% for some_item in some_sequence %}
some_function({{ some_item }}){% if not loop.last %}, {% endif %}
{% endfor %}
While in this simple example the new style makes it less clear
that some_field and some_function are at the
same SQL depth, the formatting of complex files with nested jinja blocks is much improved.
For example:
{%- for col in cols -%}
{%- if col.column.lower() not in remove | map(
"lower"
) and col.column.lower() not in exclude | map("lower") -%}
{% do include_cols.append(col) %}
{%- endif %}
{%- endfor %}
See also this discussion. Thank you @dave-connors-3 and @alrocar!
sqlfmt now supports all Postgres frame clauses, not just those that start with rows between. (#404)
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!){% endif %}) to a line could cause bad formatting of everything on that linecreate <object> ... clone statements (#313).*.sql and *.sql.jinja, even those with other dots in their filenames (#354 - thank you @ysmilda!).{% call %} blocks with arguments like {% call(foo) bar(baz) %} would cause a parsing error (#353 - thank you @IgnorantWalking!).--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.RecursionError (#343 - thank you @kcem-flyr!).{% set %} and {% call %} blocks would cause a parsing error (#338 - thank you @AndrewLane!).is [not] distinct from as a word operator (#327 - thank you @IgnorantWalking, @kadekillary!).{% call %} blocks that called a macro that wasn't statement caused a parsing error (#335 - thank you @AndrewLane!).{% materialization ... %} and {% call statement(...) %} blocks (#309).{% endmacro %}, {% endtest %}, {% endcall %}, or {% endmaterialization %} tag.create warehouse and alter warehouse statements (#312, #299).alter function and drop function statements (#310, #311), and Snowflake's create external function statements (#322).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!).return(return_())).delete statements and the associated keywords using and returning (#281).grant and revoke statements and all associated keywords (#283).create function statements and all associated keywords (#282).explain keyword (#280).table<a int64, b bytes(5), c string>.$foo as ordinary identifiers.tomli dependency.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.array<float64>[1, 2] are now supported, and spaces will no longer be inserted around < and > (#212).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).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).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.--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.python -m sqlfmt.between ... and ..., especially in situations where the source includes a line break (#207).%s and %(name)s (#198 - thank you @snorkysnark!).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).--dialect clickhouse option, sqlfmt will not lowercase names that could be case-sensitive in ClickHouse, like function names, aliases, etc. (#193 - thank you @Shlomixg!).offset() function and its brackets.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.(()) or ()[].~, the jinja string concatenation operator (#182){% else %} and {% elif %} statementsfiles as a List[pathlib.Path] instead of a List[str]pyproject.toml file. See README for more information (#90)--exclude option to specify a glob of files to exclude from formatting (#131)$1) and snowflake stages (@my_stage) as identifiersbetween operator's and keyword (#124 - thank you @WestComputing!)pipx install sqlfmt[jinjafmt]). If black is installed, but you do not want to use this feature, you can disable it with the command-line option --no-jinjafmt@>, ||/, ?-| (#105)--single-process, to force single-processing, even when formatting many filesmake profiling- as the files argument--quiet option-- fmt: off and -- fmt: on comments in sql fileswindow and qualify# comment)All notable changes to this project will be documented in this file.
All notable changes to this project will be documented in this file.
foo.select or foo.case (#599 - thank you @matthieucan!).union [all] by name is now supported (#611 - thank you @aersam!).interval is now parsed as a word operator. Parenthesized expressions like interval (10) days will maintain the space after interval.left anti & right anti joins are now supported.(NOT) (I)LIKE, ~~, ~~*, !~~, !~~*, are now supported (these use two tildes where the posix version of these operators use a single tilde) (#576 - thank you @tuckerrc!).{% for %}...{% else %}...{% endfor %} loops are now supported. Previously, a BracketError was raised if a for loop included an else tag (#549 - thank you, @yassun7010!).map<...> type declaration syntax from Athena. (#500 - thank you for the issue and fix, @benjamin-awd!){{ {'a': {'b': 1}} }}) could cause parsing errors (#471 - thank you @rparvathaneni-sc and @benjamin-awd!). This fix introduces a dependency on jinja2 > v3.0.:=) from being lexed as a single token (#502 - thank you @federico-hero!).any() and all() will no longer get spaces between the function name and the parenthesis, unless they are a part of a like any () or like all () operator (#483 - thank you @damirbk!).// comment markers are now parsed as comments and rewritten to -- on formatting (#468 - thank you @nilsonavp!).semi, anti, positional, and asof joins are now supported. (#482).--exclude would not follow symlinks when globbing
(#457 - thank you @jeancochrane!).--fmt: off comments could cause an error in formatting a file
(#447 - thank you @ramonvermeulen!).--fmt: off blocks.--fmt: off would still be formatted.
(#136).exclude paths defined in pyproject.toml files are now evaluated relative to the location of the file, not the current working directory.
Relative paths provided to the --exclude option (or env var) are evaluated relative to the current working directory. Files and exclude paths
are now compared as resolved, absolute paths. (Fixes #431 - thank you @cmcnicoll!){#-- comment --#} would cause a false positive for the
comment safety check. (#434)<=> operator (#432 - thank you @kathykwon!)/* comment */) on a single line would cause sqlfmt
to not include all comments in formatted output (#419 - thank you @aersam!)union distinct tokens (#417 - thank you, @paschmaria!)the contents of jinja blocks are now indented if the block wraps onto multiple rows (#403). This is now the proper sqlfmt style:
select
some_field,
{% for some_item in some_sequence %}
some_function({{ some_item }}){% if not loop.last %}, {% endif %}
{% endfor %}
While in this simple example the new style makes it less clear
that some_field and some_function are at the
same SQL depth, the formatting of complex files with nested jinja blocks is much improved.
For example:
{%- for col in cols -%}
{%- if col.column.lower() not in remove | map(
"lower"
) and col.column.lower() not in exclude | map("lower") -%}
{% do include_cols.append(col) %}
{%- endif %}
{%- endfor %}
See also this discussion. Thank you @dave-connors-3 and @alrocar!
sqlfmt now supports all Postgres frame clauses, not just those that start with rows between. (#404)
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!){% endif %}) to a line could cause bad formatting of everything on that linecreate <object> ... clone statements (#313).*.sql and *.sql.jinja, even those with other dots in their filenames (#354 - thank you @ysmilda!).{% call %} blocks with arguments like {% call(foo) bar(baz) %} would cause a parsing error (#353 - thank you @IgnorantWalking!).--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.RecursionError (#343 - thank you @kcem-flyr!).{% set %} and {% call %} blocks would cause a parsing error (#338 - thank you @AndrewLane!).is [not] distinct from as a word operator (#327 - thank you @IgnorantWalking, @kadekillary!).{% call %} blocks that called a macro that wasn't statement caused a parsing error (#335 - thank you @AndrewLane!).{% materialization ... %} and {% call statement(...) %} blocks (#309).{% endmacro %}, {% endtest %}, {% endcall %}, or {% endmaterialization %} tag.create warehouse and alter warehouse statements (#312, #299).alter function and drop function statements (#310, #311), and Snowflake's create external function statements (#322).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!).return(return_())).delete statements and the associated keywords using and returning (#281).grant and revoke statements and all associated keywords (#283).create function statements and all associated keywords (#282).explain keyword (#280).table<a int64, b bytes(5), c string>.$foo as ordinary identifiers.tomli dependency.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.array<float64>[1, 2] are now supported, and spaces will no longer be inserted around < and > (#212).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).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).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.--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.python -m sqlfmt.between ... and ..., especially in situations where the source includes a line break (#207).%s and %(name)s (#198 - thank you @snorkysnark!).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).--dialect clickhouse option, sqlfmt will not lowercase names that could be case-sensitive in ClickHouse, like function names, aliases, etc. (#193 - thank you @Shlomixg!).offset() function and its brackets.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.(()) or ()[].~, the jinja string concatenation operator (#182){% else %} and {% elif %} statementsfiles as a List[pathlib.Path] instead of a List[str]pyproject.toml file. See README for more information (#90)--exclude option to specify a glob of files to exclude from formatting (#131)$1) and snowflake stages (@my_stage) as identifiersbetween operator's and keyword (#124 - thank you @WestComputing!)pipx install sqlfmt[jinjafmt]). If black is installed, but you do not want to use this feature, you can disable it with the command-line option --no-jinjafmt@>, ||/, ?-| (#105)--single-process, to force single-processing, even when formatting many filesmake profiling- as the files argument--quiet option-- fmt: off and -- fmt: on comments in sql fileswindow and qualify# comment)All notable changes to this project will be documented in this file.
All notable changes to this project will be documented in this file.
foo.select or foo.case (#599 - thank you @matthieucan!).union [all] by name is now supported (#611 - thank you @aersam!).interval is now parsed as a word operator. Parenthesized expressions like interval (10) days will maintain the space after interval.left anti & right anti joins are now supported.(NOT) (I)LIKE, ~~, ~~*, !~~, !~~*, are now supported (these use two tildes where the posix version of these operators use a single tilde) (#576 - thank you @tuckerrc!).{% for %}...{% else %}...{% endfor %} loops are now supported. Previously, a BracketError was raised if a for loop included an else tag (#549 - thank you, @yassun7010!).map<...> type declaration syntax from Athena. (#500 - thank you for the issue and fix, @benjamin-awd!){{ {'a': {'b': 1}} }}) could cause parsing errors (#471 - thank you @rparvathaneni-sc and @benjamin-awd!). This fix introduces a dependency on jinja2 > v3.0.:=) from being lexed as a single token (#502 - thank you @federico-hero!).any() and all() will no longer get spaces between the function name and the parenthesis, unless they are a part of a like any () or like all () operator (#483 - thank you @damirbk!).// comment markers are now parsed as comments and rewritten to -- on formatting (#468 - thank you @nilsonavp!).semi, anti, positional, and asof joins are now supported. (#482).--exclude would not follow symlinks when globbing
(#457 - thank you @jeancochrane!).--fmt: off comments could cause an error in formatting a file
(#447 - thank you @ramonvermeulen!).--fmt: off blocks.--fmt: off would still be formatted.
(#136).exclude paths defined in pyproject.toml files are now evaluated relative to the location of the file, not the current working directory.
Relative paths provided to the --exclude option (or env var) are evaluated relative to the current working directory. Files and exclude paths
are now compared as resolved, absolute paths. (Fixes #431 - thank you @cmcnicoll!){#-- comment --#} would cause a false positive for the
comment safety check. (#434)<=> operator (#432 - thank you @kathykwon!)/* comment */) on a single line would cause sqlfmt
to not include all comments in formatted output (#419 - thank you @aersam!)union distinct tokens (#417 - thank you, @paschmaria!)the contents of jinja blocks are now indented if the block wraps onto multiple rows (#403). This is now the proper sqlfmt style:
select
some_field,
{% for some_item in some_sequence %}
some_function({{ some_item }}){% if not loop.last %}, {% endif %}
{% endfor %}
While in this simple example the new style makes it less clear
that some_field and some_function are at the
same SQL depth, the formatting of complex files with nested jinja blocks is much improved.
For example:
{%- for col in cols -%}
{%- if col.column.lower() not in remove | map(
"lower"
) and col.column.lower() not in exclude | map("lower") -%}
{% do include_cols.append(col) %}
{%- endif %}
{%- endfor %}
See also this discussion. Thank you @dave-connors-3 and @alrocar!
sqlfmt now supports all Postgres frame clauses, not just those that start with rows between. (#404)
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!){% endif %}) to a line could cause bad formatting of everything on that linecreate <object> ... clone statements (#313).*.sql and *.sql.jinja, even those with other dots in their filenames (#354 - thank you @ysmilda!).{% call %} blocks with arguments like {% call(foo) bar(baz) %} would cause a parsing error (#353 - thank you @IgnorantWalking!).--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.RecursionError (#343 - thank you @kcem-flyr!).{% set %} and {% call %} blocks would cause a parsing error (#338 - thank you @AndrewLane!).is [not] distinct from as a word operator (#327 - thank you @IgnorantWalking, @kadekillary!).{% call %} blocks that called a macro that wasn't statement caused a parsing error (#335 - thank you @AndrewLane!).{% materialization ... %} and {% call statement(...) %} blocks (#309).{% endmacro %}, {% endtest %}, {% endcall %}, or {% endmaterialization %} tag.create warehouse and alter warehouse statements (#312, #299).alter function and drop function statements (#310, #311), and Snowflake's create external function statements (#322).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!).return(return_())).delete statements and the associated keywords using and returning (#281).grant and revoke statements and all associated keywords (#283).create function statements and all associated keywords (#282).explain keyword (#280).table<a int64, b bytes(5), c string>.$foo as ordinary identifiers.tomli dependency.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.array<float64>[1, 2] are now supported, and spaces will no longer be inserted around < and > (#212).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).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).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.--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.python -m sqlfmt.between ... and ..., especially in situations where the source includes a line break (#207).%s and %(name)s (#198 - thank you @snorkysnark!).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).--dialect clickhouse option, sqlfmt will not lowercase names that could be case-sensitive in ClickHouse, like function names, aliases, etc. (#193 - thank you @Shlomixg!).offset() function and its brackets.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.(()) or ()[].~, the jinja string concatenation operator (#182){% else %} and {% elif %} statementsfiles as a List[pathlib.Path] instead of a List[str]pyproject.toml file. See README for more information (#90)--exclude option to specify a glob of files to exclude from formatting (#131)$1) and snowflake stages (@my_stage) as identifiersbetween operator's and keyword (#124 - thank you @WestComputing!)pipx install sqlfmt[jinjafmt]). If black is installed, but you do not want to use this feature, you can disable it with the command-line option --no-jinjafmt@>, ||/, ?-| (#105)--single-process, to force single-processing, even when formatting many filesmake profiling- as the files argument--quiet option-- fmt: off and -- fmt: on comments in sql fileswindow and qualify# comment)All notable changes to this project will be documented in this file.
All notable changes to this project will be documented in this file.
foo.select or foo.case (#599 - thank you @matthieucan!).union [all] by name is now supported (#611 - thank you @aersam!).interval is now parsed as a word operator. Parenthesized expressions like interval (10) days will maintain the space after interval.left anti & right anti joins are now supported.(NOT) (I)LIKE, ~~, ~~*, !~~, !~~*, are now supported (these use two tildes where the posix version of these operators use a single tilde) (#576 - thank you @tuckerrc!).{% for %}...{% else %}...{% endfor %} loops are now supported. Previously, a BracketError was raised if a for loop included an else tag (#549 - thank you, @yassun7010!).map<...> type declaration syntax from Athena. (#500 - thank you for the issue and fix, @benjamin-awd!){{ {'a': {'b': 1}} }}) could cause parsing errors (#471 - thank you @rparvathaneni-sc and @benjamin-awd!). This fix introduces a dependency on jinja2 > v3.0.:=) from being lexed as a single token (#502 - thank you @federico-hero!).any() and all() will no longer get spaces between the function name and the parenthesis, unless they are a part of a like any () or like all () operator (#483 - thank you @damirbk!).// comment markers are now parsed as comments and rewritten to -- on formatting (#468 - thank you @nilsonavp!).semi, anti, positional, and asof joins are now supported. (#482).--exclude would not follow symlinks when globbing
(#457 - thank you @jeancochrane!).--fmt: off comments could cause an error in formatting a file
(#447 - thank you @ramonvermeulen!).--fmt: off blocks.--fmt: off would still be formatted.
(#136).exclude paths defined in pyproject.toml files are now evaluated relative to the location of the file, not the current working directory.
Relative paths provided to the --exclude option (or env var) are evaluated relative to the current working directory. Files and exclude paths
are now compared as resolved, absolute paths. (Fixes #431 - thank you @cmcnicoll!){#-- comment --#} would cause a false positive for the
comment safety check. (#434)<=> operator (#432 - thank you @kathykwon!)/* comment */) on a single line would cause sqlfmt
to not include all comments in formatted output (#419 - thank you @aersam!)union distinct tokens (#417 - thank you, @paschmaria!)the contents of jinja blocks are now indented if the block wraps onto multiple rows (#403). This is now the proper sqlfmt style:
select
some_field,
{% for some_item in some_sequence %}
some_function({{ some_item }}){% if not loop.last %}, {% endif %}
{% endfor %}
While in this simple example the new style makes it less clear
that some_field and some_function are at the
same SQL depth, the formatting of complex files with nested jinja blocks is much improved.
For example:
{%- for col in cols -%}
{%- if col.column.lower() not in remove | map(
"lower"
) and col.column.lower() not in exclude | map("lower") -%}
{% do include_cols.append(col) %}
{%- endif %}
{%- endfor %}
See also this discussion. Thank you @dave-connors-3 and @alrocar!
sqlfmt now supports all Postgres frame clauses, not just those that start with rows between. (#404)
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!){% endif %}) to a line could cause bad formatting of everything on that linecreate <object> ... clone statements (#313).*.sql and *.sql.jinja, even those with other dots in their filenames (#354 - thank you @ysmilda!).{% call %} blocks with arguments like {% call(foo) bar(baz) %} would cause a parsing error (#353 - thank you @IgnorantWalking!).--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.RecursionError (#343 - thank you @kcem-flyr!).{% set %} and {% call %} blocks would cause a parsing error (#338 - thank you @AndrewLane!).is [not] distinct from as a word operator (#327 - thank you @IgnorantWalking, @kadekillary!).{% call %} blocks that called a macro that wasn't statement caused a parsing error (#335 - thank you @AndrewLane!).{% materialization ... %} and {% call statement(...) %} blocks (#309).{% endmacro %}, {% endtest %}, {% endcall %}, or {% endmaterialization %} tag.create warehouse and alter warehouse statements (#312, #299).alter function and drop function statements (#310, #311), and Snowflake's create external function statements (#322).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!).return(return_())).delete statements and the associated keywords using and returning (#281).grant and revoke statements and all associated keywords (#283).create function statements and all associated keywords (#282).explain keyword (#280).table<a int64, b bytes(5), c string>.$foo as ordinary identifiers.tomli dependency.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.array<float64>[1, 2] are now supported, and spaces will no longer be inserted around < and > (#212).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).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).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.--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.python -m sqlfmt.between ... and ..., especially in situations where the source includes a line break (#207).%s and %(name)s (#198 - thank you @snorkysnark!).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).--dialect clickhouse option, sqlfmt will not lowercase names that could be case-sensitive in ClickHouse, like function names, aliases, etc. (#193 - thank you @Shlomixg!).offset() function and its brackets.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.(()) or ()[].~, the jinja string concatenation operator (#182){% else %} and {% elif %} statementsfiles as a List[pathlib.Path] instead of a List[str]pyproject.toml file. See README for more information (#90)--exclude option to specify a glob of files to exclude from formatting (#131)$1) and snowflake stages (@my_stage) as identifiersbetween operator's and keyword (#124 - thank you @WestComputing!)pipx install sqlfmt[jinjafmt]). If black is installed, but you do not want to use this feature, you can disable it with the command-line option --no-jinjafmt@>, ||/, ?-| (#105)--single-process, to force single-processing, even when formatting many filesmake profiling- as the files argument--quiet option-- fmt: off and -- fmt: on comments in sql fileswindow and qualify# comment)All notable changes to this project will be documented in this file.
All notable changes to this project will be documented in this file.
union [all] by name is now supported (#611 - thank you @aersam!).interval is now parsed as a word operator. Parenthesized expressions like interval (10) days will maintain the space after interval.left anti & right anti joins are now supported.(NOT) (I)LIKE, ~~, ~~*, !~~, !~~*, are now supported (these use two tildes where the posix version of these operators use a single tilde) (#576 - thank you @tuckerrc!).{% for %}...{% else %}...{% endfor %} loops are now supported. Previously, a BracketError was raised if a for loop included an else tag (#549 - thank you, @yassun7010!).map<...> type declaration syntax from Athena. (#500 - thank you for the issue and fix, @benjamin-awd!){{ {'a': {'b': 1}} }}) could cause parsing errors (#471 - thank you @rparvathaneni-sc and @benjamin-awd!). This fix introduces a dependency on jinja2 > v3.0.:=) from being lexed as a single token (#502 - thank you @federico-hero!).any() and all() will no longer get spaces between the function name and the parenthesis, unless they are a part of a like any () or like all () operator (#483 - thank you @damirbk!).// comment markers are now parsed as comments and rewritten to -- on formatting (#468 - thank you @nilsonavp!).semi, anti, positional, and asof joins are now supported. (#482).--exclude would not follow symlinks when globbing
(#457 - thank you @jeancochrane!).--fmt: off comments could cause an error in formatting a file
(#447 - thank you @ramonvermeulen!).--fmt: off blocks.--fmt: off would still be formatted.
(#136).exclude paths defined in pyproject.toml files are now evaluated relative to the location of the file, not the current working directory.
Relative paths provided to the --exclude option (or env var) are evaluated relative to the current working directory. Files and exclude paths
are now compared as resolved, absolute paths. (Fixes #431 - thank you @cmcnicoll!){#-- comment --#} would cause a false positive for the
comment safety check. (#434)<=> operator (#432 - thank you @kathykwon!)/* comment */) on a single line would cause sqlfmt
to not include all comments in formatted output (#419 - thank you @aersam!)union distinct tokens (#417 - thank you, @paschmaria!)the contents of jinja blocks are now indented if the block wraps onto multiple rows (#403). This is now the proper sqlfmt style:
select
some_field,
{% for some_item in some_sequence %}
some_function({{ some_item }}){% if not loop.last %}, {% endif %}
{% endfor %}
While in this simple example the new style makes it less clear
that some_field and some_function are at the
same SQL depth, the formatting of complex files with nested jinja blocks is much improved.
For example:
{%- for col in cols -%}
{%- if col.column.lower() not in remove | map(
"lower"
) and col.column.lower() not in exclude | map("lower") -%}
{% do include_cols.append(col) %}
{%- endif %}
{%- endfor %}
See also this discussion. Thank you @dave-connors-3 and @alrocar!
sqlfmt now supports all Postgres frame clauses, not just those that start with rows between. (#404)
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!){% endif %}) to a line could cause bad formatting of everything on that linecreate <object> ... clone statements (#313).*.sql and *.sql.jinja, even those with other dots in their filenames (#354 - thank you @ysmilda!).{% call %} blocks with arguments like {% call(foo) bar(baz) %} would cause a parsing error (#353 - thank you @IgnorantWalking!).--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.RecursionError (#343 - thank you @kcem-flyr!).{% set %} and {% call %} blocks would cause a parsing error (#338 - thank you @AndrewLane!).is [not] distinct from as a word operator (#327 - thank you @IgnorantWalking, @kadekillary!).{% call %} blocks that called a macro that wasn't statement caused a parsing error (#335 - thank you @AndrewLane!).{% materialization ... %} and {% call statement(...) %} blocks (#309).{% endmacro %}, {% endtest %}, {% endcall %}, or {% endmaterialization %} tag.create warehouse and alter warehouse statements (#312, #299).alter function and drop function statements (#310, #311), and Snowflake's create external function statements (#322).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!).return(return_())).delete statements and the associated keywords using and returning (#281).grant and revoke statements and all associated keywords (#283).create function statements and all associated keywords (#282).explain keyword (#280).table<a int64, b bytes(5), c string>.$foo as ordinary identifiers.tomli dependency.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.array<float64>[1, 2] are now supported, and spaces will no longer be inserted around < and > (#212).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).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).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.--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.python -m sqlfmt.between ... and ..., especially in situations where the source includes a line break (#207).%s and %(name)s (#198 - thank you @snorkysnark!).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).--dialect clickhouse option, sqlfmt will not lowercase names that could be case-sensitive in ClickHouse, like function names, aliases, etc. (#193 - thank you @Shlomixg!).offset() function and its brackets.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.(()) or ()[].~, the jinja string concatenation operator (#182){% else %} and {% elif %} statementsfiles as a List[pathlib.Path] instead of a List[str]pyproject.toml file. See README for more information (#90)--exclude option to specify a glob of files to exclude from formatting (#131)$1) and snowflake stages (@my_stage) as identifiersbetween operator's and keyword (#124 - thank you @WestComputing!)pipx install sqlfmt[jinjafmt]). If black is installed, but you do not want to use this feature, you can disable it with the command-line option --no-jinjafmt@>, ||/, ?-| (#105)--single-process, to force single-processing, even when formatting many filesmake profiling- as the files argument--quiet option-- fmt: off and -- fmt: on comments in sql fileswindow and qualify# comment)Your coding agent can read these notes before it upgrades. Set up the MCP server →