Mutli tenancy application in laravel 5?

Question

I am trying to hack up a multi tenancy multi database application using laravel. After going through a lot of articles on the web, I now understand that there are different approaches to the database architecture.

1. To have a single database and have tenant_id foreign key to separate the tenant records
2. To have multiple databases to separate each tenant's data

The project I am working on requires that every tenant has his own database for security purposes. Now I am trying to manually hack up a Laravel 5 installation to achieve this but I am not a pro and I am seeing some setbacks.

So I started with creating the migration for the main DB which will store all the tenants. I created this migration in a folder called main under the migrations folder. The schema looks like so:

Schema::create('tenants', function (Blueprint $table) {
    $table->increments('id');
    $table->string('name');
    $table->text('description');
    $table->string('logo');
    $table->string('username');
    $table->timestamps();
});

Then I went ahead and created the migrations files for the tenant databases and I placed them in the tenants folder inside the migrations folder. The schema looks like so:

Users Table:

Schema::create('users', function (Blueprint $table) {
    $table->increments('id');
    $table->timestamps();
});

Roles Table:

Schema::create('roles', function (Blueprint $table) {
    $table->increments('id');
    $table->sting('name');
    $table->timestamps();
});

And a pivot between the two:

Schema::create('role_user', function (Blueprint $table) {
    $table->increments('id');
    $table->integer('role_id')->unsigned();
    $table->foreign('role_id')->refernces('id')->on('roles');
    $table->integer('user_id')->unsigned();
    $table->foreign('user_id')->refernces('id')->on('users');
    $table->timestamps();
});

At this point I started with a seed files which would create multiple fake tenants and then for each tenant run the migration to create the tenant's database. The seed file looks like so:

public function run()
{
    $faker = Faker::create();

    // Fetching all tenants
    $tenants = App\Tenant::all();

    // Deleting their databases
    $tenants->each(function($tenant){
        DB::statement('DROP DATABASE IF EXISTS ' . $tenant->username);
        DB::statement('DROP USER IF EXISTS ' . $tenant->username);
    });

    // Truncating the tenants table itself
    DB::table('tenants')->truncate();

    for($i = 0; $i < 3; $i++){
        $company = $faker->company();
        $description = $faker->text();
        $logo = $faker->imageUrl(50, 50);
        $username = str_random(20);

        Tenant::create([
            'name'          => $company,
            'description'   => $description,
            'logo'          => $logo,
            'username'      => $username,
        ]);

        // Creating a new user by the username of the tenant
        // Also giving him privileges on the database by the same name
        DB::statement("CREATE USER " . $username . "@'localhost' IDENTIFIED BY 'admin12345';");
        DB::statement("GRANT ALL PRIVILEGES ON *.* TO " . $username . "@'localhost';");
        DB::statement('CREATE DATABASE ' . $username);

        // Todo: Save these database credentials somewhere
        // Todo: Run migration on the databases created
    }
}

At this point I am confused how to store the DB credentials for every users? I mean I am hard coding a password right now for every database but that will soon change and every user will have a unique password. So is storing the password in the main database a good idea? I mean I can encrypt and store it and then when I need to connect to the DB, I can take the username of the tenant and the decrypted password to connect to the DB. Is this a good approach?

Other than this I am also confused on how to connect to a DB dynamically and run the migrations for the tenant tables. I know I can call artisan commands to run migrations but how to connect to the DB first?

I am also confused as how to go about checking which user is logged in. I mean every tenant has it's own users. So when a user is logging in, I am also asking then their tenant username so that I know which database to connect to and then authenticate them to the users table in it.

But again I am confused that if I am even on the right track with this approach?

So my main problem right now is to connect to a different database dynamically. How do I go about doing that?

Answer 1

The Eloquent model has a method called setConnection that you can use to set a connection.

So you could have a database where you store settings for each client, and then retrieve these settings and connect the database.

$nameKey = 'NameOfConnection';

$dataSync = AtlasBases::find($id_client);

Config::set('database.connections.' . $nameKey, array(
        'driver'    => 'mysql',
        'host'      => $dataSync->site_db_server,
        'database'  => $dataSync->site_db_base,
        'username'  => $dataSync->site_db_user,
        'password'  => $dataSync->site_db_password,
            'charset'   => 'utf8',
        'collation' => 'utf8_general_ci',
        'prefix'    => '',
));
# And set the new connection to my models

$imobModel = new ImobImoveis;
$imobModel->setConnection($nameKey);

Have a look at the following links:

https://laracasts.com/discuss/channels/general-discussion/hitting-multiple-databases-dinamically-with-laravel?page=1

https://laracasts.com/discuss/channels/general-discussion/l5-change-default-database-connection-dynamically?page=1#reply-45910

And here is a demo repository that uses a similar solution: https://github.com/uxweb/laravel-multi-db