sqlfmt

An opinionated SQL formatter that implements river alignment — right-aligning keywords so content flows along a consistent vertical column. It defaults to a single deterministic style, with a small optional config surface for operational concerns like line width and safety limits.

Quick Start

Install

npm install @vcoppola/sqlfmt

CLI Usage

# Format a file
npx sqlfmt query.sql

# Format all SQL files
npx sqlfmt "**/*.sql"

# Check formatting (CI mode)
npx sqlfmt --check "**/*.sql"

# Format in place
npx sqlfmt --write "**/*.sql"

Programmatic Usage

import { formatSQL } from '@vcoppola/sqlfmt';

const formatted = formatSQL('select id, name from users where active = true;');
// Output:
// SELECT id, name
//   FROM users
//  WHERE active = TRUE;

Table of Contents

• What it does
• When NOT to use sqlfmt
• SQL Dialect Support
• CLI Reference
• API Guide
• How the formatter works
• Edge Cases & Behavior
• FAQ
• Documentation
• Development
• Performance
• Limitations
• License

What it does

Takes messy SQL and formats it with river alignment — right-aligning keywords so content flows along a consistent vertical column:

-- Input
select e.name, e.salary, d.department_name from employees as e inner join departments as d on e.department_id = d.department_id where e.salary > 50000 and d.department_name in ('Sales', 'Engineering') order by e.salary desc;

-- Output
SELECT e.name, e.salary, d.department_name
  FROM employees AS e
       INNER JOIN departments AS d
       ON e.department_id = d.department_id
 WHERE e.salary > 50000
   AND d.department_name IN ('Sales', 'Engineering')
 ORDER BY e.salary DESC;

More examples

Multi-table JOINs:

-- Input
select o.id, c.name, p.title, o.total from orders o join customers c on o.customer_id = c.id left join products p on o.product_id = p.id left join shipping s on o.id = s.order_id where o.created_at > '2024-01-01' and s.status = 'delivered' order by o.created_at desc;

-- Output
SELECT o.id, c.name, p.title, o.total
  FROM orders AS o
  JOIN customers AS c
    ON o.customer_id = c.id

       LEFT JOIN products AS p
       ON o.product_id = p.id

       LEFT JOIN shipping AS s
       ON o.id = s.order_id
 WHERE o.created_at > '2024-01-01'
   AND s.status = 'delivered'
 ORDER BY o.created_at DESC;

CTEs (Common Table Expressions):

-- Input
with monthly_totals as (select date_trunc('month', created_at) as month, sum(amount) as total from payments group by 1), running as (select month, total, sum(total) over (order by month) as cumulative from monthly_totals) select * from running where cumulative > 10000;

-- Output
  WITH monthly_totals AS (
           SELECT DATE_TRUNC('month', created_at) AS month,
                  SUM(amount) AS total
             FROM payments
            GROUP BY 1
       ),
       running AS (
           SELECT month, total, SUM(total) OVER (ORDER BY month) AS cumulative
             FROM monthly_totals
       )
SELECT *
  FROM running
 WHERE cumulative > 10000;

Window functions:

-- Input
select department, employee, salary, rank() over (partition by department order by salary desc) as dept_rank, salary - avg(salary) over (partition by department) as diff_from_avg from employees;

-- Output
SELECT department,
       employee,
       salary,
       RANK() OVER (PARTITION BY department
                        ORDER BY salary DESC) AS dept_rank,
       salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
  FROM employees;

CASE expressions:

-- Input
select name, case status when 'A' then 'Active' when 'I' then 'Inactive' when 'P' then 'Pending' else 'Unknown' end as status_label, case when balance > 10000 then 'high' when balance > 1000 then 'medium' else 'low' end as tier from accounts;

-- Output
SELECT name,
       CASE status
       WHEN 'A' THEN 'Active'
       WHEN 'I' THEN 'Inactive'
       WHEN 'P' THEN 'Pending'
       ELSE 'Unknown'
       END AS status_label,
       CASE
       WHEN balance > 10000 THEN 'high'
       WHEN balance > 1000 THEN 'medium'
       ELSE 'low'
       END AS tier
  FROM accounts;

When NOT to use sqlfmt

• You need highly configurable style output — sqlfmt intentionally does not expose style knobs for indentation strategy, keyword casing, or alignment mode. If you need full style customization, use sql-formatter or prettier-plugin-sql.
• You exclusively target MySQL or SQL Server — sqlfmt is PostgreSQL-first. Standard ANSI SQL works fine, but vendor-specific syntax (stored procedures, MySQL-only functions) may not be fully parsed.
• You need a language server — sqlfmt is a formatter, not a linter or LSP. It does not provide diagnostics, completions, or semantic analysis.

SQL Dialect Support

