Why you should probably be using SQLite

Where you store your application data has enormous impacts on your entire application. There are implications on the entire stack based on what you decide to use here.

There are terrific solutions like MySQL and Postgres that have become the default for many people. These are fine solutions. But for most web app use cases, you could drastically simplify your life by using SQLite.

SQLite is a sql-based database with a particularly unique feature: the entire database is in a single file. Largely for this reason, many people have historically seen it as a simple database for simple use cases.

However, in recent years SQLite has received a great deal of development and attention making it a simple database for even more advanced use cases. Let’s talk about some of those advancements and why for most of us, SQLite is the best balance of trade-offs.

Zero Latency

The fact that SQLite is a single file on disk comes with a major benefit of 0 latency which drastically reduces the "n+1 problem." This means that as a developer you don’t have to spend so much time worrying about reducing the number of queries you’re making to the database (which can lead to less efficient queries and certainly less efficient developers).

And latency itself is not to be underestimated. If you’re not thoughtful about the distance between your database and your application, you can easily put a baseline on your application’s performance. It’s not uncommon for the app-to-database latency to be measured in the tens of milliseconds (and sometimes even hundreds). This means no matter what you do, your page can’t load with fresh data in less than that amount of time.

I know performance isn’t the number 1 priority of all apps, but it’s pretty important for most. I’m pretty sure even though the business executive who paid for your app and doesn’t have alternatives doesn’t like loading spinners flashing in their face for every interaction. 🌀😠

One Less Service

One huge benefit to SQLite is the fact that it runs as an embedded part of your application. So that’s one less service to babysit. Honestly, that was my primary motivation when I decided to migrate my own website from Postgres to SQLite. I just stick SQLite in the same volume I’m mounting for other data for my application already and I’m off the races.

This saves on complexity as well as cost.

And this is multiplied by the number of instances and replication considerations, but we’re getting ahead of ourselves… Let’s get into that…

Multi-instance replication

As a file on disk, you cannot "distribute" SQLite directly. However, this is where there have been advancements in this space. For my own applications that need multiple instances, I use LiteFS:

LiteFS is a distributed file system that transparently replicates SQLite databases. You can run your application like it’s running against a local on-disk SQLite database but behind the scenes the database is replicated to all the nodes in your cluster. With LiteFS, you can run your database right next to your application on the edge. You can run LiteFS anywhere!

On top of this, LiteFS has built-in support to handle the "Read Replica Consistency" challenge. So if you need your app to run in multiple instances, you need to use one of these tools.

Another solution here is Turso which uses SQLite under the hood and even has a concept called "embedded replicas" for zero latency reads. Very cool!

Database size

Another issue people sometimes bring up is database size. However, SQLite is capable of handling databases that are an Exabyte in size (that's one million Terabytes, or one billion Gigabytes 🤯). Most of us web developers don’t work with near that amount of data. You’ll have much different problems before database size is one of them with SQLite.

Even putting large amounts of data in a SQLite database record is pretty efficient! In fact, in some cases, it can be faster to retrieve data out of a SQLite database than from the file system 😆 SQLite is an incredible feat of engineering!

Development and Testing

I know some of you reading this right now are perfectly comfortable running docker compose as a regular part of your workflow before you start developing your application. But you have to admit that it’s annoying to run multiple apps at once with conflicting databases and ports. With SQLite, it’s a total non-issue. It’s just a file. So you can run multiple instances of the same app at once with no trouble whatsoever.

Also, while starting up a new Postgres database is fairly involved (there are of course tools that make it easier), SQLite has no such issue. Again, it’s just a file. Run your production migrations/seed and you’re good to go.

And this ease carries over to the testing side of things as well. When your database setup is complicated, you end up spending time evaluating ways to mock out your database at the ORM level so you can avoid having to run and connect to your database during testing and the isolation issues that can happen as a result of that.

Not a problem with SQLite. It’s just a file! Each test can have its own database with barely a thought. Just a little bit of code to create and connect the database and then your tests can run on the full database. You can definitely do this with other databases, but not as easily and efficiently as SQLite.

Weaknesses

SQLite is not without its shortcomings. And it would be unfair of me to just talk about where SQLite shines without addressing some of those.

• SQLite does not support subscriptions which can be a limitation on certain real-time use cases. However, there are plenty of reasons to recommend against using database subscriptions for real-time use cases anyway. Scaling real-time use cases is quite challenging, and personally have really enjoyed letting Partykit do that part for me in my apps.
• SQLite being a file on disk does make connecting from external clients effectively impossible. But with Fly.io at least, it’s easy to run prisma studio on the production server and proxy that for local access. If you need to connect to it from another app, then you’re out of luck and have to set up HTTP endpoints on the host app for any data you need (for now).
• SQLite does not support plugins like TimescaleDB for Postgres. While time-series data is possible with SQLite, I do not have experience with this use case and can't speak to the challenges there. My intuition says it's not advisable to use SQLite for that use case, but maybe someone else can offer me more insight.
• SQLite does not support enums which means you're forced to use strings. I have mixed feelings about this, but I mostly don't like enums anyway. The main drawback to this is when it comes to the typings for the client which doesn't allow you to ensure all values of a column are only within a set of specific possible values for the string. However, with Prisma client extensions, handling this kind of enforcement at the client (and typing) level is possible.

Conclusion

So, can you use SQLite? For the vast majority of you reading this, the answer is “yes.” Should you use SQLite? I’d say that still for the majority of you reading this, the answer is also “yes.” Though it’s complicated. As with everything in technology, there are multiple solutions that will work out. But I think you should give SQLite a serious look due to the performance, simplification, and cost benefits I’ve mentioned.

And that’s why I’m using SQLite for my own applications and why I teach you to use SQLite in the EpicWeb.dev series of workshops.

– Kent