Sqlite gives you the power of a database without the hassle of running a server, and it comes with some hard constraints in return. Only one writer at a time, never on a shared file system, and WAL mode needs handling with some care.
None of that stops you building a server which is highly concurrent, as long as you serialize the writer, keep one binary that writes to the file, and let the readers hit the db directly. The way I do that is with channels, and what follows builds it a piece at a time until you have something you can actually run. If you'd rather skip ahead to the finished thing, it lives at tee8z/sqlite-web-starter, and the code here is a stripped down version of it with the HTML, the asset pipeline, and the deployment bits taken out.
There's a little more on how I got here in Hello World.
The shape of the thing
The part that matters here is what references a request handler is allowed to hold. It gets a cheap handle it can clone, and that handle carries a read-only pool along with the sending end of a queue. There's no writable connection anywhere in it, and no method on it that will hand you one. So when a handler wants to write, it has to ask and then wait for an answer, which is the whole trick. Everything from here on is working out the details.
Building it from scratch
1. The project
cargo new sqlite-queue-demo
cd sqlite-queue-demo
mkdir migrations
[package]
name = "sqlite-queue-demo"
version = "0.1.0"
edition = "2024"
[dependencies]
anyhow = "1"
axum = "0.8"
serde = { version = "1", features = ["derive"] }
sqlx = { version = "0.9.0", default-features = false, features = ["runtime-tokio", "sqlite", "macros", "migrate"] }
tokio = { version = "1", features = ["macros", "rt-multi-thread", "net", "signal", "sync", "time"] }
tokio-util = "0.7"
That's the whole dependency list, and there's no database server to install alongside it, which is the thing we're trying to hold on to as we go.
2. The schema
migrations/0001_initial.sql:
CREATE TABLE IF NOT EXISTS counter (
id INTEGER PRIMARY KEY CHECK (id = 1),
value INTEGER NOT NULL
);
INSERT OR IGNORE INTO counter (id, value) VALUES (1, 0);
A counter living in a single row is about the meanest thing you could test this with, since every write in the whole system ends up fighting over the exact same row. Nothing gets to hide behind a lucky spread of keys.
3. Two ways to touch the file
This is where those constraints from earlier actually get written down. Here's the top of src/database.rs:
use std::{path::Path, time::Duration};
use anyhow::{Result, bail};
use sqlx::{
Connection, SqliteConnection, SqlitePool,
migrate::Migrator,
sqlite::{SqliteConnectOptions, SqliteJournalMode, SqlitePoolOptions, SqliteSynchronous},
};
use tokio::sync::{mpsc, oneshot};
use tokio_util::sync::CancellationToken;
static MIGRATOR: Migrator = sqlx::migrate!();
/// Handed to HTTP handlers. Cloneable, and deliberately holds no writable connection.
#[derive(Clone)]
pub struct Database {
readers: SqlitePool,
commands: mpsc::Sender<Command>,
}
/// Owns the single writable connection. Exactly one of these exists.
pub struct Writer {
connection: SqliteConnection,
readers: SqlitePool,
commands: mpsc::Receiver<Command>,
}
struct Command {
reply: oneshot::Sender<Result<i64, String>>,
}
pub enum WriteError {
/// Rejected before admission. Nothing ran. Safe to retry.
Unavailable,
/// Admitted, but the reply was lost. It may or may not have committed.
OutcomeUnknown,
/// The transaction itself failed.
Database(String),
}
Notice that Database derives Clone while Writer doesn't. Writer ends up being the only thing in the program holding a SqliteConnection you can write through, and the only way to get one is Database::open, which hands back both halves at once:
impl Database {
pub async fn open(path: &Path, capacity: usize) -> Result<(Self, Writer)> {
if let Some(parent) = path.parent().filter(|p| !p.as_os_str().is_empty()) {
std::fs::create_dir_all(parent)?;
}
// The one connection in the process allowed to write.
let mut connection = SqliteConnection::connect_with(
&SqliteConnectOptions::new()
.filename(path)
.create_if_missing(true)
.journal_mode(SqliteJournalMode::Wal)
.synchronous(SqliteSynchronous::Full)
.busy_timeout(Duration::from_secs(5)),
)
.await?;
MIGRATOR.run_direct(None, &mut connection, false).await?;
// Readers cannot write: read-only open flags *and* a query_only pragma.
let readers = SqlitePoolOptions::new()
.max_connections(4)
.connect_with(
SqliteConnectOptions::new()
.filename(path)
.read_only(true)
.pragma("query_only", "ON")
.busy_timeout(Duration::from_secs(5)),
)
.await?;
let (commands, receiver) = mpsc::channel(capacity);
Ok((
Self {
readers: readers.clone(),
commands,
},
Writer {
connection,
readers,
commands: receiver,
},
))
}
There are four choices buried in there doing real work, and each one traces back to a constraint we started with.
journal_mode(Wal) is what lets the readers and the writer get along. Back in the old rollback journal mode, a write would block every reader for as long as it ran. With WAL the writer appends to a side file instead, and readers carry on against the last committed snapshot. Without it, letting the readers hit the db directly doesn't really work.
synchronous(Full) makes each commit flush to disk before it returns. The usual advice with WAL is NORMAL, which is quicker and still survives the process dying, though it can lose your most recent commits if the machine loses power. I'd rather a 200 OK on a write mean the bytes are down, so I take FULL and pay for it in throughput. It's a real trade either way, so it's worth making deliberately.
read_only(true) along with query_only is belt and suspenders. The read-only flag opens the file with read-only flags, so a stray UPDATE sent through the pool fails down in sqlite rather than waiting for someone to catch it in review. The query_only pragma refuses it a second time at the statement level. What I like about this is that "only one writer" is not a rule everyone has to remember, it is something the process is incapable of breaking.
busy_timeout(5s) catches whatever lock contention we haven't coordinated away. Inside this process the queue already keeps the writer from fighting itself, so this is really there for the readers, and for anything else that might open the file.
4. Reads skip the queue
/// Reads skip the queue entirely and go straight at the file.
pub async fn counter(&self) -> Result<i64, sqlx::Error> {
sqlx::query_scalar("SELECT value FROM counter WHERE id = 1")
.fetch_one(&self.readers)
.await
}
That's all there is to it. Reads never touch the channel and never wait in line, they just run against the pool at whatever concurrency it allows. Reads are the bulk of the traffic in most applications, so it's worth being clear that all the machinery in this post exists for the smaller slice that writes.
5. Writes are a round trip, and they use two channels
/// Writes are a request/reply round trip through the queue.
pub async fn increment(&self) -> Result<i64, WriteError> {
let (reply, response) = oneshot::channel();
self.commands
.try_send(Command { reply })
.map_err(|_| WriteError::Unavailable)?;
response
.await
.map_err(|_| WriteError::OutcomeUnknown)?
.map_err(WriteError::Database)
}
}
It's only a handful of lines, and the part that usually trips people up is why there are two channels here instead of one.
The mpsc is the shared, long-lived one. Many handlers feed into it and a single writer drains it, and that's where the serialization comes from. The catch is that an mpsc only carries work in one direction, so it can't bring an answer back. That's why we use the oneshot channel along side it. Each request builds its own private one and sends the sending half along with the command, so when the writer replies it goes down that private channel to the single handler waiting on it.
One detail worth calling out is try_send rather than send. If you use send it waits for a free slot, and under load you end up with a queue of handlers waiting to get into the queue, which shows up as a latency spike right when you can least afford it. try_send gives up the moment all 64 slots are full, which gives you somewhere to do admission control and lets you answer quickly rather than slowly. What you answer with turns out to matter a fair bit, and I'll come back to that when we wire up the handlers.
6. The writer
impl Writer {
pub async fn run(mut self, shutdown: CancellationToken) -> Result<()> {
let result = self.process(shutdown).await;
self.commands.close();
self.readers.close().await;
let close = self.connection.close().await;
result?;
close?;
Ok(())
}
async fn process(&mut self, shutdown: CancellationToken) -> Result<()> {
loop {
let command = tokio::select! {
biased;
() = shutdown.cancelled() => {
self.commands.close();
break;
}
command = self.commands.recv() => {
let Some(command) = command else {
bail!("every Database handle was dropped")
};
command
}
};
self.execute(command).await?;
}
// close() refuses new commands but keeps the ones already queued.
while let Some(command) = self.commands.recv().await {
self.execute(command).await?;
}
Ok(())
}
async fn execute(&mut self, command: Command) -> Result<()> {
match increment_connection(&mut self.connection).await {
Ok(value) => {
// The write is committed. A failed reply means the caller is
// gone and saw OutcomeUnknown, thus we log it: this is the only
// place that knows the write actually landed.
if command.reply.send(Ok(value)).is_err() {
eprintln!("committed counter value {value} but the caller had gone");
}
Ok(())
}
Err(error) => {
if command.reply.send(Err(error.to_string())).is_err() {
eprintln!("write failed and the caller had gone: {error}");
}
Err(error.into())
}
}
}
}
async fn increment_connection(connection: &mut SqliteConnection) -> Result<i64, sqlx::Error> {
let mut transaction = connection.begin().await?;
let value = sqlx::query_scalar::<_, i64>(
"UPDATE counter SET value = value + 1 WHERE id = 1 RETURNING value",
)
.fetch_one(&mut *transaction)
.await?;
transaction.commit().await?;
Ok(value)
}
There's no lock anywhere in here, because the loop is already doing that job. A single task owns the connection, so &mut self.connection hands you exclusive access for free and the borrow checker keeps it that way.
Two things in there are worth slowing down on. The while let sitting after the loop is the drain, and it matters because close() only stops new commands from being accepted while everything already queued still gets run. A caller whose write made it in gets that write executed even if the shutdown signal landed a moment later.
The other is the check on command.reply.send(...). A failed send means the caller went away before we could answer, and it's tempting to shrug that off, since the transaction has already committed and the client hanging up doesn't undo it. The catch is that this is the one place in the whole program that knows the write actually landed. The client saw a dropped connection and, if it asks again, it'll be told the outcome is unknown. So log it. That line is your only record that a committed write has a caller out there who doesn't know about it, and when someone turns up asking whether a charge went through twice, it's the first thing you'll want to grep for.
7. Wiring up HTTP
Here's the interesting half of src/main.rs:
/// One write takes roughly a millisecond to commit, so a queue of 64 drains
/// fast. A second is plenty, and it gives a retry storm somewhere to wait.
const RETRY_AFTER_SECONDS: &str = "1";
async fn increment_counter(State(database): State<Database>) -> Response {
match database.increment().await {
Ok(value) => Json(Counter { value }).into_response(),
// Nothing ran, so a retry is safe. Send a wait with it so the callers
// back off far enough for the writer to drain the queue.
Err(WriteError::Unavailable) => (
StatusCode::TOO_MANY_REQUESTS,
[(header::RETRY_AFTER, RETRY_AFTER_SECONDS)],
"write queue full; retry after the given delay",
)
.into_response(),
// Something may have run. The client must NOT retry blindly.
Err(WriteError::OutcomeUnknown) => (
StatusCode::INTERNAL_SERVER_ERROR,
"write outcome unknown; do not retry blindly",
)
.into_response(),
Err(WriteError::Database(error)) => {
eprintln!("write failed: {error}");
(StatusCode::INTERNAL_SERVER_ERROR, "database write failed").into_response()
}
}
}
#[tokio::main]
async fn main() -> anyhow::Result<()> {
let path =
PathBuf::from(std::env::var("SQLITE_PATH").unwrap_or_else(|_| "./data/demo.sqlite".into()));
let (database, writer) = Database::open(&path, 64).await?;
let http_shutdown = CancellationToken::new();
let writer_shutdown = CancellationToken::new();
let writer = tokio::spawn(writer.run(writer_shutdown.clone()));
let router = Router::new()
.route("/counter", get(read_counter).post(increment_counter))
.with_state(database.clone());
let listener = TcpListener::bind((Ipv4Addr::UNSPECIFIED, 3000)).await?;
println!("listening on http://localhost:3000");
let http = tokio::spawn({
let shutdown = http_shutdown.clone();
async move {
axum::serve(listener, router)
.with_graceful_shutdown(shutdown.cancelled_owned())
.await
}
});
tokio::signal::ctrl_c().await?;
// 1. Refuse new connections, let in-flight handlers finish their writes.
http_shutdown.cancel();
http.await??;
// 2. Only now close admission and drain what was already accepted.
// Cancel before dropping our handle: if the last Database were dropped
// first, the writer would see "all senders gone" and treat a normal
// shutdown as a failure.
writer_shutdown.cancel();
drop(database);
writer.await??;
Ok(())
}
That 429 is worth dwelling on, because my first instinct was a 503 and I think that instinct is wrong.
A 503 says the server is broken. A full channel isn't broken, it's busy, and it clears in milliseconds. That gap shows up first in the places you look when something goes wrong, because 5xx is what your dashboards count, what your error budget burns down, and what eventually wakes somebody up. Spending an incident on ordinary backpressure gets you an alert that tells you nothing.
It matters to the callers too. A fair number of HTTP clients ship a blanket "retry any 5xx with backoff" policy, so a 503 gets retried by machinery you don't own and can't tune, which is the last thing a queue under pressure needs.
Kubernetes itself, for what it's worth, doesn't care either way. The kubelet only looks at the probe endpoints you configure, so a 503 on POST /counter costs you nothing. What it does watch is the readiness probe, and a failing /ready has the EndpointSlice controller pull your pod straight out of the Service. At one replica that's the entire service gone. Which is the real lesson sitting underneath all this: keep transient backpressure well away from readiness. This app manages that, since /ready only fails when the queue is closed rather than merely full.
A 429 with a Retry-After does something more useful. It tells every caller how long to wait, which is the one lever you have for thinning out a request storm, and if enough of them honour it the writer gets the quiet it needs to drain. The header is the part doing the work here, since neither status code on its own tells anyone how long to hold off. RFC 6585 frames 429 around rate limiting, though it leaves the scope open and says a server may count requests "across the entire server", which is exactly what a shared write queue is counting.
Picking the number is a judgement call. A second is a reasonable default for a 64-slot queue against a writer committing in around a millisecond, and if you want to be cleverer you can derive it from your observed commit rate and queue depth rather than hard-coding it.
The other thing to notice is that there are two cancellation tokens rather than one. That ordering turns out to matter quite a bit, and I'll come back to it further down, since I got it wrong the first time through.
8. Run it
cargo run
Then throw twenty concurrent writes at the same row:
for i in $(seq 1 20); do curl -sS -X POST http://localhost:3000/counter & done; wait
{"value":1}{"value":2}{"value":3}{"value":4}{"value":5}{"value":6}{"value":7}
{"value":8}{"value":9}{"value":10}{"value":11}{"value":12}{"value":13}
{"value":14}{"value":15}{"value":16}{"value":17}{"value":18}{"value":19}{"value":20}
Twenty requests went out, twenty different values came back, and the counter landed on 20. There was no SQLITE_BUSY anywhere and no retry logic to write. The order they arrive in is whatever the scheduler felt like doing that second, but every caller got its own answer and every increment made it into the file.
Does the queue actually help?
Now, it's fair to be a bit suspicious at this point. Sqlite still only allows one writer at a time no matter what you build on top of it, and a channel can't invent parallelism that isn't there. What changes is what happens to everybody who isn't the one holding the lock.
The starter repo has a small lab for this. It opens two throwaway databases, sets busy_timeout to zero on both so contention shows up instead of getting smoothed over, holds each transaction for 75ms, then releases 12 concurrent writes at each one. Here's a real run of it:
curl -sS -X POST http://localhost:3000/contention
| Round | How writes reach the file | Committed | SQLITE_BUSY | Final counter |
| --- | --- | --- | --- | --- |
| Direct | 12 calls, 12 independent writable connections | 1 | 11 | 1 |
| Queued | 12 calls, one channel, one connection | 12 | 0 | 12 |
Eleven of the twelve writes in the direct round were rejected outright. A counter that should have reached 12 sits at 1 instead. The queued round committed all twelve and handed back the values 1 through 12.
Do keep an honest eye on the timings though. The direct round finished in 96ms while the queued round took 1.9 seconds, which sounds damning until you remember that twelve serialized 75ms transactions can't come in under 900ms, and synchronous = FULL adds a disk flush on top of each one. The direct round was only quick because it gave up on most of the work. This is a demonstration of what happens under contention rather than a throughput benchmark, and those queued times include the wait in line.
I should also be clear that the channel is one option among several here. A mutex around a shared connection gets you the same serialization, and a generous busy_timeout with retries will get you somewhere similar with a different set of failure modes. What I like about the channel is that it also gives you a natural place to do admission control, one obvious owner for the connection, and a reply that means the write really did commit.
The pain points
Getting the architecture above in place turns out to be the straightforward part. These next few are the things that actually caught me out.
"Accepted" and "committed" are different words
This is the one I'd most like people to take away. Have another look at the ways increment can fail:
Unavailable means the command never made it into the queue at all. Nothing ran and nothing committed, so a client can retry that one with a clear conscience.
OutcomeUnknown is the awkward one. The command was accepted, and then the reply channel died before it could answer. That write might have committed or it might not have, and there's no way to tell from where the handler is standing. The writer does know, which is why it logs the case where it commits a write and finds nobody waiting, and that log line is the only thread connecting the two halves of the story.
Folding those two into a single "write failed" error is the kind of thing that quietly ships and bites you later. They look identical to a careless client, while giving a careful one completely opposite instructions, which is why they get different status codes and why the message says outright not to retry blindly.
There's a trap sitting underneath this that's worth naming. Quite a few HTTP clients ship a blanket "retry any 5xx with backoff" policy, and OutcomeUnknown is a 500, so those clients will cheerfully retry the one request you most wanted them to leave alone. Sending the safe case as 429 rather than 503 at least keeps ordinary backpressure out of that bucket, but it doesn't fix this, and no status code will. If your writes aren't idempotent, the real answer is idempotency keys, so that a retry of an unknown outcome is harmless by construction. Otherwise this distinction is the difference between a retry and a duplicate charge.
Shutdown order matters more than you'd think
Here's the order that works:
HTTP goes down first and the writer second, and it does need to be that way round. If you stop the writer first, every handler still finishing up gets Unavailable on a request you'd already accepted, and you've handed yourself a small outage on every deploy. That's what the two cancellation tokens are for, since one token has no way to say stop this, then that.
There's a subtler version of this that got me while writing the code above. My first pass moved the only Database straight into the router. Then on shutdown the HTTP server finished, the router dropped, the last sender went with it, and the writer's recv() came back None before it ever got around to noticing the cancellation. A perfectly clean shutdown ended up exiting non-zero:
listening on http://localhost:3000
Error: every Database handle was dropped
On a laptop you'd shrug at that. In a container it's an unclean exit, so your orchestrator decides the pod crashed, and anything waiting on a successful exit to flush your backups never gets to run. The fix is the drop(database) sitting after writer_shutdown.cancel(), so the writer sees a shutdown someone asked for rather than an accident. The starter repo sidesteps it by keeping a handle on the application struct for the whole lifetime of the process.
WAL means more than one file
Part of the appeal of sqlite is that it's just a file you can copy around. Have a look at what's actually on disk while the server is running though:
demo.sqlite
demo.sqlite-shm
demo.sqlite-wal
Then after a clean shutdown:
demo.sqlite
The -wal holds committed transactions that haven't been folded back into the main file yet, and the -shm is the shared memory index keeping track of who can see what. On a clean close sqlite checkpoints everything and clears both away. Which means if you copy demo.sqlite on its own while the process is still running, you walk away with a database missing every recent commit, and nothing will warn you about it. Backups need to take all three together, or be handled by something that understands WAL.
This is also where constraint number two comes from, the one about shared filesystems. That -shm file is genuine shared memory, and WAL leans on it along with working POSIX advisory locks, neither of which NFS or SMB reliably give you. SQLite's own documentation puts it plainly: every process using the database has to be on the same host. So a WAL database on a network share will corrupt on you rather than merely run slowly, which makes local disk a hard requirement here.
One owner, and nothing is enforcing it
Everything promised so far only holds within a single process. The channel serializes the writers inside this binary, and it has nothing at all to say about a second copy of that binary opening the same file.
The starter repo's Helm chart says so about as bluntly as yaml lets you:
# Exactly one owner. Scaling this creates independent, conflicting writers.
replicas: 1
Of course a comment doesn't enforce anything. Scale that to two and you get two processes, each one serializing its own writes perfectly well, neither aware the other exists, and both shipping backups to the same place. There's nothing in the chart that fences off a disconnected old owner before a new one comes up either. The trade this design makes is horizontal write scaling for operational simplicity, and it stops being correct when writers become your bottleneck.
Making it survive a restart
A single process writing to one local file on a disk that vanishes with the pod does sound like a durability problem, and it is one, until you put Litestream next to it. It follows the WAL and streams it out to object storage continuously, so the local file stops being your only copy.
That's what lets the deployment drop persistent volumes altogether. An emptyDir will do, since the durable copy lives in object storage and a replacement pod restores onto a blank disk before it serves a single request.
Two details in that diagram are doing more work than they appear. The first being Litestream runs as the parent process, so the ordering from the shutdown section carries on past your own binary. Rust drains and exits zero, and only then does Litestream take its final sync. Which is why an unclean exit matters as much as it does, since it can skip the sync that would have saved your last writes. The second is that waiting for the first replica sync is a real barrier. The app won't bind HTTP until a nonzero local transaction ID has genuinely reached the replica, so a pod never ends up serving traffic while quietly having no working backups.
There's an honest catch in all of this and it's worth stating plainly. A 200 OK on a write tells you the transaction committed locally, while replication follows along behind it asynchronously, usually a second or so back. Kill the node hard enough and you lose whatever hadn't shipped yet. For a lot of applications that window is perfectly acceptable, and it's a good deal better than the backup story most sqlite deployments actually have, but it is a window and you want to know how wide it is before you find out the hard way.
So where does this leave us?
Back where we started, with the three constraints, though hopefully with better answers than we had at the top.
Only one writer at a time stopped being a rule people have to remember and became something the process can't break, since handlers are never handed anything they could write through in the first place.
The shared filesystem problem doesn't really have a workaround, so we lean into it instead: local disk, one owner, and replication out to object storage rather than a mount.
And WAL needing special handling mostly comes down to knowing there's more than one file involved, that the extra ones only clear on a clean close, and that your shutdown path is part of how durable you actually are.
What you end up with is a single binary that needs no database server standing behind it and no extra tier to keep alive. It'll handle a good deal more traffic than most people assume. What you give up is horizontal write scaling, along with a few seconds of replication lag in the worst case.
For a surprising number of services that's a trade worth making. Have a poke around the repo, run the contention lab yourself, and see whether it fits what you're building.
All the best, and enjoy your tinkering.