sqlfmt test coverage is PostgreSQL-first. If you rely on non-PostgreSQL vendor extensions, run --check in CI and prefer --strict where parse failures should block merges.

PostgreSQL (Full Support)

• Type casts (::integer), JSON operators (->, ->>), dollar-quoting ($$...$$)
• Array constructors, window functions, CTEs, LATERAL joins
• ON CONFLICT (UPSERT), RETURNING clauses
• Note: PL/pgSQL function bodies are preserved verbatim (not reformatted)

ANSI SQL (Full Support)

• SELECT, INSERT, UPDATE, DELETE, MERGE
• JOINs (INNER, LEFT, RIGHT, FULL, CROSS, NATURAL)
• CTEs (WITH, WITH RECURSIVE)
• Window functions (PARTITION BY, ORDER BY, frame clauses)
• DDL (CREATE TABLE, ALTER TABLE, DROP, CREATE INDEX, CREATE VIEW)

MySQL (Partial)

• Standard ANSI SQL queries format correctly
• Backtick identifiers, LIMIT offset syntax, and storage engine clauses are not yet supported

SQL Server (Partial)

• Standard ANSI SQL queries format correctly
• T-SQL procedural syntax (BEGIN/END blocks, DECLARE, @@variables) is not yet supported

Recovery Mode

Unsupported syntax is passed through unchanged rather than causing errors. Use --strict to fail on unparseable SQL.

Style Guide

This formatter is inspired by and makes every attempt to conform to the Simon Holywell SQL Style Guide. Key principles from the guide that sqlfmt enforces:

• River alignment — Clause/logical keywords are right-aligned to a per-statement river width derived from the longest top-level aligned keyword
• Keyword uppercasing — Reserved words like SELECT, FROM, WHERE are uppercased
• Identifier normalization — Most unquoted identifiers are lowercased; quoted identifiers are preserved
• Right-aligned clause/logical keywords — SELECT, FROM, WHERE, AND, OR, JOIN, ON, ORDER BY, GROUP BY, etc. align within each formatted block
• Consistent indentation — Continuation lines and subexpressions are indented predictably

For the full style guide, see sqlstyle.guide or the source on GitHub.

Why sqlfmt?

sqlfmt is the right choice when you want consistent, readable SQL with minimal setup and deterministic style.

Configuration philosophy

sqlfmt keeps style deterministic by design: no indentation/casing style matrix, no formatter presets.
It does support a focused optional config file (.sqlfmtrc.json) for operational settings:

• maxLineLength
• maxDepth
• maxInputSize
• strict
• recover

CLI flags still override config values.

CLI Reference

# Format a file (prints to stdout by default)
npx @vcoppola/sqlfmt query.sql

# Format a file in place
npx @vcoppola/sqlfmt --write query.sql

# Format from stdin
cat query.sql | npx @vcoppola/sqlfmt

# Check if a file is already formatted (exits non-zero if not)
npx @vcoppola/sqlfmt --check query.sql

# List files that would change (useful in CI)
npx @vcoppola/sqlfmt --list-different "src/**/*.sql"
npx @vcoppola/sqlfmt -l "migrations/*.sql"

# Strict mode: fail on unparseable SQL instead of passing through
npx @vcoppola/sqlfmt --strict --check "**/*.sql"

# Tune output width
npx @vcoppola/sqlfmt --max-line-length 100 query.sql

# Use project config
npx @vcoppola/sqlfmt --config .sqlfmtrc.json --check "**/*.sql"

# Ignore files (can repeat --ignore)
npx @vcoppola/sqlfmt --check --ignore "migrations/**" "**/*.sql"

# Or store ignore patterns in .sqlfmtignore (one pattern per line)
npx @vcoppola/sqlfmt --check "**/*.sql"

# Control color in CI/logs
npx @vcoppola/sqlfmt --color=always --check query.sql

# Pipe patterns
pbpaste | npx @vcoppola/sqlfmt | pbcopy          # Format clipboard (macOS)
pg_dump mydb --schema-only | npx @vcoppola/sqlfmt > schema.sql
echo "select 1" | npx @vcoppola/sqlfmt

By default, npx @vcoppola/sqlfmt query.sql prints formatted output to stdout. Use --write to modify the file in place.

When present, .sqlfmtignore is read from the current working directory and combined with any --ignore flags.

CLI exit codes:

API Guide

Basic Usage

import { formatSQL } from '@vcoppola/sqlfmt';

const formatted = formatSQL('SELECT * FROM users;');

Error Recovery

By default, unparseable SQL is passed through unchanged:

const warnings: string[] = [];
const formatted = formatSQL(sql, {
  onRecover: (error, raw) => {
    warnings.push(`Line ${error.token.line}: ${error.message}`);
  }
});

Strict Mode (throw on parse errors)

