Mutli tenancy application in laravel 5?

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?

via Chebli Mohamed