If you're not familiar with the phrase multi tenancy, this is basically every traditional SaaS application applications unless you applications unless you're building something in house. If you're building something in house like a back end CRM or some sort of process automation on the back end, it's likely only used by the company that you're working for and that would be a single tenant. Now, historically, when it comes to multi tenancy, you have 2 options at the database layer. The first is shove everybody in 1 database. Totally viable.

I'm not going to besmirch that at all. It's completely reasonable. You would separate your data by having a tenant column. Now your tenant could be called, team, company, could be project based tenancy, could be user ID based tenancy, but everything that is related to the tenant has to be to get back to that tenant ID somehow. So if you are let's say that all your data is separated by user, well then your projects need to have a user ID, your comments might need to have a user ID.

Anything that is the top level needs to have a user ID so that when the user logs in, you can say select star from projects where ID equals 1 and only get back those projects. That is completely reasonable. The, benefits are we'll cover benefits and drawbacks of both. The benefits are it's very easy to manage because you're just, adding rows to a database, and we know how to do that. The drawback is your queries then become more complicated.

So you're not eliminating complexity, you're just trading it. So every query that you run, you better be gosh dang sure that you're including the tenant ID because if you miss a where tenant ID equals x, boom, you've just shown everyone's data to everyone else and that's a bad bad spot to be in. Normally, you can work around that with something like a global scope in your web application framework, such that everything is automatically scoped to tenant, otherwise it throws a nasty exception and everything grinds to a halt. That's totally fine. That's, that's single schema or single database tenancy and the tenancy is enforced through code.

I don't mind that at all. We're not gonna do that. That model doesn't line up super well with SQLite because as we've talked about over and over and over, there's contention. With SQLite, there is contention. So if you have a bunch of tenants and all of them are moderately busy, they're all fighting each other to get that single right lock.

Right? You can only have 1 writer at a time and all the tenants are fighting each other for it. So instead, with SQLite, what we're gonna do is a database per tenant. And this is neither better nor worse. It just has different trade offs.

It is more aligned with the SQLite model, but the trade offs are, the tenancy becomes harder to manage and operate. But then on your code side, it's very simple because anytime someone logs in and they're selecting from their tenant database, you're guaranteed that all the data they're seeing belongs to them because they're the only, they're the only tenant in that database. This does mean you will have as many databases as you have users, which sounds just like completely insane. But when you remember that SQLite is just a file, that's not that bad. You're gonna have a 1,000.

Maybe maybe you're a genius and you end up with a 100,000 users using your SaaS. That's not that bad. That's totally viable. So we're gonna look at a few methods for, making that easier because you have to you have to think about all the things that you have to do, and primary among them is migrating schema across multiple different databases or potentially also sharing, some resources with every single tenant database. Maybe you have a centralized database that handles something like auth and tenant routing and then the tenant databases hold all the user data.

So this is, frankly this is somewhere where Turso really shines. I'm always going to teach you vanilla SQLite because that's what this course is about, but I do have to tell you that Tersa really shines with this because you can have one central database and then basically create schema copies of that. So you can hit their API and say, hey, I got a new tenant, and they say great, we're gonna model it off of this central database that you've given us and we're gonna create a new tenant. And then also, you can run migrations across all your your child databases. So it makes it a lot easier.

If you want to not experience pain, check them out. I'm not gonna show you that because this is a course about SQLite. So let me show you some ways that you can potentially manage this yourself. You will have to write a little bit of infrastructure yourself, but you're a programmer. You can do that.

Alright, let's take a look. I'm gonna hit you with 2 things that could be helpful for rolling your own multi database multi tenancy. The first thing is we're gonna open up, this database dot SQLite and if we hit dot databases, you'll see all of the databases that are open. Now you may be thinking, what do you mean all of the databases that may be open? Well, you can attach other databases.

So imagine a scenario like this. We're going to attach, test dot SQLite in that same directory. You do need to put it in parentheses. Nope. In quotes.

