Laravel：在另一个数据库上运行迁移

Laravel: Run migrations on another database

提问by Pooya Saberian

In my app every user has its own database that created when user registered. Connection and database data (database name, username, password) are saved in a table in 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?

是否可以？

thanks

谢谢

回答by pietrovismara

In your app/config/database.php you have to:

在你的 app/config/database.php 你必须：

<?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

这应该有效。更多信息：http: //fideloper.com/laravel-multiple-database-connections

回答by Fernando

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

如果您将数据库配置放在database.php文件上，这可以帮助您：

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

回答by Muhammad Dyas Yaskur

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

我有同样的问题，我的解决方案是先使用Config::set然后运行Artisan::call("migrate"). 所以基于你的代码：

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.

配置仅在您的会话中更改，然后稍后重置为您的当前设置。

回答by andi79h

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 migrationsand clients
• Disconnect default database
• Connect to database of client_2, run migration parts, disconnect client_2
• Connect to default database again, store migration "log"

• 迁移从默认数据库开始（在我的情况下是client_1）
• 从表中获取东西migrations并clients
• 断开默认数据库
• 连接到client_2的数据库，运行迁移部分，断开client_2
• 再次连接到默认数据库，存储迁移“日志”

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.

使用此解决方案，我可以在每个客户端上运行完全相同的迁移，但迁移仅存储在我的主客户端 client_1 中。

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.

但是，请注意两者DB::disconnect();。如果没有这些，它会搞砸情况，因为然后迁移日志存储在另一个客户端的数据库等中。

Ah and by the way, ClientControllerdoes nothing special:

啊，顺便说一句，ClientController没有什么特别的：

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

回答by Tebe

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

记住哪个迁移对应哪个数据库很乏味。

For Laravel 5.5 I used this approach:

对于 Laravel 5.5，我使用了这种方法：

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.

请注意，迁移表存储在您的默认数据库中。

回答by pankaj kumar

Best solution is you can call this method on AppServiceProvide

最好的解决方案是你可以调用这个方法 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 Developmentand Production. so when the project is development mode then it will look on local Server else Live server. So you can set dynamic-DBconcept here.

它是此类问题的最佳解决方案。我在我的项目中使用它。就我而言，我有两个环境Development和Production。所以当项目处于开发模式时，它将在本地服务器上查看其他实时服务器。所以你可以在这里设置动态数据库概念。

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

你必须做出一个功能，那么你必须把这种内部启动（）函数上App\Providers\AppServiceProvide.php

    public function boot()
    {
       DBConnection();

    }

I created HelperFile for this. so my code in helper.php

我为此创建了帮助文件。所以我在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;
}

回答by JoeGalind

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.

我的 database.php 上有一个名为“租户”的连接，其中包含我所有租户的数据库名称。我也将默认连接设置为我的租户数据库。该数据库负责处理迁移表。

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

使用 foreach 语句，它遍历租户数据库并在每个租户数据库上运行迁移。

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

在默认连接上，您应该配置一个可以访问所有租户数据库的用户，以使其正常工作。

回答by A.Soliman

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

如果您的意思是使用不同的数据库连接，它存在于文档中：

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