Migrations

Migrations allow you to design, organize, and control your database schema using pure PHP. SigmaPHP provides a powerful migration system with a variety of helper methods that make creating and modifying database structures simple and maintainable.

Creating Migrations

To create a new migration file, run the following command in your terminal:

./bin/sigma-cli create:migration ProductsTable

This command will generate a new migration file called ProductsTableMigration.php inside the database/migrations directory.

If you want to change the default location where migration files are stored, open the database.php configuration file and update the migrations path:

/**
 * Default path for migration files.
 */
'path_to_migrations' => env('PATH_TO_MIGRATIONS', '/database/migrations'),

After creating the migration file, open ProductsTableMigration.php. You will find the default migration class template:

<?php

use SigmaPHP\DB\Migrations\Migration;

class ProductsTableMigration extends Migration
{
    /**
     * @return void
     */
    public function up()
    {
        // up method
    }

    /**
     * @return void
     */
    public function down()
    {
        // down method
    }
}

All schema creation logic should be written inside the up() method. If you need to rollback the migration, the statements required to undo those changes should be written inside the down() method.

For example, if you create a new table in the up() method, you should drop that table inside the down() method.

<?php

use SigmaPHP\DB\Migrations\Migration;

class ProductsTableMigration extends Migration
{
    /**
     * @return void
     */
    public function up()
    {
        $this->createTable('products', .......);
    }

    /**
     * @return void
     */
    public function down()
    {
        $this->dropTable('products');
    }
}

Running Migrations

To execute your database migrations, run the following command:

// run all migration files
./bin/sigma-cli migrate

// run specific migration file
./bin/sigma-cli migrate ProductsTableMigration

One unique feature supported by SigmaPHP is nested directory migrations. The migration runner automatically scans all sub-directories inside the root migrations path.

For example, assume the following migration structure:

parent-dir
    |
    -- sub-dir-1
        |
        -- sub-dir-2
            |
            -- MyMigration.php

When running the migration command, SigmaPHP will automatically detect and execute all migration files inside nested directories without requiring additional configuration.

// MyMigration will be detected and executed
./bin/sigma-cli migrate

// run a specific migration by providing its relative path
./bin/sigma-cli migrate parent-dir/sub-dir-1/sub-dir-2/MyMigration

This section describes the main migration methods provided by SigmaPHP. All of these methods are available inside your migration class and can be accessed using $this.

Migrations History

SigmaPHP keeps track of all executed migrations using a dedicated migrations history table. Each time a migration is successfully executed, the framework records its execution details (such as the migration name and execution date) in this table. This allows SigmaPHP to determine which migrations have already been applied and enables safe rollbacks when needed.

By default, the migrations history table is named db_logs. However, this name can be customized through the configuration file. The following configuration option controls the table name used to store the migrations history:

Changing this value allows you to store migration history in a different table if required by your project or database conventions.

/**
* Default name for migrations history table.
*/
'logs_table_name' => env('LOGS_TABLE_NAME', 'db_logs'),
    

Rollback

SigmaPHP provides a convenient way to revert previously executed migrations. By default, the rollback command will revert the latest executed migration. The framework reads the migrations history table and calls the corresponding down() method for the most recently applied migration.

You can also rollback migrations up to a specific date. In this case, SigmaPHP will revert all migrations executed after the provided date, processing them in reverse order to safely restore the database to the desired state.

./bin/sigma-cli rollback
    

The command above will rollback the latest executed migration.

./bin/sigma-cli rollback 2023-1-20
    

This command will rollback all migrations executed after 2023-1-20. The migration execution dates are stored in the migrations history table (by default db_logs), which allows SigmaPHP to determine exactly which migrations should be reverted.

Table Methods

Create table

$this->createTable(
    'products',
    [
        ['name' => 'id', 'type' => 'bigint', 'primary' => true],
        ['name' => 'title', 'type' => 'varchar', 'size' => 25],
        ['name' => 'price', 'type' => 'decimal'],
    ],
    [
        'engine' => 'innodb',
        'comment' => 'this is products table'
    ]
);

This method accepts three parameters: the table name, an array describing the fields, and an optional array containing table options.

SigmaPHP supports all common MySQL data types. Some of the most frequently used types include:

• Strings: char, varchar, text, longtext, enum, blob
• Numeric: tinyint, bool, smallint, int, bigint, float, decimal
• Date/Time: date, datetime, timestamp, time, year

For the full list of supported types, please check the official MySQL documentation.

In addition to the default MySQL data types, SigmaPHP also provides special helper types.

Example:

$this->createTable(
    'special_types',
    [
        ['name' => 'id', 'type' => 'uuid'],
        ['name' => 'soft_delete'],
        ['name' => 'timestamps']
    ]
);

Update Table

Updates the properties of an existing table without modifying its columns. This method can be used to change table-level attributes such as the engine, collation, comment, or row format. To modify the structure of the table columns, you should use the column methods instead.

<?php

$this->updateTable(
    'products',
    [
        'comment' => 'this is products table'
    ]
);
    

Rename Table

Renames an existing table in the database. This is useful when restructuring your schema or adjusting naming conventions without losing the existing table data.

<?php

$this->renameTable(
    'old_table_name',
    'new_table_name'
);
    

Check Table Existence

Checks whether a specific table exists in the database. This method is useful when writing conditional migrations or ensuring that certain operations are performed only if the table already exists.

<?php

$this->checkTable('users');
    

Change Table Primary Key

Changes the primary key of a table by replacing the existing primary key with a new one. This is helpful when modifying the structure of a table where the primary identifier needs to be updated.

<?php

