pgfmt 0.1.0

A PostgreSQL SQL formatter

pgfmt

A PostgreSQL SQL formatter with multiple style options.

pgfmt parses SQL using pgparse (PostgreSQL's own parser via libpg_query) and reformats it according to one of several well-known style guides.

Installation

pip install pgfmt

CLI Usage

# Format a file (default: river style)
pgfmt query.sql

# Format from stdin
echo "SELECT a,b FROM t WHERE x=1" | pgfmt

# Choose a style
pgfmt --style mozilla query.sql
pgfmt --style dbt query.sql

# Check if already formatted (exit 1 if not)
pgfmt --check query.sql

Library Usage

import pgfmt

sql = "SELECT a, b FROM my_table WHERE x = 1 AND y = 2"

# Default (river) style
print(pgfmt.format(sql))

# Choose a style
print(pgfmt.format(sql, style='mozilla'))
print(pgfmt.format(sql, style='dbt'))

Styles

river (default)

Based on sqlstyle.guide by Simon Holywell. Keywords are right-aligned to form a visual "river" separating keywords from content. Uppercase keywords.

SELECT a.title,
       a.release_date
  FROM albums AS a
 WHERE a.title = 'Charcoal Lane'
    OR a.title = 'The New Danger';

mozilla

Based on the Mozilla SQL Style Guide. Keywords left-aligned at column 0, content indented 4 spaces underneath. One item per line. Uppercase keywords.

SELECT
    a.title,
    a.release_date
FROM albums AS a
WHERE
    a.title = 'Charcoal Lane'
    OR a.title = 'The New Danger';

aweber

Based on river style with JOINs as river keywords. INNER JOIN, LEFT JOIN, etc. participate in river alignment. Uppercase keywords.

    SELECT r.last_name
      FROM riders AS r
INNER JOIN bikes AS b
        ON r.bike_vin_num = b.vin_num
       AND b.engines > 2;

dbt

Based on dbt Labs' SQL style. Lowercase keywords, 4-space indent, blank lines between clauses, generous whitespace. Explicit join types.

select
    a.title,
    a.release_date

from albums as a

where
    a.title = 'Charcoal Lane'
    or a.title = 'The New Danger'

gitlab

Based on the GitLab SQL Style Guide. Uppercase keywords, 2-space indent, blank lines inside CTE bodies.

SELECT
  a.title,
  a.release_date
FROM albums AS a
WHERE
  a.title = 'Charcoal Lane'
  OR a.title = 'The New Danger';

kickstarter

Based on the Kickstarter SQL Style Guide. Uppercase keywords, 2-space indent, JOIN ON on same line, compact CTE chaining.

SELECT
  a.title,
  a.release_date
FROM albums AS a
INNER JOIN orders AS o ON a.id = o.album_id
WHERE
  a.title = 'Charcoal Lane'
  AND a.year > 2000;

mattmc3

Based on the Modern SQL Style Guide by mattmc3. Lowercase river-style with leading commas. Uses plain join instead of inner join.

select a.title
     , a.release_date
  from albums as a
  join orders as o
    on a.id = o.album_id
 where a.title = 'Charcoal Lane'
   and a.year > 2000;