sqlalign
GitHub

Every setting sqlalign has, with its type, its default, and the same SQL rendered under each value. The examples on this page are produced by running the formatter when the page is built — none of them is written by hand, so none of them can drift away from what the tool actually does.

Settings go in a .sqlalign.toml file under [tool.sqlalign], and most have a CLI flag that overrides the file for one run. See Configuration for how the two combine, and CLI reference for the flags.

All settings#

keyword_case #

Whether SQL keywords are upper- or lowercase.

Type
str
Default
'upper'

Applies to keywords and function names, never to identifiers or string literals — a quoted identifier keeps the spelling you gave it, because in most dialects that spelling is load-bearing.

Given this input:

select customer_id, email, signup_date from customers where active;
'upper' · default
SELECT customer_id
     , email
     , signup_date
FROM customers
WHERE active;
'lower'
select customer_id
     , email
     , signup_date
from customers
where active;

align #

The master switch for columnar alignment.

Type
bool
Default
True

With it off, every alignment column collapses to a single space and you get the same line structure without the padding — which is what nine of ten published style guides ask for. Nothing else about the layout changes, so turning it off is not a different style so much as the same style unpadded.

Given this input:

select cust.id, cust.email, ord.total from customers cust inner join orders ord on ord.customer_id = cust.id where ord.total > 0 and cust.segment = 'enterprise';
True · default
SELECT cust.id
     , cust.email
     , ord.total
FROM customers    cust
INNER JOIN orders ord ON ord.customer_id = cust.id
WHERE ord.total    > 0
  AND cust.segment = 'enterprise';
False
SELECT cust.id
     , cust.email
     , ord.total
FROM customers cust
INNER JOIN orders ord ON ord.customer_id = cust.id
WHERE ord.total > 0
  AND cust.segment = 'enterprise';

align_targets #

Which columns are aligned, when align is on.

Type
frozenset
Default
the default set

A set of names. aliases is shorthand for column_aliases plus table_aliases. Anything not listed simply takes one space, so you can keep the join-condition column and drop the rest.

Available: aliases, case_results, column_aliases, column_constraints, column_types, join_conditions, operators, table_aliases, table_names

Given this input:

select cust.id, cust.email, ord.total from customers cust inner join orders ord on ord.customer_id = cust.id where ord.total > 0 and cust.segment = 'enterprise';
the default set · default
SELECT cust.id
     , cust.email
     , ord.total
FROM customers    cust
INNER JOIN orders ord ON ord.customer_id = cust.id
WHERE ord.total    > 0
  AND cust.segment = 'enterprise';
{operators}
SELECT cust.id
     , cust.email
     , ord.total
FROM customers cust
INNER JOIN orders ord ON ord.customer_id = cust.id
WHERE ord.total    > 0
  AND cust.segment = 'enterprise';
frozenset()
SELECT cust.id
     , cust.email
     , ord.total
FROM customers cust
INNER JOIN orders ord ON ord.customer_id = cust.id
WHERE ord.total > 0
  AND cust.segment = 'enterprise';

comma_position #

Whether a list's commas lead or trail their line.

Type
str
Default
'leading'

Leading commas put the separator where the eye already is when scanning a column of items, and make adding or removing the last item a one-line diff.

Given this input:

select customer_id, email, signup_date from customers where active;
'leading' · default
SELECT customer_id
     , email
     , signup_date
FROM customers
WHERE active;
'trailing'
SELECT customer_id,
       email,
       signup_date
FROM customers
WHERE active;

boolean_operator_position #

Whether AND / OR lead or trail their line.

Type
str
Default
'leading'

The same choice as comma_position, for predicates. Leading operators right-align under the clause keyword, so WHERE, AND and OR all end in the same column.

Given this input:

select cust.id, cust.email, ord.total from customers cust inner join orders ord on ord.customer_id = cust.id where ord.total > 0 and cust.segment = 'enterprise';
'leading' · default
SELECT cust.id
     , cust.email
     , ord.total
FROM customers    cust
INNER JOIN orders ord ON ord.customer_id = cust.id
WHERE ord.total    > 0
  AND cust.segment = 'enterprise';
'trailing'
SELECT cust.id
     , cust.email
     , ord.total
FROM customers    cust
INNER JOIN orders ord ON ord.customer_id = cust.id
WHERE ord.total    > 0 AND
      cust.segment = 'enterprise';

on_placement #

Whether a join's ON rides the join line or takes its own.

Type
str
Default
'inline'

Inline keeps a join to one row and lets the ON column align across the whole FROM block. On its own line it drops below, indented, which reads better when conditions are long.

Given this input:

