Laravel-5 and Multitenancy database setup

Question

I'm starting to develop an SaaS application and I have created my database structure. I'm planning to create a middleware file which handles the database connection for that request. Within this middleware file I want to create a model which will always select only rows from any table that corresponds to the current connection cust_id (foreign key).

For example:

$Customers->where('cust_id', $cust_id)->first();

How can I do this without having to specify where('cust_id', $cust_id) in every select statement?

Answer 1

You can easily achieve that using Eloquent's global query scopes in your models. You can read more about them here: http://laravel.com/docs/5.1/eloquent#query-scopes

First, you need to define the Multitenant scope class, that will update all the queries that run and add the constraint on cust_id field:

class MultitenantScope implements ScopeInterface
{
  public function apply(Builder $builder, Model $model)
  {
    if (Auth::id()) {
      $builder->whereCustId(Auth::id()); 
    } else {
      $model = $builder->getModel();
      // apply a constraint that will never be true
      // so that no records are fetched for unauthorized users
      $builder->whereNull($model->getKeyName()); 
    }
 }

  public function remove(Builder $builder, Model $model)
  {
    $query = $builder->getQuery();
    $query->wheres = collect($query->wheres)->reject(function ($where) {
      return ($where['column'] == 'cust_id');
    })->values()->all();
  }  
}

Then you need a trait that you will add to the models that need to be filtered:

trait MultitenantTrait
{
  public static function bootMultitenantTrait()
  {
    static::addGlobalScope(new MultitenantScope());
  }

  public static function allTenants()
  {
    return (new static())->newQueryWithoutScope(new MultitenantScope());
  }
}

The last piece is adding the MultitenantTrait to your model:

class SomeModel extends Eloquent {
  use MultitenantTrait;
}

Now, every time you do any query using Eloquent's model methods, the cust_id constraint will be applied to the query and only models that belong to given cust_id will be available.

If for some reason you'll need to access all objects, you can use allTenants() method to run the query without the additional constraint:

$allRows = SomeModel::allTenants()->get();

Please keep in mind that I haven't tested that exact code, so let me know if you see any issues and I'll be more than happy to get that working for you :)