Test PostgreSQL migrations before COMMIT

Test PostgreSQL migrations before COMMIT • pgmi

Test PostgreSQL migrations before COMMIT# By Alexey Evlampiev Most migration pipelines test before deployment or after commit. PostgreSQL allows a third checkpoint: after applying the change, but before committing it . Lint and rehearsal catch what they can in advance; integration suites catch what they can afterwards. The checkpoint in between is the only one where the real target database — with whatever drift it has accumulated — is in its migrated state while rollback is still one statement away. PostgreSQL supports this with no framework at all: run the migration and the checks that prove it worked inside one transaction, and let COMMIT depend on the checks. If an assertion fails, the deployment doesn’t fail partially — within PostgreSQL’s transactional scope, it never happened. The pattern needs no framework: first we’ll build it with plain SQL and psql, then look at where its limits are and how to automate it across a project. The foundation: DDL you can roll back# PostgreSQL’s documentation defines transactions as all-or-nothing operations and documents savepoints for selectively discarding part of a transaction. What makes the deployment story unusual is that PostgreSQL’s transactional treatment extends to most DDL: CREATE TABLE, ALTER TABLE, CREATE FUNCTION, and index creation obey BEGIN and ROLLBACK like ordinary writes. The PostgreSQL wiki’s competitive analysis compares this across engines; the short version is that a failed migration on MySQL can leave you half-migrated (DDL there commits implicitly), while PostgreSQL rolls the transaction’s schema and data changes back together. Most migration tools use this property defensively: if a statement errors, the migration rolls back. That’s good, but it only protects you from migrations that fail loudly. The more expensive failures are the ones that succeed syntactically and are wrong semantically — the backfill that missed rows, the function that returns the wrong shape, the constraint that was created NOT VALID and never validated. Transactional DDL supports something stronger than error rollback: it lets you make your own checks part of the transaction. The pattern in plain psql# Suppose a deployment adds a region column, backfills it from a mapping table, and makes it mandatory. The interesting question isn’t “did the statements run” — the DDL itself will complain if it can’t. The question is one the new NOT NULL constraint cannot answer: did every customer get the region its country implies? A backfill can write a wrong-but-non-null value — an ambiguous mapping, a join that silently matched the wrong rows — and every statement still succeeds. Ask the real question before committing: -- deploy.sql BEGIN;

ALTER TABLE customer ADD COLUMN region text;

UPDATE customer c SET region = m.region FROM region_mapping m WHERE m.country = c.country;

-- The gate: verify the migrated state inside the same transaction. DO $$ DECLARE v_unmapped bigint; v_mismatched bigint; BEGIN SELECT count(*) INTO v_unmapped FROM customer WHERE region IS NULL;

SELECT count(*) INTO v_mismatched FROM customer c JOIN region_mapping m ON m.country = c.country WHERE c.region IS DISTINCT FROM m.region;

IF v_unmapped > 0 OR v_mismatched > 0 THEN RAISE EXCEPTION 'region backfill invalid: % unmapped, % mismatched', v_unmapped, v_mismatched; END IF; END $$;

ALTER TABLE customer ALTER COLUMN region SET NOT NULL;

COMMIT; The two checks earn their place differently. The unmapped count fires before SET NOT NULL would, turning a generic constraint error into a diagnostic with numbers in it. The mismatch count is the real gate: it catches a wrong-but-non-null backfill that NOT NULL happily accepts. A persistent relational constraint — a composite foreign key onto the mapping table — could enforce this relationship continuously, and where that fits your model it is the stronger tool; the deployment gate earns its keep when such a constraint is absent, impractical, or when you want a diagnostic count before introducing it. And because the assertion describes the intended state rather than the backfill implementation, it remains valid if the backfill is rewritten before deployment or reused in a later migration — though unlike a constraint, it protects only deployments in which it actually runs. Run it with psql configured to stop on the first error: psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -f deploy.sql If the RAISE EXCEPTION fires, psql exits non-zero, the open transaction aborts, and the connection closes — PostgreSQL rolls back the transaction’s schema and data changes: the ALTER TABLE, the backfill, all of it. Your pipeline sees a non-zero exit code and stops. (Non-transactional effects are the subject of the limitations section below.) Notice what did the work here: not a testing framework, not a migration tool — one DO block and PostgreSQL’s...

Test PostgreSQL migrations before COMMIT

Related Articles

(no title)

Scientists reverse brain aging, with a nasal spray

AI has torched the market for junior programmers

Al Vigier: Canada's AI strategy shouldn't include secret Palantir bills

The Anatomy of an AI-Native Org