select cust.id, cust.email, ord.total from customers cust inner join orders ord on ord.customer_id = cust.id where ord.total > 0 and cust.segment = 'enterprise';
'inline' · default
SELECT cust.id
     , cust.email
     , ord.total
FROM customers    cust
INNER JOIN orders ord ON ord.customer_id = cust.id
WHERE ord.total    > 0
  AND cust.segment = 'enterprise';
'own_line'
SELECT cust.id
     , cust.email
     , ord.total
FROM customers    cust
INNER JOIN orders ord
  ON ord.customer_id = cust.id
WHERE ord.total    > 0
  AND cust.segment = 'enterprise';

select_placement #

Whether the first select item rides the SELECT line.

Type
str
Default
'inline'

Inline starts the list on the SELECT row itself. own_line puts every item below, indented by select_indent — the dbt and GitLab convention.

Given this input:

select customer_id, email, signup_date from customers where active;
'inline' · default
SELECT customer_id
     , email
     , signup_date
FROM customers
WHERE active;
'own_line'
SELECT
  customer_id
, email
, signup_date
FROM customers
WHERE active;

select_indent #

How far the select list is indented under SELECT.

Type
int
Default
2

Only applies when select_placement is own_line; it is greyed out in the GUI otherwise, and ignored here.

Rendered below with select_placement = own_line, since the setting does nothing without it.

Given this input:

select customer_id, email, signup_date from customers where active;
2 · default
SELECT
  customer_id
, email
, signup_date
FROM customers
WHERE active;
4
SELECT
    customer_id
  , email
  , signup_date
FROM customers
WHERE active;

clause_keyword_align #

Whether root clause keywords are left-aligned or form a river.

Type
str
Default
'left'

A river right-aligns every root keyword so its last character lands on a shared gutter, which puts every clause body in one column. It is the arrangement Holywell's style guide describes.

Given this input:

select cust.id, cust.email, ord.total from customers cust inner join orders ord on ord.customer_id = cust.id where ord.total > 0 and cust.segment = 'enterprise';
'left' · default
SELECT cust.id
     , cust.email
     , ord.total
FROM customers    cust
INNER JOIN orders ord ON ord.customer_id = cust.id
WHERE ord.total    > 0
  AND cust.segment = 'enterprise';
'river'
SELECT cust.id
     , cust.email
     , ord.total
  FROM customers         cust
       INNER JOIN orders ord ON ord.customer_id = cust.id
 WHERE ord.total    > 0
   AND cust.segment = 'enterprise';

river_gutter #

How wide the river's gutter is.

Type
int
Default
6

The column every root keyword ends on. Fixed rather than derived from the widest keyword present, so a clause added later cannot shift the whole statement sideways.

Rendered below with clause_keyword_align = river, since the setting does nothing without it.

Given this input:

select cust.id, cust.email, ord.total from customers cust inner join orders ord on ord.customer_id = cust.id where ord.total > 0 and cust.segment = 'enterprise';
6 · default
SELECT cust.id
     , cust.email
     , ord.total
  FROM customers         cust
       INNER JOIN orders ord ON ord.customer_id = cust.id
 WHERE ord.total    > 0
   AND cust.segment = 'enterprise';
10
    SELECT cust.id
         , cust.email
         , ord.total
      FROM customers         cust
           INNER JOIN orders ord ON ord.customer_id = cust.id
     WHERE ord.total    > 0
       AND cust.segment = 'enterprise';

table_alias_style #

Whether a table alias is written bare or with AS.

Type
str
Default
'bare'

FROM orders ord and FROM orders AS ord parse identically, so which one comes out is sqlalign's decision rather than yours — which is exactly why it is a setting.

Given this input:

select cust.id as customer, sum(ord.total) as lifetime_value from customers cust join orders ord on ord.customer_id = cust.id group by cust.id;
'bare' · default
SELECT cust.id        AS customer
     , SUM(ord.total) AS lifetime_value
FROM customers cust
JOIN orders    ord ON ord.customer_id = cust.id
GROUP BY cust.id;
'as'
SELECT cust.id        AS customer
     , SUM(ord.total) AS lifetime_value
FROM customers AS cust
JOIN orders    AS ord ON ord.customer_id = cust.id
GROUP BY cust.id;

neq_style #

How the not-equal operator is spelled.

Type
str
Default
'!='

<> and != parse to the same node, so the distinction is lost before layout ever runs and one of them has to be chosen. Set it to whatever your linter expects.

Given this input:

select a from t where status != 'draft' and total <> 0;
'!=' · default
SELECT a
FROM t
WHERE status != 'draft'
  AND total  != 0;
'<>'
SELECT a
FROM t
WHERE status <> 'draft'
  AND total  <> 0;

decimal_style #

