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.