Writing Idempotent SQL Data Migration Scripts

A migration script counts as idempotent if you can run it once, run it a hundred times, and the database ends up in exactly the same state every time. No errors on the second run. No duplicate rows. No drama. A migration script counts as idempotent if you can run it once, run it a hundred times, and the database ends up in exactly the same state every time, and this is worth pinning down before anything else, because a lot of teams think they've solved this problem when what they've actually done is get lucky.

Here's a fast way to test whether a migration script actually qualifies: run it ten times in a row. Not once, not twice. Ten times, back to back, on the same database. If it fails on attempt three, or silently inserts a duplicate row on attempt seven, it was never idempotent. It just hadn't been caught yet.

And it will get caught. Pipelines retry things, not because a developer fat-fingered a deploy. That's what they're built to do.

• A deploy step times out and the runner restarts it, from the top.
• A network blip drops a connection mid-DDL statement, and the next attempt re-runs the whole script, including the part that already succeeded.
• Every automated pipeline is built around the assumption that steps can be retried safely. A migration that isn't safe to retry is sitting in that pipeline like a loaded gun, waiting for the one bad network day that pulls the trigger.

When that gun goes off, it appears in three predictable ways.

• Downtime. A failed migration leaves the tracking table in a broken state, and now someone has to SSH into production and manually patch it before anyone can deploy again.
• Data corruption. A re-run inserts a second tax_rate row, or a second config record, and now application logic is reading the wrong one intermittently. Nobody notices for weeks, because the bug is a silent, occasional wrong answer. It's a silent, occasional wrong answer.
• Slow onboarding. A new engineer runs migrate on a fresh local database, hits a failure three migrations in, and now needs someone to explain the "special order" of running old scripts before they can even open the codebase.

None of these are edge cases. They're the default outcome of writing migrations the naive way. So what does the non-naive way actually look like?

The mental model shift: designing for state, not for sequence

Most SQL migrations are written as a sequence of commands: create this table, add that column, insert this row. The problem is that "sequence" assumes you know exactly what's already happened. Idempotent migrations give up on that assumption entirely. Instead of describing a series of steps, they describe a destination, the state the database needs to be in when the script finishes, regardless of what state it started in.

That's a genuinely different way of thinking about a script. A sequence says "do this, then this, then this." A state description says "make sure this exists, make sure that value is set, make sure this column is gone." The second version doesn't care if it's the first time or the fifth time. It just checks and acts accordingly.

This isn't a new idea. Configuration management tools like Ansible, Chef, and Puppet solved this problem for servers decades ago. None of them assume a blank-slate machine. They check: is this package installed? Is this file present with this content? Is this service running? Only then do they act, and only on the gap between current state and desired state. Applying that same logic to DDL and DML is really the entire trick.

Watching how SQL developers handle this over time tends to reveal three rough stages.

• Junior: writes CREATE TABLE users (...) and assumes the database starts empty. Works fine locally, breaks the second anyone re-runs it.
• Mid-level: learns to bolt on IF NOT EXISTS everywhere and feels pretty good about it. Better, but often applied as a reflex rather than a check on the actual thing that matters.
• Senior: checks the real state of the specific object being changed, at the specific level of specificity that matters. Not "does the table exist" when the real question is "does this constraint on this table already exist."

Two mechanisms work together to make this practical, and neither one alone is enough.

• Conditional guards. DDL that checks for the object's existence before acting on it.
• A version ledger. A tracking table that records which migrations have already run, so a runner can skip a completed step outright, and a retry can't accidentally record the same migration twice.

The ledger handles the "have we done this before" question at the script level. The guards handle it at the statement level, inside the script itself. You need both, because pipelines can fail in the middle of a script, not just between scripts.

DDL guards: the concrete patterns for CREATE, ALTER, and DROP

Start with the simplest, most common failure. This line is not idempotent:

ALTER TABLE users ADD COLUMN last_login_at TIMESTAMP;

Run it twice, and the second run fails, because the column already exists. In PostgreSQL, the fix is often as simple as adding four words:

ALTER TABLE users ADD COLUMN IF NOT EXISTS last_login_at TIMESTAMP;

That's the first line of defense, and for a lot of straightforward column additions, it's enough. MySQL complicates this story a bit: it doesn't support an inline IF NOT EXISTS guard for every ALTER TABLE operation, so some changes need a different approach entirely rather than a one-line fix.

