Learnings from our multi-tenant Laravel application

[00:00] in this video we'll be taking a look at

[00:01] how I built a multi-tenant food ordering

[00:04] web app in laravel I scal this web app

[00:06] to over 1,500 tenants we get over 1

[00:09] million of unique monthly visitors and

[00:12] this web app processes around € 10

[00:14] million e worth of food orders every

[00:16] single month so let's dive

[00:20] in multi-tenant applications command

[00:23] many shapes and forms and one of the

[00:25] first decision you'll have to make is

[00:27] whether you want to have a multi-

[00:28] database approach or a single shared

[00:31] database approach for our multi-tenant

[00:33] food ordering app we went with a shared

[00:35] database approach for Speed and ease of

[00:38] development however it's far from

[00:40] perfect because if our database server

[00:42] is down it'll impact every single

[00:44] mergent on top of that a single database

[00:46] approach means you're constantly doing

[00:48] mental gymnastics in your codes when

[00:50] you're doing queries to make sure you're

[00:52] not accessing data of other tenants I

[00:55] didn't use any third-party multi- Talent

[00:58] packages as there simply weren't any

[01:00] available at the time I started the

[01:02] project in

[01:05] 2014 in our single database setup every

[01:08] table has to be linked to a specific

[01:10] tenant or Merchant in our case so let's

[01:13] for example take a look at our orders

[01:15] table we have an ID which it identifies

[01:19] the order we have a type we have a

[01:21] consumption time and we have some

[01:23] customer information like the customer

[01:25] first name and the customer email to be

[01:27] able to link this specific order to the

[01:29] right Merchant we introduced a merchant

[01:31] ID column in our Merchant model we can

[01:34] then create a relationship and access

[01:36] the order on a merchant as follows some

[01:38] of our Merchants have multiple

[01:40] businesses for example a franchise so we

[01:42] also introduced a team ID so we can

[01:45] easily link multiple Merchants to a

[01:49] team here are a couple of lessons

[01:51] learned the hard way when working with

[01:53] this tenant identifier column lesson

[01:56] number one is don't use distant

[01:58] relationships Merchants love love to

[02:00] look at reports for example which

[02:02] product was sold the most for the past 7

[02:05] Days compared to the 7 days before and

[02:07] our schema looked a little something

[02:09] like this we had a team table we had a

[02:11] Merchants table we had an order table

[02:13] and we had an order products table

[02:16] Merchants were linked to the team

[02:18] through the team ID orders were linked

[02:20] to the merchant and to the team through

[02:23] the merchant ID and through the team ID

[02:25] and our order products were linked to

[02:27] the order through the order ID in an

[02:31] effort not to repeat myself I didn't

[02:33] include the merchant identifier nor the

[02:35] team identifier on the order products

[02:37] table because I figured I could access

[02:39] the order products on the merchant

[02:41] through the orders using a h many

[02:44] through relationship the way a has many

[02:46] true relationship works is that we have

[02:49] our Merchant model that has many orders

[02:51] and orders has many order products and

[02:53] we can access the order products through

[02:56] the orders on the merchant using a has

[02:59] many true relationship if you take a

[03:01] look at the merchant model we can see

[03:03] that we have a direct relation on the

[03:05] orders using a has many relationship and

[03:08] order products is a so-called distant

[03:10] relationship which allows us to access

[03:13] the order product through the order and

[03:16] while distant relationships work

[03:18] beautifully it has a massive impact on

[03:20] query performance because the complexity

[03:22] of the query drastically increases

[03:25] instead we refactored all our distant

[03:27] relationships and added a tenant

[03:29] identifier on literally every single

[03:33] table so in this case by adding a

[03:35] merchant ID and a team ID we are able to

[03:38] access order products on the merchant

[03:41] and even on the team using a has many

[03:44] relationship this reducing query

[03:46] complexity this Merchant and team

[03:48] identifier is applied to every single

[03:50] table we have in our database schema

[03:53] because we learned the hard way that

[03:54] when doing reporting queries it's best

[03:57] to jump through as few Hoops as possible

[04:00] lesson number two is to be extremely

[04:02] careful with orware queries in this type

[04:05] of single database multi-tenant setup

[04:08] the orware query should be used with

[04:10] caution because if you don't scope your

[04:12] queries correctly you'll leak orders

[04:14] from other merant let's take a look at

[04:17] an example in the example shown on

[04:19] screen we're counting the orders of the

[04:22] mergent with ID one where the type

[04:25] equals takeout or where the status

[04:27] equals completed if you take a look at

[04:29] at a raw query we'll see that we'll

[04:32] select the orders where the merchant ID

[04:35] equals in our case one and the merchant

[04:37] ID is not null and the order type equals

[04:41] in our case takeout or the status equals

[04:44] completed now because of the way MySQL

[04:46] interprets this query it will find the

[04:48] orders where the merchant ID equals 1

[04:51] and the order type equals takeout or it

[04:54] will take every single order where the

