Simple multi tenant with Laravel


While developing a small administrative tool to help the company make data changes to 800 different databases, I had little time to come up with a liable solution on how to make master/tenant connections with Laravel. When you Google multi tenancy with Laravel, about 3 packages stand out.

I had played a little with hyn, which is undeniably a great package, but it didn’t have compatibility with Laravel 5.4 (did I mention I had little time?). It made me afraid of having to constantly deal with outdated packages and I didn’t want that burden on me.

At the time, orchestral was overly complicated for me. Laravel beginners, specially when you don’t have other framework experience, might take some time understanding Service Providers, boot, etc. I was unable to fully comprehend the solution for single database and multi database.

Single database solution. Not what I needed.

Doing it yourself

Laravel makes it quite easy to just do it yourself. All you need is a connection configuration, a middleware, a trait connector and set your models accordingly.

Connection Settings

in your config/database.php file, let’s set 2 connections. Note that I erase the mysql connection, so you need your .env to say DB_CONNECTION=main

'connections' => [
    
    'main' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE', 'forge'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => true,
        'engine' => null,
    ],
    
    'tenant' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', '3306'),
        'database' => '',
        'username' => '',
        'password' => '',
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => true,
        'engine' => null,
    ]
]

The Middleware

Always assure the connection exists. All you have to do is make sure all the routes that should connect to the tenant database uses this middleware. In my particular situation, the user would select a customer (tenant) from a list and would manipulate that customer’s data, hence the use for session. But I could easily have 2 middlewares (WebTenant, ApiTenant) and rely on tokens to pick a tenant connection as well.

<?php

namespace App\Http\Middleware;

use App\Models\Main\Company;
use App\Support\Controller\TenantConnector;
use Closure;

class Tenant {

    use TenantConnector;

    /**
     * @var Company
     */
    protected $company;

    /**
     * Tenant constructor.
     * @param Company $company
     */
    public function __construct(Company $company) {
        $this->company = $company;
    }

    /**
     * Handle an incoming request.
     *
     * @param  \Illuminate\Http\Request $request
     * @param  \Closure $next
     * @return mixed
     */
    public function handle($request, Closure $next) {
        if (($request->session()->get('tenant')) === null)
            return redirect()->route('home')->withErrors(['error' => __('Please select a customer/tenant before making this request.')]);

        // Get the company object with the id stored in session
        $company = $this->company->find($request->session()->get('tenant'));

        // Connect and place the $company object in the view
        $this->reconnect($company);
        $request->session()->put('company', $company);

        return $next($request);
    }
}

TenantConnector (The Trait)

Not much to talk about here. Just have your tenant data connection be set.

<?php

namespace App\Support;

use App\Models\Main\Company;
use Illuminate\Support\Facades\Config;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

trait TenantConnector {
   
   /**
    * Switch the Tenant connection to a different company.
    * @param Company $company
    * @return void
    * @throws
    */
   public function reconnect(Company $company) {     
      // Erase the tenant connection, thus making Laravel get the default values all over again.
      DB::purge('tenant');
      
      // Make sure to use the database name we want to establish a connection.
      Config::set('database.connections.tenant.host', $company->mysql_host);
      Config::set('database.connections.tenant.database', $company->mysql_database);
      Config::set('database.connections.tenant.username', $company->mysql_username);
      Config::set('database.connections.tenant.password', $company->mysql_password);
      
      // Rearrange the connection data
      DB::reconnect('tenant');
      
      // Ping the database. This will throw an exception in case the database does not exists or the connection fails
      Schema::connection('tenant')->getConnection()->reconnect();
   }
   
}

The Models

A model in the main database will have the main connection and that’s it.

<?php

namespace App\Models\Main;

use Illuminate\Notifications\Notifiable;
use Illuminate\Foundation\Auth\User as Authenticatable;

class Admin extends Authenticatable {
   
   use Notifiable;
   
   protected $connection = 'main';
}

The Company (customer/tenant) model was just slightly different. I decided to use the TenantConnector trait here as well and provide a connect()method. This allows me to do things like Company::find($id)->connect();

<?php

namespace App\Models\Main;

use App\Support\TenantConnector;
use Illuminate\Database\Eloquent\Model;

/**
 * @property string mysql_host
 * @property string mysql_database
 * @property string mysql_username
 * @property string mysql_password
 * @property string company_name
 */
class Company extends Model {
    
    use TenantConnector;
       
    protected $connection = 'main';
    /**
     * @return $this
     */
    public function connect() {
        $this->reconnect($this);
        return $this;
    }
    
}

The tenant model will just connect to the tenant database settings.

<?php

namespace App\Models\Tenant;

use Illuminate\Database\Eloquent\Model;
class MailQueue extends Model {

   protected $connection = 'tenant';
}

The last thing would be a SelectTenantController to allow you to set the session that the middleware expects.

/**
 * @GET
 * @param Request $request
 * @param $company
 * @return \Illuminate\Http\RedirectResponse|\Illuminate\Routing\Redirector
 */
public function select(Request $request, $company) {
   $this->reconnect($this->company->findOrFail($company)); 
   $request->session()->put('tenant', $company);
   return redirect('/');
}

Conclusion

Laravel will make it easy for you to have 2 connection settings. Routes that will connect to a specific database can easily have a middleware to make sure the connection exists. You can easily pick the connection for each model (or have a MainModel / TenantModel and extend them). Everything is set and you got yourself a Laravel application able to connect to multiple databases.

I should soon write a follow-up for automation testing with tenants and token-based routes with tenants.

Anúncios

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s