Squawk

For scenarios where even minor locking is unacceptable, use a 'shadow table' approach. This involves:

1. Creating a new table.
2. Using database triggers to keep the old and new tables in sync.
3. Backfilling data from the old table to the new table.
4. Transitioning all reads and writes to the new table.
5. Deleting the old table once the transition is complete.

This pattern allows for a zero-locking migration but is significantly more complex to implement.

Inlay hints provide non-intrusive visual information to improve readability:

• Column Names: Shows column names in INSERT or CREATE VIEW statements where they are implicit.
• Function Parameter Names: Displays parameter names in function calls.
• Join Cardinality: Visualizes the relationship between joined tables (e.g., 1<->1..n for a one-to-many join).

-- Join cardinality hint example
select * from t join u /* 1<->1..n: */ using (user_id);

A statement_timeout sets an upper bound on how long any single query can run. For migrations, this prevents a single statement from consuming too many resources or holding locks for an indefinite period.

Note that some legitimate migration tasks, such as CREATE INDEX CONCURRENTLY on very large tables, may require a higher statement_timeout than standard DDL statements. Adjust the timeout based on your database size and specific migration requirements.

SET statement_timeout = '5s';

Renaming a table can break existing clients, especially during deployments when multiple versions of an application are running simultaneously. If an old version of an app depends on a table name that has been changed, it will encounter errors.

Recommended approach: If you are using an ORM (Object Relational Mapper), you can rename the object/model in your application code while keeping the underlying SQL table name unchanged. This avoids breaking database compatibility for running instances.

Squawk rules are designed to ensure safe database migrations. They focus on identifying and warning about SQL statements that could:

1. Block reads or writes (e.g., long-running locks).
2. Break existing clients (e.g., breaking changes to schema or data types).

When a rule is triggered, Squawk provides actionable, user-friendly error messages to help you resolve the potential migration risk.

The prefer-robust-stmts rule flags migrations that are not idempotent. A non-robust migration can fail partially and, when retried, fail again because the first part of the migration already applied (e.g., trying to add a column that already exists).

To make migrations robust, you should use guards like IF NOT EXISTS or IF EXISTS, or wrap statements in a transaction. This ensures that if a migration is interrupted and rerun, it can skip already-applied steps without erroring.

The legacy require-timeout-settings rule has been replaced by two distinct rules that can be enabled or disabled independently. Use these for more precise linting control in your configuration:

1. require-lock-timeout: Ensures lock_timeout is set to prevent migrations from waiting indefinitely for locks.
2. require-statement-timeout: Ensures statement_timeout is set to prevent long-running statements from consuming too many resources.

You can still use require-timeout-settings as an alias in your configuration files or within squawk-ignore comments to satisfy both requirements simultaneously.

In PostgreSQL, changing the size of a varchar field requires an ACCESS EXCLUSIVE lock, which prevents all reads and writes to the table.

To allow for easier length adjustments without downtime, use a TEXT field combined with a CHECK CONSTRAINT to enforce maximum length. This pattern is recommended to avoid heavy locks during schema migrations. For best results, ensure you follow the constraint-missing-not-valid rule when adding constraints to existing data.

-- Instead of:
CREATE TABLE "app_user" (
    "id" serial NOT NULL PRIMARY KEY,
    "email" varchar(100) NOT NULL
);

-- Use:
CREATE TABLE "app_user" (
    "id" serial NOT NULL PRIMARY KEY,
    "email" TEXT NOT NULL
);
ALTER TABLE "app_user" ADD CONSTRAINT "text_size" CHECK (LENGTH("email") <= 100);

You can ignore specific rule violations directly in your SQL files using comments.

To ignore a single rule: -- squawk-ignore <rule-name>

To ignore multiple rules (comma-separated): -- squawk-ignore <rule1>,<rule2>

To ignore all rules for an entire file: -- squawk-ignore-file

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

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