Give each of your users their own SQLite database
Journey from multi-tenant to SQLite single-tenant architecture, inspired by DHH's vision. One user, one database.
DHH argues in this post that the complexities in modern web services come from the need for a single system to accommodate serving a multitude of users. He suggests that with the current advancements in computing power, many organizations could effectively utilize a per-tenant model, where each system serves one user, to simplify data storage and access. But, can we take it further?
I was skeptical at first, even before joining the team at Turso, that SQLite per-tenant was ever going to be a thing. But, after seeing a rise in SQLite usage, I wanted to see how practical it would be to set up SQLite per-tenant in my own app to make up my mind.
A while back, I developed an application using a single database for all users - a multi-tenancy approach. Intrigued by what some are dubbing as a paradigm shift, I wanted to see how easy it would be to refactor my code to support a different model - a different database per user. It sounds crazy, right?
My single “Shared Database” contains tables that have a user_id column that I use to reference rows to specific users. This means almost every SQL statement contains WHERE to scope data to the logged in user, standard stuff, you get it. If I were going to pull off migrating from a single database to one per-user, I needed to DROP COLUMN user_id and create some kind of database that could be used as a “template”.
I've had my fair share of fixing bugs caused by a missing WHERE, so it felt good to be able to remove it.
It certainly felt at this point I was headed in the right direction. All that was left to do was write some code that runs when a new user signed up.
Transitioning to a per-user database model with SQLite was simpler than expected, challenging the long-held norm of multi-user databases.
As I delved deeper into the app migration adventure, I couldn't help but think it wasn't going to be all rainbows and butterflies. What's the deal with migrations and playing peeping Tom on user analytics? It's super important that I apply schema changes that play nice with everyone. And let's not forget, when it's showtime for my startup funding round, I need the juicy gossip on how many to-dos my users are popping out.
SQLite boasts a pretty cool feature that is ATTACH which allows multiple databases to be accessed simultaneously within a single session. Could this be the answer? It's worth exploring for sure. Using ATTACH means I get to keep my users data isolated, queries simpler, and have the ability to perform reports across all my user databases, but does make migrations a little more complicated.
DHH tossed a pretty neat idea into the ring, but going the step further to give each user their own database could be my new routine.
Turso gives you 500 databases for free, so try it out yourself.
