SQLite Works in Production… But Drogon Makes It Hard

SQLite has gotten so good, that companies like 37signals are now using it in real apps in production.

I want to do the same thing, but with C++ and the Drogon framework. In theory, it’s simple. But, there’s a nasty interaction between how SQLite is configured, and Drogon’s connection pools that makes it annoyingly hard.

Here’s what’s going on. And, how to work around it.

Using SQLite in Produciton

Two things are needed to set up SQLite for production web app use:

1. Use the right configuration, because SQLite’s defaults are optimized for maximum backward compatibility, not performance
2. Set up a live database backup system such as Litestream, to protect your data from hardware failure

Sounds easy, right? Here’s where it gets complicated…

The Problem: SQLite’s Configuration Method & Drogon’s DB Pools

Other databases store their configuration in config files. SQLite, doesn’t. Instead, you execute a series of pragma queries, such as:

PRAGMA journal_mode=WAL;
PRAGMA foreign_keys=ON;
PRAGMA busy_timeout=5000;
… a bunch of others …

This still looks easy. But, only WAL mode gets saved to the database. All the other settings need to be reapplied every time you open the database.

Here’s where we clash with drogon’s database connection pool…

Drogon uses a database connection pool, so that you can run multiple queries simultaneously. When you submit a database query, it sends it via one of the connections in the pool. You can’t control which one.

So, if you simply ran the pragma queries one by one, you don’t know which connections they’ll be sent on. And you need them to be run on EVERY SINGLE DATABASE CONNECTION.

Solution(s)

There are four possible solutions:

1. Create a connection pool of one. One connection, means that all the initialization queries will be executed on one connection. Problem solved. Alas, now all database queries get serialized, and you no longer get the benefit of multiple read queries. It doesn’t affect database writes, because SQLite only allows one write at a time anyway. Still, serializing database reads sucks
2. Modify drogon itself to execute the pragma queries when each sqlite database connection is opened. Very effective, but I try not to modify third-party dependencies because it tends to make updating them hard
3. Compile my own SQLite with the defaults baked in
4. Do it the hard way: Find a way to force Drogon to send the queries on all connections in a pool

I really wanted to choose option 3, and build my own SQLite with the preferred defaults. But, there was one per-connection setting that I need to be able to toggle at runtime

So, I still needed option 4. Here’s how it works:

Lets say that we have 5 database connections in the pool

1. I start 5 transactions to the pool. Each transaction locks up a connection in the pool. Now there are no free connections left
2. Next, I submit one of the pragma queries to the pool directly, 5 times. There are no free connections, so they will be queued
3. Now, cancel all 5 transactions. This frees up all connections in the pool. Each connection takes one query off the queue. Voila! The pragma query is executed on all connections
4. Wait for the queries to be done. And repeat for all other pragma queries
5. This should almost guarantee that the pragma queries are run on all connections. I say almost, because it depends how Drogon handles the pool internally. It may also break in future, if Drogon changes things later. For example, if transactions were to no longer reserve a connection, then we’d be screwed…

At this stage I’m sure someone will comment, hey Hans you idiot, why don’t you just run the queries inside the transaction?

I tried that, and SQLite didn’t like it. Some of the queries can be run inside a transaction, but not all of them. So, the procedure above is still needed.

Final Comments

What do you think of this solution? It works well, although I do have feel a little uneasy that future changes to Drogon might break this. I’ve submitted an enhancement request to the drogon project, which may one day turn into a complete solution. More thought is needed about the exact shape of that solution, because this problem is specific to the sqlite database backend. It’s irrelevant to all the others.