squawk 
Linter for Postgres migrations & SQL
Quick Start | Playground | Rules Documentation | GitHub Action | DIY GitHub Integration
Why?
Prevent unexpected downtime caused by database migrations and encourage best practices around Postgres schemas and SQL.
Install
npm install -g squawk-cli
# or via PYPI
pip install squawk-cli
# or install binaries directly via the releases page
https://github.com/sbdchd/squawk/releases
Or via Docker
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 directory
docker run --rm -v $(pwd):/data ghcr.io/sbdchd/squawk:latest *.sql
Or via the Playground
Use the WASM powered playground to check your SQL locally in the browser!
Or via VSCode
https://marketplace.visualstudio.com/items?itemName=sbdchd.squawk
Usage
❯ squawk example.sql
warning[prefer-bigint-over-int]: Using 32-bit integer fields can result in 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.
╭╴
6 │ "id" bigserial NOT NULL PRIMARY KEY,
╰╴ +++
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 an `IDENTITY` column instead.
╭╴
6 - "id" serial NOT NULL PRIMARY KEY,
6 + "id" integer generated by default as identity NOT NULL PRIMARY KEY,
╰╴
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.
╭╴
7 - "alpha" varchar(100) NOT NULL
7 + "alpha" text NOT NULL
╰╴
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.
╭╴
10 │ CREATE INDEX concurrently "field_name_idx" ON "table_name" ("field_name");
╰╴ ++++++++++++
warning[constraint-missing-not-valid]: By default new constraints require a table scan and block writes to the table while 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 table while 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 solutions for each rule at https://squawkhq.com/docs/rules
Found 6 issues in 1 file (checked 1 source file)
squawk --help
Find problems in your SQL
Usage: squawk [OPTIONS] [path]... [COMMAND]
Commands:
server Run the language server
upload-to-github Comment on a PR with Squawk's results
help Print this message or the help of the given subcommand(s)
Arguments:
[path]...
Paths or patterns to search
Options:
--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
-i, --include <rule>
Include opt-in rules that are disabled by default
Rules listed in --exclude take precedence over --include.
For example: --include=require-table-schema
--pg-version <PG_VERSION>
Specify postgres version
For example: --pg-version=13.0
--debug <format>
Output debug format
[possible values: lex, parse, ast]
--reporter <REPORTER>
Style of error reporting
[possible values: tty, gcc, json, gitlab]
--stdin-filepath <filepath>
Path to use in reporting for stdin
--verbose
Enable debug logging output
-c, --config <CONFIG_PATH>
Path to the squawk config file (.squawk.toml)
--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 any config file that sets this
--no-error-on-unmatched-pattern
Do not exit with an error when provided path patterns do not match any files
-h, --help
Print help (see a summary with '-h')
-V, --version
Print version
Rules
Individual rules can be disabled via the --exclude flag
squawk --exclude=adding-field-with-default,disallowed-unique-constraint example.sql
Disabling rules via comments
Rule violations can be ignored via the squawk-ignore comment:
-- squawk-ignore ban-drop-column
alter table 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-database
alter table t drop column c cascade;
To ignore a rule for the entire file, use squawk-ignore-file:
-- squawk-ignore-file ban-drop-column
alter table t drop column c cascade;
-- also ignored!
alter table t drop column d cascade;
Or leave off the rule names to ignore all rules for the file
-- squawk-ignore-file
alter table t drop column c cascade;
create table t (a int);
Configuration file
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 the Squawk website for documentation on each rule with examples and reasoning.
Bot Setup
Squawk works as a CLI tool but can also create comments on GitHub Pull
Requests using the upload-to-github subcommand.
Here's an example comment created by squawk using the example.sql in the repo:
https://github.com/sbdchd/squawk/pull/14#issuecomment-647009446
See the "GitHub Integration" docs for more information.
pre-commit hook
Integrate Squawk into Git workflow with pre-commit. Add the following
to your project's .pre-commit-config.yaml:
repos:
- repo: https://github.com/sbdchd/squawk
rev: v2.70.0
hooks:
- id: squawk
files: path/to/postgres/migrations/written/in/sql
Note the files parameter as it specifies the location of the files to be linted.
Prior Art / Related
- 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://www.sqlstyle.guide
- https://github.com/ivank/potygen
Related Blog Posts / SE Posts / PG Docs
- 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
Dev
cargo install
cargo 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
Adding a New Rule
When adding a new rule, running cargo xtask new-rule will create stubs for your rule in the Rust crate and in Documentation site.
cargo xtask new-rule 'prefer big serial'
Releasing a New Version
-
Run
s/update-version# update version in squawk/Cargo.toml, package.json, flake.nix to 4.5.3 s/update-version 4.5.3
-
Update the
CHANGELOG.mdInclude a description of any fixes / additions. Make sure to include the PR numbers and credit the authors.
-
Create a new release on GitHub
Use the text and version from the
CHANGELOG.md
Algolia
The squawkhq.com Algolia index can be found on the crawler website. Algolia reindexes the site every day at 5:30 (UTC).
How it Works
Squawk uses its parser (based on rust-analyzer's parser) to create a CST. The linters then use an AST layered on top of the CST to navigate and record warnings, which are then pretty printed!
Release files for squawk-cli 2.70.0
For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.
Source distribution (sdist)
| File | Size | Uploaded | |
|---|---|---|---|
| squawk_cli-2.70.0.tar.gz | 1.6 MB | Details |
Built distributions (wheels)
| File | Reset | |||
|---|---|---|---|---|
| squawk_cli-2.70.0-py3-none-win_amd64.whl | Python 3 | none | Windows x86-64 | Details |
| squawk_cli-2.70.0-py3-none-win32.whl | Python 3 | none | Windows x86-32 | Details |
| squawk_cli-2.70.0-py3-none-manylinux_2_28_x86_64.whl | Python 3 | none | Linux glibc 2.28+ x86-64 | Details |
| squawk_cli-2.70.0-py3-none-manylinux_2_28_aarch64.whl | Python 3 | none | Linux glibc 2.28+ ARM64 | Details |
| squawk_cli-2.70.0-py3-none-macosx_11_0_arm64.whl | Python 3 | none | macOS 11.0+ ARM64 | Details |
| squawk_cli-2.70.0-py3-none-macosx_10_12_x86_64.whl | Python 3 | none | macOS 10.12+ x86-64 | Details |
Total release size: 39.9 MB
Release files / squawk_cli-2.70.0.tar.gz
| Download URL | squawk_cli-2.70.0.tar.gz |
|---|---|
| Size | 1.6 MB |
| Tags | Source |
|
SHA-256 checksum How to use checksums |
a2d10a22cafd400e1eec78d487e662aaff996825788f99418e0c7ba85c749c32
|
|
BLAKE2b-256 checksum How to use checksums |
384b2762ef5fec8c8a86097efbb748d5c16b47794cbbfc99af6b2d6aac9e6969
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
Yes |
| Uploaded via |
maturin/1.15.0
|
Release files / squawk_cli-2.70.0-py3-none-win_amd64.whl
| Download URL | squawk_cli-2.70.0-py3-none-win_amd64.whl |
|---|---|
| Size | 5.7 MB |
| Tags | Python 3 Windows x86-64 |
|
SHA-256 checksum How to use checksums |
7da1629aee5855f6b12eed127b8dac518d71fcbd120559fd4facfce925967dfe
|
|
BLAKE2b-256 checksum How to use checksums |
347da64493a67b35f6730179a82e3ff52b827c2887230327350e0474a00abb75
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
Yes |
| Uploaded via |
maturin/1.15.0
|
Release files / squawk_cli-2.70.0-py3-none-win32.whl
| Download URL | squawk_cli-2.70.0-py3-none-win32.whl |
|---|---|
| Size | 4.9 MB |
| Tags | Python 3 Windows x86-32 |
|
SHA-256 checksum How to use checksums |
1bdaf04df63c21c60171377608875773bfdd326f693d727ab4270c9ce7121e4a
|
|
BLAKE2b-256 checksum How to use checksums |
e8913177d6412da706e93bb3459886bb44151afe8399661fff8c35bf0a2dd861
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
Yes |
| Uploaded via |
maturin/1.15.0
|
Release files / squawk_cli-2.70.0-py3-none-manylinux_2_28_x86_64.whl
| Download URL | squawk_cli-2.70.0-py3-none-manylinux_2_28_x86_64.whl |
|---|---|
| Size | 8.0 MB |
| Tags | Linux glibc 2.28+ x86-64 Python 3 |
|
SHA-256 checksum How to use checksums |
e67651bb984b01276752ca685f870b281cf493d5d7bd318607148b657c359b52
|
|
BLAKE2b-256 checksum How to use checksums |
04036391948bd32c77ae33362f16d1e480de9fc026c7ba8e4a94196b9c302718
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
Yes |
| Uploaded via |
maturin/1.15.0
|
Release files / squawk_cli-2.70.0-py3-none-manylinux_2_28_aarch64.whl
| Download URL | squawk_cli-2.70.0-py3-none-manylinux_2_28_aarch64.whl |
|---|---|
| Size | 8.4 MB |
| Tags | Linux glibc 2.28+ ARM64 Python 3 |
|
SHA-256 checksum How to use checksums |
1c352fd18443d808d8ce48f5d4efa43646e210fc951437f6c3c25e51394f4a8f
|
|
BLAKE2b-256 checksum How to use checksums |
dfa6a6e7de920f0b4b77a3a1640e086ace8523372fb415f881b5f080aa43a042
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
Yes |
| Uploaded via |
maturin/1.15.0
|
Release files / squawk_cli-2.70.0-py3-none-macosx_11_0_arm64.whl
| Download URL | squawk_cli-2.70.0-py3-none-macosx_11_0_arm64.whl |
|---|---|
| Size | 5.6 MB |
| Tags | Python 3 macOS 11.0+ ARM64 |
|
SHA-256 checksum How to use checksums |
c6164a083f8bee368fc7c9c1cd7de4704c0775aa75767eefb371c0bd37d3bf08
|
|
BLAKE2b-256 checksum How to use checksums |
221169f1c85a4bd6a66a1c945a4d3e11d2dbf39b1610ecd4564432392e7d7c9f
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
Yes |
| Uploaded via |
maturin/1.15.0
|
Release files / squawk_cli-2.70.0-py3-none-macosx_10_12_x86_64.whl
| Download URL | squawk_cli-2.70.0-py3-none-macosx_10_12_x86_64.whl |
|---|---|
| Size | 5.8 MB |
| Tags | Python 3 macOS 10.12+ x86-64 |
|
SHA-256 checksum How to use checksums |
699021805add4ec82274796081c0bc64ea2516ffd48be7690e3a3b6d60211411
|
|
BLAKE2b-256 checksum How to use checksums |
658fcf560c4cdc327b000dc2777e2cd8d96a969910908d4e7eabe17bc46ffd74
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
Yes |
| Uploaded via |
maturin/1.15.0
|
