Migration

Goals

A migration is the source of truth for the database schema. Every migration must run on both SQLite and PostgreSQL, stay easy to verify, and avoid depending on application code whose behavior can change.

Default principles:

• Use Schema and Blueprint for DDL; use raw SQL only when the schema builder cannot express the change safely.
• Use the query builder for data backfills; do not call Eloquent models inside migrations.
• Every migration must declare up() and down().
• Prefer additive changes; split high-risk changes across multiple migrations.

Creating migrations

php artisan make:migration create_orders_table
php artisan make:migration add_timezone_to_users_table --table=users
php artisan make:migration create_role_user_table --create=role_user

Migrations that create a new table should include the full schema in one file:

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', static function (Blueprint $table): void {
            $table->id();
            $table->foreignId('user_id')
                ->constrained()
                ->cascadeOnUpdate()
                ->cascadeOnDelete();
            $table->string('status', 30)->default('pending');
            $table->timestamps();

            $table->index(['user_id', 'status']);
        });
    }

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

Changing existing tables

When adding columns to an existing table, guard each column to avoid failures in environments that were hotfixed or partially deployed. New columns must be nullable() or provide a safe default for existing rows:

if (! Schema::hasColumn('users', 'timezone')) {
    Schema::table('users', static function (Blueprint $table): void {
        $table->string('timezone', 64)->nullable();
    });
}

Do not use change() to tighten constraints before cleaning up existing data. Backfill first, then change nullability or type:

DB::table('stock_production_lots')
    ->whereNull('lot_number')
    ->update(['lot_number' => 'pending']);

Schema::table('stock_production_lots', static function (Blueprint $table): void {
    $table->string('lot_number')->default('pending')->nullable(false)->change();
});

When using change(), redeclare every modifier you need to keep, such as default, comment, unsigned, and index. If an operation is incompatible between SQLite and PostgreSQL, branch explicitly by driver or choose a safer additive rollout. Do not use raw ALTER TABLE for type or nullability changes when the Laravel schema builder already supports them.

Foreign keys and indexes

Prefer foreignId()->constrained() over manually wiring column types and constraints. For nullable foreign keys, call nullable() before constrained():

$table->foreignId('manager_id')
    ->nullable()
    ->constrained('users')
    ->nullOnDelete();

Split index or constraint changes from unrelated column changes when that makes rollback and production rollout clearer. When idempotency matters, guard indexes with the API that matches your Laravel version:

if (! Schema::hasIndex('users', 'users_email_verified_at_index')) {
    Schema::table('users', static function (Blueprint $table): void {
        $table->index(
            ['email', 'email_verified_at'],
            'users_email_verified_at_index',
        );
    });
}

If SQLite does not support drop/recreate constraint operations directly, branch by driver and test both databases. Do not mix foreign-key repairs into a large feature migration when they can ship independently.

Backfill and multi-phase rollout

Simple backfills should use the query builder:

DB::table('users')
    ->whereNull('timezone')
    ->update(['timezone' => 'UTC']);

For large tables, read and update in batches to reduce lock time:

DB::table('orders')
    ->select(['id', 'status'])
    ->orderBy('id')
    ->chunkById(500, static function ($orders): void {
        foreach ($orders as $order) {
            DB::table('orders')
                ->where('id', $order->id)
                ->update([
                    'status_label' => strtoupper($order->status),
                ]);
        }
    });

Production rollout should follow this order: add a nullable column or one with a default, deploy code that can read both old and new schemas, backfill data, then tighten constraints or remove old artifacts in a later migration. Heavy backfills should be a separate step so they are easier to observe and retry.

down() and rollback

Always define down(). For migrations that create a new table, use Schema::dropIfExists(...). For simple additive migrations, you may remove the artifact you just added when reversing it is safe.

For destructive, data-moving, or heavily driver-branched migrations, do not invent a dangerous rollback. Default to a documented no-op and write a new forward migration when recovery is needed:

public function down(): void
{
    fwrite(STDERR, static::class . ': down() not implemented.' . PHP_EOL);
}

Testing and pre-run checks

Before and after risky changes, inspect status and the generated SQL:

php artisan migrate:status
php artisan migrate --pretend
php artisan migrate --path=database/migrations/2026_01_01_000000_add_timezone_to_users_table.php

After every new or edited migration, you must run:

php artisan test --filter DatabaseMigrationCompatibilityTest

The compatibility test must prove the full migration set runs cleanly on in-memory SQLite and PostgreSQL, including guarded additive changes and driver-specific branches. Add dedicated assertions for raw SQL, change(), foreign-key, or index repairs when the migration includes those pieces.

Production

Before deploy, confirm the migration has not already run, review --pretend output, and verify every new NOT NULL column has a default or a completed backfill plan. Run production migrations with:

php artisan migrate --force --isolated

--isolated only prevents multiple processes from running migrations at the same time; it does not make an unsafe migration safe. If a production migration fails partway through, prefer writing a follow-up repair migration over relying on a guessed rollback.

Quick troubleshooting

• Table already exists: check migrate:status; if the table already matches the intended schema, create a modify migration instead of making create idempotent.
• Column already exists: keep the guard for the additive change, but inspect the current column's type, default, and nullability to detect drift.
• Foreign key mismatch: compare both column types, the referenced table, and indexes; prefer foreignId()->constrained().
• Unsupported SQLite operation: switch to an additive rollout or branch explicitly by driver.
• Guard hides drift: inspect the real schema and write a targeted repair migration; do not stack more guards.

Checklist

• [ ] Migration is compatible with SQLite and PostgreSQL.
• [ ] New columns on existing tables are guarded and have safe default/nullability.
• [ ] Foreign keys and indexes have appropriate names, types, and behavior.
• [ ] Backfill does not use Eloquent models and uses batches for large tables.
• [ ] up() and down() match the risk level of the change.
• [ ] Ran migrate --pretend and DatabaseMigrationCompatibilityTest.
• [ ] Production rollout has a plan for locks, backfill, and forward repair.