Hedronite · Backend Dev · Rust · Wed 2026-10-21

The read path serving SQLite rows from an async Rust service with rusqlite

Hand each query to the blocking pool, map every row in one place, bind inputs, and treat no row as a normal answer.

Lesson Class: Dev (Rust backend · SQLite read path)
Tested with: rusqlite 0.40.2 (bundled) · axum 0.8.9 · tokio 1.54.0 · rustc/cargo 1.99.0
Lab: crate/ · cargo test (9)
Source: rusqlite 0.40.2 source · tokio spawn_blocking docs · axum 0.8.9 docs
Block Off the Runtime
rusqlite is synchronous; spawn_blocking keeps a query off the async worker threads.
One Row Mapper
Every query selects the same columns into one function; Option takes NULL.
No Row Is an Answer
.optional() turns QueryReturnedNoRows into None, and None into a 404.
Keep the worker threads free.

The problem

Suppose you keep a small catalogue of books in a SQLite file and want an HTTP API over it. GET /books/{id} returns one book, and GET /books?author=&limit= returns a list. The first version most people write calls rusqlite straight from the async handler and builds the SQL with format!, dropping the id or the author name into the string. It compiles and it answers the first request. It also has three problems.

First, rusqlite is a synchronous library. While SQLite works on a query, the call does not hand control back to the async runtime. The worker thread running your handler is stuck, and every other request scheduled on that thread waits behind it.

Second, a lookup for an id that does not exist is an error as far as rusqlite is concerned. If the handler passes it up with ?, the client gets a 500 for what is really "there is no such book".

Third, if the author text from the query string is pasted into the SQL, the caller decides what the SQL says. A value such as x' OR '1'='1 stops being a name and becomes part of the query.

The read path below avoids all three, using rusqlite 0.40.2 inside axum 0.8.9 on Tokio, and nine tests check each fix.

The idea, in plain words

Keep one database connection in shared state behind a mutex. For each request, hand the query to Tokio's blocking pool, a set of threads meant for work that cannot be async, so the async workers stay free. Turn every database row into a Rust struct through one function, so the column handling lives in one place. Pass user input to SQL as bound parameters and never as pieces of the SQL text. Treat "no row found" as a normal answer that becomes a 404, not as a failure. Finally, open the file read-only, so that a mistake in this service cannot change the data.

Concept A: synchronous SQLite inside an async server

A blocking call is a function that occupies its thread until it finishes, instead of giving the thread back to the runtime while it waits. Every rusqlite query is one. Tokio's documentation for spawn_blocking explains why that matters: "In general, issuing a blocking call or performing a lot of compute in a future without yielding is problematic, as it may prevent the executor from driving other futures forward." The same documentation says spawn_blocking "runs the provided closure on a thread dedicated to blocking operations." So the fix is to move the query off the async workers, because otherwise one slow query can hold up requests that have nothing to do with the database.

There is a second constraint, and it explains the shape of the shared state. In rusqlite's source, Connection stores its handle in a RefCell (db: RefCell<InnerConnection>), and the crate declares only unsafe impl Send for Connection {}. There is no Sync impl. In practice that means a Connection can be moved to another thread but cannot be shared by reference across threads. Axum handlers run on many threads, so the state holds Arc<Mutex<Connection>>. The Arc lets every handler own a handle to the same connection, and the Mutex makes sure only one thread uses it at a time.

#[derive(Clone)]
pub struct AppState {
    pub db: Arc<Mutex<Connection>>,
}

async fn with_db<T, F>(state: &AppState, f: F) -> Result<T, ApiError>
where
    T: Send + 'static,
    F: FnOnce(&Connection) -> rusqlite::Result<T> + Send + 'static,
{
    let db = state.db.clone();
    tokio::task::spawn_blocking(move || {
        let conn = db.lock().map_err(|_| "db mutex poisoned".to_string())?;
        f(&conn).map_err(|e| e.to_string())
    })
    .await
    .map_err(internal)?
    .map_err(internal)
}

with_db is the one place where async code meets SQLite. It clones the Arc so the closure can own its handle, then moves that handle into spawn_blocking. Inside the blocking thread it locks the mutex and runs the caller's closure with a &Connection. There are two layers of failure. The .await returns a JoinError if the blocking task did not finish normally (a panic inside the closure, for example), and the first .map_err(internal)? turns that into a 500. The closure's own rusqlite::Error (or a poisoned lock) was already turned into a String inside the thread, and the second .map_err(internal) turns that into a 500 too. Handlers call with_db with a closure and never touch threads themselves.

The lab ran 64 concurrent requests against one app on a runtime with 4 worker threads, and the test printed t9 64/64 returned 200 in 1.542667ms. All 64 got a 200 while sharing the single connection. To be plain about what this shows: the mutex serializes the queries, so they run one after another on that connection. For small reads on one file that is acceptable. A pool of several connections was not built or measured here. Also, this test shows that the design completes correctly under concurrency. It does not show what would have happened without spawn_blocking.

