sqlfmt Documentation

sqlfmt is a command-line tool for formatting .sql and .sql.jinja files.

Warning: sqlfmt may occasionally break SQL syntax. It is highly recommended to run it only on files tracked by version control (e.g., git) so changes can be easily reverted.

# List all commands and options
sqlfmt --help

# Format all SQL files in the current directory and subdirectories
sqlfmt .

# Check if files are formatted without modifying them (exits with code 1 if unformatted)
sqlfmt --check .

# Show a diff of changes required to format files
sqlfmt --diff .

# Format code from standard input (stdin) and output to stdout
echo "select 1" | sqlfmt -

When using sqlfmt with dbt, it is recommended to exclude the target and dbt_packages directories to avoid unnecessary processing of generated or dependency files.

[tool.sqlfmt]
exclude = ["target/**/*", "dbt_packages/**/*"]

You can install sqlfmt using pip or other Python package managers like pipx or poetry. Ensure you are using Python 3.9 or above and have an active virtual environment. Use the [jinjafmt] extra for Jinja support.

pip install "shandy-sqlfmt[jinjafmt]"

To contribute to sqlfmt or run tests locally, follow these steps to set up your environment using uv:

1. Install uv and make.
2. Clone the repository and enter the directory.
3. Synchronize the environment with all extras (including jinjafmt and sqlfmt_primer) using uv sync.
4. Run tests and linters using make or by calling tools individually via uv run.

# Install dependencies and extras
uv sync --all-groups --all-extras

# Run all tests and linters
make

# Or run tools individually
uv run pytest
uv run ruff
uv run mypy

The recommended way to install sqlfmt is using uv. This installs the tool into an isolated environment and adds it to your PATH. Note that the PyPI package name is shandy-sqlfmt, not sqlfmt.

To install with Jinja formatting support, use the [jinjafmt] extra.

# 1. Install uv (if not already installed)
curl -LsSf https://astral.sh/uv/install.sh | sh

# 2. Install sqlfmt with Jinja support
uv tool install "shandy-sqlfmt[jinjafmt]"

# 3. Verify installation
sqlfmt

In sqlfmt, a Dialect is an abstract base class used to define the grammar and lexing behavior for specific SQL flavors. Each dialect is responsible for providing a set of RULES (a list of Rule objects) that govern how the SQL is parsed and formatted.

Key characteristics of a dialect include:

• RULES: A list of rules, which must include a main key for the primary lexing loop.
• case_sensitive_names: A boolean flag determining if identifiers are treated as case-sensitive (e.g., ClickHouse sets this to True, while others default to False).
• get_rules(): A method that returns the dialect's rules sorted by priority.
• initialize_analyzer(): A method that returns an Analyzer instance configured with the dialect's specific rules and case-sensitivity settings.

You can persist sqlfmt settings in a pyproject.toml file under the [tool.sqlfmt] section. Command-line options will override these settings.

[tool.sqlfmt]
line-length = 100
dialect = "clickhouse"
exclude = ["target/**/*", "dbt_packages/**/*"]

sqlfmt uses a hierarchical search strategy to find its configuration:

1. Explicit Path: If a configuration path is explicitly provided, that file is used.
2. Common Parent Search: If no path is provided, sqlfmt identifies the common parent directory of all files passed to the command. It then searches for pyproject.toml in that directory and its ancestors (moving upwards).
3. Fallback: If no common parent can be determined (for example, if stdin is used), it searches starting from the current working directory (cwd).

The first pyproject.toml found in this search order is used.

The sqlfmt command is used to format SQL files. You can provide one or many paths to SQL files or directories as arguments, or use - to read from stdin.

Exit Codes:

• 0: Success.
• 1: Failed check (when using --check or --diff and files require formatting).
• 2: A handled exception caused by errors in one or more user code files.

Basic Usage Examples:

# Format all files in the current directory
sqlfmt .

# Format a specific file
sqlfmt path/to/file.sql

# Check formatting without writing changes (exits with 1 if changes needed)
sqlfmt . --check

# Print a diff of changes without writing to files
sqlfmt . --diff

# Format text from stdin and write to stdout
sqlfmt -

sqlfmt looks for configuration settings in a pyproject.toml file. It specifically reads settings located under the [tool.sqlfmt] section.

When running sqlfmt on multiple files, it searches for the pyproject.toml file starting from the lowest common parent directory of all provided files and moving upwards through parent directories. If no common parent exists (e.g., when using stdin), it defaults to the current working directory (cwd).

If you provide a specific configuration path via the CLI (implied by the config_path parameter in the loading logic), the automatic search for pyproject.toml is skipped.

[tool.sqlfmt]
dialect = "postgres"
# Other supported options from Mode fields

The only configurable aspect of the sqlfmt style is the line length. The default is 88 characters.

# Using the CLI flag
sqlfmt . --line-length 100

# Using the short flag
sqlfmt . -l 100