dfmt

create table orders(
    id    bigint  primary key,
    total numeric not null
);

update orders
set
    tier = case
        when total > 100 then 'big'
        else                  'small'
    end,
    due  = make_date(
        year  => 2026,
        month => 1
    )
where id = 7;

select
    id           as order_id,
    total * 1.25 as gross_total
from orders
where
    tier      = 'big'
    and total > 100
    and due   >= current_date;

SELECT
    customer_id,
    customer_name,
    email,
    phone,
    address,
    city,
    state,
    zip_code,
    country,
    created_at,
    updated_at
FROM customers
WHERE
    active = true
    AND country IN (
        'US',
        'CA',
        'GB',
        'DE',
        'FR'
    )
ORDER BY created_at DESC, customer_name

  select drivers.code,
         drivers.surname,
         count(*) as wins
    from results
    join drivers using (driverid)
    join races using (raceid)
   where results.position = 1
     and races.date >= date '2017-01-01'
group by drivers.code, drivers.surname
  having count(*) > 2
order by wins desc
   limit 5

23 options. No more.

It follows your lead

select
    customer_id,
    sum(total) as revenue, count(*) as orders, max(placed_at) as last_order
from orders
where status = 'paid' and region = 'EU'
group by customer_id

FROM orders
|> WHERE status = 'complete' AND total > 100
|> AGGREGATE count(*) as order_count, sum(total) as revenue
    GROUP BY customer_id
|> WHERE order_count >= 5
|> ORDER BY revenue DESC
|> LIMIT 100

What runs the file

{%
    set updater_columns = var(
        'updater_columns',
        ['is_active', 'user_id', 'organization_id', 'last_login_at', 'name']
    )
%}

with
    history as (select * from {{ ref('prep_support_ticket_field_history') }}),

    updaters as (select * from {{ ref('zendesk_users_masked_source') }}),

    final as (
        select
            history.*
            {% if updater_columns %}
                {% for col in updater_columns %}
                    {% if col == 'is_active' %}
                        , coalesce(u.{{ col }}, true) as {{ col }}
                    {% elif col in ['user_id', 'organization_id'] %}
                        , coalesce(u.{{ col }}, -1) as {{ col }}
                    {% elif col == 'last_login_at' %}
                        , coalesce(u.{{ col }}, current_timestamp) as {{ col }}
                    {% else %}
                        , coalesce(u.{{ col }}, 'trigger_change') as {{ col }}
                    {% endif %}
                {% endfor %}
            {% endif %}
        from history
        left join updaters u
            on history.updater_id = u.updater_user_id
            and history.source_relation = u.source_relation
    )
select *
from final

create table ${schema}.orders(
    id bigint generated always as identity primary key,
    customer_id bigint not null
        references ${schema}.customers(id),
    total numeric(12, 2) not null,
    created_at timestamptz not null default now()
);

create index orders_customer_idx
    on ${schema}.orders(customer_id, created_at);

insert into ${schema}.releases(version, applied_by)
values ('${flyway:timestamp}', '${flyway:user}');

\set schema analytics
\timing on
select o.id, o.total, c.email
from :schema.orders o
join :schema.customers c on o.cid = c.id
where o.status = :'status' and o.total > :min_total;
\copy (select * from :schema.orders) to 'out.csv' csv

SET DEFINE ON
DECLARE
    v_total NUMBER := 0;
    v_rows PLS_INTEGER := 0;
BEGIN
    FOR r IN (select total from orders where region = '&&region') LOOP
        v_total := v_total + r.total;
        v_rows := v_rows + 1;
        IF v_total > &max_total THEN
            EXIT;
        END IF;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(
        '&&region: ' || v_rows || ' rows, ' || v_total
    );
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('no rows for &&region');
END;
/

create table dbo.Orders(
    OrderID int not null,
    CustomerID int,
    Total decimal(12, 2),
    CreatedAt datetime2 default sysutcdatetime()
)
GO
create nonclustered index IX_Orders_Customer
    on dbo.Orders(CustomerID, CreatedAt)
GO
alter table dbo.Orders
    add constraint PK_Orders primary key clustered (OrderID)
GO

select
    o.OrderID,
    c.CustomerID,
    c.FirstName || ' ' || c.LastName as FullName,
    o.payload:"deliveryWindow" as DeliveryWindow
from Orders o
join Customers c on o.CustomerID = c.CustomerID
where o.OrderStatus = 'shipped'

select customer_id, sum(order_total) as lifetime
from orders
where status = 'shipped'
group by customer_id;

-- still typing this one: no column list yet
select   from orders  where status = 'shipped'  and created_at > now();

update customers
set tier = 'gold', updated_at = now()
where lifetime_value > 10000

SELECT id, name
FROM users
WHERE
    role = 'admin'
    OR (role = 'editor' AND active = true AND created_at > '2024-01-01')
UNION ALL
SELECT id, name
FROM archived_users
WHERE role = 'admin' OR (role = 'editor' AND deleted = false)

SELECT id, name, email FROM users WHERE active = true;

-- fmt:off
SELECT   a,    b,     c
FROM     t1
WHERE    x = 1;
-- fmt:on

SELECT id, total FROM orders WHERE status = 'complete'

Zero tokens lost

Editor and CI

dfmt diff                        # see what would change
dfmt write                       # format in place
dfmt check --changed-since main  # CI: exit 1 on anything unformatted

Benchmark

Get started

curl -fsSL https://dfmt.datoria.ai/install.sh | sh
cd your-project
dfmt          # sets the project up, then says what would change
dfmt write    # format every SQL file and dbt model in place