Laravel: starting migration to another database

Question

In my application, each user has his own database created during user registration. Connection and database data (database name, username, password) are stored in a table in the database by default.

try{ DB::transaction(function() { $website = new Website(); $website->user_id = Auth::get()->id; $website->save(); $database_name = 'website_'.$website->id; DB::statement(DB::raw('CREATE DATABASE ' . $database_name)); $websiteDatabase = new WebsiteDatabase(); $websiteDatabase->website_id = $website->id; $websiteDatabase->database_name = $database_name; $websiteDatabase->save(); }); } catch(\Exception $e) { echo $e->getMessage(); } 

Now I want to perform some migrations in a new user database after creating it.

Is it possible?

thanks

Answer 1

In your application /config/database.php you need to:

 <?php return array( 'default' => 'mysql', 'connections' => array( # Our primary database connection 'mysql' => array( 'driver' => 'mysql', 'host' => 'host1', 'database' => 'database1', 'username' => 'user1', 'password' => 'pass1' 'charset' => 'utf8', 'collation' => 'utf8_unicode_ci', 'prefix' => '', ), # Our secondary database connection 'mysql2' => array( 'driver' => 'mysql', 'host' => 'host2', 'database' => 'database2', 'username' => 'user2', 'password' => 'pass2' 'charset' => 'utf8', 'collation' => 'utf8_unicode_ci', 'prefix' => '', ), ), ); 

Now that you have prepared two database connections in your migration, you can do:

 Schema::connection('mysql2')->create('some_table', function($table) { $table->increments('id'): }); 

That should work. Further information: http://fideloper.com/laravel-multiple-database-connections

Answer 2

If you put the database configuration in the database.php file, this may help you:

 php artisan migrate --database=**otherDatabase** 

Answer 3

I think I finally understood this mess ... This solution does not need to be configured for each tenant database and should only be run once.

 class MigrationBlah extends Migration { public function up() { $dbs = DB::connection('tenants')->table('tenants')->get(); foreach ($dbs as $db) { Schema::table($db->database . '.bodegausuarios', function($table){ $table->foreign('usuario')->references('usuarioid')->on('authusuarios'); }); } } } 

Where I have a connection with the name "tenants" in my .php database, which contains the database name of all my tenants. I have a default connection with my tenant database. This database is responsible for processing the migration table.

Using the foreach statement, it goes through the tenant databases and starts the migration on each of them.

When connecting by default, you must configure a user who has access to all tenant databases for it to work.

Answer 4

I ran into the same problem and Joe's answer didn’t work in my case, since I have different database connections (so there are different hosts, ports, users and passages).

Therefore, migration must constantly perform many reconnections:

• Migration starts with the default database (in my case it is client_1)
• Selects material from the migrations and clients table
• Disable the default database
• Connect to client_2 database, start migration parts, disable client_2
• Connect to the default database again, save the "log" migration

And then loop it for each client.

  /** * Run the migrations. * * @return void */ public function up() { $defaultConnection = BackendConfig::getDatabaseConfigArray(); $clients = ClientController::returnDatabasesForArtisan(); foreach ($clients as $client) { BackendConfig::setDatabaseFromClient($client); Schema::create('newtable', function (Blueprint $table) { $table->increments('id')->unsigned(); $table->timestamps(); }); BackendConfig::setDatabaseFromArray($defaultConnection); } } 

And the class in which the magic is stored:

 class BackendConfig { public static function getDatabaseConfigArray($client_id = 1) { $connection = config('database.default'); return [ 'id' => $client_id, 'host' => config("database.connections.$connection.host"), 'port' => config("database.connections.$connection.port"), 'username' => config("database.connections.$connection.username"), 'password' => config("database.connections.$connection.password"), ]; } public static function setDatabaseFromArray($array) { self::setDatabase($array['id'], $array['host'], $array['port'], $array['username'], $array['password'], true); DB::disconnect(); } public static function setDatabaseFromClient(Client $client) { DB::disconnect(); self::setDatabase($client->id, $client->database->host, $client->database->port, $client->database->username, $client->database->password, true); } public static function setDatabase($client_id, $host, $port, $username, $password) { $connection = config('database.default'); $database_name = $connection . '_' . $client_id; config([ "database.connections.$connection.database" => $database_name, "database.connections.$connection.host" => $host, "database.connections.$connection.port" => $port, "database.connections.$connection.username" => $username, "database.connections.$connection.password" => $password, ]); } 

With this solution, I can perform the same migrations on each client, but the migration is just saved in client_1, my type is the main client.

However, pay attention to two DB::disconnect(); . This will aggravate the situation because migration logs are stored in another client database or one.

And by the way, ClientController does nothing special:

 public static function returnDatabasesForArtisan() { return Client::select('*')->with('database')->get(); }