Zero-downtime migrations — safe column changes on large tables

Advanced5 min read·lv-14-008

sqlperformanceinterview

Concept

Laravel's migration system targets multiple database drivers, but each has different capabilities and syntax. The Blueprint layer abstracts most differences, but some operations require driver awareness.

SQLite:

Does NOT support ALTER TABLE ... DROP COLUMN natively (before SQLite 3.35.0). Laravel works around this by recreating the entire table. SQLite 3.35.0+ supports column drops.
No full-text search with fullText().
No enum type — mapped to varchar.
json type is stored as text.
Limited constraint support (no onUpdate / onDelete for FKs in older versions).
Schema::disableForeignKeyConstraints() must be called for truncate and drops.

MySQL vs PostgreSQL differences:

string() → VARCHAR (MySQL), VARCHAR (Postgres). Both similar.
boolean → TINYINT(1) (MySQL), BOOLEAN (Postgres).
json → JSON (both) but Postgres has jsonb (binary, indexed) — use jsonb in Postgres: $table->jsonb('metadata').
uuid → CHAR(36) (MySQL), native UUID type (Postgres).
enum → MySQL native ENUM, Postgres creates a constraint on VARCHAR.
Auto-increment → AUTO_INCREMENT (MySQL), SERIAL / GENERATED ALWAYS AS IDENTITY (Postgres).
after($column) → MySQL only. Not supported in Postgres/SQLite.

Handling cross-DB migrations:

Check the driver: DB::connection()->getDriverName().
Use conditionals in migrations for driver-specific code.
DB_CONNECTION=sqlite for tests + DB_CONNECTION=mysql for production is very common in Laravel.

Code Example

php

<?php
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;

// Cross-driver safe migration
return new class extends \Illuminate\Database\Migrations\Migration
{
    public function up(): void
    {
        Schema::create('posts', function (Blueprint $table) {
            $table->id();
            $table->string('title');
            $table->text('body');
            $table->json('metadata')->nullable(); // JSON (MySQL) / TEXT (SQLite)
            $table->boolean('is_published')->default(false); // TINYINT(1) or BOOLEAN
            $table->timestamps();

            // after() is MySQL-only — don't use in cross-DB migrations
            // $table->string('subtitle')->after('title'); // only MySQL
        });

        // Postgres-specific: use jsonb instead of json for indexed JSON
        if (DB::connection()->getDriverName() === 'pgsql') {
            Schema::table('posts', function (Blueprint $table) {
                $table->jsonb('metadata')->nullable()->change();
            });
        }
    }

    public function down(): void
    {
        Schema::dropIfExists('posts');
    }
};

// SQLite compatibility note — testing with SQLite
// in tests, `migrate:fresh` is common; SQLite's limited ALTER TABLE
// is handled by Laravel's SchemaManager which rebuilds the table
// for operations SQLite doesn't natively support.

// Common testing pattern (phpunit.xml or TestCase)
// DB_CONNECTION=sqlite
// DB_DATABASE=:memory:
// This uses in-memory SQLite — fast but means migrations must be SQLite-compatible

// PostgreSQL JSON operations in Query Builder
// $posts = DB::table('posts')
//     ->whereRaw("metadata->>'status' = ?", ['published'])  // Postgres JSON operator
//     ->get();

// MySQL JSON operations
// $posts = DB::table('posts')
//     ->whereRaw("JSON_EXTRACT(metadata, '$.status') = ?", ['published'])
//     ->get();

PreviousMigration rollback strategies — down() method best practicesMigrations & Schema NextSchema differences — SQLite vs MySQL vs PostgreSQL in migrationsMigrations & Schema