SQLx: Orientation

SQLx is an asynchronous SQL toolkit. Its public API lets applications execute SQL directly while retaining Rust type integration, connection pooling, and optional compile-time query checking.

Design thesis

SQLx uses generic database families to share query machinery without erasing driver-specific types, and treats exclusive connection ownership as the unit that makes asynchronous protocol progress safe.

• A query is lazy until an executor drives it.
• One mutable connection serializes one wire protocol conversation.
• Pools bound concurrency and transfer temporary connection ownership.
• Runtime, driver, and blocking SQLite differences stay behind narrow traits.

Begin at the façade

The root package is named sqlx, but most implementation code lives in workspace crates. The root src/lib.rs mainly re-exports shared APIs and feature-selected drivers.

The same core also connects to sqlx-mysql and sqlx-sqlite. Separate macro crates handle code that runs during compilation:

• sqlx-macros exposes procedural-macro entry points;
• sqlx-macros-core performs query inspection and code generation;
• sqlx-cli manages migrations, databases, and offline query metadata.

Features assemble the product

The root Cargo.toml uses features to select databases, runtimes, TLS implementations, macros, migrations, and external Rust types.

[features]
runtime-tokio = ["_rt-tokio", "sqlx-core/_rt-tokio", "..."]
postgres = ["sqlx-postgres", "sqlx-macros?/postgres"]
macros = ["derive", "sqlx-macros/macros", "sqlx-core/offline", "..."]

This means sqlx is not one fixed binary arrangement. Two applications can compile substantially different portions of the workspace under the same public crate name.

The important shared contracts

sqlx-core/src/lib.rs exposes the concepts that drivers agree upon:

• Database bundles driver-specific associated types;
• Connection describes one physical database connection;
• Executor runs queries;
• Query stores SQL and encoded arguments;
• Pool<DB> manages multiple connections;
• Transaction<DB> ties ordered work to one connection.

The central relationship is:

Query<DB> must be executed by Executor<Database = DB>

A PostgreSQL query and a MySQL pool cannot accidentally meet because their types disagree.

Network drivers and SQLite diverge

PostgreSQL and MySQL speak network protocols over nonblocking sockets. Their connections are asynchronous protocol state machines.

SQLite is an in-process C library with blocking calls. SQLx gives each SQLite connection a worker thread and communicates with it through channels. Both implement the same high-level contracts, but their execution mechanisms are different.

That difference is a useful signal: shared traits describe observable capabilities, not identical implementations.

Scope of this walkthrough

The following chapters trace ordinary query execution, pooling, error handling, and compile-time query checking. Database type catalogs, migrations, TLS details, and every protocol message are outside the main path unless they explain an architectural decision.

Interactive System Map

Choose a backend process, then select any step to see what it owns, why it exists, and where it is implemented.

How to use the map

The arrows show execution order, not crate dependencies. Switching processes changes both the path and the kind of boundary being emphasized:

• Postgres query follows a public call to network responses.
• Pool contention follows admission, reuse, and release.
• SQLite query crosses from async code to a blocking worker thread.
• Cancellation follows invariant restoration rather than success output.
• Query macro follows compilation and rejoins the runtime query path.

The map is an index into the code, not a substitute for it. Later chapters explain the ownership and state transitions behind each path.

The Query Lifecycle

Consider an ordinary operation:

let user = sqlx::query("SELECT id, name FROM users WHERE id = $1")
    .bind(id)
    .fetch_one(&pool)
    .await?;

The compact chain crosses the public façade, generic core, pool, concrete driver, protocol encoder, socket, and row decoder.

Construction does not execute

sqlx_core::query::Query stores four important things:

pub struct Query<'q, DB: Database, A> {
    statement: Either<SqlStr, &'q DB::Statement>,
    arguments: Option<Result<A, BoxDynError>>,
    database: PhantomData<DB>,
    persistent: bool,
}

