Multi-Tenant Database Connection In Laravel
Motivation behind this mini project: Many companies use one centralized database and a company-specific database. I have done this in core PHP with session but not in Laravel. So, I wanted to try this out in Laravel, and it was not easy & it needed a lot of research and searching.
How did I do it?
Let's start with the route. I created a route that takes the company name and domain as input from the form. For the view, I used the blade.
-- web.php
Route::get('/registerCompany', function () {
return view('companyRegister');
});
The above code renders the view when /registerCompany URL is hit. The view is as follows:
-- companyRegister.blade.php
<!doctype html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport"
content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<meta name="csrf-token" content="{{ csrf_token() }}">
<title>RegisterCompany</title>
</head>
<body>
<form method="post" id="registerCompany">
@csrf
<label for="name">Company Name:</label>
<input type="text" id="name" name="name">
<label for="domain">Domain:</label>
<input type="text" id="domain" name="domain">
<button type="submit">Register</button>
</form>
<script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
<script src="{{ asset('js/ajax.js') }}"></script>
</body>
</html>
I have included JQuery and ajax.js files to send the POST request to the server. When the form is submitted then, the following Ajax script will run:
-- ajax.js
$('#registerCompany').on('submit', function (e) {
e.preventDefault();
let name = $('#name').val();
let domain = $('#domain').val();
let csrfToken = $('meta[name="csrf-token"]').attr('content');
console.log(name, domain, csrfToken);
$.ajax({
type: 'POST',
url: '/registerCompanyHandler',
data: {
name: name,
domain: domain
},
headers: {
'X-CSRF-TOKEN': csrfToken
},
success: function (data) {
console.log(data);
}
});
});
I have created a route in the web.php to handle the POST request sent from the JS.
-- web.php
Route::post('/registerCompanyHandler', [CompanyController::class, 'register_company']);
The above route sends the request from Ajax to CompanyController, and it passes the value to the register_company function, which processes the request.
-- CompanyController.php
public function register_company(Request $request): JsonResponse
{
$validatedData = $request->validate([
'name' => 'required',
'domain' => 'required',
]);
$company = new Company();
$company_valid = $company->create_company($validatedData);
return response()->json($company_valid);
}
The above controller validates the request. It checks whether the name & domain is present in the request. If it is not present, it returns an error that needs to be handled in JS, but I haven't done that.
After validating the request, it creates an object of the Company model. In the Company model, there are functions related to the company. It then calls the create_company method in the Company class and passes the validated request. Then, the register_company finally returns the JSON returned by the Company class, which the JS can process.
-- Company.php
<?php
namespace App\Models;
use App\Helpers\EnvUpdater;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Http\JsonResponse;
use Illuminate\Support\Facades\Artisan;
use Illuminate\Support\Facades\DB;
class Company extends Model
{
use HasFactory;
protected $table = 'companies';
protected $fillable = [
'name',
'subdomain',
'company_db'
];
public function create_company($data): array
{
$company_db = rand(100000, 999999);
$company = Company::where('name', $data['name'])->where('subdomain', $data['domain'])->get();
if ($company->count() > 0) {
$finalResponse = ['success' => false, 'message' => 'Company already exists'];
} else {
while (Company::where('company_db', $company_db)->exists()) {
$company_db = rand(100000, 999999);
}
// Insert values into the database
$company = new Company();
$company->name = $data['name'];
$company->subdomain = $data['domain'];
$company->company_db = $company_db;
$company->save();
DB::statement('CREATE DATABASE ' . "company_$company_db");
config(['database.connections.company_' . $company_db => [
'driver' => 'mysql',
'host' => env('DB_HOST', '127.0.0.1'),
'port' => env('DB_PORT', '3308'),
'database' => 'company_' . $company_db,
'username' => env('DB_USERNAME', 'root'),
'password' => env('DB_PASSWORD', 'Admin123###'),
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
'prefix' => '',
'strict' => true,
'engine' => null,
]]);
Artisan::call('config:clear');
Artisan::call('migrate', ['--database' => 'company_' . $company_db]);
$finalResponse = ['success' => true, 'message' => 'Company created successfully'];
}
return $finalResponse;
}
}
The create_company does the following things:
Generates a 6-digit random number as a unique identifier for the company database.
Then, it checks whether the company with the same name and domain is present. If it is present, it returns an array telling the company already exists.
It then checks whether or not the unique 6-digit company db name is already used. If it is already in use, then it generates another. This continues in a while loop, so it generates a number continuously until the identifier is unique.
After the unique identifier is generated, it creates a database using it. You can't build a database using only a number, so I have added a string before the identifier so we can make a database using it.
After the database is created, the database is configured in the runtime. It is done so that we can run migration files in the database.
After the database is configured in runtime, we need to clear the cache to ensure that the changes are recognized.
After the cache is cleared, the migration file is ruined on the database, ensuring that it contains all the tables that are specified.
After the migration is successful, it returns an array telling that the database creation was successful. The array is converted into JSON in the controller and then sent back from where it came.
Now that the company part is over, we need to move to the user part, where things started to get hard for me. Up to this point, it was easy.
First, let's create a route & view for the login.
-- web.php
Route::get('/login', function () {
return view('login');
});
-- login.blade.php
<!doctype html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport"
content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<meta name="csrf-token" content="{{ csrf_token() }}">
<title>Login</title>
</head>
<body>
<div class="mainContainer" id="mainContainer">
<form method="post" id="loginHandler">
@csrf
<label for="email">Email:</label>
<input type="text" id="email" name="email">
<label for="password">Password:</label>
<input type="password" id="password" name="password">
<button type="submit">Login</button>
</form>
</div>
<script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
<script src="{{ asset('js/ajax.js') }}"></script>
</body>
</html>
The view also includes JQuery and Ajax, as it did earlier. Let's go to the ajax.js file where the event is handled.
-- ajax.js
$('#loginHandler').on('submit', function (e) {
e.preventDefault();
let email = $('#email').val();
let password = $('#password').val();
let csrfToken = $('meta[name="csrf-token"]').attr('content');
$.ajax({
type: 'POST',
url: '/loginHandler',
data: {
email: email,
password: password
},
headers: {
'X-CSRF-TOKEN': csrfToken
},
success: function (data) {
console.log(data)
console.log(data.success);
if (data.success) {
console.log('haha lol');
$('#mainContainer').html('');
let company = data.message;
for (let key in company) {
if (company.hasOwnProperty(key)) {
let value = company[key];
console.log(key);
console.log(value);
$('#mainContainer').append(`
<h1>${key}</h1>
<button id="companySelectedUser" class="companySelectedUser" value="${value}">Choose company</button>
<br><br>
`);
}
}
}
}
});
});
The above Ajax request sends a post request to /loginHandler. Let's handle the request by creating an endpoint for it.
-- web.php
Route::post('/loginHandler', [UserController::class, 'validate_user']);
The above route passes the value to UserController inside of the validate_user method. Let's handle the request using this method.
-- UserController.php
public function validate_user(Request $request)
{
$validatedData = $request->validate([
'email' =>'required|email',
'password' => 'required|min:6'
]);
$user = new User();
$user_valid = $user->check_for_users($validatedData);
return response()->json($user_valid);
}
The above method first validates the request parameters. The email and password fields are required, and the email should be valid, while the password length must be at least six characters. If these conditions aren't met, it throws an error, which should be handled in JS, but this is not done currently.
After the validation is correct, it calls the check_for_users method by passing the valid parameters into the process that is present in the User class, which is a model.
-- User.php
<?php
namespace App\Models;
// use Illuminate\Contracts\Auth\MustVerifyEmail;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Foundation\Auth\User as Authenticatable;
use Illuminate\Notifications\Notifiable;
class User extends Authenticatable
{
use HasFactory, Notifiable;
protected $table = 'users';
protected $primaryKey = 'user_id';
protected $fillable = [
'user_name',
'user_email',
'user_phoneNumber',
'user_password',
'associated_company',
];
protected $hidden = [
'user_password',
];
public function check_for_users($data): array
{
$user = User::leftJoin('companies', 'users.associated_company', '=', 'companies.id')
->where('user_email', $data['email'])
->where('user_password', $data['password'])->get();
if ($user->count() > 0) {
$associated_company = $user->pluck('associated_company', 'name');
$finalResponse = ['success' => true, 'message' => $associated_company];
} else {
$finalResponse = ['success' => false, 'message' => 'User not found'];
}
return $finalResponse;
}
}
The functions use Eloquent ORM to join two tables based on the FK reference, and then the associated_company ID and name are returned to the JS. It fetches all the fields in the database associated with that email & password.
Then, the JS renders the view with the button and the company name. When the select option is clicked, the Ajax request is triggered again.
-- ajax.js
$(document).on('click', '#companySelectedUser', function () {
let company = $(this).val();
let csrfToken = $('meta[name="csrf-token"]').attr('content');
$.ajax({
type: 'POST',
url: '/companySelectedUser',
data: {
company: company
},
headers: {
'X-CSRF-TOKEN': csrfToken
},
success: function (data) {
console.log(data);
}
});
});
The above Ajax request is sent to /companySelectedUser the endpoint. Let's create an endpoint to handle it.
Before creating an endpoint, we need to create a middleware as we need to handle the multiple_database connection throughout the application. So, let's create a Middleware named SwitchDatabase.
-- SwitchDatabase.php
<?php
namespace App\Http\Middleware;
use Closure;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Config;
use Illuminate\Support\Facades\Artisan;
use Symfony\Component\HttpFoundation\Response;
use App\Models\Company;
class SwitchDatabase
{
/**
* Handle an incoming request.
*
* @param \Illuminate\Http\Request $request
* @param \Closure $next
* @return \Symfony\Component\HttpFoundation\Response
*/
public function handle(Request $request, Closure $next): Response
{
if ($request->has('company')) {
$companyId = $request->input('company');
// Find the company database based on the provided company ID
$company_db = Company::where('id', $companyId)->pluck('company_db')->first();
session(['company_db' => $company_db]);
if ($company_db) {
// Configure the new database connection
Config::set('database.connections.dynamic', [
'driver' => 'mysql',
'host' => env('DB_HOST', '127.0.0.1'),
'port' => env('DB_PORT', '3308'),
'database' => 'company_' . $company_db,
'username' => env('DB_USERNAME', 'root'),
'password' => env('DB_PASSWORD', 'Admin123###'),
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
'prefix' => '',
'strict' => true,
'engine' => null,
]);
// Clear the cache and set the default connection
Artisan::call('config:clear');
DB::setDefaultConnection('dynamic');
}
} else if (session()->has('company_db')) {
Config::set('database.connections.dynamic', [
'driver' => 'mysql',
'host' => env('DB_HOST', '127.0.0.1'),
'port' => env('DB_PORT', '3308'),
'database' => 'company_' . session('company_db'),
'username' => env('DB_USERNAME', 'root'),
'password' => env('DB_PASSWORD', 'Admin123###'),
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
'prefix' => '',
'strict' => true,
'engine' => null,
]);
Artisan::call('config:clear');
DB::setDefaultConnection('dynamic');
}
return $next($request);
}
}
The above Middleware changes the default database on a user basis. If the request is being sent to the company, then it maps to the database for that company and then selects the database & stores it in the session for future usage. If the company is not being passed, then it simply selects the database from the session. This way, we can handle multiple database connections for different users using the Middleware. We need to group the routes together that require different database connections so that we can easily implement middleware.
Route::group(['middleware' => [SwitchDatabase::class]], function () {
Route::get('home', function () {
print_r(session()->all());
dd(DB::connection()->getDatabaseName());
});
Route::get('/viewDatas', function () {
// Get the data from the database
$data1 = DB::table('projects')->get();
$data2 = DB::table('tasks')->get();
$data3 = DB::table('departments')->get();
$data4 = DB::table('employees')->get();
dd($data1, $data2, $data3, $data4);
});
Route::post('/companySelectedUser', [CompanyController::class, 'company_selected_user']);
});
Now, what this does is implement the middleware for each of the routes so the DB connection is dynamically handled in real-time. Now, if you visit '/home' endpoint, then you will get the name of the selected database. This way, you can handle the database switching.
This way, you can change the database and handle multi-tenancy in Laravel. The code is still immature since I am learning Laravel. I hope you understand it 😉.
