SQL formatter

functional

Tidy up a query without changing what it means. The formatter is careful on purpose.

The formatter is configurable, but it is deliberately cautious. SQLly re-lexes and compares its output token by token with the input; any mismatch leaves your SQL untouched. It can reindent, recase keywords, and align clauses without taking chances with comments or literals.

Format SQL

Press ⌥⇧F (Alt+Shift+F on Windows and Linux), choose Edit › Format Document, or right-click › Format Document to format the whole query whatever is selected. To format only the selection, press ⌘K ⌘F (Ctrl+K Ctrl+F) for Format Selection. Both shortcuts can be changed under Settings › Workspace › Keyboard Shortcuts. You can also choose Query › Format SQL, use the toolbar button, or run it from the command palette: with text selected, only the selection is formatted. Otherwise the whole query is. Here is a one-line query with the default settings:

-- before
select o.OrderId, c.Name, sum(l.Qty * l.Price) as total from dbo.Orders o join dbo.Customers c on c.Id = o.CustomerId left join dbo.OrderLines l on l.OrderId = o.OrderId where o.Status = 'open' and o.Total > 100 group by o.OrderId, c.Name order by total desc

-- after
SELECT
    o.OrderId,
    c.Name,
    SUM(l.Qty * l.Price) AS total
FROM
    dbo.Orders o
    JOIN dbo.Customers c ON c.Id = o.CustomerId
    LEFT JOIN dbo.OrderLines l ON l.OrderId = o.OrderId
WHERE
    o.Status = 'open'
    AND o.Total > 100
GROUP BY
    o.OrderId,
    c.Name
ORDER BY
    total DESC

Keywords and functions are upper-cased, each major clause starts a new line, select lists are stacked one column per line, the tables after FROM (with their JOINs) and the conditions after WHERE and HAVING sit on their own indented lines, and AND/OR lead their lines. A single statement that fits in 80 characters stays on one line: SELECT id, name FROM dbo.Users WHERE id = 1. Indentation follows your editor's tab settings unless you choose otherwise.

SQLly
Editor showing a multi-join Orders query after Format SQL, with keywords capitalized and clauses on separate linesEditor showing a multi-join Orders query after Format SQL, with keywords capitalized and clauses on separate lines
A one-line multi-join query after Format SQL: keywords in capitals, one column per line, and clauses aligned.

Format as you go

The Format as you go toggle at the top of Settings › Editor › Formatting formats while you type. With it on, pressing Enter reformats the statement you are typing, and the caret stays right after the same character it followed before, so you keep typing where you were. It is off by default; with it off, format on demand with Format Document (⌥⇧F on macOS, Alt+Shift+F on Windows and Linux, rebindable under Settings › Workspace › Keyboard Shortcuts).

Pressing Enter leaves the text alone when:

  • text is selected, or you are editing with multiple cursors;
  • the tab is on a Redis connection, whose editor holds commands rather than SQL;
  • the statement doesn't start with a statement keyword: SELECT, INSERT, UPDATE, DELETE, WITH, CREATE, ALTER, DROP, MERGE, TRUNCATE, GRANT, REVOKE, BEGIN, DECLARE, or EXEC.

Scripts and GO batches

A script with several statements is formatted one statement at a time, each starting on its own line, and the one-line shortcut only applies when the whole text is a single statement. Statements end at ; and, for SQL Server, at a GO batch separator. GO always keeps a line of its own, cased like your other keywords, along with any repeat count (GO 5) or comment that shares its line:

-- before
select 1
go
select name from dbo.Users
go

-- after
SELECT
    1
GO
SELECT
    name
FROM dbo.Users
GO

GO counts as a separator only when it starts a line that holds nothing else but an optional count or comment, and it sits between statements, as sqlcmd and SSMS expect. A column named go in the middle of a select list, [go], and go inside a string or comment are left as ordinary SQL. With the dialect on Auto Detect, a GO line marks the script as SQL Server. With the dialect set to another engine, GO is not a separator.

Settings

Everything lives in Settings › Editor › Formatting, which has a live preview: paste your own SQL and watch it change as you adjust settings. The fastest start is a preset:

PresetStyle
DefaultThe layout shown above.
CompactShort clauses and select lists stay on one line, and AND/OR trail their lines.
ExpandedLeading commas, a blank line before major clauses, ON on its own line, and aligned operators, data types, and aliases.
Uppercase Keywords / Lowercase KeywordsThe default layout with keywords and functions in that case.
Preserve ExistingKeeps your casing, commas, and clause placement, and tidies the rest.

