Laravel Multitenancy — Part 2
This is a continuation from Laravel Multitenancy — Part 1
Introduction…
The idea of using another laravel/lumen application for provisioning a database is because I am a huge fan of microservices. Think of microservices as another way of using separation of concerns. This application is meant to only provision a database for your tenants. However, you can have this as a class sitting in your main application and use an event to fire it.
The Database Provisioner Laravel / Lumen application
Within the new application, create a route that will accept a host, database name, username, and password. So craft one and setup your route and the migration files of the database as I’ll discuss below.
Template Database
Create 4/2 folders in the migrations folder of your new application. if you don’t need encryption within your database/migrations folders, you could only have 2. The idea behind me doing this is I like to separate the template migration scripts from the scripts that will be executed for a tenant. So create the template, template_encryption, tenants, and tenants_encryption folders or template and tenants folders. Within these folders, we will create migration files for the application. You definitely must start with the template. Think of the template as a place to have a demo version of your application and to make sure what you’re doing is correct.
Create a database, name it whatever you want, however, I suggest suffixing the template database with the word “_template”.
Once you have your template database, make sure that the default database connection in your configuration is pointing to it. Also make sure that the user that is setup in the configuration has DDL (Data Definition Language) rights. Migrate the template database using “php artisan migrate — path=database/migrations/template”. If you have exceptions, fix them. Once your template database migration files are all good and you can now migrate your template database, copy those scripts over into the tenants folder or create shortcuts. Note: The default connection within this application must be connected to the template database.
The Provisioner Class
This is the meat of the application. It implements an interface called IDatabaseProvisioner because if we are using mysql, you want to be able to execute the correct syntax for the database driver and if Sequel, you will use a different syntax. If there’s a better way to do this, I sure would like to know how.
Below is the code for my provisioner. Remember the route we created above? That route simply calls this class to execute these statements.
Provisioner Code
Looking at the code above, if you have any suggestions, please let me know. Let’s get to it… The code from the controller calls the provisionDatabase method and passes the parameters needed to create the database. First we connect to the host, create the database, create the user then migrate the files from the tenant and/or in my case the tenants_encryption folder.
Connect to the Host/Localhost (connectToHost method)
The connectToHost method disconnects from the default database and sets the config for the tenant’s database name and host. Please note that the default user in my env file must be able to connect and make DDL functions on the database server. We then set the default connection using DB::setDefaultConnection(‘benta_tenant’); DB::connection()->setFetchMode(PDO::FETCH_ASSOC); and DB::reconnect(‘benta_tenant’); Automatically after this, the default connection is set and you can then create the database.
Create the database (createDatabase method)
Next is the createDatabase method. This method only runs the statement to create the schema or database (CREATE SCHEMA [DatabaseName]). Once this query is executed, make sure you set the configuration to use the new database name. Now, I’m not sure if reconnecting to the database does anything, but I just needed to have it there to be sure the connection is refreshed.
Create the user for the database/tenant (createUser method)
The next method is createUser method which creates the user on the database server. It is a simple create user script, but I’ll just list it out notwithstanding… :)
The script above creates a new user on the database server, however, we need to make sure that the user ONLY has access to the tenant’s database (i.e the database we are creating).
Migrating the database :) (migrateDatabase method)
This was a little tricky for me, for some reason, I kept having issues with this but solved it by RECONNECTING to the database. Unusual but solved the problem. Now… if you look closely, we are not using the database user of the tenant. We are using a predefined user (Which we have defined in our env file that has DDL rights). The most important code here is $this->kernel->call(‘migrate’, [‘ — path’ => ‘/database/migrations/tenant’, ‘ — database’ => ‘benta_tenant’]); This is like going into your console and running the command “php artisan migrate” but setting the path and database to use.
And that’s it for migrating a new tenant’s database. This should automatically migrate the database for you. If you’re having issues, I can collaborate and solve issues together. The fun path (coming next weekend) is automatically connecting to the database with your front end application. Note that the front end application is connected to the admin database that has defined and stored the database per tenant. What I did was similar to this however without the migration part. Note that, you will authenticate the user within your admin database and not your tenant’s database. This will allow a central place to administer users of your system. This also allows you to have a user who CAN connect to 2 different tenant, if you wish. Like tenant A can invite johndoe@example.com and tenant Z also invites the same user. Or Tenant A and Z are collaborators on the same project and needs to be able to access each other’s data.
Tip: The main application will have a Base Controller that is not public, and will have a middleware (User must have been authenticated before the handling the middleware) that retrieves the user’s tenant and tenant_databases record and connects to it. Note, if the user is connected to 2 tenants, you should redirect to a view/page that asks the user which organization he/she wants to connect and take it on from there. Now this middleware does one thing and one thing alone. It connects to the database of the tenant/organization, nothing more.
What do you think of this? Are we going somewhere with this or you guys want a step by step video for this? Let me know and we’ll see. :)
See part 3 below…
