Linter for PostgreSQL migrations
Project description
squawk
linter for Postgres migrations
quick start | rules documentation | github action | diy github integration
Why?
Prevent unexpected downtime caused by database migrations and encourage best practices around Postgres schemas and SQL.
Also it seemed like a nice project to spend more time with Rust.
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
Usage
❯ squawk example.sql
example.sql:2:1: warning: prefer-text-field
2 | --
3 | -- Create model Bar
4 | --
5 | CREATE TABLE "core_bar" (
6 | "id" serial NOT NULL PRIMARY KEY,
7 | "alpha" varchar(100) NOT NULL
8 | );
note: Changing the size of a varchar field requires an ACCESS EXCLUSIVE lock.
help: Use a text field with a check constraint.
example.sql:9:2: warning: require-concurrent-index-creation
9 |
10 | CREATE INDEX "field_name_idx" ON "table_name" ("field_name");
note: Creating an index blocks writes.
note: Create the index CONCURRENTLY.
example.sql:11:2: warning: disallowed-unique-constraint
11 |
12 | ALTER TABLE table_name ADD CONSTRAINT field_name_constraint UNIQUE (field_name);
note: Adding a UNIQUE constraint requires an ACCESS EXCLUSIVE lock which blocks reads.
help: Create an index CONCURRENTLY and create the constraint using the index.
squawk --help
squawk
Find problems in your SQL
USAGE:
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
--list-rules
List all available rules
-V, --version
Prints version information
--verbose
Enable debug logging output
OPTIONS:
-c, --config <config-path>
Path to the squawk config file (.squawk.toml)
--dump-ast <ast-format>
Output AST in JSON [possible values: Raw, Parsed, Debug]
--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
--explain <rule>
Provide documentation on the given rule
--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 stdin
ARGS:
<path>...
Paths to search
SUBCOMMANDS:
help Prints this message or the help of the given subcommand(s)
upload-to-github Comment on a PR with Squawk's results
Rules
Individual rules can be disabled via the --exclude
flag
squawk --exclude=adding-field-with-default,disallowed-unique-constraint example.sql
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: v0.10.0
hooks:
- id: squawk
files: path/to/postres/migrations/written/in/sql
Note the files
parameter as it specifies the location of the files to be linted.
prior art
related tools
- 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
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, the s/new-rule
script will create stubs for your rule in Rust and in Documentation site.
s/new-rule 'prefer big serial'
releasing a new version
-
update the CHANGELOG.md and bump version in the cli
Cargo.toml
, ensure the lock file is updated, and updatepackage.json
and commit the changes# update version in Cargo.toml files and package.json to 4.5.3 s/update-version 4.5.3
-
create a new release on github - CI will attach the binaries automatically
-
wait for build artifacts to be attached to release.
-
login to
npm
and publish new version.npm login npm publish
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 wraps calls to libpg_query-sys in a safe interface and parses the JSON into easier to work with structures. libpg_query-sys in turn uses bindgen to bind to libpg_query, which itself wraps Postgres' SQL parser in a bit of C code that outputs the parsed AST into a JSON string.
Squawk then runs the rule functions over the parsed AST, gathers and pretty prints the rule violations.
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
Built Distributions
Hashes for squawk_cli-1.4.0-py3-none-win_amd64.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | e2792d644a99fb05d8db9dac2569f10fab5645845c493bf999bfe75f39a21d63 |
|
MD5 | cbf16515c1c2382327017282816ff217 |
|
BLAKE2b-256 | 6dd651e550e35fd6e2d8fe209a5485a39411806de5b43308e99799c1f58ebcf7 |
Hashes for squawk_cli-1.4.0-py3-none-win32.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 17f8df7ef5d6cf152676a85f0465a1743fc0ad97863a74fcb8cff5eceab08577 |
|
MD5 | 73fa6e146b0c552a8dafbfeeb088fff9 |
|
BLAKE2b-256 | 0fa37091229e68bc5f1ac9a1185b6390591786a4759d79c49523c092b97b1ab7 |
Hashes for squawk_cli-1.4.0-py3-none-manylinux_2_28_x86_64.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 43e078672c47d39e90ecd1c136a0c9957c59d01ebf8944c10d99178a9f4bf8b0 |
|
MD5 | 684c49c5bcb4dfbe66e3984aa3445fb5 |
|
BLAKE2b-256 | 01ec455ea3f630af2cc8412fc299721c316ae4f017ee7b2e525663bbf6010e52 |
Hashes for squawk_cli-1.4.0-py3-none-manylinux_2_17_aarch64.manylinux2014_aarch64.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 92d3365e46b68b067fe142d1f33bcbc773d3d3dd371e929bf23a60f1c6e5828d |
|
MD5 | 0bd371169d68224224f4c6b0386ae701 |
|
BLAKE2b-256 | 074e414ebcb6bee21e9e8f216e0063a0c8bba3ff6ba4b2b38391184f5340aff6 |
Hashes for squawk_cli-1.4.0-py3-none-macosx_11_0_arm64.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 862dab989b49c38a8d0ac7fa9397aef58216728f1df5dcd6e5867eedeb829d1a |
|
MD5 | 6901ed2fbfb7f907eeff6ed33911bf9e |
|
BLAKE2b-256 | 81a2ea670d425cb58b85d4f7eda49662f5bee8c40e1603842490d9f4cbfbd171 |
Hashes for squawk_cli-1.4.0-py3-none-macosx_10_12_x86_64.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 922ef7b38fda0868541394df27ed618fbd244b8f235e7c2dd407ac17a4990c24 |
|
MD5 | 501c0fc6eb9b3f0369057228475156ab |
|
BLAKE2b-256 | 15f19b7327d7ac69271c17918305adc2484a27c1a5fe5fdc1a6392836ebdefee |