20-2 · Chapter 20 · 5 min read

Writing Database Migration Scripts That Won't Blow Up Production

You have a new feature ready. The code is reviewed, tested, and merged. But there is one thing standing between you and deployment: a database change

Writing Database Migration Scripts That Won't Blow Up Production

You have a new feature ready. The code is reviewed, tested, and merged. But there is one thing standing between you and deployment: a database change. Maybe you need to add a column, rename a table, or introduce a new index. The question is not whether the change works on your laptop. The question is whether it will work in production without breaking anything.

This is where migration scripts come in. They are not just SQL files. They are a disciplined way to evolve your database schema without guesswork, without manual steps, and without that sinking feeling when a deployment goes wrong.

The Basic Pattern: One File, One Change

The core idea is simple. Every database change lives in its own file. The file has a unique identifier, usually a timestamp or sequence number, and contains the SQL needed to apply the change. You also create a matching rollback file that can undo it.

Say you need to add a phone column to the users table. Instead of logging into production and running ALTER TABLE directly, you create a file named 20241101_add_phone_to_users.sql. Inside, you write:

ALTER TABLE users ADD COLUMN phone VARCHAR(20);

Then you create 20241101_add_phone_to_users_rollback.sql:

ALTER TABLE users DROP COLUMN phone;

Both files go into your repository alongside your application code. They go through code review. They get merged like any other change.

Why separate files? Because every change carries its own risk. When you split changes into individual files, you can apply one change, observe the impact, and then proceed to the next. If everything is lumped into one giant script, you cannot tell which part caused a failure. Worse, if the migration fails halfway through, you have no idea where it stopped.

Order Matters More Than You Think

The timestamp or sequence number is not just a naming convention. It defines the execution order. Your migration pipeline reads all files, sorts them by this identifier, and runs them from oldest to newest. This guarantees that every environment - development, staging, production - applies changes in the same sequence.

No more "it works on my machine but fails on the server" because the migration order was different. No more silent inconsistencies where one environment skipped a step.

Make Your Scripts Idempotent

Idempotent is a fancy word for a simple idea: running the same script twice should produce the same result and not cause an error.

Compare these two statements:

-- Not idempotent: will error if column already exists
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Idempotent: safe to run multiple times
ALTER TABLE users ADD COLUMN IF NOT EXISTS phone VARCHAR(20);

The second version is safer. Pipelines sometimes need to re-run migrations from scratch, for example when spinning up a fresh staging database. If your scripts are not idempotent, that simple operation turns into a debugging session.

Idempotency also helps during development. Developers frequently apply a migration, inspect the result, and want to start over. With idempotent scripts, they can re-run without dropping and recreating the entire database.

Rollback Is Not Optional

Every forward migration must have a matching rollback. This is not just for production emergencies. Developers use rollbacks constantly during local development to test changes and iterate. Without rollback scripts, they have to drop the entire database and run all migrations from scratch, which is slow and frustrating.

But here is the honest truth: rollbacks cannot always restore data. If a migration drops a column, the rollback can recreate that column, but the data is gone. If a migration renames a table, the rollback can rename it back, but any writes that happened in between are lost.

This does not mean rollbacks are useless. It means you need to understand what your rollback actually restores. Sometimes it only restores the schema structure, not the data. That is still valuable. It gets you back to a known state where you can recover or reapply changes.

For destructive changes - dropping columns, removing tables, changing data types - you need a separate strategy. We will cover that later. For now, the rule is simple: every migration gets a rollback, even if it only restores structure.

How Parallel Work Stays Safe

Multiple developers working on different features often need database changes. One adds a column for feature A. Another creates a table for feature B. Both create migration files with different timestamps. When both branches merge, the pipeline sorts the files by timestamp and applies them in order.

Conflicts only happen when two changes touch the same schema object. That is a genuine conflict that needs human resolution, just like a code conflict. The migration file pattern does not eliminate that, but it makes the conflict visible and explicit.

The Tracking Table

How does your pipeline know which migrations have already run? It uses a special table inside the database itself. This table records every migration that has been applied, along with a timestamp or checksum. When the pipeline runs, it checks this table, compares it against the list of migration files, and only applies the ones that are missing.

This mechanism is built into most migration tools, but understanding it helps you debug when things go wrong. If a migration was partially applied, or if someone manually ran a script outside the pipeline, the tracking table will tell you.

Practical Checklist for Writing Migration Scripts

Before you merge that migration file, run through this quick list:

Does the script have a unique identifier (timestamp or sequence)?
Is there a matching rollback script?
Is the script idempotent? Can it run twice without error?
Does the rollback actually restore the previous state?
Have you tested both forward and rollback on a copy of production data?
Does the migration lock tables? If yes, can it run during low traffic?

The Takeaway

Database migrations are not just SQL. They are a contract between your team and your production data. Every migration file represents a decision: what changes, in what order, and how to undo it if things go wrong. Treat each file with the same care you give to your application code. Review it. Test it. Make sure it has a rollback. Your production database will thank you.