Database Schema Migrations in MySQL: Versioned Changes

Your schema will change. A new feature needs a column, a name is wrong, a table gets split. Migrations are the disciplined way to do it: every change is a small, numbered, versioned script that is stored with the code, reviewed, tested and applied in the same order everywhere (your laptop, staging, production).

The rules of migrations

Never edit the database by hand in production. Every change is a file in version control.
Never edit a migration that has already run somewhere. Add a new one.
Keep each migration small and focused. One change, one file.
The application and the schema are deployed in an order where both old and new code work during the switch (see expand and contract, below).
Test migrations on a copy of production data first. A change that takes a second on 100 rows can lock a table for an hour on 100 million.

File names

Text

PLAIN

migrations/
  0001_create_customers.sql
  0002_create_orders.sql
  0003_add_customers_phone.sql
  0004_add_orders_status_index.sql

The prefix gives the order. Many tools use a timestamp (20260301_1030_add_phone.sql) so that two developers do not both pick 0005. Frameworks have this built in: Laravel migrations, Doctrine Migrations, Phinx, Flyway, Liquibase, Alembic, Rails and others. Use your framework’s tool if there is one.

What a tool keeps: the schema_migrations table

The tool remembers which migrations ran, in a table in the same database:

query.sql

SQL

CREATE TABLE schema_migrations (
    version VARCHAR(50) NOT NULL PRIMARY KEY,
    applied_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO schema_migrations (version) VALUES ('0001_create_customers'), ('0002_create_orders');
SELECT version FROM schema_migrations ORDER BY version;

Output

OUTPUT

+-----------------------+
| version               |
+-----------------------+
| 0001_create_customers |
| 0002_create_orders    |
+-----------------------+
2 rows in set

On deploy, the tool lists the files, skips those already recorded, runs the others in order, and records each one when it succeeds.

A tiny runner in PHP

You do not need a framework to understand the idea. This runner holds two migrations in an array (normally they are files), applies the ones that are missing, and records them:

index.php

PHP

<?php
$pdo = require __DIR__ . '/db.php';

$pdo->exec('CREATE TABLE IF NOT EXISTS schema_migrations (version VARCHAR(50) PRIMARY KEY, applied_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP)');

$migrations = [
    '0001_create_notes' => 'CREATE TABLE notes (id INT AUTO_INCREMENT PRIMARY KEY, body VARCHAR(200) NOT NULL)',
    '0002_add_notes_pinned' => 'ALTER TABLE notes ADD COLUMN pinned TINYINT(1) NOT NULL DEFAULT 0',
];

$done = $pdo->query('SELECT version FROM schema_migrations')->fetchAll(PDO::FETCH_COLUMN);

foreach ($migrations as $version => $sql) {
    if (in_array($version, $done, true)) {
        echo "skip  $version\n";
        continue;
    }
    $pdo->exec($sql);                                     // DDL commits by itself (see below)
    $pdo->prepare('INSERT INTO schema_migrations (version) VALUES (?)')->execute([$version]);
    echo "apply $version\n";
}

Output

OUTPUT

1
2

apply 0001_create_notes
apply 0002_add_notes_pinned

Run it a second time and nothing is applied again. (Here, each example starts from a clean database, so to see the second run, call the loop twice in one script:)

index.php

PHP

<?php
$pdo = require __DIR__ . '/db.php';
$pdo->exec('CREATE TABLE schema_migrations (version VARCHAR(50) PRIMARY KEY)');

function migrate(PDO $pdo, array $migrations): void
{
    $done = $pdo->query('SELECT version FROM schema_migrations')->fetchAll(PDO::FETCH_COLUMN);
    foreach ($migrations as $version => $sql) {
        if (!in_array($version, $done, true)) {
            $pdo->exec($sql);
            $pdo->prepare('INSERT INTO schema_migrations (version) VALUES (?)')->execute([$version]);
            echo "apply $version\n";
        }
    }
}

$migrations = ['0001_create_notes' => 'CREATE TABLE notes (id INT PRIMARY KEY)'];
migrate($pdo, $migrations);
migrate($pdo, $migrations);                               // nothing to do
echo "second run applied nothing\n";

$migrations['0002_add_title'] = 'ALTER TABLE notes ADD COLUMN title VARCHAR(50) NULL';
migrate($pdo, $migrations);                               // only the new one

Output

OUTPUT

1
2
3

apply 0001_create_notes
second run applied nothing
apply 0002_add_title

Real tools add locking (so two servers do not migrate at once), checksums (to detect an edited file) and rollback files.

DDL is not transactional in MySQL

In MySQL, CREATE, ALTER and DROP commit implicitly, so they cannot be rolled back inside a transaction (Autocommit and Savepoints). If a migration with five statements fails on the fourth, the first three are already applied. Protect yourself:

Keep one DDL statement per migration, or make each statement idempotent (CREATE TABLE IF NOT EXISTS, DROP TABLE IF EXISTS), so that re-running the file after a failure is safe.
Record the migration as done only after it succeeds.
Write a down (rollback) migration for risky changes, but treat backups as the real safety net (Backup and Restore).

Online and instant changes

An ALTER TABLE on a big table can block writes or copy the whole table. MySQL’s online DDL lets many changes run while the table stays usable (Online DDL). Ask for exactly the algorithm you want, so MySQL refuses instead of silently choosing a slow one:

query.sql

SQL

CREATE TABLE notes (id INT AUTO_INCREMENT PRIMARY KEY, body VARCHAR(200) NOT NULL);
INSERT INTO notes (body) VALUES ('first'), ('second');

ALTER TABLE notes ADD COLUMN pinned TINYINT(1) NOT NULL DEFAULT 0, ALGORITHM = INSTANT;
SELECT id, body, pinned FROM notes ORDER BY id;

Output

OUTPUT

+----+--------+--------+
| id | body   | pinned |
+----+--------+--------+
|  1 | first  |      0 |
|  2 | second |      0 |
+----+--------+--------+
2 rows in set

ALGORITHM=INSTANT changes only the table’s metadata, with no row copy, so adding a column takes almost no time, even on a huge table. Not every change can be instant:

query.sql

SQL

ALTER TABLE notes MODIFY body VARCHAR(100) NOT NULL, ALGORITHM = INSTANT;

Output

OUTPUT

ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Need to rebuild the table to change column type. Try ALGORITHM=COPY/INPLACE.

MySQL refuses (error 1846) rather than starting a long rebuild. You then decide: use INPLACE or COPY, schedule a maintenance window, or use an external online schema-change tool (such as gh-ost or pt-online-schema-change) for very large tables.

Expand and contract: changes without downtime

Renaming a column while the application is running breaks the old code, the moment the migration runs. The safe way is several small steps, each compatible with the code before and after it:

Expand: add the new column (nullable), the old one stays.
Deploy code that writes both columns and reads the new one (falling back to the old).
Backfill old rows in batches (Changing Millions of Rows).
Switch the code to use only the new column.
Contract: after a safe period, drop the old column.

query.sql

SQL

-- Goal: replace customers.name with first_name and last_name
CREATE TABLE people (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(60) NOT NULL);
INSERT INTO people (name) VALUES ('Maya Rao'), ('Rohan Das');

-- 1. expand
ALTER TABLE people ADD COLUMN first_name VARCHAR(30) NULL, ADD COLUMN last_name VARCHAR(30) NULL;

-- 3. backfill (in real life: in batches of a few thousand rows)
UPDATE people SET first_name = SUBSTRING_INDEX(name, ' ', 1), last_name = SUBSTRING_INDEX(name, ' ', -1) WHERE first_name IS NULL;

-- check before making the new columns required
SELECT COUNT(*) AS rows_not_backfilled FROM people WHERE first_name IS NULL OR last_name IS NULL;

-- 5. contract, after the application no longer uses `name`
ALTER TABLE people MODIFY first_name VARCHAR(30) NOT NULL, MODIFY last_name VARCHAR(30) NOT NULL, DROP COLUMN name;
SELECT id, first_name, last_name FROM people ORDER BY id;

Output

OUTPUT

+---------------------+
| rows_not_backfilled |
+---------------------+
|                   0 |
+---------------------+
1 row in set

+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  1 | Maya       | Rao       |
|  2 | Rohan      | Das       |
+----+------------+-----------+
2 rows in set

The same pattern works for splitting a table, changing a column’s type, and adding a NOT NULL column to a table that already has rows.

Seeing what changed

Keep a record of the schema itself. A command such as mysqldump --no-data (or the schema snapshot your framework writes) lets you diff the current structure against what you expect, and catch changes made by hand:

query.sql

SQL

SHOW CREATE TABLE people\G

Output

OUTPUT

*************************** 1. row ***************************
       Table: people
Create Table: CREATE TABLE `people` (
  `id` int NOT NULL AUTO_INCREMENT,
  `first_name` varchar(30) NOT NULL,
  `last_name` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set

dangerous

Test on production-sized data

A migration that adds an index is instant on your laptop’s 1 000 rows and can take an hour in production. Measure on a copy of real size, and know the algorithm and lock level before the deploy.