Is it possible to do migration on dynamic database connection with Laravel 5?

Question

I am trying to dynamically create database for different users. (every user will have their own database server, so don't ask why I am not using a single database for all users) To do that, I have a default database storing all the connection information. I will need to:

Create a new database and run all migration files on new user registration.
Run new migration files on all database recorded in this default database when there is update in schema.

Is there a way I can dynamically set the database connection of the migration file based on the information I have on the default database?

P.S. For "dynamically set the database connection", I am NOT meaning the normal setting as you do in controller or class. I expect something that would at least create migration table in the target database and be able to self-detect what migration file to run.

alepeino · Answer 1 · 2019-06-19T15:46:57.157

13

Yes there is. First you need to add the connection details to the configuration. Once you have a named connection configured, just call the migrate command on the Artisan facade, selecting the name of the connection ("new" in this example) as option:

use Illuminate\Support\Facades\Artisan;
//...

$new_connection = 'new';

config(["database.connections.$new_connection" => [
    // fill with dynamic data:
        "driver" => "mysql",
        "host" => "",
        "port" => "",
        "database" => "",
        "username" => "",
        "password" => "",
        "charset" => "utf8",
        "collation" => "utf8_unicode_ci",
        "prefix" => "",
        "strict" => true,
        "engine" => null
    ]]);

Artisan::call('migrate', ['--database' => $new_connection]);

edited Jun 19 '19 at 15:46

answered Oct 18 '16 at 14:00

alepeino

9,551
3
28
48

1

Don't have the time to test yet, but this answer seems most reliable. I will award the bounty first, and mark the answer as correct after I test. – cytsunny Oct 25 '16 at 02:17
Very interesting approach. I like it +1 – Andre F. Nov 04 '16 at 23:09

score 1 · Answer 2 · answered Oct 18 '16 at 18:44

First you need to create the database

 DB::getConnection()->statement('CREATE DATABASE :schema', array('schema' => $schemaName));

Then change the name of the database on the fly like this

$config = app(\Illuminate\Config\Repository::class);
$config->set('database.connections.mysql.database', UserRepotory::getCurrentDatabase());

You can include Config like this or trough laravel's service container.

And finally you call Artisan::call('migrate')

score 1 · Answer 3 · answered Oct 19 '16 at 10:38

Hi little help for you,

first of all add '%new_connection%' in database.php file to handle new connection for future use too.

To dynamically create connection, let say you have a route with variable $name for database name.

step 1: in routes.file I have created and call it on your desired route url in routes.php

function appendNewConnection($name){
$path = base_path('config' . DIRECTORY_SEPARATOR . 'database.php');
            $contents = file_get_contents($path);
            $updatedContents = str_replace('%new_connection%', $name . '\' => [
            \'driver\' => \'mysql\',
            \'host\' => \'127.0.0.1\',
            \'database\' => \'' . $name . '\',
            \'username\' => \'root\',
            \'password\' => \'\',
            \'charset\' => \'utf8\',
            \'collation\' => \'utf8_unicode_ci\',
            \'prefix\' => \'\',
            \'strict\' => false,
        ],
        \'%new_connection%', $contents);
            file_put_contents($path, $updatedContents);

}

Step 2:

//to generate migration add below line in top of routes.php

use Illuminate\Support\Facades\Artisan;

add this line in function created above 
Artisan::call('migrate', ['--database' => $name]);

This sounds good on paper, I've seen this type of implementation used before, but this is bad practice. Laravel considers closures in config files to be bad practice. It'll produce app stopping errors when you attempt to run `config:clear` or `config:cache` and the only way to fix them is to delete the config cache and never run it again https://github.com/laravel/framework/issues/9625 If you have a small application, it may not be worth it to you to cache the config. Be aware though that if you use this solution you will run into app stopping errors when cacheing config. — WhyAyala, Nov 20 '17 at 19:45

score 0 · Answer 4 · answered Oct 24 '16 at 09:03

Here I have some clue regarding how can you do this:

1. There will be a global database where you are maintaining all users login details, Right?

2. Add one extra field for database name.

3. when user logs in success full then store their database details in session variable.

Now,

4. Create a database file dynamic and give database name from that session variable as:

config(["database.connections.$new_connection" => [
    // fill with dynamic data:
        "driver" => "mysql",
        "host" => "",
        "port" => "",
        "database" => "",//Here you need to set value of session variable
        "username" => "",// credential will be the same for all
        "password" => "",// credential will be the same for all
        "charset" => "utf8",
        "collation" => "utf8_unicode_ci",
        "prefix" => "",
        "strict" => true,
        "engine" => null
    ]]);

Bingo you now are ready to go :D

Is it possible to do migration on dynamic database connection with Laravel 5?

4 Answers4