Formatting¶

Preview Feature: The formatter is currently in preview. We'd love feedback from early adopters! Please report any issues or unexpected output at GitHub Issues.

The language server provides SQL formatting that produces consistent, readable code. Built on Postgres' own parser, the formatter ensures 100% syntax compatibility with your SQL.

What the formatter changes¶

The formatter changes whitespace, line breaks, the case of keywords, types and constants, and quotes that an identifier does not need. It does not change what is written:

• Schema qualifiers stay as written, pg_catalog. included.
• Function and DO bodies are kept byte for byte, and always dollar-quoted with $$ (or $body$ when the body contains $$), whatever quotes they were written with. Only language sql bodies are reformatted, unless skipFnBodies is set.
• String literals are kept as written.
• Function signatures keep their argument names and modes, in ALTER FUNCTION, GRANT and DROP as well.
• Identifiers that need quotes keep them: mixed case, reserved words such as "user", and special characters.
• Comments stay where they were written, including comments at the end of a line.

The formatter checks every statement: if the formatted statement would not parse to the same tree as the original, the statement is left as written and a diagnostic is reported.

Between statements, the formatter keeps at most one blank line, and removes blank lines at the start and the end of the file.

Configuration¶

Configure formatting behavior in your postgres-language-server.jsonc:

{
  "format": {
    "enabled": true,
    "lineWidth": 100,
    "indentSize": 2,
    "indentStyle": "spaces",
    "keywordCase": "lower",
    "constantCase": "lower",
    "typeCase": "lower",
    "functionArgumentGroups": {
      "my_pair_func": 2
    }
  }
}

Options¶

functionArgumentGroups matches the final, unqualified function name case-insensitively. A group size of 2 is useful for functions whose arguments form key/value pairs:

select jsonb_build_object(
  'amountTTC', to_amount(amount_ttc, invoices.currency),
  'amountVAT', to_amount(amount_vat, invoices.currency)
);

A group stays on one line when it fits; when it does not, the formatter breaks between its arguments rather than exceeding the line width.

json_build_object and jsonb_build_object are grouped in pairs by default, without configuration. Configure a function explicitly to use a different group size; a size of 1 opts out of the built-in default:

{
  "format": {
    "functionArgumentGroups": {
      "jsonb_build_object": 1
    }
  }
}

Example Output¶

With default settings (lowercase):

create table users (
  id serial primary key,
  name text not null,
  active boolean default true
);

select * from users where active = true;

With uppercase keywords and constants:

CREATE TABLE users (
  id serial PRIMARY KEY,
  name text NOT NULL,
  active boolean DEFAULT TRUE
);

SELECT * FROM users WHERE active = TRUE;

CLI Usage¶

Format files using the CLI:

# Check formatting and show a diff for every statement that would change
postgres-language-server format file.sql

# Format and write changes
postgres-language-server format file.sql --write

# Format entire directory
postgres-language-server format migrations/ --write

Check mode¶

Without --write, format does not modify any file. For every statement that would be reformatted it prints a diagnostic with the file path and a diff to stderr, followed by a summary (Processed N files ... Found N errors.). The command exits with a non-zero code if any file is not formatted or could not be formatted (e.g. because of a syntax error), and with 0 otherwise. This makes it suitable for CI:

postgres-language-server format migrations/

--staged and --changed (optionally with --since=<REF>) limit the run to the files staged for commit or changed compared to your default branch.

Formatting stdin¶

Use --stdin-file-path=<PATH> to format SQL piped through stdin, for example from an editor or a script. Only the formatted SQL is printed to stdout:

echo "SELECT a,b FROM t;" | postgres-language-server format --stdin-file-path=query.sql
# select a, b from t;

The file doesn't need to exist. Its path is used to apply the project configuration (for example, a path matching files.ignore is printed back unchanged) and to label diagnostics. If a statement cannot be formatted, it is printed unchanged, its diagnostics are written to stderr, and the command exits with a non-zero code. When the formatter is disabled, the input is printed unchanged. --stdin-file-path cannot be combined with file paths, --write, --staged or --changed.

Editor Integration¶

The formatter integrates with your editor via the Language Server Protocol. Use your editor's format document command (typically bound to a keyboard shortcut) to format SQL files.

JavaScript API¶

The @postgres-language-server/wasm package formats SQL in-process, with the same options as the format section of the configuration file:

import { format } from "@postgres-language-server/wasm";

const sql = await format('CREATE TABLE "public"."t" ("id" "uuid" NOT NULL)', {
  keywordCase: "lower",
});

Ignoring Files¶

Use the ignore and include options to control which files are formatted:

{
  "format": {
    "ignore": ["**/generated/**", "**/vendor/**"],
    "include": ["**/*.sql"]
  }
}