import { formatSQL, ParseError } from '@vcoppola/sqlfmt';

try {
  formatSQL(sql, { recover: false });
} catch (err) {
  if (err instanceof ParseError) {
    console.error(`Parse error: ${err.message}`);
  }
}

Depth Limits

formatSQL(sql, { maxDepth: 300 }); // Increase for deeply nested CTEs

Input Size Limits

formatSQL(sql, { maxInputSize: 5_000_000 }); // 5MB limit (default: 10MB)

Low-Level Access

import { tokenize, parse, formatStatements } from '@vcoppola/sqlfmt';

// Tokenize SQL into a token stream
const tokens = tokenize(sql);

// Parse SQL into an AST
const ast = parse(sql);

// Format AST nodes back to SQL
const output = formatStatements(ast);

Error Types

import { formatSQL, TokenizeError, ParseError, MaxDepthError } from '@vcoppola/sqlfmt';

try {
  const result = formatSQL(input);
} catch (err) {
  if (err instanceof TokenizeError) {
    // Invalid token encountered during lexing (e.g., unterminated string)
    console.error(`Tokenize error at position ${err.position}: ${err.message}`);
  } else if (err instanceof MaxDepthError) {
    // Parser nesting exceeded configured maxDepth
    console.error(`Parse depth exceeded: ${err.message}`);
  } else if (err instanceof ParseError) {
    // Structural error in the SQL (e.g., unmatched parentheses)
    console.error(`Parse error: ${err.message}`);
  } else if (err instanceof Error && err.message.includes('Input exceeds maximum size')) {
    // Input exceeded maxInputSize
    console.error(`Input too large: ${err.message}`);
  } else {
    throw err;
  }
}

How the formatter works

graph LR
    A[SQL Text] --> B[Tokenizer] --> C[Parser] --> D[AST] --> E[Formatter] --> F[Formatted SQL]

1. Tokenizer (src/tokenizer.ts) — Splits SQL text into tokens (keywords, identifiers, literals, operators, comments)
2. Parser (src/parser.ts) — Builds an AST from the token stream
3. Formatter (src/formatter.ts) — Walks the AST and produces formatted output

The key formatting concept is the river. For each statement, sqlfmt derives a river width from the longest top-level aligned keyword in that statement (for example, RETURNING can widen DML alignment). Clause/logical keywords are then right-aligned to that width so content starts in a consistent column. Nested blocks may use their own derived widths. This approach comes directly from the Simon Holywell SQL Style Guide.

Edge Cases & Behavior

Long Lines

sqlfmt targets 80-column output by default and supports maxLineLength (CLI flag or config file). It does not break individual tokens (identifiers, string literals), so single-token lines can still exceed the configured width.

Comment Preservation

Line comments and block comments are preserved. Comments attached to specific expressions maintain their association.

Keyword Casing

All SQL keywords are uppercased. Identifiers are preserved as-is (quoted identifiers keep their case and quotes). Unquoted identifiers are lowercased.

Idempotency

Formatting is idempotent: formatSQL(formatSQL(x)) === formatSQL(x) for all valid inputs.

FAQ

Q: Can I change the indentation style or keyword casing? A: No. Style output is intentionally fixed. sqlfmt provides operational configuration (line length, strictness/safety), not style customization.

Q: What happens with SQL syntax sqlfmt doesn't understand? A: In default (recovery) mode, unrecognized statements are passed through unchanged. Use --strict to fail instead.

Q: How fast is sqlfmt? A: ~5,000 statements/second on modern hardware. A typical migration file formats in <10ms.

Q: Does sqlfmt modify SQL semantics? A: No. sqlfmt only changes whitespace and keyword casing. The semantic meaning is preserved.

Q: Does sqlfmt respect .editorconfig? A: No. sqlfmt does not read .editorconfig. It does read .sqlfmtrc.json (or --config) for operational settings, but style output remains deterministic.

Q: Can I customize the river width? A: Not directly. River width is derived automatically from statement structure. You can influence wrapping via maxLineLength, but keyword alignment behavior itself is fixed.

Q: Does formatting change SQL semantics? A: sqlfmt only changes whitespace and casing. Specifically: SQL keywords are uppercased (select becomes SELECT), unquoted identifiers are lowercased (MyTable becomes mytable), and quoted identifiers are preserved exactly ("MyTable" stays "MyTable"). If your database is case-sensitive for unquoted identifiers (rare, but possible), see the Migration Guide for details.

Q: Does sqlfmt work with MySQL / SQL Server / SQLite? A: sqlfmt is PostgreSQL-first, but any query written in standard ANSI SQL will format correctly regardless of your target database. Vendor-specific extensions (stored procedures, MySQL-only syntax) may not be fully parsed. See SQL Dialect Support for details.