Two more details from the Tokio documentation. "The thread limit is very large by default, because spawn_blocking is often used for various kinds of IO operations that cannot be performed asynchronously." And "tasks spawned using spawn_blocking cannot be aborted because they are not async." Once a query has started on the blocking pool, you cannot cancel it from outside.

Common mistake

Concept B: rows to structs

Row mapping means converting one result row into a Rust value. In rusqlite you write a function that takes a &Row and returns your struct. Calling row.get("title") asks rusqlite to convert that column through its FromSql conversion into whatever Rust type the field has. This is worth doing in a single function because otherwise each handler repeats column names and types, and the copies drift apart.

#[derive(Debug, Serialize, PartialEq)]
pub struct Book {
    pub id: i64,
    pub title: String,
    pub author: String,
    pub year: Option<i64>,
}

fn book_from_row(row: &Row<'_>) -> rusqlite::Result<Book> {
    Ok(Book {
        id: row.get("id")?,
        title: row.get("title")?,
        author: row.get("author")?,
        year: row.get("year")?,
    })
}

Each row.get(...)? either produces a value of the field's type or returns a rusqlite::Error that stops the function. The first three fields are plain types because the table declares those columns NOT NULL. The year column is nullable, so its field is Option<i64>, and a NULL in the database becomes None. The lab seeds book 4 with no year, and GET /books/4 printed t7 200 {"id":4,"title":"Untitled draft","author":"Anonymous","year":null}. The None serialized to JSON null, and the request succeeded.

Every query in this service selects the same four columns, id, title, author, year, so book_from_row serves both handlers. The list handler uses query_map, which returns an iterator where each item is a Result<Book>. The handler calls .collect::<rusqlite::Result<Vec<_>>>(), which gathers the books into a Vec and stops at the first error. One bad row therefore fails the whole request, instead of being skipped and leaving the client with a list that is quietly incomplete.

Both handlers also call prepare_cached instead of prepare. The connection keeps compiled statements, so the same SQL text is not compiled again on every request. The rusqlite documentation says it "will return the same underlying SQLite statement handle without having to prepare it again." This comes from the cache feature, which is in rusqlite 0.40.2's default features (default = ["cache", "ffi-sqlite-wasm-rs"]), so you get it without extra configuration.

Common mistake

Concept C: the edges of the read path

Three boundaries remain: an id that is not there, text that comes from a stranger, and a handler with a bug that tries to write.

Missing rows

By default rusqlite treats a query that was expected to return one row but returned none as an error. The error variant is QueryReturnedNoRows, and it displays as "Query returned no rows". The OptionalExtension trait adds an .optional() method that "converts a Result<T> into a Result<Option<T>>" and handles that one error for you. The lab ran both forms on id 999: plain query_row printed Err(QueryReturnedNoRows) and with .optional() it printed Ok(None). The reason to prefer Option is that "no such book" is a normal answer, so it should be a value your code can match on. It should not travel as an error.

async fn get_book(State(state): State<AppState>, Path(id): Path<i64>) -> Result<Json<Book>, ApiError> {
    let found = with_db(&state, move |conn| {
        conn.prepare_cached("SELECT id, title, author, year FROM books WHERE id = ?1")?
            .query_row(params![id], book_from_row)
            .optional()
    })
    .await?;
    found
        .map(Json)
        .ok_or((StatusCode::NOT_FOUND, format!("no book with id {id}")))
}

The Path<i64> extractor parses the id from the URL. The closure prepares the statement, runs it with id bound to ?1, maps the row with book_from_row, and calls .optional(), so with_db returns an Option<Book>. The last lines turn Some(book) into a JSON response and None into the tuple (StatusCode::NOT_FOUND, message). Axum sends a handler's Err((StatusCode, String)) as the response, which is how the client sees status 404. Requesting id 999 printed t2 404 no book with id 999.

User input

Both handlers pass input as bound parameters: the SQL text contains placeholders (?1, ?2), and the values are supplied separately, so SQLite receives them as data and never parses them as SQL. This is the reason to avoid format! on SQL, because otherwise the caller's text becomes part of the statement.

async fn list_books(State(state): State<AppState>, Query(p): Query<ListParams>) -> Result<Json<Vec<Book>>, ApiError> {
    let limit = p.limit.unwrap_or(10).clamp(1, 100);
    let books = with_db(&state, move |conn| {
        let mut stmt = conn.prepare_cached(
            "SELECT id, title, author, year FROM books
             WHERE ?1 IS NULL OR author = ?1
             ORDER BY id LIMIT ?2",
        )?;
        let rows = stmt.query_map(params![p.author, limit], book_from_row)?;
        rows.collect::<rusqlite::Result<Vec<_>>>()
    })
    .await?;
    Ok(Json(books))
}