That different approach usually means querying information_schema (or, in PostgreSQL, pg_constraint) directly, counting whether the object in question already exists, and only running the real DDL when the count comes back zero. This is the industrial-grade fallback, the pattern you reach for once IF NOT EXISTS alone won't cover the case. A constraint addition, for example, might get wrapped like this:

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint WHERE conname = 'orders_status_check'
  ) THEN
    ALTER TABLE orders ADD CONSTRAINT orders_status_check CHECK (status IN ('open','closed'));
  END IF;
END $$;

The guard has to check everything the DDL depends on, not just the most obvious condition. Checking only "does the table exist" while ignoring "does the column exist" or "has this constraint already been applied" gives a guard that looks safe and isn't. A partial guard is still a fragile guard. It just fails less often, which arguably makes it more dangerous, because it passes testing and then breaks in production eight months later.

Rollback code needs the same treatment. If the forward migration uses IF NOT EXISTS, the reverse migration should use DROP ... IF EXISTS, so a compensating step is just as safe to re-run as the original.

There's one more pattern to know if you're working with EF Core against SQL Server. EF Core's idempotent scripts can fail in a specific, annoying way: a statement like CREATE PROCEDURE or CREATE VIEW needs to be the first statement in its batch. Once raw SQL statements are properly isolated within their own batches, the generated idempotent script can handle its IF NOT EXISTS logic correctly around each statement.

DML idempotency: making data backfills and seed inserts safe to re-run

DDL guards handle schema. But data itself needs its own version of idempotency, and this is where a lot of migration scripts quietly fall apart.

Take a basic seed insert:

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

Run this twice and you get two rows, or a primary key collision if key happens to be unique. Either way, it's broken.

The fix is the UPSERT pattern: write by key, not by append. If the record already exists, update it to the desired value. If it doesn't, insert it. Either way, the end state after the script runs is identical, no matter how many times it's been run before. This isn't one universal SQL keyword, it's implemented differently depending on the database:

• PostgreSQL: INSERT ... ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value
• MySQL: INSERT ... ON DUPLICATE KEY UPDATE
• SQL Server and Snowflake: the MERGE statement

Different syntax, same underlying principle: check the key, act accordingly, land on the same state every time.

A subtler trap occurs in numeric updates. This line looks harmless:

UPDATE metrics SET page_views = page_views + 1500;

It is absolutely not idempotent. Run it once, page views go up by 1,500. Run it again, they go up by another 1,500. Every re-run adds another increment, and the "correct" value keeps drifting further from reality. The fix is to stop describing an operation and start describing a state:

UPDATE metrics SET page_views = 1500;

That's the entire mental shift from earlier in this piece, visible in a single line of DML. Increment-based logic describes a step. Absolute assignment describes a destination.

The same principle applies to data backfills, where a WHERE clause does the guarding instead of an IF NOT EXISTS:

UPDATE orders SET total_cents = total * 100 WHERE total_cents IS NULL;

Only rows that haven't been migrated yet get touched. Without that WHERE clause, a re-run after a partial failure reprocesses rows that were already converted, potentially multiplying values that were already correct, and silently corrupting good data in the process.

Handling breaking schema changes without downtime: the expand-contract pattern

Idempotency solves the "can this be safely retried" problem. It doesn't solve the separate problem of how to make a breaking schema change (renaming a column, splitting a table, changing a data type) without taking the application offline while old code and new code disagree about what the schema looks like.

The fix here is a mental shift of its own: stop treating a schema change as one atomic event, and start treating it as a coordinated rollout spread across multiple deploys. This is usually called expand-contract, and it breaks into three phases.

• Expand. Add the new schema alongside the old one: a new column, a new table, a new index. Nothing reads from it or writes to it yet. Because this step is pure addition, guarded with IF NOT EXISTS, it's always safe to re-run.
• Migrate. Update the application code to write to both the old structure and the new one at the same time. Then backfill historical data into the new structure, using the guarded, WHERE-clause-protected DML patterns from the section above.
• Contract. Once every code path writes to (and reads from) the new structure, and nothing references the old one anymore, remove the old column or table. Use DROP ... IF EXISTS so this cleanup step is just as re-runnable as everything before it.

In practice, this rarely compresses into three deploys. It's often five or six, each one small enough to be independently revertible if something looks wrong. That's the actual point of the pattern: not speed, but the ability to stop at any phase without anything breaking.

That said, expand-contract isn't a rule to apply everywhere unthinkingly. Two situations don't need it at all.

• Pre-launch, with no real users. There's no traffic to protect, so there's nothing to lock out. A single, direct migration is fine.
• A genuine maintenance window. A B2B tool used by one company in one timezone during business hours can often take a clean, planned downtime window instead of a multi-deploy rollout. Not every system needs zero-downtime engineering; it depends on who's actually depending on the uptime.

