Migrations

Migrations let you version control your database schema, making it easy to share and deploy database changes.

Creating Migrations

Generate a new migration:

php craftsman make:migration create_users_table

This creates a timestamped migration file in the database/migrations directory.

Migration Structure

<?php

use ZephyrPHP\Database\Migration;
use Doctrine\DBAL\Schema\Schema;

return new class extends Migration
{
    public function up(Schema $schema): void
    {
        $table = $schema->createTable('users');

        $table->addColumn('id', 'integer', [
            'autoincrement' => true,
            'unsigned' => true
        ]);
        $table->addColumn('name', 'string', ['length' => 255]);
        $table->addColumn('email', 'string', ['length' => 255]);
        $table->addColumn('password', 'string', ['length' => 255]);
        $table->addColumn('active', 'boolean', ['default' => true]);
        $table->addColumn('created_at', 'datetime');
        $table->addColumn('updated_at', 'datetime');

        $table->setPrimaryKey(['id']);
        $table->addUniqueIndex(['email']);
    }

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

Running Migrations

# Run all pending migrations
php craftsman migrate

# Run a specific number of migrations
php craftsman migrate --step=1

# Check migration status
php craftsman migrate:status

Rolling Back

# Rollback the last migration
php craftsman migrate:rollback

# Rollback multiple migrations
php craftsman migrate:rollback --step=3

# Rollback all migrations
php craftsman migrate:reset

# Rollback and re-run all migrations
php craftsman migrate:fresh

Column Types

Column Options

// Nullable column
$table->addColumn('phone', 'string', [
    'length' => 20,
    'notnull' => false
]);

// Default value
$table->addColumn('status', 'string', [
    'length' => 20,
    'default' => 'pending'
]);

// Unsigned integer
$table->addColumn('views', 'integer', [
    'unsigned' => true,
    'default' => 0
]);

// Decimal with precision
$table->addColumn('price', 'decimal', [
    'precision' => 10,
    'scale' => 2
]);

Indexes

// Primary key
$table->setPrimaryKey(['id']);

// Unique index
$table->addUniqueIndex(['email']);

// Regular index
$table->addIndex(['status']);

// Composite index
$table->addIndex(['user_id', 'created_at']);

// Named index
$table->addIndex(['category_id'], 'idx_category');

Foreign Keys

public function up(Schema $schema): void
{
    $table = $schema->createTable('posts');

    $table->addColumn('id', 'integer', ['autoincrement' => true]);
    $table->addColumn('user_id', 'integer', ['unsigned' => true]);
    $table->addColumn('title', 'string', ['length' => 255]);
    $table->addColumn('content', 'text');

    $table->setPrimaryKey(['id']);
    $table->addIndex(['user_id']);

    // Add foreign key
    $table->addForeignKeyConstraint(
        'users',           // Reference table
        ['user_id'],       // Local columns
        ['id'],            // Reference columns
        [
            'onDelete' => 'CASCADE',
            'onUpdate' => 'CASCADE'
        ]
    );
}

Modifying Tables

public function up(Schema $schema): void
{
    $table = $schema->getTable('users');

    // Add column
    $table->addColumn('phone', 'string', [
        'length' => 20,
        'notnull' => false
    ]);

    // Add index
    $table->addIndex(['phone']);
}

public function down(Schema $schema): void
{
    $table = $schema->getTable('users');

    $table->dropIndex('IDX_...');
    $table->dropColumn('phone');
}

Renaming Tables/Columns

public function up(Schema $schema): void
{
    // Rename table
    $schema->renameTable('users', 'members');

    // Rename column
    $table = $schema->getTable('members');
    $table->renameColumn('name', 'full_name');
}

Dropping Tables

public function up(Schema $schema): void
{
    // Drop single table
    $schema->dropTable('temporary_data');

    // Drop if exists
    if ($schema->hasTable('old_logs')) {
        $schema->dropTable('old_logs');
    }
}

Example: Blog Schema

<?php

use ZephyrPHP\Database\Migration;
use Doctrine\DBAL\Schema\Schema;

return new class extends Migration
{
    public function up(Schema $schema): void
    {
        // Categories table
        $categories = $schema->createTable('categories');
        $categories->addColumn('id', 'integer', ['autoincrement' => true]);
        $categories->addColumn('name', 'string', ['length' => 100]);
        $categories->addColumn('slug', 'string', ['length' => 100]);
        $categories->setPrimaryKey(['id']);
        $categories->addUniqueIndex(['slug']);

        // Posts table
        $posts = $schema->createTable('posts');
        $posts->addColumn('id', 'integer', ['autoincrement' => true]);
        $posts->addColumn('user_id', 'integer');
        $posts->addColumn('category_id', 'integer', ['notnull' => false]);
        $posts->addColumn('title', 'string', ['length' => 255]);
        $posts->addColumn('slug', 'string', ['length' => 255]);
        $posts->addColumn('content', 'text');
        $posts->addColumn('published', 'boolean', ['default' => false]);
        $posts->addColumn('published_at', 'datetime', ['notnull' => false]);
        $posts->addColumn('created_at', 'datetime');
        $posts->addColumn('updated_at', 'datetime');

        $posts->setPrimaryKey(['id']);
        $posts->addUniqueIndex(['slug']);
        $posts->addIndex(['user_id']);
        $posts->addIndex(['category_id']);
        $posts->addIndex(['published', 'published_at']);

        $posts->addForeignKeyConstraint('users', ['user_id'], ['id'], [
            'onDelete' => 'CASCADE'
        ]);
        $posts->addForeignKeyConstraint('categories', ['category_id'], ['id'], [
            'onDelete' => 'SET NULL'
        ]);
    }

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