CODEWITHSUNDEEP
laraveleloquentsaasmultiple-tablesmulti-tenant
stackflow
stackflow

Reputation: 2122

Laravel - Multi-tenancy - One database multiple tables

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!

Upvotes: 0

Views: 3449

Answers (1)

Razor
Razor

Reputation: 9835

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()); 
}

Upvotes: 2

Related Questions