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

Getting Started

For more, see the documentation.

Installation

pip install pgrubic

pgrubic is only supported on Python 3.12 or higher.

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 the difference between `file.sql` and how the formatted file would look like

pgrubic can also be used as a pre-commit hook:

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

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.

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

cache-dir = ".pgrubic_cache"
include = []
exclude = []
respect-gitignore = true
[lint]
target-postgres-version = 14
additional-non-volatile-functions = []
select = []
ignore = []
include = []
exclude = []
ignore-noqa = false
disallowed-schemas = []
allowed-extensions = []
allowed-languages = []
fix = false
fixable = []
unfixable = []
disallowed-data-types = []
required-columns = []
timestamp-column-suffix = "_at"
date-column-suffix = "_date"
regex-partition = "^.+$"
regex-index = "^.+$"
regex-constraint-primary-key = "^.+$"
regex-constraint-unique-key = "^.+$"
regex-constraint-foreign-key = "^.+$"
regex-constraint-check = "^.+$"
regex-constraint-exclusion = "^.+$"
regex-sequence = "^.+$"

[format]
include = []
exclude = []
comma-at-beginning = true
compact-parenthesized-lists-margin = 90
uppercase-keywords = true
type-casting-style = "standard"
rewrite-function-calls-as-equivalent-syntax = true
new-line-before-semicolon = false
remove-pg-catalog-from-functions = true
remove-default-index-access-method = true
lines-between-statements = 1
check = false
diff = false
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 here.

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.