Metadata-Version: 2.5
Name: sqlalign
Version: 1.0.2
Summary: A SQL formatter that cannot change what your SQL means
Project-URL: Homepage, https://sqlalign.lumaru.app/
Project-URL: Documentation, https://sqlalign.lumaru.app/v1/
Project-URL: Source, https://github.com/pua2/sqlalign
Project-URL: Issues, https://github.com/pua2/sqlalign/issues
Author: Pavan Amin
License-Expression: MIT
License-File: LICENSE
Keywords: dbt,formatter,plpgsql,postgres,redshift,sql,tsql
Classifier: Development Status :: 5 - Production/Stable
Classifier: Environment :: Console
Classifier: Intended Audience :: Developers
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Programming Language :: SQL
Classifier: Topic :: Database
Classifier: Topic :: Software Development :: Quality Assurance
Classifier: Topic :: Text Processing :: Filters
Classifier: Typing :: Typed
Requires-Python: >=3.12
Requires-Dist: sqlglot<30.15,>=30.14
Provides-Extra: lint
Requires-Dist: sqlfluff<5,>=4.2; extra == 'lint'
Description-Content-Type: text/markdown

# sqlalign

A SQL formatter for **Postgres**, **Redshift** and **SQL Server** that **cannot change what your
SQL means**. Every statement it formats is re-parsed and AST-compared against the
input; if the output would differ semantically — or if the engine doesn't fully
model the construct — the statement is passed through **byte-identical** with a
warning instead. It reformats presentation and nothing else: identifiers, string
literals, cast form (`::` vs `CAST`), `GROUP BY` references, and alias choices all
survive exactly as written.

Its layout is **columnar alignment**: operators, aliases, `AS` clauses, and
`ON`/`AND` conditions are padded into vertical columns by a fixpoint resolver —
alignment is the layout engine itself, not a post-pass over already-printed text.
It applies that same engine *inside* dollar-quoted (`$$`) plpgsql procedure and
function bodies.

```sql
FROM customers               cust
INNER JOIN orders            ord        ON ord.customer_id     = cust.customer_id
LEFT JOIN order_line_items   line_items ON line_items.order_id = ord.order_id
LEFT JOIN shipping_addresses addr       ON addr.order_id       = ord.order_id
                                       AND addr.address_type   = 'shipping'
```

## How it compares

Alignment is not unique to sqlalign, and this README won't pretend otherwise:
**sqlfluff** aligns column aliases (`layout:type:alias_expression` with
`spacing_before = align`), SSMS ships alignment options, and DataGrip aligns a
good deal including inside `$$` bodies. **pgFormatter**, **DataGrip**, and
**prettier-plugin-sql-cst** all format `$$` bodies too.

What is actually distinctive here:

- **The `ON`/`AND` condition column.** Aligning every JOIN's `ON` and `AND`
  conditions into one column spanning the whole `FROM` block (see above) is not
  something the surveyed tools do — their analogues break the line instead of
  padding it.
- **Alignment as the engine, not a bolt-on.** sqlalign lays out lines first and
  resolves every alignment column in a second fixpoint pass, so alignment
  composes with wrapping instead of fighting it. For contrast, sql-formatter
  removed its `tabulateAlias` and `commaPosition` options in v14 — both were
  post-hoc rewrites over already-printed text.
- **The semantic guarantee above.** Formatters that also apply lint fixes can
  rewrite your SQL — running `sqlfluff fix` on a query will happily turn `JOIN`
  into `INNER JOIN`, insert `AS`, and reorder your `ON` operands. sqlalign
  structurally cannot: a changed AST means the statement is passed through
  untouched.

sqlalign deliberately does **not** lint. It won't unify your cast styles, force
aliases, or make `GROUP BY` references consistent — that's sqlfluff's job, and
the two are designed to run together.

**Want only some of the alignment?** `--align-targets` picks which columns are
padded; anything left out collapses to a single space. `--no-align` is the
shorthand for none of them.

