Laravel: Run migrations on another database

Question

In my app, every user has its own database that created when the user registered. Connection and database data (database name, username, password) are saved in a table in the default database.

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 run some migrations on new user's database after its creation.

Is it possible?

Cuisse

Add laravel environments for your databases and invoke the artisan tool in these environments? Why are you creating a database for each of your customers? It sounds like an usual decision for a Laravel app. – Nomarch

Our Web app is something like wix.com. every user can create websites for its own and we want every website has its own separate database. In this code I created separate database for every user and save its data in default database, now I want run some migrations on this new database for creating new database schema. – Cuisse

Answer 1

If you mean using different database connection, it exists in the docs:

Schema::connection('foo')->create('users', function (Blueprint $table) {
    $table->bigIncrements('id');
});

Prearrange

Answer 2

In your app/config/database.php you have 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 prepared two database connections in your migration you can do:

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

This should work. More infos on: http://fideloper.com/laravel-multiple-database-connections

Blanks

i don't have secondary database connection in my config file. database connections details are saved in primary database! – Cuisse

I think you should clarify your question and maybe show some code to make it understandable... – Blanks

I believe this will create the migrations table itself in the default database however if running migrations from scratch? (That was my experience just now on Laravel 10). Fernando's answer doesn't have this issue. – Geffner

Answer 3

If you place database config on the database.php file, this can help you:

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

Insanity

I have set two migration on Laravel Forge Deploy Script as php artisan migrate and php artisan migrate --database=arc_mysql – Mor

with 4 asterisks? you cannot be serious – Semang

Answer 4

I have the same problem, my solution is to change the database using Config::set first then run Artisan::call("migrate"). so based on your code:

DB::statement(DB::raw('CREATE DATABASE ' . $database_name));
Config::set('database.connections.mysql.database', $database_name);
Artisan::call("migrate --database=mysql");

the config only changed on your session then reset later as your current setting.

Annalee

Answer 5

This is tedious to remember which migration corresponds to which database.

For Laravel 5.5 I used this approach:

public function up()
{

 // this line is important
  Illuminate\Support\Facades\DB::setDefaultConnection('anotherDatabaseConnection');


   Schema::table('product',
          function (Blueprint $table)
     {
        $table->string('public_id', 85)->nullable()->after('ProductID');
     });

  // this line is important, Probably you need to set this to 'mysql'
   Illuminate\Support\Facades\DB::setDefaultConnection('nameOfYourDefaultDatabaseConnection');
}

All migrations can be run automatically without taking care of specifying database manually when running them.

Please note that migrations table is stored inside your default database.

Congest

sure, though native is not aware of it – Congest

Answer 6

I actually faced the same problem and the answer of Joe did not work in my case, as I have different database connections (so different host, port, user and pass).

Therefore the migration must do a lot of reconnects all the time:

• Migration starts with default database (in my case that is client_1)
• Fetches stuff from table migrations and clients
• Disconnect default database
• Connect to database of client_2, run migration parts, disconnect client_2
• Connect to default database again, store migration "log"

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 where 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 run the exact same migrations on every client, yet the migration is just stored in client_1, my sort of master client.

However, pay attention to the two DB::disconnect();. It will screw up the situation without those as then migrations logs are stored in another client's database or such.

Ah and by the way, ClientController does nothing special:

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

Singles

Answer 7

I think I finally figured out this mess... This solution doesn't need a configuration for each tenant's database and has to be run only 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 named "tenants" on my database.php, which contains the database name of all of my tenants. I have the default connection set to my tenants database as well. That database is the one responsible for taking care of the migrations table.

With the foreach statement, it goes through the tenant databases and runs the migration on each one.

On your default connection, you should configure a user that has access to all tenant's databases for it to work.

Batch

By using this approach you can't migrate for a single tenant. – Boat

Answer 8

Best solution is you can call this method on AppServiceProvide

it is the best solution for this type of problem. I am using this in my project. In my case, I have two environments Development and Production. so when the project is development mode then it will look on local Server else Live server. So you can set dynamic-DB concept here.

you have to make a function then you have to call this inside of boot() Function on App\Providers\AppServiceProvide.php

    public function boot()
    {
       DBConnection();

    }

I created Helper File for this. so my code in helper.php

 function DBConnection()
 {
  if( env('APP_ENV') ==  'local' )
   {   $databse_name = "test_me";
       $host = '127.0.0.1';
       $user="root";
      $password="";
   }
    else
  {
    $databse_name = 'tic_tac';
    $host = 'localhost';
    $user="";
    $password="";
  }
    $state = true;
   try {
      Config::set('database.connections.myConnection', array(
        'driver'    => 'mysql',
        'host'      => $host,
        'database'  => $databse_name,
        'username'  => $user,
        'password'  => $password,
        'charset'   => 'utf8',
        'collation' => 'utf8_unicode_ci',
        'prefix'    => '',
        'strict' => false,
     ));
    /* \DB::setDefaultConnection('myConnection');
      $state = \DB::connection()->getPdo();*/

     Config::set('database.connections.myConnection.database', $databse_name);
     \DB::setDefaultConnection('myConnection');
      \DB::reconnect('myConnection');

   } catch( \Exception $e) {
      $state = false;
   }
 return $state;
}

Fugate

this will work not only migration but also everywhere. – Fugate