Laravel - Multi-tenancy - One database multiple tables

Question

I want to create a multi tenancy application using Laravel. I am using the one database, multiple tenant tables database architecture.

I want to create a new set of tenant related tables dynamically, whenever someone registers a new tenant account.

Please consider the following:

design

tenants table holds all the client registrations. Each tenant will have dedicated tables only for them. In this example every tenant has their own dedicated customer table.

Ex:

tenant01's tables will have tenant01 prefix. (tenant01.customer - only tenant01's customers)

tenant02's tables will have tenant02 prefix. (tenant02.customer - only tenant02's customers)

I don't want to use multiple databases as they are costly and I don't want to use one table for all the tenants, as there will be lots of customers/products etc in the system.

I'm planning to identify the tenant at the logging process and set the tenant name(or a code/Id) in a session. ex: tenant440

After that, in all customer related eloquent model classes, I could dynamically append that prefix (ex: tenant440) into the table name like this:

<?php

class Customer extends Eloquent {

    protected $tenant_name = //get name/code from the session. ex: tenant440

    //table name will become tenant440.'customers'
    protected $table = $tenant_name.'customers';


?>

Is this a correct way to achieve this? What is the simplest to do this? Do you know any kind of resources/packages/libraries for this?

Thanks in advance!

Razor · Accepted Answer · 2014-08-07 04:45:00Z

2

You can set tenant name as a prefix in your database file:

Config::set('database.connections.mysql.prefix',$tenantName); // assuming 'mysql' is the default database connection name

I recommend to put it inside a filter

// routes.php
Route::group(array('before'=>'setTablePrefix'), function($noob)
{
    Route::resource('customers', 'CustomersController');
    Route::controller('sales', 'SalesController');
});
Route::get('/login',array('as' => 'login', 'uses' => 'UserController@getLogin'));


// filters.php
Route::filter('setTablePrefix', function($route, $request) 
{
    if (!Session::has('prefixTable'))
        App::abort(404);
    Config::set('database.connections.mysql.prefix',Session::get('prefixTable'));
});

To get data from all tables, you probably need two queries (or one if you use Session)

$tenants = DB::table('tenants')->lists('name'); // you can set it in Session
if($tenants){
    $allCustomers = DB::table($tenants[0].'.customers');
    for ($i = 1; $i < count($tenants); $i++) { 
        $allCustomers->unionall(DB::table($tenants[$i].'.customers'));
    }
    var_dump($allCustomers->get()); 
}

edited Aug 7, 2014 at 4:45

answered Aug 5, 2014 at 13:47

Razor

9,8633 gold badges39 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

stackflow Over a year ago

Hi Thanks a lot for the tip! May I ask you, What if there is a scenario that admin needs to view all the customers(or sales) in the entire database? How can I read from all the tables? For an example what if admin needs to know the entire sales total? Is it possible to retrieve?

Collectives™ on Stack Overflow

Laravel - Multi-tenancy - One database multiple tables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related