$this->changeTablePrimaryKey(
    'users',
    'old_table_primary_key',
    'new_table_primary_key'
);
    

Drop Table

Removes a table completely from the database along with all of its data. This operation is usually placed inside the down() method to roll back tables created in the up() migration.

<?php

$this->dropTable('comments');
    

Columns Methods

Add Column

Adds a new column to an existing table. You must provide the table name, the column name, and an array describing the column type and options such as size, default value, or nullability.

<?php

$this->addColumn(
    'users',
    'phone',
    [
        'type' => 'varchar',
        'size' => 25
    ]
);
    

Update Column

Modifies the properties of an existing column such as its size, default value, or nullability. This method allows you to evolve your schema without recreating the entire table.

<?php

$this->updateColumn(
    'users',
    'phone',
    [
        'size' => 50,
        'not_null' => true
    ]
);
    

Rename Column

Changes the name of an existing column while preserving its data. This is useful when adjusting naming conventions or refactoring database structures.

<?php

$this->renameColumn(
    'users',
    'phone',
    'phone_number'
);
    

Check Column Existence

Checks whether a specific column exists in a given table. This is commonly used in conditional migrations to prevent errors when running migrations multiple times.

<?php

$this->checkColumn(
    'users',
    'phone'
);
    

Drop Column

Removes a column from a table along with all data stored in that column. This operation is typically used when cleaning up obsolete fields in the database schema.

<?php

$this->dropColumn(
    'users',
    'phone'
);
    

Index Methods

Add Index

Creates a new index on one or more columns in a table. Indexes improve query performance by allowing the database engine to locate rows more efficiently. SigmaPHP supports multiple index types such as normal, unique, fulltext, and descending indexes.

<?php

$this->addIndex(
    'table_name',
    'index_name',
    ['columns' .....],
    'index_type',
    ['fields_order']
);
    

Example:

<?php

$this->addIndex(
    'users',
    'user_index',
    ['name', 'email'],
    'normal',
    [
        'name' => 'desc',
        'email' => 'asc'
    ]
);
    

Check Index

Checks whether a specific index exists on a table. This method helps prevent duplicate indexes and allows safer schema updates.

<?php

$this->checkIndex(
    'users',
    'user_index'
);
    

Drop Index

Removes an existing index from a table. This can be useful when optimizing queries or restructuring database indexes.

<?php

$this->dropIndex(
    'users',
    'user_index'
);
    

Foreign Key Methods

Add Foreign Key

Creates a foreign key constraint between two tables. Foreign keys enforce referential integrity by ensuring that values in the local table correspond to valid records in the referenced table.

<?php

$this->addForeignKey(
    'constraint',
    'localTable',
    ['localIds' ......],
    'referenceTable',
    ['foreignIds' ......],
    ['options']
);
    

Example:

<?php

$this->addForeignKey(
    'test_foreign_key',
    'test',
    'id',
    'test2',
    'id',
    [
        'on_delete' => 'NO ACTION',
        'on_update' => 'NO ACTION',
    ]
);
    

Check Foreign Key

Checks whether a foreign key constraint exists on a specific table. This method is useful when writing safe migrations that may run multiple times.

<?php

$this->checkForeignKey(
    'users',
    'user_foreign_key'
);
    

Drop Foreign Key

Removes an existing foreign key constraint from a table. This may be required when restructuring relationships between tables.

<?php

$this->dropForeignKey(
    'users',
    'user_foreign_key'
);
    

Default Migration Templates

The SigmaPHP CLI tool provides built-in migration templates for the most common database operations, such as creating new tables and adding columns to existing tables. To take advantage of these templates, simply name your migration file according to the predefined template naming pattern.

Create Table Migration Template

SigmaPHP can automatically generate a migration template for creating a new table. To use this feature, simply name your migration using the pattern CreateXXXXXXXXTable, where XXXXXXXX represents the table name.

For example, to generate a migration template for creating a posts table, run the following command:

./bin/sigma-cli create:migration CreatePostsTable

This command will generate a new migration file named CreatePostsTableMigration.php with the following default structure:

<?php

use SigmaPHP\DB\Migrations\Migration;

class CreatePostsTableMigration extends Migration
{
    /**
     * @return void
     */
    public function up()
    {
        $this->createTable(
            'posts',
            [
                ['name' => 'id', 'type' => 'bigint', 'primary' => true],
                /**
                 * add your columns !
                 */
                ['name' => 'timestamps']
            ]
        );
    }

    /**
     * @return void
     */
    public function down()
    {
        $this->dropTable('posts');
    }
}

The generated template already includes a basic table structure and a rollback implementation. You can simply add your desired columns inside the createTable() method.

Add Column Migration Template

SigmaPHP can also generate a migration template for adding a new column to an existing table. To use this feature, follow the naming pattern AddColumnXXXXXXXXToYYYYYYYYTable, where XXXXXXXX represents the column name and YYYYYYYY represents the table name.

For example, to add a size column to the post_images table, run:

./bin/sigma-cli create:migration AddColumnSizeToPostImagesTable

This command will generate the file AddColumnSizeToPostImagesTableMigration.php with the following default template:

<?php

use SigmaPHP\DB\Migrations\Migration;

class AddColumnSizeToPostImagesTableMigration extends Migration
{
    /**
     * @return void
     */
    public function up()
    {
        $this->addColumn(
            'post_images',
            'size',
            [
                'type' => 'SET_COLUMN_TYPE',
                // other options
            ]
        );
    }

    /**
     * @return void
     */
    public function down()
    {
        $this->dropColumn('post_images', 'size');
    }
}