Picking a preset rewrites every layout setting but keeps your format triggers (Format as you go, Format on save, and Format on paste), so trying a different preset no longer switches them off. Changing any single setting afterwards switches it to Custom. Individual sections cover keyword casing (overall or per group, such as data types or JOINs), identifier casing and quoting, clause layout, select lists, comma style, boolean conditions, CASE, operators, JOINs, functions, subqueries and CTEs, DML, DDL, comments, indentation, line length (120 by default), and statements. The SQL dialect setting defaults to Auto Detect, which works the dialect out from the SQL itself.

Format on save and Format on paste are under Format triggers. Both are off by default.

FROM and WHERE placement

Two settings under Clause layout choose whether a clause's body starts on the keyword's line or on its own indented line below it:

OptionWhat it does
Table after FROMNew line, indented (the default) puts the first table on its own line under FROM, and lines its JOINs up with it; an ON on its own line indents one step further. Same line as the keyword keeps the table on the FROM line, with JOINs placed by the JOIN layout setting.
First condition after WHERE / HAVINGNew line, indented (the default) puts the first condition on its own line under WHERE or HAVING, aligned with the AND/OR lines that follow. Same line as the keyword keeps it on the keyword's line.

The Compact and Preserve Existing presets keep both on the keyword's line. A comment written right after FROM or WHERE stays on that line.

CTEs, DML, and index columns

These options control how common statement shapes are laid out:

OptionWhat it does
CTE styleStacked (the default) puts each CTE's body on its own lines. Inline puts each CTE's name AS ( … ) on one line. A CTE that contains a -- comment, or that would run past the max line length, stays stacked.
DML styleStandard (the default) is the usual layout. Compact puts an INSERT, UPDATE, DELETE, or MERGE on one line when it fits the max line length. Expanded puts each INSERT column, each VALUES item, and each UPDATE SET assignment on its own line, and is never collapsed by Keep short queries on one line.
Multi-line index columnsWhen an index has more than one column, each key column and each INCLUDE column of a CREATE INDEX goes on its own line. The same applies to PRIMARY KEY, UNIQUE, INDEX, and KEY column lists inside CREATE TABLE. FOREIGN KEY column lists are left as they are.
Move trailing comments with expressionWorks together with Keep inline comments inline. With both on, an end-of-line -- comment stays at the end of its code's line after the formatter re-breaks that line.

The Compact and Expanded presets turn on the matching options from this group. In CREATE INDEX … ON table (…), the table name keeps its case; it is no longer upper-cased like a keyword.

Completions follow your settings

When you accept an IntelliSense suggestion, SQLly inserts it laid out the way Format SQL would lay out that statement: keyword and function case, identifier case and quoting, JOIN and ON placement, and the rest of your formatting settings. For example, with ON clause placement set to On new line, a join suggestion puts its ON on its own line. A statement short enough for Keep short queries on one line stays on one line, just as it would with Format SQL. If the formatter can't line its result up with what you typed, the suggestion is inserted as-is.

Leaving SQL alone

Mark a region to leave exactly as written when the formatter should keep its distance:

-- formatter:off
SELECT weird , hand_aligned
       , columns   -- left exactly as typed
FROM t;
-- formatter:on

The switch works a statement at a time. A statement that starts after -- formatter:off is kept verbatim, and so is every statement after it, until a -- formatter:on. A marker in the middle of a statement doesn't protect part of it, so end statements with ; or a GO line. You can turn the markers off with Respect control comments in the Comments section.

Cannot hurt the SQL. The token check turns a formatting bug into a no-op, not corrupted SQL. SQL that doesn't lex cleanly, such as an unterminated string or comment, is returned unchanged rather than reflowed. Comments are never dropped.

Format SQL does nothing on Redis connections, whose editor holds commands rather than SQL.

Minify SQL

Going the other way, one compact line for a log, a JSON payload, or a command line, Minify SQL strips ordinary comments and collapses all whitespace to single spaces while leaving string literals, quoted identifiers, and dollar-quoted bodies untouched, so the compact SQL still runs exactly like the original. -- sqlly directive comments and /*+ hint */ optimizer hints are kept, a SQL Server GO separator keeps its own line because the server requires it, and SQL that does not parse cleanly is left unchanged rather than mangled.

Select the SQL to compact, or place the caret in a statement, or select nothing to minify the whole query, and press ⌘⌥⇧F (Ctrl+Alt+Shift+F on Windows and Linux), choose Query › Minify SQL, or run it from the command palette. The result lands as a single undoable edit, so one undo restores your formatted SQL.