Don’t get me wrong, I’m not saying to use a single SQLite database file in today’s so-popular microservice architecture. Go for PostgreSQL in those cases (and stop writing “microservices” — services is just enough). But I really love (yes, love) to begin my small side projects with SQLite.

To my surprise, SQLite is actually relatively young compared to other major SQL databases like the already mentioned PostgreSQL, MySQL, Oracle, or MS SQL Server. Yet, it’s considered a very mature database engine and is actually the most used database in the world, as it’s embedded in every single smartphone, major browser, and operating system. That being said, SQLite is meant for exactly those local places.

So why the hell would anyone use SQLite for a non-local concurrent environment? I’d been asking this question for some time. Back then, I usually automatically opted for PostgreSQL or MySQL as the so-called standards for FE ↔ BE web applications. But here are some truths:

• Your app won’t make it beyond your machine, so why are you running a whole PostgreSQL instance in Docker?
• Your app ends up running on your VPS. Will you install PostgreSQL there? Will you spin it up in Docker just as you did for your local machine? Do you see why that might not be the best idea?
• Your app gets deployed to some cloud provider. And in that case… whatever.

Basically all my side projects fall under the first two options. And even though self-hosting solutions are great these days (Dokploy, talking about you) and provide you a reliable way to run your own SQL database with proper backups and recovery scenarios, it still feels like extreme overkill for applications that have literally zero or a very small number of users.

And so I ended up trying SQLite as a starter database for almost all my projects. It has limited capabilities compared to my second favorite, PostgreSQL, but I don’t care in most cases. But my perfectionist tendencies and fanciful, grandiose plans were still there, and I kept asking myself the most ridiculous question:

What if I make this application public, it gets huge traffic, and I’ll regret so much that I went for SQLite instead of PostgreSQL?

Disclaimer: never happened and probably never will. Yet, I must have a backup plan for such a fictional scenario because… I just have to, alright?

WAL

Well, it turned out SQLite can actually handle concurrent access rather well if configured properly.

By default, SQLite uses something called a rollback journal to implement transactions. Very simply put, when you make a write transaction to SQLite in this mode, the original database content is stored in the rollback journal (just another file), then it writes your changes into the original file. On COMMIT, the rollback journal is removed (or not, depending on the journal mode). And in case of failure or ROLLBACK, the rollback journal is loaded back into the original file to revert all changes already made within your transaction.

Write-Ahead Log (WAL) handles transactions differently. Your write transactions don’t modify the original database file like they do with the rollback journal. Instead, your changes are recorded in a separate file (hence “write-ahead”) and transferred to the database file later. A COMMIT indicates a successful append of your transaction to the WAL. This allows readers (actually all readers) to still read the original database file without being blocked by the writer. They can even read the WAL file itself (quite effectively, using something called wal-index). This setup still allows only a single writer (appending to WAL must be synchronized).

Okay, so how do the changes logged in the WAL get to the database file? SQLite calls this checkpointing — the moment when all recorded changes transfer to the database file and the writer can start from the beginning again. Checkpointing happens automatically once the WAL file reaches a certain size. It basically checks whether the very last COMMIT exceeds the size threshold and if it does, the transfer starts. That might mean your COMMITs are usually very fast, but from time to time one is slower (yet, I don’t think it’s noticeable in the context of web applications with a reasonable size of write transactions). It’s possible that some reader is currently peeking into the WAL file, so the checkpoint might transfer only part of the WAL and continue when the rest is free.

All you need to do is run a PRAGMA statement:

PRAGMA journal_mode=WAL;

Unlike other journal modes, setting WAL mode is persistent, so you do it once with your database (e.g., right after creation) and all connections will use WAL mode as well.

There are advantages and disadvantages of using WAL with SQLite, but I find the concept and trade-offs surprisingly easy to understand. And yet, you’re able to validate your idea or product with a significant number of real users. Just with a single file. And if SQLite stops handling your load properly, switching to PostgreSQL should be easy — and mainly, worth the price by that point.

I encourage you to go through the official documentation of SQLite’s Write-Ahead Logging if you’re interested in the topic.