gem-hunter

squawk

Why?

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

# 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

Or via VSCode

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

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

Disabling rules via comments

-- squawk-ignore ban-drop-column
alter table t drop column c cascade;

-- squawk-ignore ban-drop-column, renaming-column,ban-drop-database
alter table t drop column c cascade;

-- squawk-ignore-file ban-drop-column
alter table t drop column c cascade;
-- also ignored!
alter table t drop column d cascade;

-- squawk-ignore-file
alter table t drop column c cascade;
create table t (a int);

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/squawk
    rev: v2.71.0
    hooks:
      - id: squawk
        files: path/to/postgres/migrations/written/in/sql

Prior Art / Related

Related Blog Posts / SE Posts / PG Docs

Dev

cargo install
cargo 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