pgsafemigrate - PostgreSQL Schema & Data Migration Linter

pgsafemigrate is a command-line tool and Go library that performs a series of checks on PostgreSQL schema or data migrations SQL statements.

Its design supports the sql-migrate migration file format and is automatically recognized. The tool is thus aware of:

1. The migration direction (up/down).
2. Whether statements are wrapped or not in a transaction.

Features

Migration Direction Context

sql-migrate is a popular tool for handling database migrations. The migration file format requires & defines migration directives in special SQL comments:

-- +migrate Up
-- Forward migration statements in this section

-- +migrate Down
-- Rollback migration statements included in this section

pgsafemigrate is aware of the migration direction and passes this condition to each rule as execution context. This means that it is able to ignore certain rules that would only apply to forward migrations or vice versa. For example, a rule that warns against columns being dropped can potentially be ignored when they're part of a down migration.

No-Lint Annotations

There will always be exceptions to the rules. For example, engineers may be aware that a table is empty while the feature is under development and receives no traffic at the time. To accommodate these cases, pgsafemigrate uses a special annotation format defined in a SQL comment:

-- +migrate Up
-- pgsafemigrate:nolint:high-availability-avoid-table-rename
ALTER TABLE "movies" RENAME TO "films";

⚠️ No-lint annotations apply to all statements in the same migration direction (sql-migrate format) or the same migration file.

Transactions & Idempotency

If a migration consists of multiple statements, and the migration fails mid-step it is likely that it cannot be resumed in an idempotent manner. Fixing the database state or modifying the migration can be a risky and stressful operation.

sql-migrate by default wraps all migration statements in a transaction.

Nested Transaction Detection

PostgreSQL supports marking different sections of a transaction with SAVEPOINT, instead of supporting nested transactions.

Transaction-Incompatible Statements

Certain statements cannot be executed as part of a transaction. A frequent operation that falls into this category is creating an index concurrently. pgsafemigrate assumes all statements are wrapped in a transaction, unless the sql-migrate notransaction command is defined.

Rules

High Availability

high-availability-alter-column-not-null-exclusive-lock

Setting a column as NOT NULL acquires an exclusive lock on the table until the constraint is validated on all table rows.

high-availability-avoid-non-concurrent-index-creation

Non-concurrent index creation will not allow writes while the index is being built.

high-availability-avoid-non-concurrent-index-drop

Non-concurrent index drop will not allow writes while the index is being built.

high-availability-avoid-required-column

Newly added columns must either define a default value or be nullable.

high-availability-avoid-table-rename

Renaming a table can cause errors in previous application versions.

Maintainability

maintainability-describe-new-column-with-comment

Newly added columns should also include a COMMENT for documentation purposes.

maintainability-indexes-name-is-required

Indexes should be explicitly named.

Transactions

transactions-concurrent-index-operation-cannot-be-executed-in-transaction

Concurrent index operations cannot be executed inside a transaction.

transactions-index-if-not-exists-missing

Creating/removing an index outside of a transaction without an IF (NOT) EXISTS option can cause a migration to not be idempotent.

transactions-no-nested-transactions

Nested transactions are not supported in PostgreSQL.

Contributing

Adding New Rules

A Rule must implement the following interface:

type Rule interface {
    // Documentation returns a string containing more in-depth details and insights on the rule logic,
    // along with any guidelines about how to fix or handle this operation optimally.
    Documentation() string
    // Alias returns the unique identifier of the rule. The alias is prefixed with a shared category prefix,
    // followed by a code that briefly explains the rule scope.
    Alias() string
    // Process receives a parsed SQL statement that is part of the migration,
    // along with the entire set of migration statements and a flag denoting that
    // the statement is executed within an active transaction or not.
    // Returns true if the rule matches and a warning must be produced for this statement.
    Process(node *pg_query.Node, allStatements []*pg_query.Node, inTransaction bool) bool
}

pg_query nodes provide access to the full range of PostgreSQL syntax. See existing rules for examples. You can start by writing a test case with a statement sample that you want to test and then inspect the Parse Tree to find out the node properties that need to be accessed and checked accordingly.