22-3 · Chapter 22 · 4 min read

Why You Should Always Dry-Run Database Migrations Before Touching Real Data

You have written a migration script. It looks correct. The logic is sound. You run it on staging, and everything passes. But when you run it on

Why You Should Always Dry-Run Database Migrations Before Touching Real Data

You have written a migration script. It looks correct. The logic is sound. You run it on staging, and everything passes. But when you run it on production, something goes wrong. Maybe a column constraint fails. Maybe a foreign key violation appears. Maybe the migration takes 45 minutes instead of the expected 5, locking a critical table and causing a cascade of timeouts across your application.

This scenario is common. And it is preventable. The technique is simple: dry-run your migration before you ever let it touch real data.

What a Dry-Run Actually Does

A dry-run is exactly what it sounds like. You execute your migration script, but you never commit the changes. No tables are altered. No rows are moved. No columns are dropped. You are only checking whether the script runs without errors, and if there are problems, you find out before any data is affected.

The most common way to do this is by wrapping your migration inside a database transaction and ending it with ROLLBACK instead of COMMIT. If you have a script that adds a new column and populates it from another table, you wrap the whole thing in BEGIN TRANSACTION ... ROLLBACK. If an error occurs mid-way, the transaction automatically cancels and the database returns to its original state. If no error occurs, you still roll back manually. You are not looking to apply the change. You are looking for confirmation that the script is valid.

Here is a concrete example of what that looks like in SQL:

BEGIN TRANSACTION;

-- Example migration: add a new column and populate it
ALTER TABLE users ADD COLUMN last_login_at TIMESTAMP;

UPDATE users
SET last_login_at = NOW()
WHERE last_login_at IS NULL;

-- Verify the data looks correct before rolling back
SELECT id, email, last_login_at FROM users LIMIT 10;

-- Roll back the transaction, leaving the database unchanged
ROLLBACK;

What a Dry-Run Tells You Beyond Syntax Errors

Checking for syntax errors or constraint violations is the obvious benefit. But a dry-run gives you much more useful information.

You can see how long the migration will take. You can see how many rows will be affected. You can see whether the migration will lock tables and for how long. This is critical information, especially if your migration targets a production database handling thousands of requests per second. If your dry-run shows the migration will take 30 minutes and during that time the main table will be locked, you know you need a different strategy. Maybe you run the migration during off-peak hours. Maybe you use an online migration technique that avoids long locks. Maybe you break the migration into smaller batches.

Without a dry-run, you are guessing. With a dry-run, you have data to make an informed decision.

Where to Run Your Dry-Run

The ideal place is a staging environment with data that closely mirrors production. But not every team has a staging database with realistic data volume. If that is your situation, take a snapshot of production data at a single point in time and load it into a separate database. This snapshot should never be used for real transactions, but it is good enough to test your migration.

Some teams go further and automate dry-runs. Every time a migration script changes in a pull request, a CI job runs the dry-run automatically. This catches problems before the code is even merged. It is a small investment that saves significant debugging time later.

How to Read Dry-Run Results

A clean output with no errors and no warnings is reassuring. But do not stop there. Go through the logs carefully.

Look for rows that failed to populate because the data did not match the new column type. Look for index conflicts. Look for foreign key violations. Sometimes a dry-run succeeds technically but fails logically. For example, the data you moved might be empty because your WHERE clause was wrong. The script ran fine. No errors. But the result is useless.

To catch this, after the dry-run completes, run a SELECT query to verify the contents of the modified table as if the migration had actually happened. You can do this within the same transaction before you roll back. Open a separate session, run the migration inside a transaction, and before rolling back, query the modified tables to confirm the data is correct. This extra step turns a dry-run from a syntax check into a logic check.

What a Dry-Run Cannot Guarantee

A dry-run is not a guarantee that the migration will run smoothly in production. Some factors cannot be fully replicated. The concurrent query load will be different. The data volume may be much larger. The timing of locks may shift. But a dry-run reduces surprises. If you have tested the migration in an environment close to production and the results match your expectations, you can run the real migration with much higher confidence.

Practical Checklist Before Running a Migration

Before you run any migration that touches production data, go through this short list:

Wrap the migration in a transaction and run it with ROLLBACK
Check the execution time and lock duration
Verify the number of affected rows matches expectations
Query the modified tables within the transaction to confirm data correctness
Review logs for constraint violations, index conflicts, or type mismatches
If the migration takes too long or locks too heavily, plan an alternative strategy

The Concrete Takeaway

A dry-run is not an extra step. It is the step that separates a confident deployment from a stressful one. Run your migration in a transaction. Roll it back. Check the results. Then decide whether you are ready to commit. Your production data will thank you.