pgrubic

pgrubic is a PostgreSQL linter and formatter for schema migrations and design best practices.

Features

• Over 100+ rules
• Automatic violation correction (e.g., automatically add concurrently to index create statements)
• River style code formatting for DML statements
• Almost identical styling with pg_dump for DDL statements
• Python 3.12+ compatibility
• Automatic caching to avoid reformatting unchanged files
• Violations suppression, statement level, and file level
• Can be used as a library in your own Python projects, not just as a CLI tool

Getting Started

For more, see the documentation.

Installation

pgrubic is only supported on Python 3.12 or higher.

via PyPI

pip install pgrubic

via GitHub

pip install git+https://github.com/bolajiwahab/pgrubic.git

via Docker

docker run --rm -it -v $PWD:/sql ghcr.io/bolajiwahab/pgrubic:2.1.0 lint *.sql     # Lint SQL files
docker run --rm -it -v $PWD:/sql ghcr.io/bolajiwahab/pgrubic:2.1.0 format *.sql   # Format SQL files

via Github Actions

- uses: azellarhq/pgrubic-action@v2
  with:
    src: "./src"
    pgrubic-version: "2.1.0"

via pre-commit

- repo: https://github.com/bolajiwahab/pgrubic
  rev: 2.1.0
  hooks:
    - id: pgrubic-lint
    - id: pgrubic-format

via Web Browser

Lint, format, and fix your migrations directly in your browser, no installation required: Try it online

Usage

For linting, try any of the following:

pgrubic lint                         # Lint SQL files in the current directory (and any subdirectories)
pgrubic lint .                       # Lint SQL files in the current directory (and any subdirectories)
pgrubic lint directory               # Lint SQL files in *directory* (and any subdirectories)
pgrubic lint directory/*.sql         # Lint SQL files in *directory*
pgrubic lint directory/file.sql      # Lint `file.sql` in *directory*
pgrubic lint file.sql                # Lint `file.sql`
pgrubic lint directory/*.sql --fix   # Lint SQL files in *directory* and fix violations automatically
pgrubic lint file.sql --fix          # Lint `file.sql` and fix fixable violations automatically

Sample output from linting:

pgrubic lint *.sql

file.sql:1:38: TP017: Boolean field should be not be nullable

1 | ALTER TABLE public.example ADD COLUMN foo boolean DEFAULT false;

pgrubic file.sql

test.sql:1:38: TP017: Boolean field should be not be nullable

1 | ALTER TABLE public.example ADD COLUMN foo boolean DEFAULT false;

For formatting, try any of the following:

pgrubic format                         # Format SQL files in the current directory (and any subdirectories)
pgrubic format .                       # Format SQL files in the current directory (and any subdirectories)
pgrubic format directory               # Format SQL files in *directory* (and any subdirectories)
pgrubic format directory/*.sql         # Format SQL files in *directory*
pgrubic format directory/file.sql      # Format `file.sql` in *directory*
pgrubic format file.sql                # Format `file.sql`
pgrubic format directory/*.sql --check # Check if SQL files would have been modified, returning a non-zero exit code
pgrubic format file.sql --diff         # Report if `file.sql` would have been modified, returning a non-zero exit code as well as the difference between `file.sql` and how the formatted file would look like

Configuration

pgrubic can be configured via the [pgrubic.toml] file in either the current directory, up to the root directory or the path set by the PGRUBIC_CONFIG_PATH environment variable. Config values can also be overridden using the --config command-line argument, which accepts a TOML <KEY> = <VALUE> pair and may be repeated, e.g. --config "lint.target-postgres-version = 17".

The following configuration options are available in the [pgrubic.toml] with the following defaults:

# Path to the cache directory
cache-dir = ".pgrubic_cache"

# Include all files
include = []

# Exclude no files
exclude = []

# Respect gitignore
respect-gitignore = true

[lint]
# Target version 14 of PostgreSQL
target-postgres-version = 14

# Enable all rules
select = []

# Disable no rules
ignore = []

# Include all files
include = []

# Exclude no files
exclude = []

# Ignore suppressing violations that are marked as `noqa`
ignore-noqa = false

# List of additional non-volatile functions
additional-non-volatile-functions = []

# Disallowed schemas
disallowed-schemas = []

# Allowed extensions
allowed-extensions = []

# Allowed languages
allowed-languages = []

# Do not fix violations automatically
fix = false

# Consider all rules as fixable
fixable = []

# Consider all rules as fixable
unfixable = []

# Disallowed data types
disallowed-data-types = []

# Required columns
required-columns = []

# Suffix timestamp columns with `_at`
timestamp-column-suffix = "_at"

# Suffix date columns with `_date`
date-column-suffix = "_date"

# Allow any naming convention for partitions
regex-partition = "^.+$"

# Allow any naming convention for indexes
regex-index = "^.+$"

# Allow any naming convention for primary key constraints
regex-constraint-primary-key = "^.+$"

# Allow any naming convention for unique keys
regex-constraint-unique-key = "^.+$"

# Allow any naming convention for foreign keys
regex-constraint-foreign-key = "^.+$"

# Allow any naming convention for check constraints
regex-constraint-check = "^.+$"

# Allow any naming convention for exclusion constraints
regex-constraint-exclusion = "^.+$"

# Allow any naming convention for sequences
regex-sequence = "^.+$"

[format]
# Include all files
include = []

# Exclude no files
exclude = []

# Comma at the beginning of an item
comma-at-beginning = true

# Compact parenthesized lists margin of 90
compact-parenthesized-lists-margin = 90

# Uppercase keywords
uppercase-keywords = true

# Type casting style is standard
# CAST(value AS type)
type-casting-style = "standard"

# Rewrite some function calls using their equivalent SQL syntax.
# For example, pg_catalog.timezone('UTC', value) is formatted as "value AT TIME ZONE 'UTC'"
rewrite-function-calls-as-equivalent-syntax = true

# Do not place the semicolon on a new line
new-line-before-semicolon = false

# Remove pg_catalog from functions
remove-pg-catalog-from-functions = true

# Remove default index access method (btree)
remove-default-index-access-method = true

# Separate statements by N new lines (default: 1)
lines-between-statements = 1

# Check if files would have been modified, returning a non-zero exit code
check = false

# Report the diff between the current file and how it would be formatted,
# returning a non-zero exit code if changes would be made
diff = false

# Read the cache to avoid reformatting unchanged files
no-cache = false

Some configuration options can be supplied via CLI arguments such as --check, --diff, --fix.

pgrubic format --check

pgrubic format --diff

pgrubic lint --fix

Rules

There are 100+ rules. All rules are enabled by default. For a complete list, see rules.

Formatting style

pgrubic uses River style code formatting.

Contributing

We welcome and greatly appreciate contributions. If you would like to contribute, please see the contributing guidelines.

Support

Encountering issues? Take a look at the existing GitHub issues, and don't hesitate to open a new one.

Acknowledgments

pgrubic is inspired by a number of similar tools such as Strong Migrations, squabble, squawk, pgextwlist, Don't_Do_This and schemalint.

pgrubic is built upon the shoulders of:

• pglast - Python bindings to libpg_query
• libpg_query - PostgreSQL parser outside of the server environment

License

pgrubic is released under GPL-3.0 license.