Calling query() creates this value. Calling bind(id) encodes the argument into DB::Arguments. Neither operation performs database I/O.

The value is marked #[must_use] because dropping it has no effect.

fetch_one delegates to an executor

The query method is mostly a typed handoff:

pub async fn fetch_one<'e, 'c: 'e, E>(self, executor: E)
    -> Result<DB::Row, Error>
where
    E: Executor<'c, Database = DB>,
{
    executor.fetch_one(self).await
}

The query knows its database type. The executor supplies the mechanism.

Executor defines a small set of primitive operations, including fetch_many, fetch_optional, and prepare_with. Default methods construct higher-level behavior:

• fetch filters rows out of fetch_many;
• fetch_all collects a row stream;
• fetch_one turns an absent row into Error::RowNotFound;
• execute collects query-result values.

Executing through a pool

The implementation of Executor for &Pool<DB> acquires a connection and then delegates again:

let mut conn = pool.acquire().await?;
let mut stream = conn.fetch_many(query);

while let Some(value) = stream.try_next().await? {
    yield value;
}

The checked-out connection remains owned by this stream until execution finishes or the stream is dropped.

PostgreSQL becomes concrete

sqlx-postgres/src/connection/executor.rs implements Executor for &mut PgConnection. Its fetch_many implementation extracts the generic query parts and calls PgConnection::run.

For a prepared query, run performs this sequence:

Messages are encoded into a write buffer and flushed together. The response loop then handles messages such as:

• RowDescription, which supplies column metadata;
• DataRow, which becomes PgRow;
• CommandComplete, which becomes PgQueryResult;
• ReadyForQuery, which marks the connection ready for another operation.

A state trace

The exclusive connection borrow is more than a borrowing detail. It protects a state machine whose buffers, prepared statements, transaction status, and pending responses must change in one order.

Generics as Architecture

SQLx uses generics to share behavior without erasing which database is in use. The key parameter is usually DB.

Database is a family of related types

The Database trait does not primarily describe methods. It associates one driver marker with all of the types belonging to that driver:

pub trait Database: 'static + Sized + Send + Debug {
    type Connection: Connection<Database = Self>;
    type TransactionManager: TransactionManager<Database = Self>;
    type Row: Row<Database = Self>;
    type QueryResult: Send + Sync + Default;
    type TypeInfo: TypeInfo;
    type Arguments: Arguments<Database = Self>;
    type Statement: Statement<Database = Self>;
}

For PostgreSQL, those choices include PgConnection, PgRow, PgArguments, and PgStatement. A generic pool can therefore name DB::Connection without carrying separate parameters for every related type.

Bounds express agreement

This bound appears throughout execution code:

E: Executor<'c, Database = DB>

Read it in two parts:

• E: Executor<'c> — E can execute work using a connection valid for 'c;
• Database = DB — its chosen database must match the query’s DB.

The equality constraint connects two otherwise generic values.

Encoding depends on the database

Binding requires:

T: Encode<'t, DB> + Type<DB>

Type<DB> describes the SQL type corresponding to T for this database. Encode<'t, DB> knows how to write the value into this driver’s argument buffer.

The DB parameter matters because databases do not share one representation or type catalog.

Lifetimes connect borrowed pieces

Query<'q, DB, A> may borrow SQL or a prepared statement for 'q. Executor<'c> may borrow a connection for 'c. Query methods introduce an execution lifetime 'e and require both borrows to remain valid long enough:

'q: 'e,
'c: 'e,

The annotations describe which borrowed inputs must outlive the returned future or stream. They do not measure how long execution takes.

Static and dynamic dispatch coexist

Ordinary database selection uses generics and associated types. The compiler knows that Pool<Postgres> contains PostgreSQL connections.

Database-reported errors use dynamic dispatch:

Error::Database(Box<dyn DatabaseError>)

The public error enum can store a PostgreSQL, MySQL, or SQLite error without making the entire enum generic. Callers retain common operations such as code() and kind(), and can downcast when driver-specific detail matters.

