R&D

Multi-Tenancy System Migration With Separate Databases

Migrations are like version control for your database, as they allow you to easily modify and share your application’s database schema. In this article, we will focus on a few techniques used to deal with laravel multi-tenancy migration that has separate databases (master and child databases).

Let’s say the existing system’s migration mechanism is to handle all database schema versions using the console commandPHP artisan migrate“. This migration mechanism creates multiple versions for a single database by default, which is not appropriate when you are dealing with multi-tenancy that has separate database applications. You may need to handle migration for all the tenants/customers available in your system. For this, you may need a master database with separate migration and schema. Therefore, you need to modify the default migration mechanism by overriding the migration console commands.

Solution

Laravel framework is shipped with a couple of useful commands that will help utilise such a mechanism. Run “PHP artisan help migratecommand under your project’s root directory. The following options can be used to resolve this problem:

  1. –database” option is used to run the migration to specify connections. In this process, one of the connections available through “/config/database.phpfile will have the same or different credentials of your database host with tenant database. But the main problem with this method is that you have to mention all of the tenant connection information in “config/database.phpfile and call them one by one manually to run migration.
  2. –path” option helps to define path-to-migration against specific connections mentioned in “config/database.phpfile. This will duplicate migration and custom files on tenant requirements. This is not the most highly recommended solution.
    .

You can choose either of these options based on your enterprise level application, non- enterprise level application or SaaS based application. Adopting one of these mechanisms will make managing tenant migration schema easier, with all the available connections. Now, utilise application migration structure to categorize migration for the master database and for all available tenants such as:

Config/database.php

In the above mentioned code, the second connection “tenant_mysql” involves information about all the available tenant databases with different database names. Additionally, “migration_typeattribute enables specific migration, according to specific connections. Other than this, you can also utilise the “TenantMigrationTrait” to override the default migration console command as follows:

TenantMigrationTrait

Migrator

The next step is to filter all migration files according to their migration types. For this, you can use Migrator; a library used to handle migrations in Laravel:

You can also extend all available migration commands by inserting the code given below for the following commands respectively:

Migration Command
Refresh Command
Reset Command
Rollback Command
Now, register all these overridden commands using actual migration commands from the service provider. After that, place the given service provider in “config/app.php” file, using provider array as follows:
The code has been implemented and is ready to perform now. In order to run the migration, you need to create “users tableclass to proceed:
You can reverse this functionality by using the following code:

Conclusion

The above solution is implemented to provide a better mechanism for handling migration for all tenant databases. It enables the application to use multi-tenancy, with separate databases; whether the tenant information is stored in the database or otherwise defined in it’s configuration file.