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 command “PHP 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 migrate” command under your project’s root directory. The following options can be used to resolve this problem:
- “–database” option is used to run the migration to specify connections. In this process, one of the connections available through “/config/database.php” file 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.php” file and call them one by one manually to run migration.
- “–path” option helps to define path-to-migration against specific connections mentioned in “config/database.php” file. 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
```php
'default' => env( 'DB_DRIVER', 'mysql' ),
'connections' => [
'mysql' => [
'driver' => 'mysql',
'host' => env( 'DB_HOST', 'localhost' ),
'database' => env( 'DB_DATABASE', 'forge' ),
'username' => env( 'DB_USERNAME', 'forge' ),
'password' => env( 'DB_PASSWORD', '' ),
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => 'tbl_',
'strict' => false,
'migration_type' => 'default'
],
'tenant_mysql' => [
'driver' => 'mysql',
'host' => env( 'DB_HOST', 'localhost' ),
'database' => "database_prefix_",
'username' => env( 'DB_USERNAME', 'forge' ),
'password' => env( 'DB_PASSWORD', '' ),
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => 'tbl_',
'strict' => false,
'migration_type' => 'tenant'
],
]
In the above mentioned code, the second connection “tenant_mysql” involves information about all the available tenant databases with different database names. Additionally, “migration_type” attribute 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
``` php
use SymfonyComponentConsoleInputInputOption;
trait BatchMigrationTrait {
/**
* @var string
*/
protected $migrationType = 'default';
/**
* Extends the default options by type-option and db-option
*
* @return array
*/
protected function getOptions() {
$optExtend = array(
array('type', null, InputOption::VALUE_OPTIONAL, 'The migration type to be executed.', 'default'),
array('db', null, InputOption::VALUE_OPTIONAL, 'The migration DataBase Name to be executed.', 'default'),
);
return array_merge(parent::getOptions(), $optExtend);
}
/**
* Filters the connections and only returns the ones that match the migration type
*
* @param array $connection The database connections
*
* @return bool Returns TRUE on a match, else FALSE
*/
protected function filterConnections($connection) {
switch ($this->migrationType) {
case 'default':
return ( empty($connection['migration_type']) || $connection['migration_type'] == 'default' );
break;
default:
return (!empty($connection['migration_type']) && $connection['migration_type'] == $this->migrationType ? true : false );
break;
}
}
/**
* Returns the default DB connection
*
* @return array
*/
protected function getDefaultConnection() {
$defaultConnection = app('db')->getDefaultConnection();
$connection = config()->get('database.connections.' . $defaultConnection);
return ( empty($connection) ? array() : array($defaultConnection => $connection) );
}
/**
* Returns the default Tenant DB connection
*
* @return array
*/
protected function getTenantConnection() {
$defaultTenantConnectionName = "tenant_mysql";
$connection = config()->get('database.connections.' . $defaultTenantConnectionName);
return ( empty($connection) ? array() : array($defaultTenantConnectionName => $connection) );
}
/**
* Retrieves database connections by type
*
* @param null|string $filter When specified (--database option), only this connection will be checked
*
* @return array An array containing the matching connections
*/
protected function getConnectionsByType($filter = null) {
config()->set('database.connections.' . $filter . '.database', $this->option('db'));
$connections = array();
if ($this->migrationType == "default" && empty($filter)) {
return $this->getDefaultConnection();
} elseif (!empty($filter)) {
$connections = config()->get('database.connections.' . $filter);
if (!empty($connections)) {
$connections = array($filter => $connections);
}
if (empty($connections)) {
$connection = $this->getTenantConnection();
if (!is_null($this->option('db')))
//if database name not null override the database for tenant connection.
// db option could be *: for all tenant, tenant_id:for specific tenant database
$connection['tenant_mysql']['database'] = $this->option('db');
$connections = array($filter => $connection['tenant_mysql']);
}
} else {
//get all connection from database and prepare `$connections` arrays by preparing the database with tenant_mysql database connection
//Otherwise
//$connections = config()->get('database.connections');
}
if (!empty($connections)) {
$connections = array_filter($connections, array($this, 'filterConnections'));
}
return (array) $connections;
}
/**
* Retrieves and sets the migration type
*/
protected function setMigrationType() {
$this->migrationType = $this->input->getOption('type');
}
/**
* Run a batch migration on the specified connections
*
* @param array $connections
*/
protected function runMigrationsOnConnections($connections) {
foreach ($connections as $name => $connection) {
$this->input->setOption('database', $name);
if (isset($this->migrator)) {
$this->migrator->setMigrationType(array_get($connection, 'migration_type', 'default'));
}
parent::fire();
}
}
/**
* Default command override
*/
public function fire() {
$this->setMigrationType();
$connections = $this->getConnectionsByType($this->input->getOption('database'));
if (empty($connections)) {
$this->info("specified migration type not available for any connection");
} else {
$this->runMigrationsOnConnections($connections);
}
}
}
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:
```php
/**
* Class Migrator
* The custom Migrator filters migrations of a specific type
*/
class Migrator extends IlluminateDatabaseMigrationsMigrator {
/**
* @var string
*/
Protected $migrationType = 'default';
/**
* Sets the migration type filter
*
* @param string $type
*/
public function setMigrationType( $type ) {
$this->migrationType = $type;
}
/**
* Returns the migration type filter
*
* @return string
*/
public function getMigrationType() {
return $this->migrationType;
}
/**
* Resolves the migration and filters those that don't match the migration type
*
* @param string $migration
*
* @return bool Returns TRUE on a match, else FALSE
*/
protected function filterMigrations( $migration ) {
$instance = $this->resolve( $migration );
if ( empty( $instance->type ) ) {
$instance->type = 'default';
}
if ( ! is_array( $instance->type ) && $this->migrationType != $instance->type ) {
return false;
} else if ( is_array( $instance->type ) && in_array( $this->migrationType, $instance->type ) ) {
return true;
} else {
return true;
}
}
/**
* Gets a filtered list of migrations and runs them
*
* @param array $migrations
* @param bool $pretend
*/
public function runMigrationList( $migrations, $pretend = false ) {
$this->note( "Running " . ( $this->migrationType == "default" ? "default" : "custom" ) . " migrations for DB " . $this->connection );
$migrations = array_filter( $migrations, array( $this, "filterMigrations" ) );
parent::runMigrationList( $migrations, $pretend );
}
}
You can also extend all available migration commands by inserting the code given below for the following commands respectively:
Migration Command
```php
use BatchMigrationTrait;
use SymfonyComponentConsoleInputInputOption;
class MigrateCommand extends IlluminateDatabaseConsoleMigrationsMigrateCommand {
use BatchMigrationTrait;
}
Refresh Command
``` php
use BatchMigrationTrait;
class RefreshCommand extends IlluminateDatabaseConsoleMigrationsRefreshCommand {
use BatchMigrationTrait;
public function call( $command, array $arguments = array() ) {
if ( $command === 'migrate' || $command === 'migrate:reset' ) {
$arguments['--type'] = $this->input->getOption( 'type' );
}
return parent::call( $command, $arguments );
}
}
Reset Command
```php
use BatchMigrationTrait;
class ResetCommand extends IlluminateDatabaseConsoleMigrationsResetCommand {
use BatchMigrationTrait;
}
Rollback Command
```php
use BatchMigrationTrait;
class RollbackCommand extends IlluminateDatabaseConsoleMigrationsRollbackCommand {
use BatchMigrationTrait;
}
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:
```php
use TenantMigrationsMigrateCommand;
use TenantMigrationsRefreshCommand;
use TenantMigrationsResetCommand;
use TenantMigrationsRollbackCommand;
use TenantMigrationsMigrator;
use IlluminateDatabaseMigrationServiceProvider as ServiceProvider;
class TenancyServiceProvider extends ServiceProvider {
/**
* Register the migrator service.
*
* @return void
*/
Protected function registerMigrator() {
// The migrator is responsible for actually running and rollback the migration
// files in the application. We'll pass in our database connection resolver
// so the migrator can resolve any of these connections when it needs to.
$this->app->singleton( 'migrator', function ( $app ) {
$repository = $app['migration.repository'];
return new Migrator( $repository, $app['db'], $app['files'] );
} );
}
/**
* Register the "migrate" migration command.
*
* @return void
*/
protected function registerMigrateCommand() {
$this->app->singleton( 'command.migrate', function ( $app ) {
$packagePath = $app['path.base'] . '/vendor';
return new MigrateCommand( $app['migrator'], $packagePath );
} );
}
/**
* Register the "rollback" migration command.
*
* @return void
*/
protected function registerRollbackCommand() {
$this->app->singleton( 'command.migrate.rollback', function ( $app ) {
return new RollbackCommand( $app['migrator'] );
} );
}
/**
* Register the "reset" migration command.
*
* @return void
*/
protected function registerResetCommand() {
$this->app->singleton( 'command.migrate.reset', function ( $app ) {
return new ResetCommand( $app['migrator'] );
} );
}
/**
* Register the "refresh" migration command.
*
* @return void
*/
protected function registerRefreshCommand() {
$this->app->singleton( 'command.migrate.refresh', function () {
return new RefreshCommand;
} );
}
}
The code has been implemented and is ready to perform now. In order to run the migration, you need to create “users table” class to proceed:
``` php
use IlluminateDatabaseSchemaBlueprint;
use IlluminateDatabaseMigrationsMigration;
class CreateUsersTable extends Migration
{
/**
* Migration type to syncronized with multiple/single database connection
*/
public $type = ["default","tenant"];
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('users', function (Blueprint $table) {
$table->increments('id');
$table->string('name');
$table->string('email')->unique();
$table->string('password', 60);
$table->rememberToken();
$table->timestamps();
});
}
You can reverse this functionality by using the following code:
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::drop('users');
}
}
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.
0 Comments