[04:57] status equals completed also the one of

[05:00] the other Merchants so it's super

[05:02] important to scope the query correctly

[05:04] using the wear method on the orders

[05:06] relationship using this wear method

[05:09] we're essentially adding parentheses in

[05:11] the Raw MySQL Query and if we take a

[05:14] look at the resulting raw MySQL Query

[05:16] we'll see that the order type in our

[05:18] case should be take out or the status

[05:21] should be completed but we removed the

[05:24] or statement and we added these

[05:26] parentheses and this way MySQL is a able

[05:29] to scope the query correctly lesson

[05:32] number three is to pay extra attention

[05:34] to the indices our orders table quickly

[05:37] grew to tens of millions of rows and

[05:40] obviously our Merchants expect Snappy

[05:42] performance on those Peak moments when

[05:44] they're preparing dozens of orders per

[05:47] hour to be able to guarantee this it's

[05:49] important to profile every single query

[05:52] that gets executed and add the necessary

[05:55] indices before you even start thinking

[05:57] about caching a single thing if thing we

[06:00] always did was add a foreign index to

[06:02] the merchant ID and the teams ID column

[06:05] but often we added composite indices to

[06:08] really squeeze all the performance we

[06:10] can out of a query to figure out which

[06:12] index MySQL uses for a query you can add

[06:15] the word explain before it and in the

[06:18] result MySQL will tell you which index

[06:20] it used to execute your query and how

[06:23] many rows it had to look through unique

[06:26] indices for example a slug also have to

[06:29] be scoped to the tenant otherwise you'd

[06:31] get unique constrainted errors when the

[06:33] same value already occurs with the other

[06:36] tenants and lesson number four is don't

[06:39] use Global Scopes Global Scopes

[06:41] initially sound like an amazing idea you

[06:43] can apply a scope to all queries without

[06:46] repeating yourself however I found

[06:49] myself removing the global Scopes more

[06:51] often than not using the query without

[06:54] Global scope method for example for our

[06:56] internal reporting we decided to never

[06:59] use Global scopes for Tenon specific

[07:02] queries and we only used Global scopes

[07:04] for Global query constraints like for

[07:07] example only querying the orders of The

[07:10] Last 5 Years orders that are older than

[07:13] that are considered archived and we Mark

[07:16] those orders with an archived at date

[07:19] kind of like soft elets and apply a

[07:21] global scope on it for all other

[07:23] purposes we opted for local Scopes and

[07:26] applied them manually where needed

[07:31] now that we talked about the database

[07:33] setup let's dive into another big aspect

[07:35] of a multi-tenant application and that

[07:38] is tenant resolving we provide a webshop

[07:41] for every tenant that can be visited

[07:43] using a dedicated subdomain or a custom

[07:46] domain we provide every tenant with a

[07:48] free subdomain out of the box and using

[07:51] middleware we are able to figure out the

[07:53] tenant when someone visits the subdomain

[07:55] so let's take a look at our Merchants

[07:57] buy domain middleware we have a domain

[07:59] stable in our database that Maps a

[08:02] domain or a subdomain to a merchant our

[08:04] Merchant by doain middleware is able to

[08:06] figure out which domain or subdomain is

[08:09] linked to which mergent and then injects

[08:12] the resolved mergent into the service

[08:14] container if the mergent is suspended

[08:16] for example when they don't pay their

[08:18] invoice we throw an exception and render

[08:20] a specific view for it and when no

[08:23] Merchant is found we throw a merchant

[08:25] not found exception which essentially

[08:27] renders a 404 when Merchants prefer to

[08:30] have a custom domain we have a reverse

[08:33] proxy setup that will handle SSL

[08:35] certificates and proxying to our system

[08:38] with minimal effort we use a tool called

[08:40] CX which makes managing these custom

[08:43] domains a breeze but more on that in a

[08:45] future video we also have an API that's

[08:48] accessible through a tenant specific API

[08:50] key and in a similar fashion we apply a

[08:53] merchant by API Key Middle bear that's

[08:56] able to resolve a merchant from an API

[08:58] key and instead of the domain we use

[09:00] this API key in many of our services for

[09:03] example our iPad Point of Sales app it's

[09:06] important to pay attention to jobs

[09:08] dispatched onto a queue from within a

[09:10] tenant context because they are not

[09:13] tenant aware by default to provide the

[09:15] job with the necessary context we always

[09:18] pass in the current Merchant as an

[09:20] argument so we're able to access the

[09:23] merchant model inside of the job Handler

[09:25] and that concludes the video if you

[09:28] found this content helpful or insightful

[09:30] give it a thumbs up and consider

[09:32] subscribing I may do a second part

[09:34] because there's just so much more to

[09:36] talk about I created an entire video

[09:39] about how a flesh sale on a single

[09:41] tenant took down our entire system so if

[09:43] you're interested to learn more about

[09:45] that specific issue I'll link it in the

[09:47] description thank you for watching and I

[09:49] will see you in the next one