Linter for Postgres migrations & SQL

Quick Start |Playground |Rules Documentation |GitHub Action |DIY GitHub Integration

Prevent unexpected downtime caused by database migrations and encourage bestpractices around Postgres schemas and SQL.

npm install -g squawk-cli# or via PYPIpip install squawk-cli# or install binaries directly via the releases pagehttps://github.com/sbdchd/squawk/releases

You can also run Squawk using Docker. The official image is available on GitHub Container Registry.

# Assuming you want to check sql files in the current directorydocker run --rm -v$(pwd):/data ghcr.io/sbdchd/squawk:latest*.sql

Use the WASM powered playground to check your SQL locally in the browser!

https://play.squawkhq.com

https://marketplace.visualstudio.com/items?itemName=sbdchd.squawk

❯ squawk example.sqlwarning[prefer-bigint-over-int]: Using 32-bit integer fields can resultin hitting the max`int` limit. --> example.sql:6:10|6|"id" serial NOT NULL PRIMARY KEY,|          ------|  = help: Use 64-bit integer values instead to prevent hitting this limit.warning[prefer-identity]: Serial types make schema, dependency, and permission management difficult. --> example.sql:6:10|6|"id" serial NOT NULL PRIMARY KEY,|          ------|  = help: Use Identity columns instead.warning[prefer-text-field]: Changing the size of a`varchar` field requires an`ACCESS EXCLUSIVE` lock, that will prevent all reads and writes to the table. --> example.sql:7:13|7|"alpha" varchar(100) NOT NULL|             ------------|  = help: Use a`TEXT` field with a`CHECK` constraint.warning[require-concurrent-index-creation]: During normal index creation, table updates are blocked, but reads are still allowed.  --> example.sql:10:1|10| CREATE INDEX"field_name_idx" ON"table_name" ("field_name");| ------------------------------------------------------------|   = help: Use`CONCURRENTLY` to avoid blocking writes.warning[constraint-missing-not-valid]: By default new constraints require a table scan and block writes to the tablewhile that scan occurs.  --> example.sql:12:24|12| ALTER TABLE table_name ADD CONSTRAINT field_name_constraint UNIQUE (field_name);|                        --------------------------------------------------------|   = help: Use`NOT VALID` with a later`VALIDATE CONSTRAINT` call.warning[disallowed-unique-constraint]: Adding a`UNIQUE` constraint requires an`ACCESS EXCLUSIVE` lock which blocks reads and writes to the tablewhile the index is built.  --> example.sql:12:28|12| ALTER TABLE table_name ADD CONSTRAINT field_name_constraint UNIQUE (field_name);|                            ----------------------------------------------------|   = help: Create an index`CONCURRENTLY` and create the constraint using the index.Find detailed examples and solutionsfor each rule at https://squawkhq.com/docs/rulesFound 7 issuesin 1 file (checked 1source file)

squawkFind problems in your SQLUSAGE:    squawk [FLAGS] [OPTIONS] [path]... [SUBCOMMAND]FLAGS:        --assume-in-transaction            Assume that a transaction will wrap each SQL file when run by a migration tool            Use --no-assume-in-transaction to override this setting in any config file that exists    -h, --help            Prints help information    -V, --version            Prints version information        --verbose            Enable debug logging outputOPTIONS:    -c, --config <config-path>            Path to the squawk config file (.squawk.toml)        --debug <format>            Output debug info [possible values: Lex, Parse]        --exclude-path <excluded-path>...            Paths to exclude            For example: --exclude-path=005_user_ids.sql --exclude-path=009_account_emails.sql            --exclude-path='*user_ids.sql'    -e, --exclude <rule>...            Exclude specific warnings            For example: --exclude=require-concurrent-index-creation,ban-drop-database        --pg-version <pg-version>            Specify postgres version            For example: --pg-version=13.0        --reporter <reporter>            Style of error reporting [possible values: Tty, Gcc, Json]        --stdin-filepath <filepath>            Path to use in reporting for stdinARGS:    <path>...            Paths to searchSUBCOMMANDS:    help                Prints this message or the help of the given subcommand(s)    upload-to-github    Comment on a PR with Squawk's results

Individual rules can be disabled via the--exclude flag

squawk --exclude=adding-field-with-default,disallowed-unique-constraint example.sql

Rule violations can be ignored via thesquawk-ignore comment:

-- squawk-ignore ban-drop-columnaltertable t drop column c cascade;

You can also ignore multiple rules by making a comma seperated list:

-- squawk-ignore ban-drop-column, renaming-column,ban-drop-databasealtertable t drop column c cascade;

To ignore a rule for the entire rule, usesquawk-ignore-file:

