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

``shell
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
`

$3

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

`shell


Assuming you want to check sql files in the current directory


docker run --rm -v $(pwd):/data ghcr.io/sbdchd/squawk:latest *.sql
`

$3

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

$3

Usage

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

$3

`
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

-V, --version
Prints version information

--verbose
Enable debug logging output


OPTIONS:
-c, --config
Path to the squawk config file (.squawk.toml)

--debug
Output debug info [possible values: Lex, Parse]

--exclude-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 ...
Exclude specific warnings

For example: --exclude=require-concurrent-index-creation,ban-drop-database

--pg-version
Specify postgres version

For example: --pg-version=13.0
--reporter
Style of error reporting [possible values: Tty, Gcc, Json]

--stdin-filepath
Path to use in reporting for stdin


ARGS:
...
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

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

$3

Rule violations can be ignored via the squawk-ignore comment:

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

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

`sql
-- 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:

`sql
-- 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

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

$3

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.

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

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

Example .squawk.toml

`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:

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:

`yaml
repos:
- repo: https://github.com/sbdchd/squawk
rev: 2.40.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

-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-

Related Blog Posts / SE Posts / PG Docs

-
-
-
-
-
-
-
-
-

Dev

`shell
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
`

$3

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

`bash
cargo xtask new-rule 'prefer big serial'
`

$3

1. Run s/update-version

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

2. Update the CHANGELOG.md

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

3. Create a new release on GitHub

Use the text and version from the CHANGELOG.md`

$3

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!