What is a database migration in PHP and how to migrate data in Yii2

When we develop a web application usually there is the need for a database for storing the data, one of the firsts steps is creating a schema and at some later point in time, maybe we need to make changes. Changes could include adding a new table, or column, foreign key, etc. So not only we need to track changes of source code with a version control system also we need to keep records of changes on the database schema alterations.

This is very useful because if you are part of a team, so other members can take quickly the changes without reloading data dumping a DB or if a project is in production you can easily rollback if something goes wrong. If you web development should be familiar with that type of functionality that is included in moder web application frameworks such as yii2, CakePHP, Symfony have command-line tools that allow them to create and apply migrations.

How to a make a database migration in Yii2

Let's see an example in yii2, if we have a table with name product and we want to add an extra field, we will have to choose the command migrate/create and give a descriptive name to this migration

php yii migrate/create add_field_market_in_products

this will create a file under the folder named migrations, it will assign to file with random prefix and in the end the name of the migration add_field_market_in_products, in this file there two functions one with a name up and one with the name down

use yii\db\Schema;
use yii\db\Migration;
class m150608_200458_add_field_market_in_products extends Migration {

    public function up() {
         $this->addColumn('product', 'marketOpportinities, 'TEXT AFTER productDescription');
     }

     public function down() {
         echo "m150608_200458_add_field_market_in_products cannot be reverted.\n";
         return false;
     }
}

then in command line we will have to press

yii migrate

History of migrations are stored in the database in a table with name migration, it includes two columns the first is the name of migration, the other column has name apply_time and stores the time that the migration applied in unixtimestamp format.

Laravel migration

All modern ORMs and PHP framework support database migrations using a command line tool. If we have  a laravel development project you can use the artisan tool to generate migrations, a new migrated file could be done with the following

php artisan make:migration add_field_market_in_products

This is the file that we store our PHP coding that will create a new migration to the database schema. Usually, migration is a PHP class file that contains two public functions that are named up() and down() and this is exactly the same also to yii2 and to Laravel Framework.

In function up() we will place code that adds tables, columns in the database, in function down() we add code the drop tables or columns. More info about laravel documentation

Conclusion

So migration is an important concept for database versioning and every web developer if working alone or as part of a team should advantage of such functionality. Communication sync and development will be easier but also the deployments of code could faster and more secure and will reduce the probability of something go wrong.

A short note

Database migration in this post we refer to schema database migration because the same definition can apply to a different context like database migration as moving your database from one database system homogenous type Oracle to Amazon RDS for Oracle or heterogenous Oracle to SQL Server.

If you need to php developer with backend skills and ETL processes for moving data between heterogeneous systems to contact us.