Migrations, Seeders, Fixtures, and Safe Schema Evolution
Write versioned, reversible database migrations with up/down methods, distinguish seeders from fixtures, and evolve a live schema safely using backward-compatible, multi-step migration patterns instead of destructive one-shot changes.
A database schema is never really 'finished' -- new features need new tables, new columns, new constraints, and old ones eventually need to change shape or disappear. If you edit a production database by hand with an ad-hoc ALTER TABLE, that change lives only in your head (or a chat message) and is nearly impossible to reproduce on another developer's machine, a staging server, or a fresh production replica. Migrations solve this by turning schema changes into ordinary, version-controlled code: small, ordered files that describe exactly how to move the schema forward (and, ideally, how to move it back). This lesson covers how migrations are structured, how seeders and fixtures differ, and -- the part that actually matters in production -- how to change a live schema without taking the application down or losing data.
What a Migration File Is
A migration is a single, timestamped (or sequentially numbered) file that describes one schema change: create a table, add a column, add an index, drop a constraint. Each migration typically exposes two operations: an up() method that applies the change, and a down() method that reverses it. A migration runner keeps a table in the database itself (often literally called migrations) that records which migration files have already been applied, so running the migration command again is safe -- it only executes the ones it hasn't seen yet, in order.
The filename or an embedded timestamp fixes the order migrations run in, which matters enormously: a migration that adds a foreign key to orders.user_id must run after the migration that creates the users table, not before. Because migrations are just files checked into version control alongside the application code, every developer, every CI job, and every environment can reconstruct the exact same schema by replaying the same ordered sequence of files from scratch.
Treating schema changes as code buys you three things ad-hoc SQL in a notepad never gives you: reproducibility (any environment can be brought to the same schema state), history (git blame tells you who added a column and why, and when), and reversibility (a bad migration can, in principle, be rolled back with down() instead of manually reverse-engineered).
• Migration = one ordered, version-controlled file describing a single schema change. • up() applies the change; down() reverses it. • A migrations tracking table in the DB records which files have already run. • Order matters -- migrations run in filename/timestamp order, so dependencies must be respected. • Migrations make schema state reproducible across every developer machine, CI run, and environment.
Seeders vs Fixtures
A seeder inserts data that the application needs to function or to be demonstrated with -- things like a default admin role, a list of countries, a set of subscription plans, or realistic-looking fake data for local development. Seeders are usually written to be safely re-runnable (they check for existing rows or use upserts) and are commonly run right after migrations, both in local development setup scripts and sometimes in production for truly static reference data (like a fixed list of currency codes).
A fixture is a related but distinct idea: a fixed, known snapshot of data used specifically to make automated tests deterministic. Where a seeder might generate 500 random fake users for a nice-looking dev environment, a fixture loads exactly three specific users with exactly known IDs, emails, and roles so that a test asserting 'user 2 cannot access admin routes' behaves identically every single time it runs, on every machine, forever.
The practical distinction is audience and stability: seeders target humans (developers browsing a seeded local database, or genuinely static production reference data) and can tolerate some randomness or growth over time; fixtures target automated tests and must be exact, minimal, and stable, because a test suite that gets different data on different runs is a test suite you can no longer trust.
• Seeder: populates default/demo/reference data; often randomized or bulk-generated for dev environments. • Fixture: a fixed, exact, minimal dataset that automated tests depend on for deterministic assertions. • Seeders answer 'what data does this app need to look/work right?'; fixtures answer 'what data does this test need to be repeatable?'. • Both are usually just PHP classes/scripts that insert rows -- the difference is intent and stability, not mechanism.
Schema Evolution and Backward-Compatible Changes
In a system with any real traffic, a migration doesn't run against an idle database -- it runs while old application code is still handling live requests (during a rolling deploy, some servers are running the old code, some the new code, for anywhere from seconds to minutes). This means every migration should be written so that it doesn't break the code that is *currently* running against the *old* schema, even for the brief window before the new application code is fully rolled out.
The safest schema changes are purely additive: adding a new nullable column, adding a new table, adding a new index. Old code simply ignores the new column and keeps working. The dangerous changes are the ones old code depends on: adding a column with a NOT NULL constraint and no default will break every existing INSERT statement issued by old code that doesn't know the new column exists, the instant the migration runs -- even before any new application code is deployed.
The fix is almost always to add columns as nullable first (or NOT NULL with a sensible DEFAULT), deploy application code that starts populating it, backfill existing rows in a separate batched step, and only *then*, in a later migration, tighten the constraint to NOT NULL once you've confirmed every row has a value. The same staged thinking applies to renaming a column: renaming it directly in one step breaks any code (old app servers, in-flight reports, other services) still reading the old name. The safe multi-step rename pattern is: (1) add the new column, (2) write to both old and new columns from the application, (3) backfill the new column from the old one, (4) switch reads to the new column, (5) stop writing the old column, (6) only once nothing references it, drop the old column in a later migration.
• Purely additive changes (new nullable column, new table, new index) are safe under rolling deploys. • NOT NULL with no default on an existing table breaks old code's INSERTs immediately. • Safe pattern for tightening a constraint: add nullable -> backfill -> switch to NOT NULL in a later migration. • Safe rename pattern: add new column -> dual-write -> backfill -> switch reads -> stop writing old -> drop old (later). • Never combine 'add and immediately require' or 'rename in place' in a single migration on a live system.
Destructive Changes and Production Risk
Destructive migrations -- DROP COLUMN, DROP TABLE, TRUNCATE, changing a column's type in a way that loses precision -- are the highest-risk category because they are usually not reversible in practice, even if a down() method exists on paper. A down() that recreates a dropped column can restore the column's *definition*, but it cannot restore the *data* that was in it; once a DROP COLUMN runs and the storage is reclaimed, that data is gone unless you have a separate backup.
The safe approach to genuinely removing something is to treat it as a multi-stage process spread across multiple deploys: first, remove every reference to the column/table from application code and confirm (via logs, metrics, or a search across the codebase) that nothing reads or writes it anymore; then let the schema element sit unused for a defined period (days to weeks, depending on your risk tolerance and rollback window); only then run the actual DROP migration, and always take a fresh backup or export of that specific data immediately before doing so.
A closely related discipline is keeping every migration reversible where that's realistically possible, and being explicit with the team when it isn't. A migration that adds a column can trivially reverse by dropping it. A migration that drops a column cannot trivially reverse -- its down() can only recreate an empty column, which is why teams often write down() as a no-op with a loud comment rather than pretend the data will come back, and instead lean on database backups as the real recovery path for destructive changes.
• Destructive operations: DROP COLUMN, DROP TABLE, TRUNCATE, lossy type changes. • A down() method can restore a column's definition but never the data that was already deleted. • Safe removal is staged: stop using it in code -> confirm nothing references it -> wait -> backup -> drop. • Always take a fresh backup immediately before running any destructive migration in production. • Some migrations genuinely can't be undone by code -- be honest about that instead of writing a fake down().
A Typical Migration Class Shape
up() applies the forward change; down() must be the exact inverse operation. The migration class name/filename usually embeds an ordering timestamp. A runner records applied migrations in a tracking table so re-running is a no-op for files already applied.
final class Migration_2024_03_15_120000_AddPhoneToUsers
{
public function up(PDO $db): void
{
$db->exec(
'ALTER TABLE users ADD COLUMN phone VARCHAR(32) NULL'
);
}
public function down(PDO $db): void
{
$db->exec('ALTER TABLE users DROP COLUMN phone');
}
}Code Examples
The runner is intentionally small: it keeps a migrations table of already-applied names, and for each migration passed in, applies it (calling up()) and records it only if it hasn't run before. Every real migration framework (Laravel, Doctrine, Phinx) does the same core thing with far more tooling around it. This idempotency check is what makes running migrate() a safe operation to repeat -- deploying twice, or re-running CI, never re-applies a migration that already succeeded.
if ($stmt->fetch() !== false) { continue; } Splitting a 'required column' rollout into add-nullable, backfill-in-batches, then tighten-to-NOT-NULL means old application code (which doesn't know about status yet) never fails an INSERT, and the batched backfill avoids locking the entire orders table in one giant UPDATE. Batching the backfill with LIMIT and looping until zero rows are affected keeps each UPDATE short, so it doesn't hold long locks on a busy production table.
UPDATE orders SET status = 'completed' WHERE status IS NULL LIMIT 1000
INSERT IGNORE makes the seeder safe to re-run without duplicating roles. The fixture, in contrast, deliberately wipes and recreates an exact, small, known dataset so the test that depends on it behaves identically no matter how many times or in what order tests run.
Common mistake: Using a seeder's randomized/bulk data inside a test suite -- if the seeder's data isn't pinned, assertions like 'there are exactly 3 admins' become flaky as soon as the seeder's content changes.
<?php
declare(strict_types=1);
interface Migration
{
public function up(PDO $db): void;
public function down(PDO $db): void;
}
final class CreateUsersTable implements Migration
{
public function up(PDO $db): void
{
$db->exec(<<<SQL
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
created_at DATETIME NOT NULL
)
SQL);
}
public function down(PDO $db): void
{
$db->exec('DROP TABLE users');
}
}
final class AddPhoneToUsers implements Migration
{
public function up(PDO $db): void
{
// Nullable first -- safe under a rolling deploy, old code is unaffected.
$db->exec('ALTER TABLE users ADD COLUMN phone VARCHAR(32) NULL');
}
public function down(PDO $db): void
{
$db->exec('ALTER TABLE users DROP COLUMN phone');
}
}
final class MigrationRunner
{
public function __construct(private PDO $db) {}
/** @param array<string, Migration> $migrations name => instance, in run order */
public function migrate(array $migrations): void
{
$this->db->exec(
…<?php
// Migration 1 -- deploy alongside app code that starts writing this column.
final class AddStatusToOrdersNullable implements Migration
{
public function up(PDO $db): void
{
$db->exec(
"ALTER TABLE orders ADD COLUMN status VARCHAR(20) NULL"
);
}
public function down(PDO $db): void
{
$db->exec('ALTER TABLE orders DROP COLUMN status');
}
}
// Separate backfill step (run once, outside the hot request path,
// in batches so it doesn't lock the whole table at once).
function backfillOrderStatus(PDO $db): void
{
$stmt = $db->prepare(
"UPDATE orders SET status = 'completed'
WHERE status IS NULL LIMIT 1000"
);
do {
$stmt->execute();
$affected = $stmt->rowCount();
} while ($affected > 0);
}
// Migration 2 -- runs only AFTER the backfill has confirmed every row has a value.
final class MakeOrderStatusRequired implements Migration
{
public function up(PDO $db): void
{
$db->exec(
"ALTER TABLE orders MODIFY status VARCHAR(20) NOT NULL DEFAULT 'pending'"
);
}
public function down(PDO $db): void
{
$db->exec('ALTER TABLE orde
…<?php
declare(strict_types=1);
// Seeder: safe to run repeatedly, targets a real (dev/prod) database.
final class RoleSeeder
{
public function run(PDO $db): void
{
$roles = ['admin', 'editor', 'viewer'];
$stmt = $db->prepare(
'INSERT IGNORE INTO roles (name) VALUES (?)'
);
foreach ($roles as $role) {
$stmt->execute([$role]);
}
}
}
// Fixture: exact, minimal, known rows a test suite relies on.
final class UserFixture
{
public static function load(PDO $db): void
{
$db->exec('DELETE FROM users'); // start from a known-empty state
$db->exec(
"INSERT INTO users (id, email, role) VALUES
(1, '[email protected]', 'admin'),
(2, '[email protected]', 'viewer')"
);
}
}
// A test can now rely on user id 2 existing with role 'viewer', every single run.
function test_viewer_cannot_delete_users(PDO $db): void
{
UserFixture::load($db);
// ... exercise the app as user id 2 and assert a 403 ...
}Safe Multi-Step Rename: old_name -> new_name
Each step is its own deploy; nothing ever breaks old code that hasn't caught up yet.
Step 1 add new_name (nullable) [old_name] [new_name: empty]
Step 2 app dual-writes both columns [old_name] [new_name: kept in sync]
Step 3 backfill new_name from old [old_name] [new_name: fully populated]
Step 4 app reads from new_name only [old_name: idle] [new_name: live]
Step 5 app stops writing old_name [old_name: frozen] [new_name: live]
Step 6 drop old_name (separate deploy) [ removed ] [new_name: live]Important Rules
Every migration should have both up() and down() -- even if down() for a destructive change is an explicit, documented no-op.
Never add a NOT NULL column with no default to a table that already has rows and live traffic.
Never rename or drop a column/table in the same migration that adds its replacement -- split it into stages.
Batch backfills of existing rows; don't run one giant UPDATE against a live table.
Take a fresh backup immediately before any DROP/TRUNCATE runs in production.
Seeders must be idempotent (safe to re-run); fixtures must be exact and deterministic for tests.
Why This Matters in Production
At companies running continuous deployment, a schema migration and an application deploy are almost never truly simultaneous -- there's always a window where old code, new code, and the migrated (or not-yet-migrated) schema coexist. Teams that don't plan for this window learn about it the hard way: a spike of 500 errors the moment a NOT NULL migration runs, or a silent data-loss incident from a DROP COLUMN nobody backed up first. Backward-compatible, staged migrations turn a scary, all-or-nothing schema change into a sequence of small, boring, individually-safe steps -- which is exactly what you want a production database change to be.
ALTER TABLE users ADD COLUMN phone VARCHAR(32) NOT NULL; -- no default, deployed while old code is still running
ALTER TABLE users ADD COLUMN phone VARCHAR(32) NULL; -- then backfill, then tighten to NOT NULL in a later migration once every row has a value
A NOT NULL column with no DEFAULT immediately breaks every INSERT statement issued by code that doesn't set it -- which includes every currently-running server during a rolling deploy, not just old versions left over after a full rollback.
public function down(PDO $db): void { $db->exec('DROP TABLE orders'); } -- a fake rollback for a migration that dropped the orders table's discount_code column and its historical data public function down(PDO $db): void { /* Not reversible: dropped column data cannot be restored by code. Restore from the pre-migration backup taken on 2024-03-15 if needed. */ }
Writing a down() that merely recreates a column's shape gives a false sense of safety -- the data is already gone. Being explicit that recovery requires a backup is more honest and more useful during an actual incident.
[multiple-choice] Why is adding a NOT NULL column with no default value dangerous on a table that already has live traffic? A. It makes SELECT queries slower B. It immediately breaks any currently-running INSERT statement that doesn't set the new column, including servers still running old code during a rolling deploy C. MySQL rejects the ALTER TABLE statement outright D. It has no real risk as long as down() is defined
B. It immediately breaks any currently-running INSERT statement that doesn't set the new column, including servers still running old code during a rolling deploy — A NOT NULL column with no default has no fallback value, so any INSERT that omits it -- which includes every server still running code from before the migration -- fails immediately, not just after a full rollout.
[multiple-choice] What is the key difference between a seeder and a fixture? A. Seeders use raw SQL, fixtures use an ORM B. There is no real difference; the terms are interchangeable C. A seeder populates default/demo/reference data and can tolerate change over time; a fixture is an exact, stable, minimal dataset that automated tests rely on for deterministic results D. Fixtures can only run in production, seeders only in development
C. A seeder populates default/demo/reference data and can tolerate change over time; a fixture is an exact, stable, minimal dataset that automated tests rely on for deterministic results — Seeders target humans and general app usability and can grow or randomize; fixtures target automated tests, which need exact, unchanging data so assertions stay reliable run after run.
[multiple-choice] Why can't a migration's down() method truly undo a DROP COLUMN in practice? A. down() methods are not allowed to contain ALTER TABLE statements B. down() can recreate the column's definition, but the data that was in it is already gone once the column was dropped -- only a backup can restore that data C. MySQL automatically prevents you from writing a down() for a DROP COLUMN D. down() only works for CREATE TABLE migrations
B. down() can recreate the column's definition, but the data that was in it is already gone once the column was dropped -- only a backup can restore that data — Recreating a column gives you an empty column with the right name and type -- the actual values that existed before the drop are gone unless recovered from a backup taken before the DROP ran.
You need to rename the users.username column to users.handle on a live production system with zero downtime. Write out the sequence of migrations (as up()/down() pseudocode) and application changes you'd deploy, in order, to accomplish this safely.
1) Migration: ADD COLUMN handle VARCHAR(255) NULL. 2) App deploy: write both username and handle on every insert/update. 3) Backfill job: UPDATE users SET handle = username WHERE handle IS NULL, batched. 4) App deploy: switch all reads to handle. 5) App deploy: stop writing username. 6) Migration (later, after confirming nothing reads username): DROP COLUMN username.
Follow the six-step pattern: add the new column, dual-write, backfill, switch reads, stop writing the old column, then drop it -- each as a separate deploy.