How the fixed-point type is spelled.

Type
str
Default
'NUMERIC'

DECIMAL and NUMERIC are synonyms that parse to one node — the same forced choice as neq_style.

Given this input:

create table t (amount decimal(10, 2), rate numeric(5, 4));
'NUMERIC' · default
CREATE TABLE t (
    amount NUMERIC(10, 2)
  , rate   NUMERIC(5, 4)
);
'DECIMAL'
CREATE TABLE t (
    amount DECIMAL(10, 2)
  , rate   DECIMAL(5, 4)
);

width #

The column the formatter tries to stay inside.

Type
Width
Default
100

A trigger for the breaks that are modelled, not a hard ceiling: an expression with no modelled break stays on one line however long it is. 0 turns the limit off entirely.

0 turns the limit off, which is the same as a width nothing reaches — so it is described here rather than shown.

Given this input:

select customer_id, coalesce(sum(case when status = 'paid' then amount end), 0.00) as paid_total from ledger group by customer_id;
100 · default
SELECT customer_id
     , COALESCE(SUM(CASE WHEN status = 'paid' THEN amount END), 0.00) AS paid_total
FROM ledger
GROUP BY customer_id;
60
SELECT customer_id
     , COALESCE(SUM(CASE WHEN status = 'paid'
                           THEN amount
                     END), 0.00) AS paid_total
FROM ledger
GROUP BY customer_id;

blank_lines_between_statements #

How many blank lines separate two statements.

Type
int or unset
Default
None

Unset, the house rule applies: exactly one blank line between two statements when both are multi-line, and otherwise whatever adjacency the input had. A number overrides that for every pair.

Given this input:

select a from t;
update u set b = 2 where c = 3;
None · default
SELECT a
FROM t;

UPDATE u
SET b = 2
WHERE c = 3;
0
SELECT a
FROM t;
UPDATE u
SET b = 2
WHERE c = 3;
2
SELECT a
FROM t;


UPDATE u
SET b = 2
WHERE c = 3;

body_blank_lines #

How many blank lines separate statements inside a $$ body.

Type
int
Default
1

A procedure body has its own vertical rhythm, separate from the one between top-level statements.

Given this input:

create function bump() returns int as $$ begin update t set n = n + 1; return 1; end; $$ language plpgsql;
1 · default
CREATE FUNCTION bump()
RETURNS INT
LANGUAGE plpgsql
AS $$

BEGIN

UPDATE t
SET n = n + 1;

RETURN 1;

END;
$$;
0
CREATE FUNCTION bump()
RETURNS INT
LANGUAGE plpgsql
AS $$

BEGIN
UPDATE t
SET n = n + 1;
RETURN 1;
END;
$$;

format_dollar_bodies #

Whether $$ … $$ procedure bodies are formatted at all.

Type
bool
Default
True

With it off the whole CREATE FUNCTION is left byte-identical. Useful for staging a migration: format the repository, leave the procedures for a later pass.

Given this input:

create function bump() returns int as $$ begin update t set n = n + 1; return 1; end; $$ language plpgsql;
True · default
CREATE FUNCTION bump()
RETURNS INT
LANGUAGE plpgsql
AS $$

BEGIN

UPDATE t
SET n = n + 1;

RETURN 1;

END;
$$;
False
create function bump() returns int as $$ begin update t set n = n + 1; return 1; end; $$ language plpgsql;

protect_templating #

Whether Jinja / dbt templating is masked before parsing.

Type
bool
Default
True

{{ ref('x') }} is not SQL and sqlglot cannot parse it. With this on, each template expression is replaced by a placeholder for the duration of the parse and restored afterwards, so a dbt model formats instead of declining.

Given this input:

select * from {{ ref('stg_orders') }} where status = 'paid';
True · default
SELECT *
FROM {{ ref('stg_orders') }}
WHERE status = 'paid';
False

Declined — passed through unchanged.
parse error

select * from {{ ref('stg_orders') }} where status = 'paid';

Presets#

A preset is a named bundle of the settings above. --preset NAME applies one, and any explicit setting still wins over it.

Given this input:

select customer_id, email, signup_date from customers where active;
compact
SELECT customer_id
     , email
     , signup_date
FROM customers
WHERE active;
dbt
select
    customer_id,
    email,
    signup_date
from customers
where active;
gitlab
SELECT
  customer_id,
  email,
  signup_date
FROM customers
WHERE active;
house
SELECT customer_id
     , email
     , signup_date
FROM customers
WHERE active;
river
SELECT customer_id
     , email
     , signup_date
  FROM customers
 WHERE active;
trailing
SELECT customer_id,
       email,
       signup_date
FROM customers
WHERE active;