Roll-forward thinking: why a corrective migration often beats a rollback

Once a migration is idempotent and expand-contract is handling the risky changes, a different question comes up: if something goes wrong, do you roll back, or push forward with a fix?

The honest answer is that "can you roll back" isn't really the deciding factor. The real question is where the threshold sits, the point past which rolling forward becomes the safer option than rolling back.

That threshold is usually the moment new data gets written to the new column or structure. Before that point, rolling back is clean, nothing's been lost. After that point, a rollback means destroying whatever was written to the new structure in the meantime. Once real data is on the line, fixing the problem in place and deploying a corrective migration is almost always the better call than reverting and losing that data.

That has a direct planning implication: every migration with destructive potential should ship with a documented roll-forward plan, not just a down() method that assumes rollback is always safe. And the compensating step itself, the corrective migration, needs the same idempotency treatment as everything else: DROP ... IF EXISTS, guarded conditionals, all of it. A partial rollback that can't be safely re-run is just the original problem wearing a different hat.

CI/CD integration: reviewing, linting, and testing migrations before they hit production

None of the patterns above matter much if a risky migration can still slip into production without anyone looking at it twice. The baseline discipline is simple: migrations live in version control, next to the application code, and go through the same pull request review as any feature change. No separate process, no exceptions.

During that review, a few patterns should get flagged every time, whether by a human reviewer or an automated check:

• An UPDATE with no WHERE clause
• An ALTER that's going to rewrite a large table
• An index build missing CONCURRENTLY (in PostgreSQL) or an equivalent non-locking build option in other databases
• DROP TABLE or DROP COLUMN without IF EXISTS
• An ALTER adding a NOT NULL column with no DEFAULT value
• TRUNCATE sitting inside a migration script
• A missing rollback or roll-forward plan

A lot of this can be caught before a human ever opens the pull request. Simple scripts, often just Python, run inside the CI pipeline and scan migration files for these exact dangerous patterns. The goal is to catch the pattern that tends to produce mistakes, which is a much more tractable problem. It's to catch the pattern that tends to produce mistakes, which is a much more tractable problem.

Testing matters just as much as review, and it needs real data to mean anything. A migration that runs fine locally against a small dataset can behave very differently against a large production table, holding locks far longer than expected. Tools like Testcontainers and Synthesized exist specifically to let migrations get tested in isolated environments against production-like, masked data, which is the only way to catch a slow migration before it becomes an incident instead of a test failure.

Choosing a migration tool: Flyway, Liquibase, and the newer declarative alternatives

All of these patterns can be hand-rolled. Most teams eventually reach for a tool instead, and the two names that come up most often are Flyway and Liquibase. They solve the same underlying problem, tracking which migrations have run and applying the ones that haven't, but they take fairly different approaches to get there.

Flyway works with plain, numbered SQL files: V1__create_users.sql, V2__add_email_column.sql, and so on. Flyway keeps track of what's already been applied and runs whatever's left. Its biggest strength is exactly that simplicity, it's a natural fit for teams that want sequential schema changes with as little ceremony as possible. Complex rollback scenarios and team collaboration raise weaknesses, since it has no built-in merge conflict detection, and undo migrations (its "U" scripts) are only available in paid editions, not in the free community version. Redgate has discontinued the Teams tier for new customers and is pushing toward Enterprise, which changes the cost calculation for teams that are growing past the free tier.

Liquibase takes a different shape entirely: changes are defined in XML, YAML, JSON, or SQL changelogs, with an abstraction layer sitting on top that enables support across PostgreSQL, MySQL, Oracle, and SQL Server. That abstraction is the whole draw for teams governing migrations across multiple database engines at once. Liquibase also auto-generates rollback logic for changesets written in its own structured formats, which is a real advantage over Flyway, though raw SQL changesets still need explicit, hand-written rollback scripts of their own. On the licensing side, Liquibase moved to the Functional Source License starting with version 5.0: the open-source edition is subject to FSL licensing terms that impose restrictions on certain uses, and certain advanced features are gated behind paid editions rather than available in the free tier.

So which one fits? If the goal is standardized rollback behavior, gated deployments using preconditions, tagging, and drift detection across several environments, Liquibase's feature set is built around exactly that. If the goal is straightforward, sequential schema changes with minimal overhead and a lighter mental model, Flyway's simplicity is the draw. Neither choice replaces the patterns covered above, guards, upserts, expand-contract, roll-forward planning. The tool just handles the bookkeeping around them. The actual safety still comes from writing every script as if it's going to run ten times in a row, because eventually, it will.