| Target | Aligns |
|---|---|
| `aliases` | both of the next two |
| `column_aliases` | `AS x` in a select list |
| `table_aliases` | the alias in `FROM orders o` |
| `table_names` | the table after each `FROM`/`JOIN`, padding the keyword out to a shared column — **opt-in**, not part of the house style |
| `operators` | `=`, `!=`, `<`, `LIKE`, `IS` … in `WHERE`/`ON`/`HAVING`/`CASE` |
| `join_conditions` | the `ON`/`AND` column across a whole `FROM` block |
| `case_results` | `THEN` in a short-form `CASE` |
| `column_types` | column types in `CREATE TABLE` |
| `column_constraints` | `NOT NULL`/`DEFAULT`, and Redshift `ENCODE` |

```sh
sqlalign --align-targets aliases,operators query.sql
```

`table_names` is the one target the default set leaves out, because it changes
the shape of the `FROM` block rather than refining it:

```sql
-- house                        -- with table_names
FROM customers      cust        FROM       customers cust
LEFT JOIN orders    ord         LEFT JOIN  orders    ord
INNER JOIN payments pay         INNER JOIN payments  pay
```

```sh
sqlalign --align-targets aliases,table_names,operators,join_conditions query.sql
```

**Prefer trailing commas?** `--comma-position trailing` moves every separator
comma to the end of the preceding line (including onto the last line of a
multi-line item, and onto a CTE's closing paren):

```sql
SELECT cust.customer_id,
       cust.email,
       ord.total
FROM customers cust
```

**Don't want the alignment?** `--no-align` keeps sqlalign's line structure and
drops the padding, which is what most published SQL style guides call for:

```sql
FROM customers cust
INNER JOIN orders ord ON ord.customer_id = cust.customer_id
LEFT JOIN order_line_items line_items ON line_items.order_id = ord.order_id
LEFT JOIN shipping_addresses addr ON addr.order_id = ord.order_id
  AND addr.address_type = 'shipping'
```

## Install

```sh
pip install sqlalign
```

That puts a `sqlalign` command on your PATH. To keep it out of a project's
environment, `uv tool install sqlalign` does the same thing in isolation.

`--lint` runs sqlfluff over the formatted result and needs the optional extra:

```sh
pip install 'sqlalign[lint]'
```

Requires Python ≥ 3.12. Runtime dependency: `sqlglot` (pinned to the 30.14.x
line — the layout engine depends on exact AST shapes; see `pyproject.toml`).

To work on sqlalign itself, install from a clone instead — that adds the dev
tools (`pytest`, `ruff`, `sqlfluff`):

```sh
git clone https://github.com/pua2/sqlalign && cd sqlalign
uv sync
```

## Usage

```sh
sqlalign query.sql                 # format in place (rewrites the file)
sqlalign .                         # every *.sql under here, recursively
sqlalign --stdout query.sql        # print the formatted result, leave the file
sqlalign --check *.sql             # exit non-zero if any file is not formatted
sqlalign --dialect redshift ddl.sql
sqlalign --dialect tsql query.sql   # SQL Server: TOP, [brackets]
sqlalign --width 120 query.sql
```

### Before → after

Input (`tests/fixtures/input/13.sql`):

```sql
-- #13: multi-character aliases (mixed lengths)
select cust.customer_id, cust.email, ord.order_id, ord.total, line_items.product_id, line_items.quantity, addr.city from customers cust inner join orders ord on ord.customer_id = cust.customer_id left join order_line_items line_items on line_items.order_id = ord.order_id left join shipping_addresses addr on addr.order_id = ord.order_id and addr.address_type = 'shipping' where ord.order_date >= '2026-07-01' and cust.segment = 'enterprise';
```

Output:

```sql
-- #13: multi-character aliases (mixed lengths)
SELECT cust.customer_id
     , cust.email
     , ord.order_id
     , ord.total
     , line_items.product_id
     , line_items.quantity
     , addr.city
FROM customers               cust
INNER JOIN orders            ord        ON ord.customer_id     = cust.customer_id
LEFT JOIN order_line_items   line_items ON line_items.order_id = ord.order_id
LEFT JOIN shipping_addresses addr       ON addr.order_id       = ord.order_id
                                       AND addr.address_type   = 'shipping'
WHERE ord.order_date >= '2026-07-01'
  AND cust.segment    = 'enterprise';
```

## dbt / Jinja

Templated SQL isn't valid SQL, so most formatters decline it. sqlalign masks each
template expression with a **same-width** placeholder, formats normally, then puts
the original back — so alignment is computed against the real text width:

```sql
SELECT o.id
     , o.total
FROM {{ ref('orders') }}    o
JOIN {{ ref('customers') }} c ON c.id = o.customer_id
```

`{{ … }}`, `{% … %}` and `{# … #}` are recognised. An expression too short to hold
a unique placeholder (`{{x}}`) makes the file pass through untouched rather than be
approximated. Turn it off with `--no-protect-templating`.

## Configuration

Commit your team's style rather than passing flags every time. sqlalign reads a
`.sqlalign.toml`, or a `[tool.sqlalign]` table in `pyproject.toml`, discovered by
walking up from each file being formatted — so a repo can hold one config at its
root and a subdirectory can override it.

```toml
# .sqlalign.toml
width                     = 100            # 0 turns wrapping off
align                     = true
align_targets             = ["aliases", "operators", "join_conditions"]
comma_position            = "leading"      # or "trailing"
boolean_operator_position = "leading"      # or "trailing"
on_placement              = "inline"       # or "own_line"
format_dollar_bodies      = true
protect_templating        = true       # mask Jinja/dbt before formatting
exclude                   = ["vendor/*", "*.gen.sql"]
# blank_lines_between_statements = 1   # unset = one blank line only between
#                                      # two multi-line statements
neq_style                 = "!="           # or "<>"
decimal_style             = "NUMERIC"      # or "DECIMAL"
keyword_case              = "upper"        # or "lower"
table_alias_style         = "bare"         # or "as" -> FROM orders AS o
select_placement          = "inline"       # or "own_line"
select_indent             = 2              # when the list starts below SELECT
clause_keyword_align      = "left"         # or "river"
body_blank_lines          = 1              # inside a $$ body
river_gutter              = 6
```

Start from a **preset** and override what you want:

| Preset | What it is |
|---|---|
| `house` | the columnar default — aligned, leading separators, `ON` inline |
| `compact` | same line structure, no alignment padding (9 of 10 style guides are unpadded) |
| `trailing` | keeps the alignment, moves commas and `AND`/`OR` to end of line |
| `dbt` | lowercase keywords, list stacked under a bare `select` at 4, trailing commas, no padding (one deviation: a CTE body indents 2, not 4) |
| `river` | [Holywell's guide](https://www.sqlstyle.guide/) — root keywords right-aligned to a 6-column gutter, joins on the far side of it, otherwise unpadded |
| `gitlab` | [GitLab's published guide](https://handbook.gitlab.com/handbook/enterprise-data/platform/sql-style-guide/) — list stacked at 2, trailing commas, `ON` on its own line, `AS` on table aliases, and column aliases the only thing aligned |

```toml
preset         = "compact"
comma_position = "trailing"   # layers on top of the preset
```

Precedence is **built-in defaults → preset → config file → command-line flags**.
`sqlalign --show-config file.sql` prints the effective settings (as TOML you can
paste into a config) and says which file they came from. `--isolated` ignores any
config; `--config PATH` uses a specific one.

An unknown key is an **error**, not a silent no-op — a typo in a committed config
would otherwise mean a team believes it has a setting it doesn't have.
`--no-strict-config` downgrades that to a warning.

## Flags

| Flag | Default | Effect |
|------|---------|--------|
| `files …` | — | files or directories (a directory is searched recursively for `*.sql`) |
| `--exclude GLOB` | none | skip files matching this glob (repeatable; also `exclude` in config) |
| `--blank-lines-between-statements N` | auto | force N blank lines between every pair of statements |
| `--check` | off | write nothing; name the files that would change; exit `1` if any would |
| `--diff` | off | write nothing; print a unified diff of what would change; exit `1` if any would |
| `--stdout` | off | write the result to stdout instead of rewriting the file |
| `--dialect {postgres,redshift,tsql}` | `postgres` | parse/emit dialect |
| `--width N` | `100` | target line width for wrapping decisions; `0` turns wrapping off |
| `--no-align` | off | emit one space between tokens instead of padding them into columns — same line structure, no alignment |
| `--align-targets a,b,…` | all but `table_names` | which alignment columns to keep (see below) |
| `--line-ending {auto,lf,crlf}` | `auto` | line endings to write; `auto` preserves each file's own |
| `--comma-position {leading,trailing}` | `leading` | where the separator comma sits in a stacked list |
| `--boolean-operator-position {leading,trailing}` | `leading` | where `AND`/`OR` sit when a predicate spans lines |
| `--on-placement {inline,own_line}` | `inline` | whether a JOIN's `ON` rides the table line or drops below it |
| `--no-format-bodies` | off | leave dollar-quoted (`$$`) procedure/function bodies untouched |
| `--no-protect-templating` | off | don't mask Jinja/dbt template expressions |
| `--neq-style {!=,<>}` | `!=` | spelling for the not-equal operator |
| `--decimal-style {NUMERIC,DECIMAL}` | `NUMERIC` | spelling for the NUMERIC/DECIMAL type |
| `--table-alias-style {bare,as}` | `bare` | print a table alias as `t a` or `t AS a` |
| `--select-placement {inline,own_line}` | `inline` | whether the first select item rides the `SELECT` line |
| `--select-indent N` | `2` | columns the select list indents when it starts below `SELECT` |
| `--clause-keyword-align {left,river}` | `left` | right-align root clause keywords to a gutter |
| `--river-gutter N` | `6` | the column a river aligns them to |
| `--keyword-case {upper,lower}` | `upper` | case for keywords, function names and types |
| `--preset {compact,dbt,gitlab,house,river,trailing}` | `house` | named starting point (flags and config keys layer on top) |
| `--config PATH` | discovered | use a specific config file |
| `--isolated` | off | ignore any config file |
| `--print-sqlfluff-config` | — | print a `.sqlfluff` that lets sqlfluff run alongside sqlalign, and exit |
| `--lint` | off | after formatting, run sqlfluff over the result (needs `pip install 'sqlalign[lint]'`) |
| `--body-blank-lines N` | `1` | blank lines between the elements of a `$$` body |
| `--gui` | — | *(experimental)* open a settings panel with a live preview, and exit |
| `--report` | off | print a coverage summary: how many statements formatted, and what the rest declined on. Adds output without changing the mode — pair with `--check` to survey without writing |
| `--max-declines N` | — | exit 1 if more than N statements pass through unformatted (implies `--report`) |
| `--show-config` | off | print effective settings as TOML and exit |
| `--no-strict-config` | off | warn on unknown config keys instead of failing |

`--neq-style` and `--decimal-style` exist because sqlglot's parser collapses each
of those pairs to a single node, so a spelling has to be chosen when printing.
They are the *only* two places sqlalign picks for you — everything the parser
preserves is passed through as written.

`--check` and `--stdout` are mutually exclusive. Exit codes: `0` success (or
`--check` clean); `1` a `--check` file differs; `2` an unreadable file or a
safety abort (other files in the same run still process).

## Linting inside `$$` bodies

sqlfluff cannot lint a plpgsql body — to its parser the whole body is one string
literal, so a function full of badly-written SQL passes clean. `--lint` closes
that: sqlalign already locates those statements in order to format them, so it
hands them to sqlfluff too, and a finding's line and column are the real ones.

```console
$ sqlalign --check --lint report.sql
L:   1 | P:  17 | CP03 | Function names must be upper case.
All Finished!
== [report.sql] inside $$ bodies (sqlfluff cannot reach these on its own)
L:   8 | P:   1 | AM04 | Query produces an unknown number of result columns. [ambiguous.column_count]
```

See the [guide](docs/guide/getting-started.md) for how it works and what it does
not cover.

## Guarantees & scope

- **Byte-for-byte golden fixtures** — 25 hand-formatted samples in
  `tests/fixtures/expected/` are the executable specification of the style; the
  suite asserts `format(input) == expected` for every one, plus idempotency
  (`format(expected) == expected`) and AST-equivalence.
- **sqlfluff lint gate** — every standard-SQL Postgres expected fixture lints
  clean under `.sqlfluff`. Three rules the house style deliberately conflicts
  with are excluded there, each documented: `ambiguous.column_references` (the
  source GROUP BY reference form is preserved), `aliasing.expression` (aliases
  are not forced), and `convention.casting_style` (`::` vs `CAST(...)` is
  preserved as written). The Redshift and plpgsql fixtures in
  `samples/queries.sql` are outside the gate (dialect / linter-model limits) —
  see `tests/test_sqlfluff_gate.py`.
- **v1 limitations** — constructs the engine does not model (e.g. some `MERGE`
  variants, cursors/`FOR` loops in plpgsql bodies, comments buried inside an
  expression) pass through byte-identical with a warning rather than being
  reformatted. This is by design: correctness over coverage.

## Adding a new golden sample

The fixtures are the spec — grow them, never weaken them:

1. Add the **hand-formatted** query to `samples/queries.sql` under a new
   `-- #N: description` header.
2. Regenerate the split fixtures: `python scripts/build_fixtures.py samples/queries.sql tests/fixtures/expected`.
   Add the corresponding messy `tests/fixtures/input/N.sql`.
3. Run `PYTHONPATH=src pytest -q`. If the new golden fails, either the sample
   exposes a real gap (fix the layout handler) or it's an unmodeled construct
   (it should pass through — confirm the warning, not a wrong render).
4. If the new expected output should lint clean, confirm the sqlfluff gate still
   passes (or document a new exclusion in `.sqlfluff` with a comment).

## Documentation

The documentation site is at **<https://sqlalign.lumaru.app/>**.

The [settings reference](https://sqlalign.lumaru.app/v1/settings.html) is the page to start with: it
shows every setting with the same SQL rendered under each of its values.

To read the site locally:

```sh
python3 -m http.server -d docs 8000    # then open http://localhost:8000/
```

**The site is generated and committed.** GitHub Pages runs no build step, so the
HTML in `docs/v1/` is what ships. Prose is authored in
`docs/guide/*.md`; every configuration example is produced by *running the
formatter* at build time, so no example on the site can drift from what the tool
does. After editing a guide page, a setting, or anything that changes output:

```sh
uv run python tools/build_docs.py     # rebuild docs/v1/
```

`tests/test_docs_site.py` fails if the committed site does not match what the
generator produces, so a forgotten rebuild cannot ship.

Versions are directories. `docs/v1/` is the only one today; a new version means
building into a new directory and adding it to `VERSIONS` in the generator.
Nothing in a built page points outside its own version, so an old one keeps
working untouched.

## Contributing

See [CONTRIBUTING.md](CONTRIBUTING.md). In short: `uv sync`, then `uv run pytest
-q` and `uv run ruff check .` must both be clean — CI runs the same checks on
every pull request. The goldens in `tests/fixtures/expected/` are the
specification, not snapshots.

Release notes are in [CHANGELOG.md](CHANGELOG.md).

## Reference

- **Style reference:** `samples/queries.sql` — the hand-formatted goldens.
- **Lint config:** `.sqlfluff`.
