sqlfmt CHANGELOG

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

Unreleased

0.11.1 - 2022-09-17

Features

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

Documentation

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

0.11.0 - 2022-08-21

Breaking API Changes

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

Features

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

Formatting Changes + Bug Fixes

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

0.10.1 - 2022-08-05

Features

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

Formatting Changes + Bug Fixes

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

0.10.0 - 2022-08-02

Features

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

Formatting Changes + Bug Fixes

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

0.9.0 - 2022-06-02

Features

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

Fixes

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

Primer

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

0.8.0 - 2022-05-04

Formatting Changes

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

0.7.0 - 2022-04-24

Breaking Changes

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

Features

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

0.6.0 - 2022-03-21

Formatting Fixes

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

0.5.1 - 2022-02-08

Fixes

• adds support for numbered field references (e.g., $1) and snowflake stages (@my_stage) as identifiers
• do not split lines before the between operator's and keyword (#124 - thank you @WestComputing!)

0.5.0 - 2022-02-02

Formatting changes

• formats the contents of jinja tags (the code between the curlies) using black, the Python formatter. If black is not already installed, you can use this feature by re-installing sqlfmt with the jinjafmt extra (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
• no longer inserts spaces around colons (#103 - thank you @noel!)

Fixes

• adds "cross join" to list of supported join types. No longer merges the "cross" keyword with the previous statement (#110 - thank you @rdeese!)
• adds support for every valid operator in postgresql, even the weird ones, like @>, ||/, ?-| (#105)

0.4.3 - 2022-01-31

Fixes

• removes an unnecessary dependency on black that broke installation (#98 - thank you @ljhopkins2!)

0.4.2 - 2022-01-26

Features

• adds an option, --single-process, to force single-processing, even when formatting many files

Under the Hood

• when formatting multiple files, uses multiprocessing for ~3x faster throughput

0.4.1 - 2022-01-20

Formatting changes

• preserves leading and trailing blank lines when merging lines with content
• no longer prints whitespace on blank lines

0.4.0 - 2022-01-20

Formatting changes

• adds special support for jinja, with new formatting rules for jinja statements, expressions, and blocks
• safely standardizes whitespace around jinja statements and expressions
• merges lines within and across jinja blocks while balancing start/end statements
• jinja block tags can no longr be indented farther than any of their contents

Features

• developers can now easily profile sqlfmt performance (after installing the sqlfmt_primer extra) with make profiling

Fixes

• no longer fails with a parsing error if "end" is used as a name in the query

Under the Hood

• refactors lexing using typical callable architecture for more flexibility
• adds new token types for jinja statements and blocks
• refactors calculation of node and line depth to include jinja blocks
• adds some caching to line properties for performance enhancements

0.3.0 - 2021-12-16

Formatting changes

• refactors comment parsing for improved formatting of comments and merging around comments
• standardizes comments and splits long comments onto multiple lines if necessary
• improves splitting and merging of lines with operators (like "+", "as", "on", etc.)
• improves formatting of queries that use leading commas
• improves merging of statements with chained brackets ("( something ) + ( something_else )" )

Features

• adds a simple cache and skips formatting files that have not changed since last successful run
• improves the welcome message displayed when running sqlfmt with no arguments

Fixes

• supports all postgres and bigquery string literals and quoted identifiers, including triple quotes, escaped quotes, dollar-delimited, etc.
• no longer fails with a parsing error when encountering a semicolon
• properly delineates between "*" as "all fields" and as the multiplication operator

0.2.1 - 2021-12-04

Performance

• refactors line splitting algorithm and creating a line from nodes; provides 3x speedup of sqlfmt (now formats roughly 100 files/sec)

Fixes

• refactored lexer for better parsing of tokens on multiple lines

0.2.0 - 2021-11-16

Features

• can format text through stdin by passing - as the files argument
• supports --quiet option
• supports -- fmt: off and -- fmt: on comments in sql files
• supports more select keywords, like window and qualify
• supports back-ticks for quoting relation names
• supports MySQL-style comments (# comment)
• adds a new cli tool, sqlfmt_primer, to run sqlfmt against known OSS projects to gauge changes

Fixes

• fixes parsing of jinja tags (use lazy regex so we don't match multiple tags at once)
• fixes issue with whitespace around jinja tags
• fixes capitalization of word operators (on, and, etc.)
• fixes parsing error caused by comments without leading spaces

[0.1.0] - 2021-11-08

Features

• initial release
• discovers .sql and .sql.jinja files
• formats the files it discovers
• supports --check and --diff options
• supports --no-color