Migration — a versioned, repeatable change to a schema

Beginner5 min read·eng-15-005

sqlinterview

Concept

Migration — a versioned, repeatable script that describes a schema change. Migrations let you evolve your database schema over time alongside your code.

Why migrations exist: Without migrations, schema changes are manual, error-prone, and hard to reproduce. One developer adds a column in production — other developers' local databases diverge. Migrations solve this by:

Versioning schema changes in code (committed to git).
Making them reproducible on any environment.
Supporting rollback (down() method).
Tracking what's been applied (the migrations table).

How migrations work (Laravel):

Each migration file has a timestamp prefix: 2024_01_15_000001_create_orders_table.php.
php artisan migrate runs all pending migrations (those not in the migrations table).
php artisan migrate:rollback calls down() on the last batch.
php artisan migrate:fresh drops all tables and re-runs all migrations.
php artisan migrate:status shows which migrations have run.

up() and down() methods:

up(): Apply the change. Schema::create(...), Schema::table(...).
down(): Reverse the change. Schema::drop(...), Schema::table(...) with opposite changes.

Migration best practices:

One concern per migration (don't create 5 tables in one file).
Never edit a migration that has already been run in production — add a NEW migration instead.
down() must exactly reverse up() — otherwise rollback breaks.
For large tables, add indexes in a separate migration (avoids locking the table during the main migration).

Code Example

php

<?php
// Creating a migration
// php artisan make:migration create_orders_table
// php artisan make:migration add_tracking_number_to_orders_table

// 2024_01_15_000001_create_orders_table.php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('orders', function (Blueprint $table) {
            $table->id();
            $table->foreignId('user_id')->constrained()->onDelete('cascade');
            $table->decimal('total', 10, 2);
            $table->string('status')->default('pending');
            $table->timestamps();
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('orders'); // exactly reverses up()
    }
};

// 2024_02_01_000001_add_tracking_number_to_orders_table.php
return new class extends Migration
{
    public function up(): void
    {
        Schema::table('orders', function (Blueprint $table) {
            $table->string('tracking_number')->nullable()->after('status');
        });
    }

    public function down(): void
    {
        Schema::table('orders', function (Blueprint $table) {
            $table->dropColumn('tracking_number'); // reversal of up()
        });
    }
};

// Zero-downtime migrations — for columns on large tables
// Never drop a column that code still reads. Steps:
// 1. Deploy: Stop writing the old column
// 2. Migrate: Create the new column
// 3. Backfill: Fill new column from old data
// 4. Deploy: Switch reads to new column
// 5. Migrate: Drop old column (now safe)

// Dangerous migration — adding NOT NULL to a big table (table lock!)
// Instead: add nullable, backfill, then add NOT NULL in a later migration
return new class extends Migration
{
    public function up(): void
    {
        // Step 1: Add nullable (no lock — just schema change)
        Schema::table('users', function ($table) { $table->string('slug')->nullable(); });

        // Step 2: Backfill (in chunks to avoid memory issues)
        \DB::table('users')->orderBy('id')->chunk(500, function ($users) {
            foreach ($users as $user) {
                \DB::table('users')->where('id', $user->id)->update(['slug' => \Str::slug($user->name)]);
            }
        });

        // Step 3: Now safe to make it NOT NULL
        Schema::table('users', function ($table) { $table->string('slug')->nullable(false)->change(); });
    }
};

PreviousSchema — the structure definition of a database (tables, columns, types)Vocabulary — Database & SQL NextIndex — a data structure that speeds up reads at the cost of write overheadVocabulary — Database & SQL