Skip to content
croakyPublic

About

Formatter for Postgres SQL files

Resources

Stars

0 stars

Watchers

0 watching

Forks

Repository files navigation

pgfmt

A formatter for Postgres SQL files. One style, no options, so a diff shows what the query changed and never how it was typed.

go get -tool github.com/croaky/pgfmt/cmd/pgfmt

go tool pgfmt -c file.sql ...   # check; exit 1 if any file would change
go tool pgfmt -w file.sql ...   # format in place
go tool pgfmt file.sql ...      # format to stdout
go tool pgfmt < file.sql        # format stdin to stdout

-c writes nothing and reports only formatting, never your edits, so a clean working tree is not required. Without a flag, pgfmt prints every named file, formatted, so pgfmt in.sql > out.sql always fills out.sql.

The library is the same thing without the flags:

out, err := pgfmt.Format(src)

The style

Keywords uppercase. Two spaces per nesting level. A clause keyword owns its line and its operands are indented under it, one per line, so adding a column is a one-line diff:

SELECT
  id,
  name
FROM
  t
  JOIN u
    ON u.id = t.uid
WHERE
  t.x = 1
ORDER BY
  t.id DESC;

CASE always expands. An expression that fits stays on its line; one that does not wraps at its arguments. Header comments above a statement pass through. A trailing -- comment does not: the printer joins tokens onto a line, and a line comment would swallow what followed.

Postgres versions

pgfmt reads the Postgres 17 and 18 grammar, and is tested against those two servers. It accepts what they accept, so a file it formats can hold a form an older server rejects: the underscore separator of 1_000_000 and the radix prefixes of 0x1f, 0o777, and 0b1010 all arrived in Postgres 16.

pgfmt reads a plain '...' string with standard_conforming_strings on, the Postgres default since 9.1. With the setting off, a backslash before a quote ends a string at a different byte. Do not format SQL for a server with the setting off.

Correctness

Format lexes its own output and compares the token stream to the input's, ignoring comments. A mismatch is an error rather than a written file, so a bug in the printer costs a failed check and not a query that means something else.

That check cannot see a token the lexer split, since both halves survive the trip. So the lexer refuses instead where a split would hide: a letter against the end of a number, such as 1e, is an error, and Postgres rejects the same input.

The check also cannot see a token that ends at a different byte than in Postgres. So the lexer ends each token where Postgres does. A name continues through $, so like$1 is one name. In an E'...' string, a backslash escapes the next byte. Block comments nest. A -- comment ends at CR or LF. Postgres joins two strings that a newline separates, and pgfmt refuses that form with an error.

An operator is the longest run of operator characters, so << and <-> stay whole. A run that ends in + or - loses that tail, as in Postgres, unless it holds one of ~ ! @ # ^ & | ` ? %. Each byte of the tail is then one operator. A prefix against a quote is part of the string: B'101', X'1F', N'abc', E'a', and U&'a' each stay one literal, with the prefix in uppercase.

Limits

pgfmt refuses a ( that never closes and a ) with no (, as Postgres does. It also refuses parens and CASE that nest deeper than 100 levels. An END closes only a CASE inside the same parens. Each level costs stack, a deeper indent on each line, and one more pass to measure the line. The limit keeps the output, memory, and time of Format in proportion to the input, whatever the input. Hand-written queries nest far less deep.

GitHub repo is a mirror

Development happens on sockeye, a self-hosted review and CI server, which holds in progress branches. GitHub receives main and the tags so go get works.

License

MIT

About

Formatter for Postgres SQL files

Resources

Stars

0 stars

Watchers

0 watching

Forks

Releases

Used by

Contributors

Languages