SQLx therefore chooses dispatch according to the relationship it needs:

queries and pools       → static database identity
heterogeneous errors    → runtime-selected implementation

Why boxed futures and streams appear

Async blocks and iterator adapters produce concrete types that are difficult or impossible to name in trait signatures. SQLx often returns:

BoxFuture<'e, Result<T, Error>>
BoxStream<'e, Result<DB::Row, Error>>

The box erases the concrete future or stream type while the generic parameters continue to preserve database identity and output relationships.

This is not “generics versus trait objects” as a repository-wide choice. SQLx uses each form at the boundary where it simplifies the contract.

Async and Concurrency

SQLx’s concurrency model has three layers:

nonblocking work within one query
multiple in-flight queries across pooled connections
physical parallelism in runtimes, workers, and database servers

These layers are related but not interchangeable.

One connection is serial

Network drivers implement Executor for an exclusive connection borrow:

impl<'c> Executor<'c> for &'c mut PgConnection { /* ... */ }

Two ordinary query futures cannot simultaneously borrow the same connection mutably. This matches the implementation: one connection owns one ordered protocol conversation.

PostgreSQL can pipeline protocol messages in some situations, but SQLx’s normal query API does not multiplex unrelated operations over one connection.

The pool creates query concurrency

Independent operations use the shared pool:

let (user, account) = tokio::try_join!(
    load_user(&pool),
    load_account(&pool),
)?;

Each operation attempts to acquire its own connection. PoolInner<DB> contains:

• an async semaphore limiting capacity;
• an ArrayQueue of idle connections;
• atomics tracking size, idle count, and closed state;
• shared configuration and lifecycle hooks.

When the pool reaches max_connections, another task awaits a permit. It does not block an operating-system thread.

Socket operations suspend

SQLx’s runtime-neutral Socket trait exposes readiness polling. A read future first tries a nonblocking read. When the socket reports WouldBlock, it asks the runtime to wake the task when the socket becomes readable and returns Poll::Pending.

The task is inactive during that wait. Tokio or another supported runtime can poll other tasks on the same thread.

SQLx wraps runtime-specific operations in sqlx-core/src/rt/mod.rs, including:

• spawning tasks and blocking work;
• sleeping and timeouts;
• yielding;
• runtime-specific socket and semaphore behavior.

Streams control result consumption

fetch() returns an asynchronous row stream. For PostgreSQL, there is no separate task eagerly collecting the full result set. Polling the stream drives the response loop and yields rows incrementally.

This limits application-side buffering, but it also means the stream holds its pool connection while the caller consumes it.

let mut rows = query.fetch(&pool);

while let Some(row) = rows.try_next().await? {
    process(row).await; // the connection remains checked out here
}

Slow per-row work can reduce pool availability. Collecting first releases the connection after reading finishes but uses memory proportional to the result set.

SQLite crosses a thread boundary

SQLite’s C API is blocking. Each SqliteConnection owns a dedicated worker thread created in sqlx-sqlite/src/connection/worker.rs.

The worker executes commands sequentially for one connection. Multiple pooled SQLite connections have multiple worker threads, although SQLite’s own locking rules still constrain concurrent writes.

Bounded command and row channels prevent unlimited buffering. A slow receiver eventually makes the producer wait.

Where parallelism may occur

• A multithreaded runtime may poll independent query tasks simultaneously.
• SQLite connections may execute on separate worker threads.
• PostgreSQL or MySQL may execute different connections in separate server processes or threads.
• A database query plan may itself use parallel workers.

None of these possibilities changes the one-mutable-owner rule for an SQLx connection.

Errors, Cancellation, and Cleanup

SQLx centralizes public failures in sqlx_core::error::Error, then separately decides whether the connection and pool accounting remain valid.

One public error surface

Important categories include:

