Implementing Multitenancy in Laravel.
Well, this took me close to a month, with a couple of sleepless nights and a few drops of tears on being asked about the status of the task, since I was very new to Laravel.
Multi-tenancy refers to a software architecture in which a single instance of software runs on a server and serves multiple tenants. In my case, my application(single instance) has multiple customers and I wish to have different databases for each of them.
My scenario deals with having different databases for all the customers. I have one main database for tables which are common to all the customers and then there are customer databases. The customer databases will have customer specific tables and whenever I select a specific customer from the application the database connection should change for that request and connect to the customer’s database. It sounds a little overwhelming, I know …Will try to explain my best.
Let’s start:
I will start with explaining to you the databases that I have :
- Main Database (test_db) → This contains the customer table and all other tables which would remain common to all the customers.
- Customer Specific Databases →This would contain all the customer-specific tables.
Firstly, to start with, my config/database.php file contains 2 connection strings, one is for the main database(mysql_main), test_db in my case and another one for the customer specific databases(mysql_tenant). The customer specific database connection string values are set dynamically for every request.
I have an application form that takes in the customer details like the name, contact details and also the MySQL username and password(This is used to make a connection with the MySQL instance and to create the customer database and the customer specific tables).
The customer details are stored in the main database(test_db) and then there is an event call which invokes a listener to create the database for that customer and also runs the migrations to create tables. (This runs asynchronously) To make the events run in the background you can specify the QUEUE_DRIVER=database in the .env file and also make sure that your Listener implements the ShouldQueue.
The listener code to create the database:
The ‘database’ variable contains the name of the database to be created. (It is a combination of the customer id and the customer uid(auto-generated)).
The listener code to run migrations(To create tables in the above-created database).
The ‘customer’ variable contains all the customer details. The ‘Config::set’ plays a very important role here, I am making it change my database connection string values for one request. The database connection string being used is ‘mysql_tenant’ which I had defined above in the database.php file.
In the artisan command, I specify the connection string to be used using the ‘ — database’ => ‘mysql_tenant’.The path contains the folder details that have my migrations.
This creates my customer database and also the tables in that database.
This happens whenever a new customer is created. My application works with selecting a specific customer from the drop-down after login. The customer id of the selected customer is then added to the session so that it is available across and the queries know which customer database to be used to fetch the details.
Likewise, for all the controllers and models which are customer specific, we use Config to dynamically changing the connection variables of the mysql_tenant connection string.
I have created a BaseTenantModel which extends the default Model. All the models which are customer specific would extend this BaseTenantModel.
The model is as follows:
So whenever a model function is invoked in your child class, the superclass constructor gets invoked and it changes the connection values. I have also specified the connection string to be used at the start i.e ‘mysql_tenant’
Similarly, I have created a BaseTenantController which extends the default Controller. All the controllers which are customer specific would extend this BaseTenantController.
The controller is as follows:
Example:
If I have a customer-specific table as Project and I wish to query it, I don't need to specify the connection string name, since at the start of the constructor I set my connection=’mysql_tenant’, my Project model would extend my BaseTenantModel, and the constructor makes sure that the connection string to be used is mysql_tenant.
For other queries, we can specify the connection string in the query statement as:
DB::connection(‘mysql_tenant’)->table(‘table_name’)
->where(‘some_column’,$some_value)
->update([‘some_column’ => $some_value]);
All the tables which are not customer specific would be in the test_db database and that use the default connection string i.e the mysql_main in the database.php.
My customer table itself is present in the test_db database and the customer model and controller extend the default model and controller respectively. So all the queries to the customer table happen using the default connection string i.e the mysql_main in the database.php file.
That's it, this takes care of the multi-tenancy, make sure to add your customer id to the session so that its available and we can use it to get the database details of the customer.
You can always encrypt the MySQL credentials in case of security concerns.
Please get in touch in case of any queries …
Thanks.
