Migrations

Migrations provide version control for your database schema. Each migration is a PHP class that represents a set of database changes.

Please note that only MySQL/MariaDB based migrations are supported.

Creating Migrations

To create a new migration file, fire this command from console:

php console create:migration create_table_products

This will create the migration file prefixed with current datetime in database/migrations folder. The migration class contains methods up() and down().

The up() method contains definition for required schema changes. Any reverse operations should go inside down() folder.

In short: up() is the do part of a migration; down() is the undo.

Running Migrations

To run your migration files:

php console migrate:up

This command will run the up() method in all the migration scripts defined inside database/migrations folder.

To track the files that have been migrated, it will also create a migrations table in the database.

Rollback Migrations

To rollback or undo all your migrations:

php console migrate:down

This will run the down() method in all the migration scripts inside database/migrations folder.

To rollback a limited number of migrations, provide the steps flag. For example, this will rollback last two batches of migrations if present.

php console migrate:down --steps=2

To rollback all the migrations in one go, provide the --all flag:

php console migrate:down --all

Defining Migrations

All table operations take a closure that receives a Table object:

$this->create('users', function(Table $table) {
    // column definitions, indexes, foreign keys...
});

$this->alter('users')->add(function(Table $table) {
    // add columns, indexes, foreign keys...
});

Complete Example

public function up(): void
{
    $this->create('posts', function(Table $table) {
        $table->id();
        $table->varchar('title');
        $table->varchar('slug')->unique();
        $table->text('body')->nullable();
        $table->foreignKey('user_id')->references('id')->on('users');
        $table->timestamps();
    });
}

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

Create Table

public function up(): void
{
    $this->create('users', function(Table $table) {
        $table->id();
        $table->varchar('name');
        $table->varchar('email');
    });
}

public function down(): void
{
    $this->drop('users');
}

Rename Table

public function up(): void
{
    $this->rename('users', 'customers');
}

public function down(): void
{
    $this->rename('customers', 'users');
}

Truncate Table

public function up(): void
{
    $this->truncate('users');
}

Execute Raw SQL

You can execute raw SQL queries when needed:

public function up(): void
{
    $this->execute('ALTER TABLE users ADD COLUMN custom_field VARCHAR(255)');
}

Alter Table

You may alter an existing table definition as documented below.

Add New Columns

public function up(): void
{
    $this->alter('users')->add(function(Table $table) {
        $table->varchar('password');
        $table->timestamps();
    });
}

Modify Existing Columns

public function up(): void
{
    $this->alter('users')->modify(function(Table $table) {
        $table->varchar('name', 55);
    });
}

// Modify an enum column
public function up(): void
{
    $this->alter('users')->modify(function(Table $table) {
        $table->enum('status', ['active', 'inactive', 'banned']);
    });
}

Drop Existing Columns

public function up(): void
{
    // Drop single column
    $this->alter('users')->dropColumn('password');
}

public function down(): void
{
    // Drop multiple columns at once
    $this->alter('users')->dropColumn('password', 'email', 'phone');
}

Rename Column

public function up(): void
{
    $this->alter('users')->renameColumn('name', 'full_name');
}

public function down(): void
{
    $this->alter('users')->renameColumn('full_name', 'name');
}

Add/Drop Indexes

When altering a table, add or remove indexes directly:

// Add indexes
$this->alter('users')->add(function(Table $table) {
    $table->index('email');
    $table->unique('phone');
});

// Drop indexes by name
$this->alter('users')->dropIndex('email_index');
$this->alter('users')->dropUnique('phone_unique');

Add Foreign Keys to Existing Tables

// Add a foreign key to an existing table
public function up(): void
{
    $this->alter('products')->add(function(Table $table) {
        $table->foreignKey('category_id')->references('id')->on('categories');
    });
}

// Add a column and foreign key together
public function up(): void
{
    $this->alter('products')->add(function(Table $table) {
        $table->column('category_id')->type('bigint')->attribute('unsigned');
        $table->foreignKey('category_id')->references('id')->on('categories');
    });
}

Table Columns

All column methods return a Column object for chaining configuration.

Column Types

Column Configuration

Chain these after any column method:

$table->varchar('username', 50)->unique()->nullable();
$table->int('age')->default(0)->unsigned();
$table->decimal('balance', 12, 2)->attribute('UNSIGNED');
$table->datetime('created_at')->current();

Table Indexes

Supported Index Types

• Primary Key
• Unique Index
• Regular Index
• Fulltext Index
• Spatial Index

Index Naming

You can provide a custom name, or let the framework generate one based on the column name(s) and index type.

Tip: When dropping an index, pass the exact name shown in SHOW INDEXES or generated as above.

primary()

• Defines a primary key (single or composite).
• primary(string|array $columns)