Before any SQL runs, limit defaults to 10 and is clamped to the range 1 to 100, so a client cannot ask for a huge or negative page. The params![p.author, limit] line binds ?1 to the optional author and ?2 to the limit. A None author binds as NULL, so WHERE ?1 IS NULL OR author = ?1 keeps every row when no author is given and filters when one is. That makes one statement serve both cases, and it caches as one statement. The lab shows both behaviours: author=Gjengset returned just book 3, and limit=2 returned books 1 and 2. For the attack string x' OR '1'='1 (URL-encoded in the test), the response was t4 200 []. The text was compared to the author column as a value, matched nothing, and was never parsed as SQL.

Write protection

pub fn open_read_only(path: &std::path::Path) -> rusqlite::Result<Connection> {
    Connection::open_with_flags(
        path,
        OpenFlags::SQLITE_OPEN_READ_ONLY | OpenFlags::SQLITE_OPEN_NO_MUTEX,
    )
}

open_with_flags takes a set of open flags that tell SQLite how to treat the file. SQLITE_OPEN_READ_ONLY opens it for reading only, so any write attempt is refused by the database itself. The lab tried an INSERT on such a connection and got attempt to write a readonly database. SQLITE_OPEN_NO_MUTEX tells SQLite not to add its own locking around the connection. The reasoning for using it here is that the Rust Mutex already guarantees one user at a time. That is the argument for the choice, not a measured result.

Common mistake

Putting it together

The three problems from the start map onto the nine tests like this.

TestWhat it provedProblem it answers
t1 get_existing_book_is_200_json/books/3 returns 200 with the book as JSONThe happy path works
t2 missing_id_is_404_not_500Unknown id gives 404 with "no book with id 999"Missing row
t3 list_filters_with_bound_parameterAuthor filter returns book 3; limit=2 returns two books (the test asserts the 200; the limit result is printed)Bound parameters
t4 injection_text_is_data_not_sqlThe attack string returns []String-built SQL
t5 read_only_connection_refuses_writesAn INSERT fails with "attempt to write a readonly database" (printed)Write protection
t6 wrong_rust_type_is_a_mapping_errorReading text as i64 gives InvalidColumnType (printed)Typed mapping errors
t7 null_column_maps_to_noneNULL year becomes "year":nullTyped mapping with Option
t8 query_row_without_optional_is_an_errorPlain gives Err(QueryReturnedNoRows), .optional() gives Ok(None) (printed)Missing row
t9 concurrent_reads_share_one_connection64 of 64 concurrent requests returned 200Blocking work off the async workers

The tests call the axum router directly with tower's oneshot, so no network socket is involved.

How to run it

Dependencies in Cargo.toml:

[dependencies]
axum = "=0.8.9"
rusqlite = { version = "=0.40.2", features = ["bundled"] }
serde = { version = "1", features = ["derive"] }
serde_json = "1"
tokio = { version = "1", features = ["full"] }

[dev-dependencies]
tower = { version = "0.5", features = ["util"] }
http-body-util = "0.1"

The bundled feature on the rusqlite line compiles SQLite into your binary, so no system SQLite library is needed. On the lab machine the cold build compiled libsqlite3-sys v0.38.2 along with the rest of the tree and finished in 7.97 s, so the C compile is a one-time cost, not a per-test one. Run:

cargo test --test read_path -- --nocapture --test-threads=1

The result line from the lab run was:

test result: ok. 9 passed; 0 failed; 0 ignored; 0 measured; 0 filtered out; finished in 0.01s

Tested with rustc and cargo 1.99.0, axum 0.8.9, rusqlite 0.40.2 (bundled), and tokio 1.54.0.

Limits

These were not run, so treat them as open questions:

Check yourself

  1. Why does the state hold Arc<Mutex<Connection>> and not a plain Arc<Connection>? *Connection is Send but not Sync (its handle sits in a RefCell), so threads cannot share it by reference. The Mutex gives one thread at a time access, and the Arc lets handlers share ownership.*
  1. What are the two error layers with_db maps into a 500? *The JoinError from awaiting the blocking task, and the error from inside the closure (a rusqlite::Error or a poisoned lock, both turned into a String).*
  1. A column is nullable and your struct field is i64. What happens for a row with NULL, and what is the fix? *The conversion fails with a mapping error like the InvalidColumnType seen in t6, and the fix is to make the field Option<i64>.*
  1. What is the difference in result between query_row and query_row(...).optional() for an id that does not exist? *Plain gives Err(QueryReturnedNoRows); with .optional() it gives Ok(None), which the handler maps to a 404.*
  1. Why did the injection string x' OR '1'='1 return []? *It was bound to ?1 as a value, so SQLite compared it against the author column as text and found no match. It was never parsed as SQL.*

Sources