SQL Formatter - River Style

A VS Code formatter that lays SQL out in river style, the layout described in sqlstyle.guide. Clause keywords are right-aligned into a gutter, so the whitespace between keywords and content forms a vertical channel running down the query.

Unlike most SQL formatters, the gutter here has a fixed width of 6 columns by default, the length of SELECT, instead of being computed from the longest keyword in each statement. The practical consequence is that every query in a file aligns to the same column, whether or not it happens to contain a GROUP BY.

The extension also works on SQL embedded in other languages. Select a query inside a Go raw string, a TypeScript template literal or a Python docstring and format just the selection, keeping the surrounding indentation intact.

SELECT c.id,
       c.title,
       COUNT(s.id) AS total_subscriptions,
       COALESCE(SUM(s.amount_cents), 0)::BIGINT AS revenue_cents,
       RANK() OVER (PARTITION BY c.category_id ORDER BY COUNT(s.id) DESC) AS category_rank
  FROM contents c
  LEFT JOIN subscriptions s ON s.content_id = c.id
   AND s.cancelled = FALSE
 WHERE c.category_id IN (10, 20)
   AND c.active = TRUE
 GROUP BY c.id, c.title, c.category_id
HAVING COUNT(s.id) > 0
 ORDER BY category_rank, c.title
 LIMIT 50;

Installation

code --install-extension raykavin.sql-river

Or from source: copy the extension folder into ~/.vscode/extensions/ and run Developer: Reload Window. It is plain JavaScript with no dependencies and no build step, so editing src/formatter.js and reloading is enough to try a change.

To make it the default formatter for .sql:

"[sql]": {
  "editor.defaultFormatter": "raykavin.sql-river",
  "editor.formatOnSave": true
}

Usage

Ctrl+Alt+Q works in any language. It reads the indentation of the line where the selection starts and reapplies it to every generated line, so formatting a query inside a Go raw string doesn't disturb the code around it.

The CLI reads files or stdin, writes in place with -w, and takes every setting below as a flag:

