The Ultimate Guide for Laravel Multi Tenant with Multi Database

A while ago I wrote an article called Simple Multi Tenant with Laravel. I received a lot of good feedback from it. But that article was about an internal admin panel tool which the user would choose which tenant they wanted to manipulate. Recently I started working on a new project where the authenticated user belongs to a specific tenant and should be allowed to extract data only from his own database. This article is the approach I took to achieve that.

1- Every story starts with a Test: PostsTest

Letting the tests drive my code became a passion for me. Multi Tenant should not be different. First we lay down how we want our test to look like and then we make the code happen.

This is a pretty simple and fair test to expect. In order to make it work, the first requirement is to have TenantTestCase setting up the tenant. But before we dive into that, let’s quickly write our create helper.

This was a clean and brilliant idea by Jeffrey Way on his awesome Let’s Build a Forum. Check it out on Laracasts.

2- TenantTestCase

From the test we can expect that the TenantTestCase class will be responsible for creating a new company, a new user and authenticating that user. A database-specific connection should also be set.

By using RefreshDatabase, the main database should be migrated automatically. The call to actingAs will establish the authentication. As for the User Factory, since a user belongs to a company, it should recursively resolve that dependency.

3- The Factories

The UserFactory will be responsible for creating a new user for us. Laravel’s default UserFactory is almost good enough, we just need to add the company_id field to it.

Once a new user is required to be created, Laravel will try to resolve the CompanyFactory. Let’s write that next.

The CompanyFactory is the moment where a new company comes to life for our tests, so I decided to leverage it to automatically setup the tenant database connection as expected by the PostsTest.

The last factory is the PostFactory. For organization purpose, I decided to move all my main factories into database/factories/main and make room for my tenant factories at database/factories/tenant. The PostFactory is incredibly simple.

3- The tenant_connect() and tenant_migrate() helpers

As their name clearly suggests, tenant_connect() will establish a database connection with the tenant database. Since this is the Test stage, we should make sure that the tenant database receives it’s own migrations, such as the posts table.

This file could be placed under database/helpers.php. Don’t forget to add it to composer autoload.

"autoload": {"classmap": ["database/seeds","database/factories"],"files": ["database/helpers.php"],"psr-4": {"App\\": "app/"}}

That brings us to the next topic: Migrations.

4- The Main Migrations

The default database/migrations folder will hold the database structure for the main connection (users and companies). We need to create the Companies migration and tweak the Users to accommodate a company_id field.

Creating the companies migration manually with the name 2014_10_12_000000_create_companies_table.php will place it before the users migration, which will prevent any foreign key conflict since the companies table should already exist for the users table to be created.

5- The Tenant Migrations

On item (3) we established a tenant_migrate() method that will use the folder database/migrations_tenant as the source for the tenant database structure. Let’s create our create_posts_table migration there.

This concludes all the migrations necessary. Next, we need to deal with the application database settings and the connections for the Models.

6- The Application Database Settings

For database connection, let’s reuse the same strategy applied in the previous article: a main and a tenant database. Special attention to the new default connection pointing to main. Another point of interest is the DB_DRIVER variable that will allow us to switch between SQLite and MySQL between tests and live application.

Since we’re talking about database settings, let’s take a moment to setup the phpunit.xml file. The file should contain these variables

<env name="DB_DRIVER" value="sqlite"/><env name="DB_CONNECTION" value="main"/><env name="DB_DATABASE" value=":memory:"/>

7- The Models

The models also need to be split between Tenant and Main models. Let’s create the MainModel and the TenantModel under App/Models namespace.

Tables on the main connection, such as Companies and Users should extend MainModel whereas the Posts table is part of the customer-specific database structure and should extend TenantModel. Personally, I use the namespaces App\Models\Main and App\Models\Tenant respectively.

7- The Route

Since the focus of the article is to talk about Tenancy, let’s not waste time talking about routing.

That’s it! The test should now pass.

8- Wrapping up

We started with a test that creates a Post and hit an endpoint to see if it gets returned. But before the test gets executed, the setUp method on TenantTestCase is invoked.The Tenant Test Case class will create a user, which belongs to a company. During the creation of that company, a new database connection will be established and a migration will run through the tenant_migrate() helper. Once all that is done, the Tenant Test Case class will have a user that will be given as authenticated.Once the /posts endpoint is called, a tenant database has been created and a connection has been established. By using the Post model that has a default connection pointing to tenant it will automatically fetch the records from the connected database.

All that gives us a successful test implemented. But what about the actual application?

9- The Tenant Middleware

The test is green, but the actual application doesn’t work. The reason for that is because the Tests are setting up a tenant connection, but the application isn’t. We can solve that by implementing a Tenant Middleware.

This article is not going to implement any kind of authentication system. Whether you use Session, Cookies or Token is somewhat irrelevant. Let’s focus on the relevant part.

You can imagine a middleware before this one established an authentication with a specific user. Now we just grab that user and connect to it’s company. For this last piece of the puzzle to properly work we need to:

• A) Register the Middleware in the Route
• B) Create the User Belongs to Company relationship
• C) Expose a connect method from the Company model.

A) and B) are extremely simple:

For C) we’re going to do 2 things: Check if a connection is already established and, if not, call tenant_connect().

10- Conclusion

I chose the strategy of presenting a simple test code as a starting point and make it work layer by layer. It presents a complete solution with Tests, Migrations and a small tenant-specific feature. You can see the whole project on GitHub. Did I forget to include any must-have feature in this article? Tell me about it either in the section below or on Twitter!

If you wanna know how I deal with Data Migrations on a Multi Tenant application, check out this article.

Follow me on Medium for more articles like this. See you next time!