// single primary key
$table->primary('id');

// composite key
$table->primary(['user_id', 'post_id']);

dropPrimary()

• Drops the primary key constraint (not the column).

Important Notes:

• Dropping the primary key does not remove the column from the table but only the primary key constraint.
• Remember that there can be only one auto column and it must be defined as a key. So if the primary key is defined as auto, you will need to remove the auto attribute first.
• Do not forget to drop or alter foreign keys referencing the primary key.

$table->dropPrimary();

unique()

• Adds a unique index to one or more columns.
• unique(string|array $columns, ?string $indexName = null)
• Supports custom unique index name.

Note: You should remove duplicate values from the columns before adding unique index otherwise it may result in "mysql error 1062".

$table->unique('email');
$table->unique(['first', 'last'], 'name_unique');

dropUnique()

• Drops a unique index by name.
• dropUnique(string $indexName)

$this->alter('users')->dropUnique('email_unique');

index()

• Adds a regular (non-unique) index.
• index(string|array $columns, ?string $indexName = null)
• Supports custom index name.

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

dropIndex()

• Drops a regular index by name.
• dropIndex(string $indexName)

$this->alter('users')->dropIndex('user_status_idx');

fulltext()

• Adds a FULLTEXT index for text search.
• fulltext(string|array $columns, ?string $indexName = null)
• Supports custom full text index name.

$table->fulltext('body');
$table->fulltext(['title', 'body'], 'post_fulltext');

dropFulltext()

• Drops one or more FULLTEXT indexes by name.
• dropFulltext(string ...$indexName)

// Drop single fulltext index
$this->alter('posts')->dropFulltext('post_fulltext');

// Drop multiple fulltext indexes
$this->alter('posts')->dropFulltext('title_fulltext', 'body_fulltext');

spatial()

• Adds a SPATIAL index for GIS data.
• spatial(string|array $columns, ?string $indexName = null)
• Support passing custom spatial index name.

$table->spatial('location', 'loc_idx');

dropSpatial()

• Drops a SPATIAL index by name.
• dropSpatial(string $indexName)

$this->alter('locations')->dropSpatial('loc_idx');

Table Configuration

You can configure table engine, charset, and collation:

public function up(): void
{
    $this->create('users', function(Table $table) {
        $table->id();
        $table->varchar('name');

        // Configure table settings
        $table->engine('InnoDB');  // Default: InnoDB
        $table->charset('utf8mb4'); // Default: utf8mb4
        $table->collation('utf8mb4_unicode_ci'); // Default: utf8mb4_unicode_ci
    });
}

Foreign Keys

Foreign keys enforce referential integrity between tables, ensuring that a column (or set of columns) in one table matches the primary key or unique key in another table. Foreign keys can be defined during table creation or added/removed during schema alteration. The following document details the support for working with foreign keys.

Defining Foreign Keys

To define a foreign key constraint, use the foreignKey() method. The constraint name is auto-generated as fk_{table}_{column} — always predictable. Use ->name() to override.

$table->foreignKey('author_id')
    ->references('id')
    ->on('users')
    ->cascadeOnDelete();
// Generates: CONSTRAINT `fk_posts_author_id` FOREIGN KEY (`author_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE RESTRICT

// Custom constraint name:
$table->foreignKey('author_id')->name('my_fk_name')->cascadeOnDelete();

Foreign Key Actions

Available actions for ON UPDATE and ON DELETE:

• CASCADE: Automatically update/delete related rows
• RESTRICT: Prevent update/delete if related rows exist (default)
• SET NULL: Set foreign key column to NULL

Available Methods:

• cascadeOnDelete(): Set ON DELETE CASCADE
• cascadeOnUpdate(): Set ON UPDATE CASCADE
• restrictOnDelete(): Set ON DELETE RESTRICT (default)
• restrictOnUpdate(): Set ON UPDATE RESTRICT (default)
• nullOnDelete(): Set ON DELETE SET NULL
• nullOnUpdate(): Set ON UPDATE SET NULL

Note: Default behavior is RESTRICT for both ON UPDATE and ON DELETE.

Example:

$table->foreignKey('category_id')
    ->references('id')
    ->on('categories')
    ->nullOnDelete()      // Set to NULL when parent is deleted
    ->restrictOnUpdate(); // Prevent parent updates if children exist

Dropping Foreign Keys

Constraint names follow the pattern fk_{table}_{column}, so they are always predictable:

public function up(): void
{
    // Drop single foreign key
    $this->alter('posts')->dropForeign('fk_posts_author_id');

    // Drop multiple foreign keys
    $this->alter('posts')->dropForeign('fk_posts_author_id', 'fk_posts_category_id');
}

Note: If you used ->name() to set a custom constraint name, pass that name to dropForeign() instead.