squawk

Why?

Install

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

Or via Docker

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

Or via the Playground

Or via VSCode

Usage

❯ 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)

squawk --help

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

Rules

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

Disabling rules via comments

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

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

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

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

Configuration file

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

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

Bot Setup

pre-commit hook

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

Prior Art / Related

Related Blog Posts / SE Posts / PG Docs

Dev

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

$ 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

cargo xtask new-rule'prefer big serial'

Releasing a New Version

Algolia

How it Works