Laravel 5 Multi-Tenancy App with separate databases - users have access to multiple installations

Question

Over the past couple of years I have developed a very customised PHP/MySQL application that is used for a number of clients. I have been creating a new database and new installation for each client up to this point.

The first obvious problem here is keeping multiple installations up to date with any code changes; a second problem is that each installation has a large amount of users; and for most clients; some of these users are the same - and they have to have a number of seperate user accounts and urls to remember.

I am moving the application over to Laravel 5 at the moment and looking into the best implementation for multi-tenancy; so looking for a little advice on the best implementation. I've used Laravel before but am by no means an expert.

This is what I am thinking as far as setup.

1 Master Database that holds tables for:

1. All User account information
2. Access Control Table - which installations the users can access; what their user level on that installation is.
3. Configuration table for each installation - database connection info, basic configuration etc.

Then a seperate database for each installation that contains all the information that is needed for, and submitted to, that installation.

The ideal setup is that a user can go to a subdomain i.e installationname.appname.com; sign in with their master login details and automatically go to the required installation; OR go to appname.com, sign in and then select which installation to connect to.

My questions are:

1. Is this the best arrangement to achieve what I am looking for.
2. What's the best method for storing which installation the user is looking at (session variable)
3. Can I define a model between 2 databases - perhaps define one connection as master connection, then dynamically define another connection using the database connection information in the master database to connect to the correct installation. - the system will often need to check the user info for access level etc.

I'm sure there's a lot of issues that I have not thought of; but if anyone has any links or guidance that may help that would be great. First time asking a question on SO but have found a huge amount of research help here in the past so thanks to the community!

---

UPDATE - So I think I have a way to make this work now; using seperate databases as above; set

protected $connection = 'tenant_connection'

in the models relating to tenant-specific database content.

Then somewhere in a header file set the tenant_connection that is wanted based on a session variable which has been set on login/by subdomain.

$tenant = Installation::where('installation', '=', $session['installation'])->first();
Config::set('database.connections.tenant_connection', array('driver' => 'mysql', 'host' => $tenant->db_hostname, 'username' => $tenant->db_username)... etc.

Assuming relationships will work across connections; I don't see why this would not work; just need to work out best place to set the tenant connection.

Vindictive

Just curious, were you able to get this fully functional? If so, could you post your solution as an answer? I'm facing the same issue – Correa

I would love the same...I am looking exactly what you did for you... – Jollanta

I'll try and post what I did now as an answer though it may make little sense! – Vindictive

Answer 1

Ok so what I ended up doing was having all user info, names of installations and mappings of what users can access what installations in one database, and all tenant info in seperate databases.

I then had two connections, mysql and mysql_tenant; where mysql_tenant database is not pre-set but dynamic.

The User, Installations and mappings model use the mysql connection, all others use mysql_tenant

Created a code for each installation, and used this as the name of the tenant database; storing this code in the session.

Used a middleware MultiTenant, to control the switching between installations using these key lines:

$tenant_id = session()->get('tenant');

\Config::set('database.connections.mysql_tenant.database', $dbname);
\DB::setDefaultConnection('mysql_tenant');

There's a lot more to it for building the method to switch etc, but this is the gist.

Vindictive

can you post an in depth answer as I have the same situation of multiple entries in relation to geographical data! – Accoucheur

Answer 2

It is difficult to answer most of your question - as it is specific to your application and opinion based.

But the one bit I can answer is different models can have different database connections. So your user model uses the normal default connection - but your other models can use another connection:

class Example extends Model {

    protected $connection= 'second_db_connection';

}

Then in your DB connection file - you would have something like this:

return array(
    'connections' => array(
        'mysql' => array(
            'driver'    => 'mysql',
            'host'      => 'localhost',
            'database'  => 'database1',
            'username'  => 'user1',
            'password'  => 'pass1'
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
        ),

        'second_db_connection' => array(
            'driver'    => 'mysql',
            'host'      => 'localhost',
            'database'  => 'database2',
            'username'  => 'user2',
            'password'  => 'pass2'
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
        ),
    ),

Tailored

Thanks - I think the question here is that either the second database connection name would need to be defined dynamically in the model; with all possible connections listed in the DB connection file; or the details of the second database connection would need to be dynamically defined from the master database - so not sure which of these would work best/be possible. – Vindictive

Answer 3

Laravel 5 is advanced enough that you should be able to have simply one installation along with a strategic database, with well defined relations and keys. There is rarely ever a need for multiple databases.

If you can be more specific about your requirements I can provide a more specific answer.

Croupier

Thanks for your answer; the reason for multiple databases is twofold, firstly for speed of searches - there will be up to 6,000 entries per table in some installations. Secondly, the application is for use in registering personnel for event work; then checking them in on site - sometimes there is no internet connection on site so I need to use a local server setup, in this instance I would just want to take the specific database I need offline and import onto the local server; then re-upload online afterwards. Having one database would cause issues here. – Vindictive

In my case customers require their data to be inside their own country, and completely separate from other customers. This is due to legal requirements. So there is definitely a use case for separating databases. – Verde