When we development a web application usually there is the need for a database for storing the data, one of the firsts steps is create a schema and in some later time point, maybe we need to make changes. Changes could include adding a new table, or column, a 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 dumbping a db or if a project is in production you can easily rollback if something goes wrong. Many php frameworks such as yii2, cakephp, symfony have command line tools that allows to create and apply migrations.

Database Migration 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 an file under 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 name up and one with 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 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 coud 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 a migration is 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 plase code that add 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. Communiation 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 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 help with Azure database migration service or database migration AWS contact us.