# squawk [![npm](https://img.shields.io/npm/v/squawk-cli)](https://www.npmjs.com/package/squawk-cli) > Linter for Postgres migrations & SQL [Quick Start](https://squawkhq.com/docs/) | [Playground](https://play.squawkhq.com) | [Rules Documentation](https://squawkhq.com/docs/rules) | [GitHub Action](https://github.com/sbdchd/squawk-action) | [DIY GitHub Integration](https://squawkhq.com/docs/github_app) ## 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 ``` ### Or via Docker 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 ``` ### Or via the Playground Use the WASM powered playground to check your SQL locally in the browser! ### Or via VSCode ## 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) ``` ### `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 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 -i, --include 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 Specify postgres version For example: --pg-version=13.0 --debug Output debug format [possible values: lex, parse, ast] --reporter Style of error reporting [possible values: tty, gcc, json, gitlab] --stdin-filepath Path to use in reporting for stdin --verbose Enable debug logging output -c, --config 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 ```shell 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: ```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); ``` ### 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. ```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](https://squawkhq.com/docs/rules) 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](https://squawkhq.com/docs/github_app) for more information. ## `pre-commit` hook Integrate Squawk into Git workflow with [pre-commit](https://pre-commit.com/). Add the following to your project's `.pre-commit-config.yaml`: ```yaml repos: - repo: https://github.com/sbdchd/squawk rev: v2.58.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 ``` ### 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. ```bash cargo xtask new-rule 'prefer big serial' ``` ### Releasing a New Version 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` ### Algolia The squawkhq.com Algolia index can be found on [the crawler website](https://crawler.algolia.com/admin/crawlers/9bf0dffb-bc5a-4d46-9b8d-2f1197285213/overview). 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!