Migrations

Introduction

A migration is a file that changes your tables and knows how to take the change back. Migrations live in your repository and run in order, so every machine, yours, a colleague's, production, arrives at the same tables by the same steps.

The default database's migrations are in database/migrations. To keep them elsewhere, set migrations on its connection to the folder; another database always needs a folder of its own.

Writing a migration

php cua make:migration create_posts_table
# Created database/migrations/2026_10_10_101500_create_posts_table.php

A name of the form create_<table>_table starts the file with that table. Any other name, such as add_phone_to_users, starts it empty. If the stub cannot be written, the command exits nonzero and names the destination and permissions to check; it reports creation only after a successful write.

use Crab\Contracts\Database\Migration;
use Crab\Contracts\Database\Schema;
use Crab\Contracts\Database\Table;

return new class implements Migration {
    public function up(Schema $schema): void
    {
        $schema->create('posts', function (Table $table) {
            $table->id();
            $table->foreignId('user_id')->references('users', onDelete: 'cascade');
            $table->string('title');
            $table->string('slug')->unique();
            $table->text('body')->nullable();
            $table->boolean('published')->default(false);
            $table->timestamps();
            $table->index(['user_id', 'published']);
        });
    }

    public function down(Schema $schema): void
    {
        $schema->drop('posts');
    }
};

up() makes the change and down() takes it back.

Running migrations

php cua migrate                  # runs those that have not run
php cua migrate:status           # lists each and whether it has run
php cua migrate:rollback         # takes back the last run
php cua migrate:fresh            # drops every table, then runs them all

Migrations run in the order of their file names. Each migrate is a batch, and migrate:rollback takes back the last batch, newest first.

While you develop, migrate:fresh is the quick way back to a clean database after you change a migration or switch branches: it drops every table and view, whether a migration made it or not, and runs every migration again as one batch, without calling any down(). Every row is lost, so never run it against a database whose data you need.

Run php cua migrate as a step of every deploy, before the new code goes live.

[!WARNING] Never edit a migration that has run anywhere but your own machine. Write a new one that makes the correction.

Deploying from several machines

During a rolling deploy, several machines may run php cua migrate together. A database lock prevents them from applying the same migration twice. MySQL holds the lock for the command. SQLite locks each migration and checks its record again before making changes, so several commands may share the work.

database.migration_lock_timeout is the number of whole seconds to wait for a lock, 60 by default. Zero does not wait; fractional configuration values are cast to integers. Increase it when your migrations take longer:

// config/database.php
return [
    'connections' => [
        'main' => [
            'driver' => 'mysql',
            'database' => env('DB_DATABASE'),
        ],
    ],
    'migration_lock_timeout' => 120,
];

If the wait expires, the command fails and tells you to inspect php cua migrate:status. Status and creating a migration file remain available while another command holds the lock. Status shows committed work, including each SQLite migration as it finishes. A rollback chooses the latest committed batch and skips migrations another rollback already took back.

MySQL uses a connection lock named crab_migrations_ followed by the migrations table name. Applications using the same table name on one MySQL server share that lock. The command holds one pooled connection and gives it to the migration's Schema; a migration that also asks the application's Database service for a connection needs a pool of at least two, or it fails with “No database connection became free …; the pool holds 1.” If that session loses its lock, the command stops before the next migration or before returning, warns that a migration may be half applied, and tells you to inspect migrate:status.

SQLite commits each migration separately. Its writer transaction blocks other writes only for that migration, and its commit callbacks run before the next one starts. A failed migration rolls back its own changes; earlier committed migrations remain. A commit callback that fails reports its migration's name, but its database changes are already committed. Both databases release their locks after success or failure.

Creating tables

Columns

Use decimal for money on MySQL; use integer minor units for exact money on both databases. SQLite does not enforce unsigned() either.

Column options

$table->string('phone')->nullable();                 // may be empty
$table->integer('stock')->default(0);
$table->dateTime('created_at')->useCurrent();        // the time of insertion
$table->integer('quantity')->unsigned();
$table->string('email')->unique();
$table->string('status')->index();

A non-generated column must be given a value on insert unless it is nullable() or has a default; id() is generated and may be omitted.

Indexes

$table->index('email');
$table->index(['user_id', 'created_at']);            // one index over two columns
$table->unique(['team_id', 'slug']);
$table->primary(['user_id', 'role_id']);             // in place of id(), for a join table

Add an index for every column you filter or join on in a query that runs often.

Foreign keys

$table->foreignId('user_id')->references('users');                          // users.id
$table->foreignId('team_id')->references('teams', onDelete: 'cascade');     // deleted with its team
$table->foreignId('author_id')->references('users', 'id', onDelete: 'set null')->nullable();

Changing tables

$schema->table() changes a table that exists:

public function up(Schema $schema): void
{
    $schema->table('users', function (Table $table) {
        $table->string('phone')->nullable();
        $table->index('phone');
        $table->renameColumn('name', 'full_name');
        $table->dropColumn('legacy_code');
    });
}

public function down(Schema $schema): void
{
    $schema->table('users', function (Table $table) {
        $table->dropIndex(['phone']);
        $table->dropColumn('phone');
        $table->renameColumn('full_name', 'name');
        $table->string('legacy_code')->nullable();
    });
}

dropIndex() and dropUnique() take an array of column names, such as ['phone'], or a string containing the exact index name, such as 'users_phone_index'.

[!NOTE] A column's type cannot be changed in table(), because SQLite has no statement for it. Add a column of the new type, copy the values, and drop the old one.

A column added to a table that already has rows must be nullable() or have a default, since the existing rows need a value.

Moving data

$schema->connection() is the database, for rows that must move with a change:

public function up(Schema $schema): void
{
    $schema->table('users', function (Table $table) {
        $table->string('status')->default('active');
    });

    $schema->connection()->table('users')->where('banned', true)->update(['status' => 'banned']);
}

MySQL and SQLite

The same file runs on both; the schema is written for the database in use. Besides column constraints, they differ in transactions:

• On SQLite each migration runs in a transaction. A failure before commit rolls back its changes; a failed commit callback leaves its already committed changes.
• MySQL commits each change to a table as it is made. A migration that fails halfway has done the first half. Keep each migration small, one change to one table where you can, so that a failure leaves little to tidy.

SQLite checks foreign keys on the connections the plugin opens.

Several databases and packages

Each configured database has its own migrations folder and its own record of what ran. --database must name a configured connection exactly; missing, empty and unknown names are refused before opening a connection. The commands take --database=:

php cua make:migration create_totals_table --database=reports
php cua migrate --database=reports

A package adds its own folder of migrations from its plugin, through Crab\Contracts\Database\Migrator::path().

The framework itself ships no migrations. A component that needs a table writes the migration into your folder with an install command, where you own it. One that is told to keep its table in another database, with queue.database or cache.database, writes it into that database's folder; run migrate --database= for it.

php cua token:install       # authentication tokens
php cua queue:install        # the queue's jobs
php cua cache:install        # the cache's database store