2000 SQLite databases distributed globally sounds like an ops nightmare, and you have good reason to think that. However, there is a legitimate reason to be excited. Like they say at the gym, "No pain, No gain."
I am more of a "gain" type of guy, so I will completely ignore any downsides and focus on potential solutions to common problems I discovered after experimenting with distributed SQLite databases.
Check out the original post from here.
Active Search
Active search means a search that updates results while you type. Usually, it's easier to create these with some client-side trickery, and thus, a JS framework or caching library is a must.
Using edge-based distributed databases means you can keep your server and database close to your users. This setup makes it totally doable to run things with just HTML—or HTMX if you're into the cool stuff.
Multi-Tenancy
Multi-tenant applications have multiple users and their organizations within the same application. Think Google Workspace with its users and organizations.
The challenge is that users should only access data from an organization they are part of. A common way to do this is by having an organization_id field in every database table and querying them with WHERE organization_id = ?. While this is a simple way to do it, it has two main problems:
- Extra query params in every database query
- Developers should never forget to add them, or all hell breaks loose.
With distributed SQLite databases, you could have a dedicated database per organization or tenant. This means that there will be a ton of databases, but with Database-as-a-Service providers such as Turso, it is done for you. It could provide the following benefits:
- Simple queries.
- Data is always only for that organization.
- Data can be provided close to the actual user organization. If you are a Swedish company, you could have your data and server in the Stockholm data center.
- Canary releases with DB migrations are easy.
- It will be GDPR and CCPA-friendly.
- Data exports for an org are a breeze; it's just a file.
Of course, there may be some downsides to managing 2000 databases instead of three. Especially migrations could be a pain. That is, however, a future me problem and thus will not impact my enthusiasm for bleeding-edge tech.
Testing Environments
Usually, creating a dedicated testing environment takes a lot of work. Thus, many organizations have just a few of them, and then developers and QA trade turns in accessing them, and breaking one of them may lead to significant delays in releases.
It's challenging to have multiple of these because:
- Big server environments cost money if they need to run all the time.
- Complicated configuration for all server environments.
- Databases are slow to copy and hard to maintain multiple ones.
With Sqlite databases and edge servers like on fly.io, which allows scaling to zero, you could have testing environments galore. Per organization, the DB is smaller by default, so it's fast to copy. Also, it's just a file, so you don't need fancy equipment. With these two combined, you could have:
- Testing environments per feature.
- Devs could have their own environments. They could even omit local setups and run their own on-the-edge environment while working.
This setup would also allow testing in the actual environment faster and thus remove many errors from the "well, it works on my computer" type of situation.
Of course, you still would need to keep source data consistent and GDPR friendly and remember to shut down the dev environment so that your CFO won't get a heart attack. Though you could have a deadman's switch on dev environments and thus remove the human element, plus anything with a deadman's switch is exponentially much cooler than without!
Conclusions
Distributed SQLite databases are cool and could help with some problems that have plagued the industry for a while. They aren't a silver bullet, and it's a fairly new tech, so you should weigh in on whether the solutions provided outweigh their downsides and risks. For some of us, it will be worth it and even preferable.
Having companies develop new and ingenious ways to push the industry forward and expand how we think solutions to problems is always a feat to be applauded. All hail my 2k Sqlite databases!
Have you tried using distributed SQLite databases? Have you solved a problem like this or come up with a completely new solution using them? If you have, comment below!
Antti Kaarnikka
Co-Founder | Tech lead at Quteo
Ps. Check out the code from here https://github.com/hanshoi/go-turso
Top comments (0)