And we're gonna, attach it as, we'll attach this as the central database. So if you attach that and then run databases again, you'll see now we have our main database, which is gonna be we'll say that the main database is your tenant's data. So all of my data is in database dot SQLite and then in the central database, you've got stuff like global settings and maybe the actual tenant routing layer. So if we clear this out, I always do that, if we clear this out then we can do select star from and we named it central and we can say central dot tenants, because I know that that's a table that's in there. And give me box mode and let's run that again.

You can see that my sub domain is aaron. So you can already imagine how this works in a SaaS scenario. So maybe you go to aaron.yoursas.com and then it looks up in this tenant table what the actual database is. And then for the rest of my session it's going to, connect me to that particular database. So you can keep your tenant routing central, but you can also have stuff like if you were to be within the context of a tenant.

So I'm Aaron. I'm on aaron.yoursasdot com, I'm doing stuff, and I need to look at some centralized global setting, that can also be a good time. I think it's settings. That can be another good thing to have in this attached database here. So you can keep common data that needs to be the same across all tenants.

Now if you have local settings where I could change my session time out to 60 and somebody else could change theirs to 10, you don't want to put it in this attached database. You wanna put that in the tenant's database itself. But the idea that you can have a tenant database and then a centralized like master control database can be really helpful. One of the great things about using a multi database setup is you can have, data authority. You can put my database in the United States and you can put someone else's database in Germany and that's a really nice way, one, to make sure that it's, you know, fast because if your if your tenant lives in Germany, having their database in Germany is pretty awesome.

But if they live in Germany, they might need their data co located or rather stored only in the EU. I don't know all the laws. I hope I never have to learn all the laws. But if that law applies to you, this can be super helpful. Again, with Terso you can place those databases anywhere that you want.

There's one other thing I want to show you here when it comes to, managing your own multi database setup, and that is how are you gonna roll out migrations to all of these tenants? That's gonna be kind of a pain. There's one other thing we need to talk about when it comes to having a multi, database tenancy system and that's how do you roll out migrations to all of these databases. That is gonna be kind of a pain and you're gonna have to roll a little bit of your own infrastructure around that, but there's one thing I wanna show you that can be helpful as a part of that process. The first the first thing I've already shown you is having a centralized repository that holds all of your tenants.

That's gonna be pretty vital because you're gonna need to know what all the databases are so that you can loop through them. There's another thing I wanna show you, and that is this, this Pragma user version. So if you run that, you'll get back likely the number 0. I've already set this one to 1 but you'll likely get back 0. And this is, honestly, it's kinda awesome.

You're in control. SQLite does not use this at all ever for anything. It says as much in the docs. That's why it's called the user version. There are other schema versions and application ID's.

There there are other things that SQLite uses. This one is totally up to you. So you can imagine if you're rolling out a new migration across a fleet of a 1000 SQLite databases, you kinda need to know which ones have been done and which ones haven't been done and this might be a nice surefire way to track within the tenant database itself what version this schema is on. Now, you can only use the user version for one thing. So if you're versioning anything else and storing that data here, well you you're kinda hosed.

But if you're not using it and most people aren't, you could use this to keep track of the schema version that your tenant is on and whether whether or not they need to upgrade. So you could use this to to do, rolling upgrades, such that you upgrade a few people and then you make sure everything's okay and then you roll it out to the rest of the people. Or you could you could use it as sort of like, to sync up to your application state. So you can write some application code that says, well, hey, hang on, if I'm not on, if I'm not on schema version 2 yet, actually don't go down this code path, go down this code path and we will wait for the schema version to catch up before this code path starts to get executed. So those are a few things that you can use to help you roll your own multi database, multi tenancy.

You are gonna, you're gonna have to, you're gonna have to write some code around it. And, that's, I think that's completely reasonable. You know how to create a file. You know how to talk to a SQLite database, that's totally fine. With Terso you can hit an API to create a new database in a certain region that is a child of a parent database and that makes it really easy, but I'm fully aware that not everyone is using Terso.

So hopefully, these primitives get you along that path and if you think of something, that helps you even more, please let me know and I will do another video on it.