-- squawk-ignore-file ban-drop-columnaltertable t drop column c cascade;-- also ignored!altertable t drop column d cascade;

Or leave off the rule names to ignore all rules for the file

-- squawk-ignore-filealtertable t drop column c cascade;createtablet (aint);

Rules can also be disabled with a configuration file.

By default, Squawk will traverse up from the current directory to find a.squawk.toml configuration file. You may specify a custom path with the-c or--config flag.

squawk --config=~/.squawk.toml example.sql

The--exclude flag will always be prioritized over the configuration file.

Example.squawk.toml

excluded_rules = ["require-concurrent-index-creation","require-concurrent-index-deletion",]

See theSquawk website for documentation on each rule with examples and reasoning.

Squawk works as a CLI tool but can also create comments on GitHub PullRequests using theupload-to-github subcommand.

Here's an example comment created bysquawk using theexample.sql in the repo:

https://github.com/sbdchd/squawk/pull/14#issuecomment-647009446

See the"GitHub Integration" docs for more information.

Integrate Squawk into Git workflow withpre-commit. Add the followingto your project's.pre-commit-config.yaml:

repos:  -repo:https://github.com/sbdchd/squawkrev:v0.10.0hooks:      -id:squawkfiles:path/to/postgres/migrations/written/in/sql

Note thefiles parameter as it specifies the location of the files to be linted.

• https://github.com/erik/squabble
• https://github.com/yandex/zero-downtime-migrations
• https://github.com/tbicr/django-pg-zero-downtime-migrations
• https://github.com/3YOURMIND/django-migration-linter
• https://github.com/ankane/strong_migrations
• https://github.com/AdmTal/PostgreSQL-Query-Lock-Explainer
• https://github.com/stripe/pg-schema-diff
• https://github.com/kristiandupont/schemalint
• https://github.com/supabase-community/postgres-language-server
• https://github.com/premium-minds/sonar-postgres-plugin
• https://engineering.fb.com/2022/11/30/data-infrastructure/static-analysis-sql-queries/
• https://github.com/xNaCly/sqleibniz
• https://github.com/sqlfluff/sqlfluff
• https://atlasgo.io/lint/analyzers
• https://github.com/tobymao/sqlglot
• https://github.com/paupino/pg_parse
• https://github.com/sql-formatter-org/sql-formatter
• https://github.com/darold/pgFormatter
• https://github.com/sqls-server/sqls
• https://github.com/joe-re/sql-language-server
• https://github.com/nene/sql-parser-cst
  • https://github.com/nene/prettier-plugin-sql-cst
• https://www.sqlstyle.guide
• https://github.com/ivank/potygen

• https://www.braintreepayments.com/blog/safe-operations-for-high-volume-postgresql/
• https://gocardless.com/blog/zero-downtime-postgres-migrations-the-hard-parts/
• https://www.citusdata.com/blog/2018/02/22/seven-tips-for-dealing-with-postgres-locks/
• https://realpython.com/create-django-index-without-downtime/#non-atomic-migrations
• https://dba.stackexchange.com/questions/158499/postgres-how-is-set-not-null-more-efficient-than-check-constraint
• https://www.postgresql.org/docs/10/sql-altertable.html#SQL-ALTERTABLE-NOTES
• https://www.postgresql.org/docs/current/explicit-locking.html
• https://benchling.engineering/move-fast-and-migrate-things-how-we-automated-migrations-in-postgres-d60aba0fc3d4
• https://medium.com/paypal-tech/postgresql-at-scale-database-schema-changes-without-downtime-20d3749ed680

cargo installcargo run./s/test./s/lint./s/fmt

... or with nix:

$ nix develop[nix-shell]$ cargo run[nix-shell]$ cargo insta review[nix-shell]$ ./s/test[nix-shell]$ ./s/lint[nix-shell]$ ./s/fmt

When adding a new rule, runningcargo xtask new-rule will create stubs for your rule in the Rust crate and in Documentation site.

cargo xtask new-rule'prefer big serial'

1. Update theCHANGELOG.md

   Include a description of any fixes / additions. Make sure to include the PR numbers and credit the authors.

2. Runs/update-version

   # update version in squawk/Cargo.toml, package.json, flake.nix to 4.5.3s/update-version 4.5.3

3. Create a new release on GitHub

   Use the text and version from theCHANGELOG.md

The squawkhq.com Algolia index can be found onthe crawler website. Algolia reindexes the site every day at 5:30 (UTC).

Squawk uses its parser (based on rust-analyzer's parser) to create a CST. Thelinters then use an AST layered on top of the CST to navigate and recordwarnings, which are then pretty printed!