Skip to main content

Clause alignment

clauseAlignment says where a query's clause keywords stand once its clauses come apart: left, each at the query's own column, or right, each padded so that every keyword ends at one column. That column is set by the widest keyword in the query, and every clause body begins one column to its right. The gap down the page between the two is the river.

Default: left. It is how most SQL is written, and the layout most tools produce.

right is the style of Dimitri Fontaine's The Art of PostgreSQL, and of sqlfmt, the formatter he wrote for it. Keywords on the left of the river, what they act on to the right, so a reader can scan either column on its own.

One query, both values​

select status, count(*) from results join races using (raceid) where date >= :season group by status having count(*) >= 10 order by count(*) desc
select status, count(*)
from results
join races using (raceid)
where date >= :season
group by status
having count(*) >= 10
order by count(*) desc
select status, count(*) from results join races using (raceid) where date >= :season group by status having count(*) >= 10 order by count(*) desc
  select status, count(*)
    from results
    join races using (raceid)
   where date >= :season
group by status
  having count(*) >= 10
order by count(*) desc

group by and order by are the widest keywords here, so they set the edge, and select is padded two columns like every other clause. A join is a clause of the query by default, so it takes part as well. joinPlacement keeps joins in their FROM instead.

A query that fits on one line keeps it. The river only appears when the clauses come apart:

select a from t where x = 1

A list too long for one line​

select alpha_column, beta_column, gamma_column, delta_column, epsilon_column from some_table
select
    alpha_column,
    beta_column,
    gamma_column,
    delta_column,
    epsilon_column
from some_table
select alpha_column, beta_column, gamma_column, delta_column, epsilon_column from some_table
select alpha_column,
       beta_column,
       gamma_column,
       delta_column,
       epsilon_column
  from some_table

Under left, a list too long for its keyword's line moves below the keyword and indents. Under right the river is the body column, so the list stays beside select and each item begins one column right of the edge.

WHERE conditions​

select a from some_table where first_condition_column = 1 and second_condition_column = 2 and third_condition_column = 3
select a
from some_table
where
    first_condition_column = 1
    and second_condition_column = 2
    and third_condition_column = 3
select a from some_table where first_condition_column = 1 and second_condition_column = 2 and third_condition_column = 3
select a
  from some_table
 where first_condition_column = 1
   and second_condition_column = 2
   and third_condition_column = 3

Each and ends at the river, as a keyword would, so every condition starts in the same column as the first one. A leading comma under commaStyle leading stands in the margin the same way.

Nested queries​

with recent as (select customer_id, max(ordered_at) as last_order from orders group by customer_id) select customer_id, last_order from recent where last_order > :cutoff order by last_order desc
with
    recent as (
        select customer_id, max(ordered_at) as last_order
        from orders
        group by customer_id
    )
select customer_id, last_order
from recent
where last_order > :cutoff
order by last_order desc
with recent as (select customer_id, max(ordered_at) as last_order from orders group by customer_id) select customer_id, last_order from recent where last_order > :cutoff order by last_order desc
with
    recent as (
          select customer_id, max(ordered_at) as last_order
            from orders
        group by customer_id
    )
  select customer_id, last_order
    from recent
   where last_order > :cutoff
order by last_order desc

The query inside the CTE is aligned on its own widest keyword, and so is the one after it. A with stands in front of the query rather than in it, so it stays at the statement's column and does not move the edge.

INSERT … SELECT​

insert into rate (currency, validity, value) select currency, validity, rate from rates where rate > 0
insert into rate(currency, validity, value)
select currency, validity, rate
from rates
where rate > 0
insert into rate (currency, validity, value) select currency, validity, rate from rates where rate > 0
insert into rate(currency, validity, value)
     select currency, validity, rate
       from rates
      where rate > 0

insert into is the widest keyword, and the clauses of the query it inserts end under its into. A values list stands in the same place.