Sign in Subscribe
By Sabatino Masala —

Learnings from a multi-tenant Laravel application

Learnings from a multi-tenant Laravel application

In this post we’ll be taking a look at how I built a multi tenant food ordering webapp in Laravel. I scaled this webapp to over 1500 tenants, we get over 1 million of unique monthly visitors and this webapp processes around 10 million euro’s worth of food orders every month - so let’s dive in!

Multi tenancy in Laravel

Multi-tenant applications come in many shapes and forms, and one of the first decisions you’ll have to make is whether you want to have a multi-database approach, or a single, shared-database approach.

For our multi-tenant food ordering app, we went with the single shared database approach for speed and ease of development. It’s far from perfect, because if our database server is down, it’ll impact every single merchant. On top of that, a single-database approach means you’re constantly doing mental gymnastics when doing queries to make sure you’re not accessing data of other tenants.

Things like Order::all() are tenant-agnostic in our setup and we're only able to use these type of queries when doing global reporting across all tenants (for internal reports for example).

I didn’t use any third party multi-tenant packages, as there weren’t any available at the time I started this project, in 2014.

Database setup

In our single database setup, every table has to be linked to a specific tenant, or merchant in our case. So for example, let’s take a look at our orders table:

orders:
  id - integer
  type - string
  consumption_time - date
  customer_firstname - string
  customer_email - string
  ...

To be able to link this specific order to the right merchant, we introduced a merchant_id column. In our Merchant model, we can then create a relationship and access the orders on a merchant as follows:

// Relationship
public function orders() {
  return $this->hasMany(Order::class);
}

// Get orders
Merchant::firstOrFail()->orders;

Some of our tenants have multiple businesses, for example a franchise, so we also introduced a team_id so we can easily link multiple merchants to a team.

orders:
  id - integer
  type - string
  consumption_time - date
  customer_firstname - string
  customer_email - string
  merchant_id - integer <- add this
  team_id - integer <- add this
  ...

Here are a couple of lessons learned the hard way when working with this tenant identifier.

1) Don't use distant relationships

Merchants love to look at reports, for example: which product was sold the most for the past 7 days (compared to the 7 days before).

Our schema looked a little something like this:

teams:
  id - integer
  name - string
  
merchants:
  id - integer
  team_id - integer
  name - string
  
orders:
  id - integer
  merchant_id - integer
  team_id - integer
  ...
  
order_products:
  id - integer
  order_id - integer
  product_name - string
  ...

In an effort not to repeat myself, I didn’t include the merchant identifier nor the team identifier on the order_products table, because I figured I could access the OrderProducts on the merchant, through the orders, using a hasManyThrough relationship.

And while this works, it has a massive impact on query performance, because the complexity of the query drastically increases.

Instead, we refactored all of our distant relationships and added a tenant identifier on literally every table, so in this case, we can access order_products on the merchant, and even on the team, reducing query complexity.

So if we take a look at our updated schema, we’ll see that we now have added a merchant_id and team_id to the order_products table:

teams:
  id - integer
  name - string
  
merchants:
  id - integer
  team_id - integer
  name - string
  
orders:
  id - integer
  merchant_id - integer
  team_id - integer
  ...
  
order_products:
  id - integer
  order_id - integer
  product_name - string
  merchant_id - integer <- add this
  team_id - integer <- add this
  ...

This merchant and team identifier is applied to every single table we have in our database schema, because we learned the hard way that when doing reporting queries, it’s best to have as few hoops to jump through as possible.

While distant relationships work, they have a massive impact on query performance, because the complexity of the query drastically increases.

2) Be careful with ‘orWhere’ queries

In this type of single-database multitenant setup, the orWhere query can be a bit dangerous, because if you don’t scope your queries correct, you’ll leak orders from other merchants.

Let’s take a look at an example:

Merchant::find(1)
  ->orders()
  ->where('type', 'TAKEOUT')
  ->orWhere('status', 'COMPLETED')
  ->count()

This will result in the following MySQL Query:

select * from `orders` where `orders`.`merchant_id` = ?
  and `orders`.`merchant_id` is not null
  and `order_type` = ?
  or `status` = ?

Just like a multiplication operation takes precedence over an addition operation, AND has precedence over OR in MySQL queries. This means that the query is interpreted as:

Give me every order where the (merchant ID is 1 AND the merchant ID is not null AND the order_type is "TAKEOUT") OR every order where the status equals "COMPLETED"

This means we now are leaking orders from other tenants into the results of this query - big problem!

To correct this, we need to use the ‘where’ method and scope the where/orWhere query inside of it.