• Database, containing an error reported by the server;
• Io, for transport failures;
• Protocol, for malformed or unexpected protocol state;
• Encode, Decode, and ColumnDecode;
• PoolTimedOut and PoolClosed;
• WorkerCrashed for a lost SQLite worker.

The enum is #[non_exhaustive], allowing the library to add variants without requiring every caller to update an exhaustive match.

Native errors retain their detail

Each driver implements DatabaseError for its native error type. PostgreSQL’s PgDatabaseError, for example, preserves SQLSTATE, detail, hint, table, column, and constraint fields.

The generic wrapper stores it as:

Error::Database(Box<dyn DatabaseError>)

Common constraint categories are normalized into ErrorKind, while callers can downcast when they need a PostgreSQL-specific field.

Drivers detect error messages centrally

PostgreSQL’s stream receive loop recognizes ErrorResponse, decodes a PgDatabaseError, and returns it as Error::Database. MySQL performs the same normalization when a packet begins with its error marker. SQLite converts C result codes into SqliteError.

Central detection keeps query loops focused on valid response messages.

Stream errors can arrive late

Constructing a stream does not prove that a query can execute. Errors can occur while acquiring, preparing, receiving the first row, decoding a later row, or processing the final completion message.

PostgreSQL’s fetch_optional continues consuming responses after finding its first row because deferred database constraints may fail near the end of the operation.

Cancellation is dropping a future

An async operation can be cancelled at an .await by dropping its future or stream. At that moment SQLx might have:

• acquired a semaphore permit;
• incremented the pool’s connection count;
• removed an idle connection from the queue;
• sent a query whose response is incomplete;
• begun a transaction.

Returning Err is not enough to restore those states.

RAII protects pool accounting

DecrementSizeGuard<DB> owns responsibility for a reserved pool slot. Unless explicitly cancelled, its Drop implementation decrements the size and releases the semaphore permit.

The guard is installed before connection work awaits. Every early return and cancellation therefore passes through the same compensation path.

reserve shared state
        │
        ▼
install drop guard
        │
        ▼
perform fallible async work
        │
        ├── success → disarm or transfer guard
        └── failure/cancel → Drop restores state

Connections must be synchronized before reuse

PostgreSQL tracks how many ReadyForQuery messages are pending. Before starting another operation, wait_until_ready flushes queued writes and drains responses until the protocol reaches its completion boundary.

When a pooled connection is returned, SQLx tests whether it can be restored to a usable state. A connection that fails the check is closed rather than placed back in the idle queue.

Drop cannot await

Dropping PoolConnection may spawn an async cleanup task because returning a connection can require I/O and Rust’s Drop::drop is synchronous.

Transactions use a related approach. Dropping an open transaction calls the driver’s start_rollback, which queues or signals rollback work so a later async operation completes it.

The architectural lesson is broader than SQLx:

Error propagation reports failure. Lifecycle code restores invariants and decides whether resources are reusable.

Compile-Time Query Checking

query!() looks like a runtime query API, but part of its work happens while the application is compiling.

Two timelines

compile time                         runtime
------------                         -------
read SQL                             construct Query
describe SQL or load cache           acquire connection
infer parameter/output types         execute protocol
generate Rust expression             decode rows

Keeping these timelines separate prevents a common misunderstanding: the macro does not replace the runtime executor or driver.

The procedural-macro entry point

sqlx-macros/src/lib.rs exposes expand_query. It parses the macro input and passes it to sqlx-macros-core with the enabled database drivers.

sqlx-macros-core/src/query/mod.rs then chooses a data source:

• a live database selected through DATABASE_URL; or
• cached query metadata from an offline .sqlx directory.

The URL scheme or cached database name selects the matching macro driver.

Description becomes generated types

For a live database, SQLx asks the driver to describe the SQL. The result contains parameter and column information. Macro expansion then:

1. verifies the number of arguments;
2. checks or generates parameter type expressions;
3. maps output columns to Rust types;
4. generates a record type when necessary;
5. emits an ordinary typed query expression.

Invalid SQL or mismatched types can therefore become compilation errors.

