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?

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. — Niels B., Sep 20 '14 at 19:11
Our Web app is something like http://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. — Pooya Saberian, Sep 21 '14 at 17:53

score 59 · Answer 1 · edited Apr 23 '19 at 13:48

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

i don't have secondary database connection in my config file. database connections details are saved in primary database! — Pooya Saberian, Sep 21 '14 at 15:28
I think you should clarify your question and maybe show some code to make it understandable... — pietrovismara, Sep 21 '14 at 16:18

score 58 · Answer 2 · edited Sep 26 '14 at 13:48

58

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

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

edited Sep 26 '14 at 13:48

Yoel

9,144
7
42
57

answered Sep 26 '14 at 13:00

Fernando

681
1
4
2

I have set two migration on Laravel Forge Deploy Script as `php artisan migrate` and `php artisan migrate --database=arc_mysql` – Anon30 Jun 05 '20 at 05:09
with 4 asterisks? you cannot be serious – Toskan Nov 12 '21 at 18:00

score 14 · Accepted Answer · answered Feb 24 '20 at 10:55

14

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

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

answered Feb 24 '20 at 10:55

A.Soliman

315
3
13

score 6 · Answer 4 · answered Jan 15 '20 at 03:16

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.

score 2 · Answer 5 · answered Jun 24 '18 at 06:55

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.

sure, though native is not aware of it – Tebe Jan 14 '19 at 10:25 — Tebe, Jan 14 '19 at 10:25

andi79h · Answer 6 · 2018-03-26T17:38:26.107

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();
}

score 0 · Answer 7 · answered Jul 28 '16 at 05:03

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.

By using this approach you can't migrate for a single tenant. — Renan Coelho, Feb 07 '18 at 09:56

score -1 · Answer 8 · answered Nov 19 '19 at 11:40

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;
}

this will work not only migration but also everywhere. – pankaj Nov 19 '19 at 11:41 — pankaj, Nov 19 '19 at 11:41

Laravel: Run migrations on another database

8 Answers8

Linked