Merchant::find(1)
  ->orders()
  ->where(function($query) {
    $query
        ->where('type', 'TAKEOUT')
        ->orWhere('status', 'COMPLETED');
  })
  ->count()
select * from `orders` where `orders`.`merchant_id` = ?
  and `orders`.`merchant_id` is not null
  and (`order_type` = ? or `status` = ?)

The 'where' method essentially adds parentheses into the raw MySQL query, and gets rid of the OR query, so our query is now correctly scoped to the tenant.

3) Pay extra attention to the indices

Our orders table quickly grew to tens millions of rows, and obviously our merchants expect snappy performance on those peak moments when they’re preparing dozens of orders per hour. To guarantee this, it’s important to profile every query that gets executed, and add necessary indices, before you even start thinking about caching. A thing we always did was add a foreign index to the merchant_id and teams_id column, but often we added composite indices to really squeeze all the performance we can out of a query.

To figure out which index MySQL uses, you can use the EXPLAIN query, and in the result MySQL will tell you which index it used to execute your query, and how many rows it had to look through.

Unique indices, for example a slug, also have to be scoped to the tenant, otherwise you’d get unique constraint errors when the same value already occurs with the other tenants.

4) Don’t use global scopes for tenant-specific queries

Global scopes initially sound like an amazing idea: you can apply a scope to all queries without repeating yourself.

// Don't use this
static::addGlobalScope('tenant', function (Builder $builder) {
    $builder->where('tenant_id', auth()->user()->tenant_id);
});

However, I found myself removing the global scopes more often than not using the query::withoutGlobalScope method, for example for our internal reporting.

We decided to never use global scopes for tenant-specific queries, we only used global scopes for global query constraints like for example only querying the orders of the last 5 years. Orders older than that are considered archived, and we mark those orders with an archived_at date - kinda like SoftDeletes - and apply a global scope on it.

For all other purposes, we opted for local scopes and applied them manually where needed.

Tenant resolving

Now that we talked about the database setup, let’s dive into another big aspect of a multi-tenant application - and that is Tenant Resolving.

We provide a webshop for every tenant, that can be visited using a dedicated subdomain or custom domain.

We provide every tenant with a free subdomain out of the box, and using middleware, we are able to figure out the tenant when someone visits the subdomain.

We have a ‘Domains’ table in our database that maps a domain or subdomain to a merchant. Our MerchantByDomain middleware is able to figure out which domain or subdomain is linked to which merchant, and then injects the resolved merchant into the service container.

If the merchant is suspended, for example when they don’t pay their invoice, we throw an exception and render a specific view for it. And when no merchant is found we throw a MerchantNotFoundException, which essentially renders a 404.

<?php

namespace App\Http\Middleware;

use App\Exceptions\MerchantSuspendedException;
use App\Exceptions\MerchantNotFoundException;
use Closure;

class MerchantByDomain
{
    public function handle($request, Closure $next)
    {
        $host = $this->getHost($request);

        try {
            $cacheKey = 'host-' . $host;
            $domain = \Cache::remember($cacheKey, 60 * 60, function() use ($host) {
                return Domain::where('url', $host)->firstOrFail();
            });

            if ($domain->merchant->is_suspended) {
                throw new MerchantSuspendedException();
            }

            app()->bind(Merchant::class, function () use ($domain) {
                return $domain->business;
            });

            return $next($request);
        } catch (\Exception $e) {
            if ($e instanceof BusinessSuspendedException) {
                throw $e;
            }
            throw new MerchantNotFoundException();
        }
    }
    
    protected function getHost($request)
    {
        // ...
    }
    
}

When a merchant prefers to have a custom domain, we have a reverse-proxy setup that will handle SSL certificates and proxying with minimal effort. We use a tool called Ceryx which makes managing these custom domains a breeze - but more on that in a future video.

We also have a public API, that is accessible through a tenant-specific API key. In a similar fashion, we apply a MerchantByApiKey middleware, that is able to resolve a merchant from an API key. We use this API key in many of our services, for example our iPad point of sales app.

It’s important to pay attention to jobs dispatched onto a queue from within a tenant context, because they are not tenant-aware by default. To provide the job with context, we always pass in the current merchant as an argument, so we are able to access the merchant model inside of the job handler.

Final words

And that concludes the article, thanks for sticking with me!

I may do a second part, because there’s so much more to talk about.

I created an entire video about how a flash sale on a single tenant took down our entire system, so if you’re interested to learn more about that specific issue, you can take a look:

See you next time!

Subscribe to sabatino.dev

Don’t miss out on the latest issues. Sign up now to get access to the library of members-only issues.
jamie@example.com
Subscribe