Database Migrations

Suppose we are starting a new big project and of course first thing to do is to compose our database architecture. And we want to use some sort of relational database system such as MySQL.

Surely we can go ahead and create our database and the tables through some GUI or via terminal. And then we can start codding our project business logic. If on the project works more people than us, then we should share our DB schema with other developers. We can do it by exporting the SQL dump and sending to them to import.

Sounds good so far…

Now suppose some developers are adding new columns on some of the tables on their branch. Other developers are removing some columns (as per requirement of their task). Some other developers may change some columns (like changing type or names or indexes, etc.).

This quickly becomes a mess, as nobody can tell exactly which operations should be taken in the first place, which are in the second and so on…

Migrations comes to help

Database migrations or simply migrations, are controlled sets of changes developed to modify the structure of the objects within a relational database. Migrations help transition database schemas from their current state to a new desired state. Whether that involves adding tables and columns, removing elements, splitting fields, or changing types and constraints.

The goals of database migrations are to make database changes repeatable, shareable, and testable.
In general, migration systems create files that can be shared, applied to multiple database systems, and stored in version control.

Migrations in Quantum framework

From version 2.7.0 Quantum comes with new migration feature which allows developers to fill that gap they have previously. It currently supports MySQL 8 and above as well as Mariadb 10.5.x and above. In near future it’s expected to have SQLIte and PostgreSQL included. The way of creating and applying migrations, are done via console commands and it mostly similar to other PHP frameworks if you tried one already.

The first thing to do is to generate a migration file. which can be done via migration:generate command. The command accepts arguments, "type" and "table name". There are 4 types available for generating migrations, create, alter, rename and drop.

Create table migration

To generate create type of migration we specify the type as first parameter and the table name as second.

> php qt migration:generate create users

The new migration file will be created in the project migrations directory and will named something like create_table_users_1655307916.php with some prefilled content.

<?php 
use Quantum\Migration\QtMigration; 
use Quantum\Factory\TableFactory; 
use Quantum\Libraries\Database\Schema\Type; 
use Quantum\Libraries\Database\Schema\Key; 
class Create_table_users_1655307916 extends QtMigration 
{ 
    public function up(?TableFactory $tableFactory) 
    {
        $table = $tableFactory->create('users'); 
    } 
    public function down(?TableFactory $tableFactory) 
    { 
        $tableFactory->drop('users'); 
    } 
}

Alter table migration

To generate alter type of migration we specify the type as first parameter and the table name as second.

> php qt migration:generate alter users

The new migration file will be created in the project migrations directory and will named something like alter_table_users_1655308339.php with some prefilled content

<?php 
use Quantum\Migration\QtMigration; 
use Quantum\Factory\TableFactory; 
use Quantum\Libraries\Database\Schema\Type; 
use Quantum\Libraries\Database\Schema\Key; 
class Alter_table_users_1655308339 extends QtMigration 
{ 
    public function up(?TableFactory $tableFactory) 
    { 
        $table = $tableFactory->get('users'); 
    } 
    public function down(?TableFactory $tableFactory) 
    { 
        $table = $tableFactory->get('users'); 
    } 
}

Rename table migration

To generate rename type of migration we specify the type as first parameter and the table name as second.

> php qt migration:generate rename users

The new migration file will be created in the project migrations directory and will named something like rename_table_users_1655308655.php with some prefilled content.

Notice, that the new table name need to be added there as a 2nd argument of rename() method.

<?php 
use Quantum\Migration\QtMigration;  
use Quantum\Factory\TableFactory; 
class Rename_table_users_1655308655 extends QtMigration 
{ 
    public function up(?TableFactory $tableFactory) 
    { 
        $tableFactory->rename('users', $newName); 
    } 
    public function down(?TableFactory $tableFactory) 
    { 
        $tableFactory->rename($newName, 'users'); 
    } 
}

Drop table migration

To generate drop type of migration we specify the type as first parameter and the table name as second.

> php qt migration:generate drop users

The new migration file will be created in the project migrations directory and will named something like drop_table_users_1655308866.php with some prefilled content

<?php 
use Quantum\Migration\QtMigration; 
use Quantum\Factory\TableFactory; 
class Drop_table_users_1655308866 extends QtMigration 
{ 
    public function up(?TableFactory $tableFactory) 
    { 
        $tableFactory->drop('users'); 
    } 
    public function down(?TableFactory $tableFactory) 
    { 
        // 
    } 
}

As we can see, every time it creates a class which extends the QTMigration base class and it always has 2 public methods: up() and down().

The up method is used to create new tables, columns, or indexes to your database, while the down method should reverse the operations performed by the up method.

Updating migration file content

Ok, now let’s open our migration file and add some new columns to the table with addColumn() method..

public function up(?TableFactory $tableFactory) 
{ 
    $table = $tableFactory->create('users'); 
    $table->addColumn('id', Type::INT, 11)->autoIncrement(); 
    $table->addColumn('username', Type::VARCHAR, 100)->unique(); 
    $table->addColumn('is_active', Type::BOOL); 
    $table->addColumn('options', Type::ENUM, ['op1', 'op2', 'op3'])->default('op2'); 
}

We have added 3 columns to the table:

id as integer with autoincrement
username as varchar with unique index
options as enum with default value
We can also alter the existing column paramters with modifyColumn() method.

public function up(?TableFactory $tableFactory) 
{ 
    $table = $tableFactory->get('users'); 
    $table->modifyColumn('username', Type::VARCHAR, 50); 
    $table->modifyColumn('is_active', Type::TINYINT, 1); 
}

Here we have modified 2 columns:

username column length was changed from 100 to 50
is_active column type was changed from boolean to tinyint
We can also rename columns with renameColumn() method.

public function up(?TableFactory $tableFactory) 
{ 
    $table = $tableFactory->get('users'); 
    $table->renameColumn('username', 'email'); 
}

To drop columns we use dropColumn() method.

public function up(?TableFactory $tableFactory) 
{ 
    $table = $tableFactory->get('users'); 
    $table->dropColumn('options'); 
}

Playing with indexes

When adding new column we can also specify the index with it as well, like:

$table->addColumn('profile_id', Type::INT, 11)->index();

Also we can specify the index name:

$table->addColumn('profile_id', Type::INT, 11)
      ->index('idx_profile_id');

There are 5 types of indexes are available: primary, index, unique, fulltext and spatial.

In order to add index key to an existing column we can use addIndex() method like:

$table->addIndex('profile_id', Key::INDEX);

Or with the index name:

$table->addIndex('profile_id', Key::INDEX, 'idx_profile_id');

Remember, if you are not specifying the index name, it will take the column name is its key name.

To drop the index from the column we can use dropIndex() method by passing the index name as paramter like this:

$table->dropIndex('idx_profile_id');

Migrating migrations

Ok, now when we have our table schemas defined in the migration files, it’s time to migrate them.

So to apply the migrations we simply run this command (the argument up is optional and can be skipped):

> php qt migration:migrate up

If you running this command first time, it will create new table in your database with name migrations, where it will store all applied migrations. Then it will iterate through the migrations which were created and run them all in chronological order.

Notice that the migration:migrate command runs only up migrations and only ones that weren't yet migrated.

If you want to roll back the migrated migrations you will need to run:

> php qt migration:migrate down

Be aware that this will iterate through all the migrations in reverse chronological order and run down migrations. If you want to roll back not all the migrations but down to specific migration, you can specify the step:

> php qt migration:migrate down --step=2

This is all for today, hope you find it useful, write comments if you have questions… Good luck.