Laravel Project Multi-tenant, Multi-database and one domain

Question

Hello can you please help me to put project laravel Multi-tenant with Multi-database :

I want to create user database, for example in my system there are two user A and B. I have a master Database and two database user_a (for user A) and user_b (for user B). In master Database i have the all users information. Now what i want, when user A logged in system it access user_a database, and when user B logged in database connection should be user_b database.

Answer 1

1. First, In .env:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=master_db
DB_USERNAME=root
DB_PASSWORD=

DB_CONNECTION_A=mysql
DB_HOST_A=127.0.0.1
DB_PORT_A=3306
DB_DATABASE_A=a_db
DB_USERNAME_A=root
DB_PASSWORD_A=

DB_CONNECTION_B=mysql
DB_HOST_B=127.0.0.1
DB_PORT_B=3306
DB_DATABASE_B=b_db
DB_USERNAME_B=root
DB_PASSWORD_B=

2. In config/database.php:

'mysql' => [
    'driver' => 'mysql',
    'url' => env('DATABASE_URL'),
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '3306'),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'unix_socket' => env('DB_SOCKET', ''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'prefix_indexes' => true,
    'strict' => true,
    'engine' => null,
    'options' => extension_loaded('pdo_mysql') ? array_filter([
        PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
    ]) : [],
],

'mysql_a' => [
    'driver' => 'mysql',
    'url' => env('DATABASE_URL'),
    'host' => env('DB_HOST_A', '127.0.0.1'),
    'port' => env('DB_PORT_A', '3306'),
    'database' => env('DB_DATABASE_A', 'forge'),
    'username' => env('DB_USERNAME_A', 'forge'),
    'password' => env('DB_PASSWORD_A', ''),
    'unix_socket' => env('DB_SOCKET_A', ''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'prefix_indexes' => true,
    'strict' => true,
    'engine' => null,
    'options' => extension_loaded('pdo_mysql') ? array_filter([
        PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
    ]) : [],
],

'mysql_b' => [
    'driver' => 'mysql',
    'url' => env('DATABASE_URL'),
    'host' => env('DB_HOST_B', '127.0.0.1'),
    'port' => env('DB_PORT_B', '3306'),
    'database' => env('DB_DATABASE_B', 'forge'),
    'username' => env('DB_USERNAME_B', 'forge'),
    'password' => env('DB_PASSWORD_B', ''),
    'unix_socket' => env('DB_SOCKET_B', ''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'prefix_indexes' => true,
    'strict' => true,
    'engine' => null,
    'options' => extension_loaded('pdo_mysql') ? array_filter([
        PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
    ]) : [],
],

3. In migration file if users table exist in main database and books table (for example) in both database A and B:

Schema::connection('mysql')->create('users', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->string('database');
    $table->string('email')->unique();
    $table->timestamp('email_verified_at')->nullable();
    $table->string('password');
    $table->rememberToken();
    $table->timestamps();
});

Schema::connection('mysql_a')->create('books', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->timestamps();
});

Schema::connection('mysql_b')->create('books', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->timestamps();
});

4. Now you have three database connections, When you want to change connection on Model or DB you can do: For models:

$books = Book::on('mysql_a')->find(1);

For database queries:

DB::connection('mysql_b')->table('books')->get();

5. An example for selecting books according the user database type:

$user = auth()->user();

switch ($user->database) {
    case 'A':
        return Book::on('mysql_a')->all();
        break;
        
    case 'B':
        return Book::on('mysql_b')->all();
        break;
}

Note: don't forget to run php artisan config:cache when changing .env file or config/database.php