[0:00] in this video we'll be taking a look at
[0:01] how I built a multi-tenant food ordering
[0:04] web app in laravel I scal this web app
[0:06] to over 1,500 tenants we get over 1
[0:09] million of unique monthly visitors and
[0:12] this web app processes around € 10
[0:14] million e worth of food orders every
[0:16] single month so let's dive
[0:20] in multi-tenant applications command
[0:23] many shapes and forms and one of the
[0:25] first decision you'll have to make is
[0:27] whether you want to have a multi-
[0:28] database approach or a single shared
[0:31] database approach for our multi-tenant
[0:33] food ordering app we went with a shared
[0:35] database approach for Speed and ease of
[0:38] development however it's far from
[0:40] perfect because if our database server
[0:42] is down it'll impact every single
[0:44] mergent on top of that a single database
[0:46] approach means you're constantly doing
[0:48] mental gymnastics in your codes when
[0:50] you're doing queries to make sure you're
[0:52] not accessing data of other tenants I
[0:55] didn't use any third-party multi- Talent
[0:58] packages as there simply weren't any
[1:00] available at the time I started the
[1:02] project in
[1:05] 2014 in our single database setup every
[1:08] table has to be linked to a specific
[1:10] tenant or Merchant in our case so let's
[1:13] for example take a look at our orders
[1:15] table we have an ID which it identifies
[1:19] the order we have a type we have a
[1:21] consumption time and we have some
[1:23] customer information like the customer
[1:25] first name and the customer email to be
[1:27] able to link this specific order to the
[1:29] right Merchant we introduced a merchant
[1:31] ID column in our Merchant model we can
[1:34] then create a relationship and access
[1:36] the order on a merchant as follows some
[1:38] of our Merchants have multiple
[1:40] businesses for example a franchise so we
[1:42] also introduced a team ID so we can
[1:45] easily link multiple Merchants to a
[1:49] team here are a couple of lessons
[1:51] learned the hard way when working with
[1:53] this tenant identifier column lesson
[1:56] number one is don't use distant
[1:58] relationships Merchants love love to
[2:00] look at reports for example which
[2:02] product was sold the most for the past 7
[2:05] Days compared to the 7 days before and
[2:07] our schema looked a little something
[2:09] like this we had a team table we had a
[2:11] Merchants table we had an order table
[2:13] and we had an order products table
[2:16] Merchants were linked to the team
[2:18] through the team ID orders were linked
[2:20] to the merchant and to the team through
[2:23] the merchant ID and through the team ID
[2:25] and our order products were linked to
[2:27] the order through the order ID in an
[2:31] effort not to repeat myself I didn't
[2:33] include the merchant identifier nor the
[2:35] team identifier on the order products
[2:37] table because I figured I could access
[2:39] the order products on the merchant
[2:41] through the orders using a h many
[2:44] through relationship the way a has many
[2:46] true relationship works is that we have
[2:49] our Merchant model that has many orders
[2:51] and orders has many order products and
[2:53] we can access the order products through
[2:56] the orders on the merchant using a has
[2:59] many true relationship if you take a
[3:01] look at the merchant model we can see
[3:03] that we have a direct relation on the
[3:05] orders using a has many relationship and
[3:08] order products is a so-called distant
[3:10] relationship which allows us to access
[3:13] the order product through the order and
[3:16] while distant relationships work
[3:18] beautifully it has a massive impact on
[3:20] query performance because the complexity
[3:22] of the query drastically increases
[3:25] instead we refactored all our distant
[3:27] relationships and added a tenant
[3:29] identifier on literally every single
[3:33] table so in this case by adding a
[3:35] merchant ID and a team ID we are able to
[3:38] access order products on the merchant
[3:41] and even on the team using a has many
[3:44] relationship this reducing query
[3:46] complexity this Merchant and team
[3:48] identifier is applied to every single
[3:50] table we have in our database schema
[3:53] because we learned the hard way that
[3:54] when doing reporting queries it's best
[3:57] to jump through as few Hoops as possible
[4:00] lesson number two is to be extremely
[4:02] careful with orware queries in this type
[4:05] of single database multi-tenant setup
[4:08] the orware query should be used with
[4:10] caution because if you don't scope your
[4:12] queries correctly you'll leak orders
[4:14] from other merant let's take a look at
[4:17] an example in the example shown on
[4:19] screen we're counting the orders of the
[4:22] mergent with ID one where the type
[4:25] equals takeout or where the status
[4:27] equals completed if you take a look at
[4:29] at a raw query we'll see that we'll
[4:32] select the orders where the merchant ID
[4:35] equals in our case one and the merchant
[4:37] ID is not null and the order type equals
[4:41] in our case takeout or the status equals
[4:44] completed now because of the way MySQL
[4:46] interprets this query it will find the
[4:48] orders where the merchant ID equals 1
[4:51] and the order type equals takeout or it
[4:54] will take every single order where the
[4:57] status equals completed also the one of
[5:00] the other Merchants so it's super
[5:02] important to scope the query correctly
[5:04] using the wear method on the orders
[5:06] relationship using this wear method
[5:09] we're essentially adding parentheses in
[5:11] the Raw MySQL Query and if we take a
[5:14] look at the resulting raw MySQL Query
[5:16] we'll see that the order type in our
[5:18] case should be take out or the status
[5:21] should be completed but we removed the
[5:24] or statement and we added these
[5:26] parentheses and this way MySQL is a able
[5:29] to scope the query correctly lesson
[5:32] number three is to pay extra attention
[5:34] to the indices our orders table quickly
[5:37] grew to tens of millions of rows and
[5:40] obviously our Merchants expect Snappy
[5:42] performance on those Peak moments when
[5:44] they're preparing dozens of orders per
[5:47] hour to be able to guarantee this it's
[5:49] important to profile every single query
[5:52] that gets executed and add the necessary
[5:55] indices before you even start thinking
[5:57] about caching a single thing if thing we
[6:00] always did was add a foreign index to
[6:02] the merchant ID and the teams ID column
[6:05] but often we added composite indices to
[6:08] really squeeze all the performance we
[6:10] can out of a query to figure out which
[6:12] index MySQL uses for a query you can add
[6:15] the word explain before it and in the
[6:18] result MySQL will tell you which index
[6:20] it used to execute your query and how
[6:23] many rows it had to look through unique
[6:26] indices for example a slug also have to
[6:29] be scoped to the tenant otherwise you'd
[6:31] get unique constrainted errors when the
[6:33] same value already occurs with the other
[6:36] tenants and lesson number four is don't
[6:39] use Global Scopes Global Scopes
[6:41] initially sound like an amazing idea you
[6:43] can apply a scope to all queries without
[6:46] repeating yourself however I found
[6:49] myself removing the global Scopes more
[6:51] often than not using the query without
[6:54] Global scope method for example for our
[6:56] internal reporting we decided to never
[6:59] use Global scopes for Tenon specific
[7:02] queries and we only used Global scopes
[7:04] for Global query constraints like for
[7:07] example only querying the orders of The
[7:10] Last 5 Years orders that are older than
[7:13] that are considered archived and we Mark
[7:16] those orders with an archived at date
[7:19] kind of like soft elets and apply a
[7:21] global scope on it for all other
[7:23] purposes we opted for local Scopes and
[7:26] applied them manually where needed
[7:31] now that we talked about the database
[7:33] setup let's dive into another big aspect
[7:35] of a multi-tenant application and that
[7:38] is tenant resolving we provide a webshop
[7:41] for every tenant that can be visited
[7:43] using a dedicated subdomain or a custom
[7:46] domain we provide every tenant with a
[7:48] free subdomain out of the box and using
[7:51] middleware we are able to figure out the
[7:53] tenant when someone visits the subdomain
[7:55] so let's take a look at our Merchants
[7:57] buy domain middleware we have a domain
[7:59] stable in our database that Maps a
[8:02] domain or a subdomain to a merchant our
[8:04] Merchant by doain middleware is able to
[8:06] figure out which domain or subdomain is
[8:09] linked to which mergent and then injects
[8:12] the resolved mergent into the service
[8:14] container if the mergent is suspended
[8:16] for example when they don't pay their
[8:18] invoice we throw an exception and render
[8:20] a specific view for it and when no
[8:23] Merchant is found we throw a merchant
[8:25] not found exception which essentially
[8:27] renders a 404 when Merchants prefer to
[8:30] have a custom domain we have a reverse
[8:33] proxy setup that will handle SSL
[8:35] certificates and proxying to our system
[8:38] with minimal effort we use a tool called
[8:40] CX which makes managing these custom
[8:43] domains a breeze but more on that in a
[8:45] future video we also have an API that's
[8:48] accessible through a tenant specific API
[8:50] key and in a similar fashion we apply a
[8:53] merchant by API Key Middle bear that's
[8:56] able to resolve a merchant from an API
[8:58] key and instead of the domain we use
[9:00] this API key in many of our services for
[9:03] example our iPad Point of Sales app it's
[9:06] important to pay attention to jobs
[9:08] dispatched onto a queue from within a
[9:10] tenant context because they are not
[9:13] tenant aware by default to provide the
[9:15] job with the necessary context we always
[9:18] pass in the current Merchant as an
[9:20] argument so we're able to access the
[9:23] merchant model inside of the job Handler
[9:25] and that concludes the video if you
[9:28] found this content helpful or insightful
[9:30] give it a thumbs up and consider
[9:32] subscribing I may do a second part
[9:34] because there's just so much more to
[9:36] talk about I created an entire video
[9:39] about how a flesh sale on a single
[9:41] tenant took down our entire system so if
[9:43] you're interested to learn more about
[9:45] that specific issue I'll link it in the
[9:47] description thank you for watching and I
[9:49] will see you in the next one