Runtime execution rejoins the normal path

The generated expression ultimately constructs the same Query family used by the non-macro functions. At runtime it still travels through:

Query → Executor → Pool/Connection → Driver → Database

Network failure, pool exhaustion, permission changes, constraint violations, and returned data remain runtime concerns.

Why separate crates exist

Procedural macros execute in the compiler’s process and have different build and dependency constraints from the runtime library. Keeping macro entry points, expansion logic, core traits, and drivers in separate crates lets Cargo assemble only the required pieces and avoids making the façade itself a procedural-macro crate.

Build a Smaller SQLx

Reimplementing all of SQLx is not a useful first target. A smaller project can preserve its central architecture without supporting three databases, several runtimes, TLS choices, migrations, macros, and years of compatibility behavior.

Rebuild the architectural center, not a toy imitation of the public syntax.

This chapter is the overview for a hands-on, multi-stage reconstruction. Each stage will become its own chapter and leave behind runnable code.

What are we preserving?

The smaller implementation must genuinely demonstrate:

• exclusive mutable connection access;
• lazy query execution;
• incremental row streaming;
• pool-limited concurrency;
• cancellation-safe resource accounting; and
• a worker-thread bridge for blocking operations.

It does not initially need TLS, real PostgreSQL authentication, dozens of SQL types, migrations, procedural macros, or optimized lock-free queues.

Stage 1: one concrete connection

Begin with one database and no generic traits:

struct Connection {
    socket: BufferedSocket,
    state: ProtocolState,
}

impl Connection {
    async fn execute(&mut self, query: Query) -> Result<QueryResult, Error>;
    fn fetch(&mut self, query: Query)
        -> impl Stream<Item = Result<Row, Error>> + '_;
}

Keep &mut self. It makes protocol serialization explicit and prevents a multiplexing problem before the decoder is correct.

Stage 2: framing and cancellation-safe reads

Build an incremental decoder that distinguishes an incomplete buffer from a complete message:

enum Decode<T> {
    Need(usize),
    Complete(T),
}

Do not remove bytes from the shared buffer until a full message has arrived. This lets a cancelled read resume without losing the message boundary.

Stage 3: stream rows

Return rows incrementally. Record the protocol completion marker separately from row messages so the connection cannot be declared reusable too early.

Test these cases:

• zero rows;
• many rows with a slow consumer;
• an error after some successful rows;
• dropping the stream early;
• the server closing the socket mid-message.

Stage 4: add a simple pool

Use understandable synchronization first:

struct Pool {
    idle: Mutex<VecDeque<Connection>>,
    permits: Semaphore,
}

The semaphore limits checked-out connections. The queue stores reusable ones. Only replace these structures with lock-free or specialized versions after measurement establishes a need.

Install an RAII guard before every await that follows shared-accounting changes.

Stage 5: define recovery

Decide what happens when a query future is dropped:

• drain the remaining response to a protocol boundary;
• send a database-specific cancellation request; or
• mark the connection broken and close it.

Closing is often the simplest correct first policy.

Stage 6: bridge a blocking implementation

To model SQLite, place blocking connection state on a worker thread. Use a bounded command channel and a bounded result channel. Define shutdown behavior before adding more commands.

Stage 7: extract generic contracts

After a second implementation exists, compare concrete types and extract only the behavior they genuinely share. Introduce a Database family of associated types and connect queries to executors with an equality constraint.

Stage 8: add compile-time tooling last

Compile-time SQL inspection is valuable but independent of basic execution. Add it after the runtime path has stable query, argument, row, and type-info contracts.

What the smaller version omits

At this point it will probably lack:

• robust TLS and authentication choices;
• statement-cache eviction;
• database-specific type coverage;
• optimized buffers and queues;
• hooks, tracing, migrations, and offline metadata;
• carefully tested behavior under every cancellation point.

Those omissions are useful. They expose which parts constitute the design and which parts harden it for production.