database postgresql migrations devops ci-cd

Stop Writing Fragile Migrations: The Case for Idempotency

Foundry24 •

If you have ever had a deployment fail, tried to re-run the pipeline, and immediately hit Error: Relation "users" already exists, you have a fragility problem.

At Foundry24, we treat database migrations with the same rigor as application code. The golden rule for our internal ventures (like CompStacker) is simple: You must be able to run the migration script ten times in a row without it failing or corrupting data.

This is called Idempotency.

Most engineers know the word, but few practice it religiously. Here is how to actually write idempotent migrations (specifically for Postgres/SQL) that won’t wake you up at 3 AM.

1. The Structure (DDL): Beyond “IF NOT EXISTS”

The junior approach is assuming the database is empty. The mid-level approach is using IF NOT EXISTS. The senior approach is checking the state of the object.

Bad:

ALTER TABLE users ADD COLUMN phone_number VARCHAR(20);
-- Fails if the column exists.

Better:

ALTER TABLE users ADD COLUMN IF NOT EXISTS phone_number VARCHAR(20);
-- Postgres specific, but safe.

Industrial Grade: Sometimes IF NOT EXISTS isn’t supported for complex changes (like constraints or index modifications). In those cases, wrap it in a pragmatic block:

DO $
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'users_email_unique') THEN
        ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);
    END IF;
END $;

Why? Because automated deployment scripts often retry on network flukes. If your migration isn’t safe to retry, your auto-deployment is a loaded gun.

2. The Data (DML): The “Seed” Trap

This is where most startups mess up. You want to seed the database with a default Admin user or standard configuration values.

The Trap:

INSERT INTO settings (key, value) VALUES ('tax_rate', '0.05');

Result: Run this twice, and you have two tax rates. Or a primary key collision failure.

The Fix (Postgres ON CONFLICT):

INSERT INTO settings (key, value)
VALUES ('tax_rate', '0.05')
ON CONFLICT (key) DO UPDATE SET value = '0.05';

This ensures that if the record exists, it is corrected to the desired state. If it doesn’t exist, it is created. The end state is always the same.

3. The “Refactor” Migration

Moving data from Column A to Column B is dangerous if not idempotent.

Fragile:

UPDATE orders SET total_cents = total * 100;

Risk: If you run this, realize you missed a WHERE clause, and run it again… you just multiplied your revenue by 10,000.

Robust:

UPDATE orders
SET total_cents = total * 100
WHERE total_cents IS NULL; -- Only touch untouched rows

Why This Matters for the Business

This isn’t just code aesthetics. Non-idempotent migrations cause:

  • Downtime: When a deploy fails, you have to manually SSH into the DB to “un-stick” the migration table.
  • Data Corruption: Duplicate config rows can cause silent logic errors.
  • Slow Onboarding: New developers can’t just run make migrate to get a working local environment.

At Foundry24, we build software without shortcuts. That means writing five extra lines of SQL today to prevent a fire drill tomorrow.

Database migrations keeping you up at night? Get in touch to discuss how we can help stabilize your deployment pipeline.

Back to Blog