The existing system used migration mechanism to handle all of the database schema version handling using console command php artisan migrate. By default this migration mechanism allow version for single database. Which is not appropriate when you are dealing with multi-tenancy with separate database application, you might need to handle migration for all available tenants/customers available in your system, and you might also need a master database with separate migration and schema. Thats why, we need to modify default migration mechanism by overriding the migration console commands.
Laravel ship by default with couple of available useful commands which might help to utilize such mechanism, Following of the option can use for such problem:
By typing the command php artisan help migrate under your project root directory.
- One of them option is
--databaseoption which is indicates to one of the connection available through/config/database.phpwith might be same or different credential to your database host with tenant database. But the main problem with this method is, you have to mentioned all of the tenant connection information inconfig/database.phpand you have to call one by one to run migration to individual. And that would run all of the migration to specify connection. Second option is--pathoption which helps to define path/to/migration which we want to run against specific connection mentioned inconfig/database.phpwhich of course lead to duplication of migration files, custom file separation base on tenant requirement. Which is also might not be suitable solution we up to. - So to resolve such requirement, we can approach such mechanism that helps us easily manage tenant migration schema with all available connections. To achieve such requirement, we can utilize application migration structure to categorize migration for master database and for all available tenants.
'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'
],
]Second connection tenant_mysql is the connection information about all available tenant databases with different database name. And migration_type attribute would help you to call specific migration according to specific connection.
On the other hand we would also override/extend the default migration console command.
use Symfony\Component\Console\Input\InputOption;
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);
}
}
}This file actually responsible to filter all migration files according to migration type.
/**
* Class Migrator
* The custom Migrator filters migrations of a specific type
*/
class Migrator extends \Illuminate\Database\Migrations\Migrator {
/**
* @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 );
}
}Extend all available migration commands:
use BatchMigrationTrait;
use Symfony\Component\Console\Input\InputOption;
class MigrateCommand extends \Illuminate\Database\Console\Migrations\MigrateCommand {
use BatchMigrationTrait;
}use BatchMigrationTrait;
class RefreshCommand extends \Illuminate\Database\Console\Migrations\RefreshCommand {
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 );
}
}use BatchMigrationTrait;
class ResetCommand extends \Illuminate\Database\Console\Migrations\ResetCommand {
use BatchMigrationTrait;
} use BatchMigrationTrait;
class RollbackCommand extends \Illuminate\Database\Console\Migrations\RollbackCommand {
use BatchMigrationTrait;
} And finally register all of the override commands using override actual migration commands service provider, and put this service provider to config/app.php inside provider array.
use Tenant\Migrations\MigrateCommand;
use Tenant\Migrations\RefreshCommand;
use Tenant\Migrations\ResetCommand;
use Tenant\Migrations\RollbackCommand;
use Tenant\Migrations\Migrator;
use Illuminate\Database\MigrationServiceProvider 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;
} );
}
}use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
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();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::drop('users');
}
}To provide better mechanism to handle migration for all tenant databases, the above solution was implemented that enabled any application that uses multi-tenancy with separate database weather tenant information stored in database or defined in database configuration file to manage all of the migration out of the box.