squawk-cli: PostgreSQL Migration Linter

2.47.0 · active · verified Thu Apr 16

Squawk-cli is a linter for PostgreSQL migrations and SQL, designed to identify potentially dangerous operations that could lead to database locks, downtime, or compatibility issues. It helps prevent unexpected downtime by enforcing best practices for schema changes. The current version is 2.47.0. It maintains a relatively active release cadence, with multiple minor versions often released within a few weeks to months.

Common errors

```
squawk <file.sql> exited with code 1 (or similar CI/CD failure message for non-zero exit)
```
cause Squawk-cli treats warnings as failures, returning an exit code of 1 even if no 'errors' (as distinct from warnings) were found.

fix If warnings are acceptable, modify your CI/CD configuration to allow exit code 1. Alternatively, configure Squawk (e.g., via `.squawk.toml` or `--exclude` flags) to ignore specific rules that generate warnings you deem acceptable.
```
Error: Received 404: Not Found (during npm install squawk-cli or similar binary fetching issue)
```
cause While specifically an `npm` issue, a '404: Not Found' error for the binary indicates a problem with the release assets, which could potentially affect `pip` installations if the underlying binary download mechanism faces issues.

fix Ensure your network can access `https://github.com/sbdchd/squawk/releases/download`. Verify the project's GitHub releases page for any reported issues or temporary outages. Try installing a specific older version if the latest is problematic (e.g., `pip install squawk-cli==X.Y.Z`).
```
Squawk still lints files or rules that should be excluded (e.g., via --exclude-path or .squawk.toml)
```
cause There can be unexpected interactions or priority rules between command-line flags and configuration file settings for exclusions. For example, command-line `--exclude-path` flags take precedence over `.squawk.toml` settings.

fix Double-check your `.squawk.toml` configuration and command-line arguments. Ensure paths are correctly specified (glob matching is supported). Understand the precedence rules: `--exclude`, `--exclude-path`, and `--pg-version` flags override configuration file settings.

Warnings

breaking Squawk-cli exits with a non-zero status code (1) for detected warnings, which can cause CI/CD pipelines configured to treat any non-zero exit code as a failure to break.
Fix: Configure your CI/CD pipeline to accept exit code 1 for warnings, or use squawk's configuration to exclude specific rules or files causing warnings if they are deemed acceptable. There is an open discussion for a flag to differentiate warnings and errors.
breaking The `ods/squawk-cli-python-package` on PyPI is obsolete. The official `squawk-cli` package is now built and published directly from the main `sbdchd/squawk` project. Installing from the old `ods` package will result in an outdated binary.
Fix: Ensure you are installing `squawk-cli` directly via `pip install squawk-cli` from the `squawk` project maintained by `sbdchd`. Reinstall if unsure.
gotcha Operations like adding `UNIQUE` constraints, adding columns with `DEFAULT` values (especially on Postgres <11), or changing `varchar` field sizes can acquire `ACCESS EXCLUSIVE` locks, blocking reads and writes to the table.
Fix: Use strategies like `NOT VALID` with `VALIDATE CONSTRAINT` for new constraints, add nullable columns first, then backfill and make `NOT NULL`, and prefer `TEXT` fields over `VARCHAR` with length limits. For indexes, always use `CONCURRENTLY`. Squawk provides suggestions in its output.
gotcha Squawk's parser may not support all PostgreSQL syntax variations or newer features immediately after a Postgres release, leading to syntax errors on otherwise valid SQL.
Fix: Check the `squawk` changelog and update to the latest version to ensure support for newer PostgreSQL versions and syntax. Report parsing issues to the project maintainers if your valid SQL is not recognized.

Install

pip install squawk-cli Install via pip

Quickstart

This quickstart demonstrates how to install `squawk-cli` and run it against a sample SQL migration file to detect potential issues. It creates a temporary SQL file, executes `squawk` on it, and then cleans up the file.

import os

# Create a dummy SQL migration file
sql_content = '''
-- migration_001.sql

CREATE TABLE users (
    id serial NOT NULL PRIMARY KEY,
    username varchar(255) NOT NULL,
    email varchar(255) NOT NULL UNIQUE
);

-- This will trigger a warning in squawk if not handled concurrently
CREATE INDEX email_idx ON users (email);

-- This might also trigger a warning depending on Postgres version and rules
ALTER TABLE users ADD COLUMN age integer DEFAULT 18;
'''

file_path = 'migration_001.sql'
with open(file_path, 'w') as f:
    f.write(sql_content)

print(f"Created {file_path}:")
print(sql_content)

# Run squawk-cli on the file
print(f"\nRunning squawk on {file_path}...")
os.system(f"squawk {file_path}")

# Clean up the dummy file
os.remove(file_path)
print(f"\nCleaned up {file_path}.")

view raw JSON →