node bin/sql-river.js query.sql
node bin/sql-river.js -w migrations/*.sql
cat query.sql | node bin/sql-river.js --river-width=8 --keyword-case=lower

Rules applied

1. Right-aligned keywords. SELECT, FROM, WHERE, AND/OR, the JOIN family, GROUP BY, ORDER BY, HAVING, LIMIT, OFFSET, UNION, and in DML INSERT, UPDATE, SET, DELETE, VALUES and RETURNING, all end at the gutter's last column, with content starting one column after it. In a multi-word clause only the first word sits in the gutter: LEFT is aligned and JOIN opens the content, same for GROUP/BY and ORDER/BY. A keyword longer than the gutter, such as RETURNING or INTERSECT, starts at column 0 and breaks the river. That is an unavoidable consequence of a fixed width.
2. One item per line in SELECT and in UPDATE ... SET, aligned under the first item. The other comma-separated lists (GROUP BY, ORDER BY, RETURNING) stay on one line while they fit in maxLineLength, and only then break one per line.
3. AND/OR take their own gutter line inside WHERE, HAVING and join conditions. The AND belonging to a BETWEEN, and any AND/OR inside a CASE or inside parentheses, are left alone.
4. ON stays on the JOIN line by default, and the conditions that follow break as in rule 3. Set joinConditionOnNewLine to give ON its own line.
5. Uppercase: reserved words, function names, and type names after a :: cast or inside CREATE/ALTER/DECLARE. Preserved: every identifier, meaning tables, columns, aliases, CTE names and functions you define. The formatter never changes the case of something it didn't recognize as SQL.
6. An identifier counts as a function call only when the ( is glued to it in the source. COALESCE(x) is a function, while ON people (active) is a table followed by a column list and keeps both its spacing and its case. This replaces the guesswork a keyword list would need.
7. CTEs are expanded. Each one opens its parenthesis, indents its body and closes with ) on the last line, followed by the comma. WITH sits at column 0 rather than in the gutter, so the chain reads as a list.
8. Subqueries in an expression anchor a gutter of their own at the column just after (, and switch to the expanded form only when the whole item would exceed maxLineLength. Derived tables in FROM/JOIN are formatted recursively the same way.
9. CREATE TABLE breaks its column and constraint list one per line, with the closing ) on a line of its own. CREATE INDEX ... (cols) and CREATE TABLE ... AS SELECT are left alone.
10. Long boolean chains inside parentheses, typically a CHECK constraint, break before each AND/OR, with the operands aligned and the operator tucked to the right of them, mirroring the clause gutter. Controlled by breakLongConditions, which defaults to DDL only.
11. Statements are separated by a blank line and keep their ;. Statements the formatter has no clause model for, such as DROP, GRANT, TRUNCATE and session commands, are left on one line at column 0 with their keywords uppercased, never indented into the gutter.
12. Nothing is added, removed or reordered. The tokenizer is lossless and the formatter only rewrites whitespace and letter case. A dollar-quoted function body is a single token and comes back byte for byte.

Examples

Chained CTEs, each with a gutter of its own:

WITH monthly_charges AS (
  SELECT subscriber_id,
         DATE_TRUNC('month', charged_at) AS charge_month,
         COUNT(*) AS charges
    FROM charges
   WHERE status = 'paid'
     AND charged_at >= CURRENT_DATE - INTERVAL '12 months'
   GROUP BY subscriber_id, DATE_TRUNC('month', charged_at)),
churn_risk AS (
  SELECT m.subscriber_id,
         COUNT(*) FILTER (WHERE m.charges = 0) AS months_without_charge,
         MAX(m.charge_month) AS last_month
    FROM monthly_charges m
   GROUP BY m.subscriber_id)
SELECT s.id,
       s.email,
       cr.months_without_charge
  FROM subscribers s
  JOIN churn_risk cr ON cr.subscriber_id = s.id
 WHERE cr.months_without_charge >= 3
 ORDER BY cr.months_without_charge DESC;

A subquery in an expression anchors its gutter after the parenthesis:

SELECT p.id,
       p.name
  FROM people p
 WHERE p.id IN (SELECT b.person_id
                  FROM blocklist b
                 WHERE b.active = TRUE)
   AND p.deleted = FALSE;

A derived table in FROM is formatted recursively:

SELECT e.id,
       e.total
  FROM (SELECT id,
               COUNT(*) AS total
          FROM events
         WHERE kind = 'click'
         GROUP BY id) e
  JOIN users u ON u.id = e.id
 WHERE e.total > 10;

DML shares the same gutter:

UPDATE contents
   SET title = BTRIM(title),
       updated_at = NOW()
 WHERE id = ?
   AND deleted = FALSE
RETURNING id, title;

INSERT INTO audit_log (content_id, action, created_at)
VALUES (1, 'sync', NOW()),
       (2, 'sync', NOW());

DELETE
  FROM contents
 WHERE category_id = 7
   AND deleted = TRUE;

CREATE TABLE, with a CHECK long enough to break its boolean chain:

CREATE TABLE bank_accounts (
  id BIGSERIAL,
  bank_id BIGINT,
  agency_number CHARACTER VARYING(20),
  account_number CHARACTER VARYING(20),
  account_type INTEGER,
  CONSTRAINT bank_accounts_pkey PRIMARY KEY (id),
  CONSTRAINT check_bank_accounts_complete CHECK (((bank_id IS NOT NULL)
                                              AND (agency_number IS NOT NULL)
                                              AND ((agency_number)::TEXT <> ''::TEXT)
                                              AND (account_number IS NOT NULL)
                                              AND ((account_number)::TEXT <> ''::TEXT))),
  CONSTRAINT check_valid_account_type CHECK ((account_type = ANY (ARRAY[0, 1, 2, 3, 4]))),
  CONSTRAINT fk_bank_accounts_bank FOREIGN KEY (bank_id) REFERENCES banks (id) ON UPDATE CASCADE ON DELETE RESTRICT
);

A function definition: the header is normalized, the dollar-quoted body is not touched.

CREATE OR REPLACE FUNCTION update_updated_at_column() RETURNS TRIGGER LANGUAGE plpgsql AS $function$
begin
  new.updated_at = now();
  return new;
end;
$function$;

CREATE TRIGGER set_updated_at BEFORE UPDATE ON people FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

Configuration

Every setting is also a CLI flag, in kebab-case: --river-width=8, --break-long-conditions=always.

Scope and known limitations

• CASE ... END always renders inline. A long CASE overflows maxLineLength instead of breaking into WHEN/THEN lines.
• Keywords longer than the gutter break the river. RETURNING, INTERSECT and EXCEPT start at column 0. Widening riverWidth to 9 fixes it, at the cost of pushing every other clause to the right.
• PL/pgSQL is out of scope. Everything between $$ or $tag$ is preserved verbatim. Reformatting it would need a second parser for a different language, and formatting it wrongly is worse than leaving it alone.
• INSERT ... ON CONFLICT isn't modeled. It stays concatenated at the end of the VALUES line, correctly cased but not broken into clauses.
• DDL beyond CREATE TABLE is only recased. DROP, GRANT, ALTER, CREATE INDEX and friends come out on a single line at column 0.
• Not a validating parser. Syntactically invalid SQL is reformatted, not rejected. The formatter reports no errors, it just rearranges whitespace around whatever it tokenized.
• The reserved-word list is deliberately conservative. Words that are also common column names, such as name, status, code, value, date and text, are not uppercased. The cost is leaving a few genuine keywords lowercase in unusual statements. Type names are only uppercased where a type is structurally guaranteed: after ::, and inside CREATE/ALTER/DECLARE.
• Function detection depends on source spacing (rule 6). Writing coalesce (x, y) with a space keeps both the space and the lowercase.
• Comments are preserved, not rewrapped. A -- comment keeps the rest of its line, as SQL semantics demand. A comment opening a block is aligned with that block's first keyword.

Development

No build step. The extension runs the JavaScript in src/ directly.

src/tokenizer.js   lossless tokenizer: concatenating every token's value
                   reproduces the input exactly
src/formatter.js   clause splitting, gutter alignment, recursion into
                   subqueries and CTEs
extension.js       VS Code providers and the format-selection command
bin/sql-river.js   CLI wrapper over the same formatter

The pipeline splits statements at top-level ;, then splits each statement into clauses whenever a head keyword appears at paren depth zero and outside a CASE, then right-aligns the head in the gutter and splits the body at top-level commas. Subqueries recurse with their anchor set to the column after (.

Formatting twice produces the same result. That invariant is worth keeping when changing anything here.

To debug inside VS Code, open the extension folder as a workspace and press F5 for an Extension Development Host.

To build an installable package:

npx @vscode/vsce package

License

MIT, © Raykavin Meireles.