Sqlx

sqlx — Async SQL Toolkit for Rust

sqlx is an async-first, pure-Rust SQL toolkit with compile-time query checking. It supports PostgreSQL, MySQL, and SQLite without a DSL — you write raw SQL, and sqlx validates it against a live database at compile time.

Crate Architecture

Quick Start: Minimal Dependency Setup

Always include at least one database driver and a runtime feature. Without a runtime feature, the pool will panic at runtime.

# Cargo.toml
[dependencies]
sqlx = { version = "0.8", features = ["runtime-tokio", "tls-rustls", "postgres", "chrono", "uuid"] }
tokio = { version = "1", features = ["macros", "rt-multi-thread"] }
dotenvy = "0.15"

# .env
DATABASE_URL=postgres://postgres:password@localhost:5432/mydb

use sqlx::PgPool;

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    dotenvy::dotenv().ok();
    let pool = PgPool::connect(&std::env::var("DATABASE_URL").unwrap()).await?;
    let users = sqlx::query_as!(User, "SELECT id, name FROM users")
        .fetch_all(&pool).await?;
    Ok(())
}

Critical: DATABASE_URL must be set at compile time for query!() macros to work. Set it in .env or use SQLX_OFFLINE=true with prepared metadata (see Offline Mode section).

Quick Reference: Which Guide Do I Need?

• Compile-time query macros, type overrides, offline mode -> Read references/compile-time-checking.md
• Connection pools, PgConnectOptions, pool tuning -> Read references/connection-pooling.md
• Rust-to-SQL type mapping, FromRow, custom types -> Read references/type-mapping.md
• Transactions, nested transactions, savepoints -> Read references/transactions-migrations.md
• #[sqlx::test], fixtures, test patterns -> Read references/testing-mocking.md
• PostgreSQL specifics (PgListener, COPY, arrays, ranges) -> Read references/database-specifics.md
• Error handling, sqlx::Error enum -> Read references/error-handling.md
• Feature flags, Cargo.toml patterns -> Read references/feature-flags.md

Core Patterns at a Glance

1. Query with Compile-Time Checking (Preferred)

The query!() macro connects to the database at compile time, validates SQL syntax, checks that parameter types match, and verifies return column types.

// Returns an anonymous struct with typed fields (no FromRow needed)
let record = sqlx::query!("SELECT id, name, email FROM users WHERE id = $1", user_id)
    .fetch_one(&pool)
    .await?;
// record.id: i64, record.name: String, record.email: String

// query_as! maps to a named struct (needs #[derive(FromRow)])
let user: User = sqlx::query_as!(User, "SELECT id, name FROM users WHERE id = $1", user_id)
    .fetch_one(&pool)
    .await?;

// query_scalar! returns a single column value
let count: i64 = sqlx::query_scalar!("SELECT COUNT(*)::BIGINT FROM users")
    .fetch_one(&pool)
    .await?;

2. Runtime Query Functions (No Compile-Time DB Needed)

Use these when you cannot set DATABASE_URL at compile time. They are not type-checked.

// query() returns anonymous PgRow — access columns by name or index
// Note: `sqlx::Row` trait must be in scope for .get()
use sqlx::Row;

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

3. Fetch Strategies

Every query supports multiple fetch methods. Choose based on expected result count:

// .fetch_one()       — Exactly one row. Error if 0 rows (RowNotFound) or >1 rows
// .fetch_optional()  — 0 or 1 row. Returns Option<T>. None if no rows.
// .fetch_all()       — All rows as Vec<T>. Empty vec if no rows.
// .fetch(_)          — Returns a Stream (row by row, lazy). Use with futures::StreamExt.

4. Connection Pool Configuration

use sqlx::postgres::PgPoolOptions;
use std::time::Duration;

let pool = PgPoolOptions::new()
    .max_connections(20)
    .min_connections(5)
    .acquire_timeout(Duration::from_secs(3))
    .idle_timeout(Duration::from_secs(600))
    .max_lifetime(Duration::from_secs(1800))
    .connect("postgres://user:pass@localhost/db")
    .await?;

5. Transactions

Transactions implement Executor, so you can pass them to any function that accepts a pool or connection.

use sqlx::Acquire;

// Manual transaction
let mut tx = pool.begin().await?;
sqlx::query!("INSERT INTO users (name) VALUES ($1)", "Alice")
    .execute(&mut *tx)
    .await?;
sqlx::query!("INSERT INTO audit_log (action) VALUES ($1)", "user created")
    .execute(&mut *tx)
    .await?;
tx.commit().await?;
// Dropping tx without commit() triggers automatic rollback.

// Closure-based (auto-commit on Ok, auto-rollback on Err)
// Requires acquiring a connection first — .transaction() is on Connection, not Pool.
let mut conn = pool.acquire().await?;
let result = conn.transaction::<_, _, sqlx::Error>(|tx| {
    Box::pin(async move {
        sqlx::query!("INSERT INTO users (name) VALUES ($1)", "Bob")
            .execute(&mut **tx).await?;
        Ok(())
    })
}).await?;

// Nested transactions (SAVEPOINTs)
let mut tx = pool.begin().await?;
let mut nested = tx.begin().await?; // Creates a SAVEPOINT — requires `Acquire`
nested.rollback().await?;           // Only rolls back to savepoint
tx.commit().await?;                 // Outer transaction unaffected

6. Generic Functions over Executor

Write functions that work with pools, connections, and transactions alike using the Executor trait:

use sqlx::{Executor, PgExecutor};

async fn find_user_by_id<'e, E>(executor: E, id: i64) -> sqlx::Result<User>
where
    E: PgExecutor<'e>,
{
    sqlx::query_as!(User, "SELECT * FROM users WHERE id = $1", id)
        .fetch_one(executor)
        .await
}

// Works with pool, connection, or transaction:
let user = find_user_by_id(&pool, 1).await?;
let user = find_user_by_id(&mut tx, 1).await?;

7. FromRow Derive

Map SQL rows to Rust structs with #[derive(FromRow)]. The struct field names must match SQL column names.

use sqlx::FromRow;

#[derive(Debug, FromRow)]
struct User {
    id: i64,
    name: String,
    email: String,
    created_at: chrono::DateTime<chrono::Utc>,
}

// Column renaming when names don't match
#[derive(Debug, FromRow)]
struct User {
    id: i64,
    #[sqlx(rename = "full_name")]
    name: String,
}

// Flatten for JOINs (combines fields from multiple tables)
#[derive(Debug, FromRow)]
struct UserWithPost {
    #[sqlx(flatten)]
    user: User,
    #[sqlx(flatten)]
    post: Post,
}

// Skip columns from the query that you don't need
#[derive(Debug, FromRow)]
struct UserSummary {
    id: i64,
    name: String,
    #[sqlx(skip)]
    _ignored_field: String,
}

8. JSON Columns

use sqlx::types::Json;
use serde::{Deserialize, Serialize};

#[derive(Debug, Serialize, Deserialize, sqlx::FromRow)]
struct User {
    id: i64,
    #[sqlx(json)]
    metadata: Json<Metadata>,
}

#[derive(Debug, Serialize, Deserialize)]
struct Metadata {
    role: String,
    preferences: std::collections::HashMap<String, String>,
}

// Insert with JSON value
sqlx::query!(
    "INSERT INTO users (name, metadata) VALUES ($1, $2)",
    "Alice",
    Json(Metadata { role: "admin".into(), preferences: Default::default() })
).execute(&pool).await?;

// Read — Json<T> automatically deserializes
let user: User = sqlx::query_as(
    "SELECT id, name, metadata FROM users WHERE id = $1"
).bind(1).fetch_one(&pool).await?;
println!("Role: {}", user.metadata.0.role);

9. QueryBuilder for Dynamic Queries

When SQL structure depends on runtime conditions, use QueryBuilder instead of string concatenation.

use sqlx::QueryBuilder;

let mut qb = QueryBuilder::new("SELECT * FROM users WHERE ");

// Conditionally add clauses
if let Some(name) = &filter.name {
    qb.push("name LIKE ").push_bind(format!("%{}%", name));
}

if filter.active_only {
    if filter.name.is_some() {
        qb.push(" AND ");
    }
    qb.push("active = ").push_bind(true);
}

let users: Vec<User> = qb.build_query_as::<User>()
    .fetch_all(&pool)
    .await?;

10. Migrations

# Create a new migration
sqlx migrate add create_users

# Run all pending migrations
sqlx migrate run

# Rollback the last migration
sqlx migrate revert

# Show migration status
sqlx migrate info

Embed and run migrations in code:

let migrator = sqlx::migrate!(); // Reads from ./migrations by default
migrator.run(&pool).await?;

11. #[sqlx::test] Attribute

Automatically creates an isolated test database, runs migrations, and cleans up afterward.

#[sqlx::test(migrations = "./migrations")]
async fn test_create_user(pool: PgPool) -> sqlx::Result<()> {
    sqlx::query!("INSERT INTO users (name) VALUES ($1)", "Alice")
        .execute(&pool).await?;

    let user = sqlx::query_as!(User, "SELECT * FROM users WHERE name = $1", "Alice")
        .fetch_one(&pool).await?;

    assert_eq!(user.name, "Alice");
    Ok(())
}

Placeholder Syntax by Database

This is a frequent source of errors. Different databases use different placeholders:

Compile-time macros handle this automatically based on DATABASE_URL. Runtime queries must use the correct syntax for the target database.

Key Imports Reference

use sqlx::{
    // Query functions (runtime, no compile-time checking)
    query, query_as, query_scalar,
    // Traits
    Executor, FromRow, Decode, Encode, Type,
    // Pool types
    PgPool, MySqlPool, SqlitePool, AnyPool, Pool,
    // Connection types
    PgConnectOptions, Transaction,
    // Error types
    Error, Result,
    // JSON support
    types::Json,
    // PostgreSQL extras
    postgres::{PgConnectOptions, PgListener, PgPoolOptions, PgSslMode},
    // MySQL extras
    mysql::{MySqlConnectOptions, MySqlPoolOptions},
    // SQLite extras
    sqlite::{SqliteConnectOptions, SqlitePoolOptions, SqliteJournalMode},
};

Common Pitfalls

1. Missing DATABASE_URL at compile time — query!() macros connect to the DB during cargo build. Set DATABASE_URL in .env or use offline mode with cargo sqlx prepare.

2. Missing runtime feature flag — Always include runtime-tokio or runtime-async-std in features. Without it, pool creation will panic.

3. Missing database feature flag — Just adding sqlx = "0.8" without a DB feature only enables any, json, macros, migrate, and derive. Always add e.g. features = ["postgres"].

4. Wrong placeholder syntax — PostgreSQL uses $1, MySQL/SQLite use ?. Compile-time macros enforce this; runtime queries do not.

5. Transaction dereference — Execute queries on a transaction with &mut *tx (not &tx).

6. SQLite boolean mapping — Booleans are stored as INTEGER (0/1). Use integers in SQL comparisons.

7. Slow compile times with many queries — Use offline mode, or consider query_unchecked!() for non-critical queries.

8. RETURNING clause — PostgreSQL supports RETURNING * in INSERT/UPDATE. MySQL does not (use LAST_INSERT_ID() instead).

Reference Files

For detailed information on any topic, read the appropriate reference file:

• references/compile-time-checking.md — query!, query_as!, query_scalar!, type overrides, unchecked variants, offline mode, .sqlx/ directory, query files
• references/connection-pooling.md — Pool options, connect options, PgConnectOptions, SSL modes, after_connect hooks, pool lifecycle
• references/type-mapping.md — Full Rust-to-SQL type table, feature-flag types, FromRow attributes, custom Encode/Decode, transparent types, enum mapping
• references/transactions-migrations.md — Transactions (manual, closure, nested), reversible migrations, migrate! macro, migration CLI commands
• references/testing-mocking.md — #[sqlx::test] macro, fixtures, repository pattern for mockability, in-memory SQLite for tests
• references/database-specifics.md — PostgreSQL (PgListener, COPY, arrays, ranges, composite types), MySQL specifics, SQLite (in-memory, journal mode, PRAGMA, extensions)
• references/error-handling.md — sqlx::Error enum, database error codes, RowNotFound, decode errors, pool errors
• references/feature-flags.md — Complete feature flag table, Cargo.toml patterns per use case, TLS options, runtime options

Known Issues Found During sqlx Skill Testing (sqlx v0.8.6, Rust 1.95, April 2026)

This document records verified bugs, misleading examples, or common pitfalls encountered while running real-world test code against the sqlx skill's md files. Every issue was confirmed with actual code execution before being added.

---

Issue 1: MySQL LAST_INSERT_ID() returns BIGINT UNSIGNED, not i64

Location in Skill

• references/database-specifics.md — MySQL INSERT and Auto-Increment section

Original Skill Code (Buggy)

let id: i64 = sqlx::query_scalar("SELECT LAST_INSERT_ID()")
    .fetch_one(&pool)
    .await?;

Actual Error

Error: ColumnDecode { index: "0", source: "mismatched types; Rust type `i64` (as SQL type `BIGINT`) is not compatible with SQL type `BIGINT UNSIGNED`" }

Root Cause

MySQL's LAST_INSERT_ID() returns a BIGINT UNSIGNED. sqlx maps unsigned MySQL integers to u64, not i64.

Verified Fix

let id: u64 = sqlx::query_scalar("SELECT LAST_INSERT_ID()")
    .fetch_one(&pool)
    .await?;

Additionally, LAST_INSERT_ID() is connection-specific. If using a pool, you must execute the INSERT and the LAST_INSERT_ID() query on the same acquired connection:

let mut conn = pool.acquire().await?;
sqlx::query("INSERT INTO users (name) VALUES (?)")
    .bind("Alice")
    .execute(&mut *conn)
    .await?;
let id: u64 = sqlx::query_scalar("SELECT LAST_INSERT_ID()")
    .fetch_one(&mut *conn)
    .await?;

---

Issue 2: #[derive(sqlx::Type, sqlx::Encode, sqlx::Decode)] causes conflicting trait implementations

Location in Skill

• references/type-mapping.md — Transparent Wrapper and Enum Mapping sections

Original Skill Code (Buggy)

#[derive(Debug, sqlx::Type, sqlx::Encode, sqlx::Decode)]
#[sqlx(type_name = "user_role", rename_all = "lowercase")]
enum UserRole {
    Admin,
    User,
    Guest,
}

Actual Error

error[E0119]: conflicting implementations of trait `sqlx::Encode<'_, _>` for type `UserRole`

Root Cause

The sqlx::Type derive macro already generates Encode and Decode implementations (as well as sqlx::Encode / sqlx::Decode trait derivations). Explicitly adding sqlx::Encode and sqlx::Decode as separate derive macro arguments creates duplicate/conflicting impls.

Verified Fix

Use only #[derive(sqlx::Type)] for enums and structs with #[sqlx(transparent)]:

#[derive(Debug, sqlx::Type)]
#[sqlx(type_name = "user_role", rename_all = "lowercase")]
enum UserRole { Admin, User, Guest }

#[derive(Debug, sqlx::Type)]
#[sqlx(transparent)]
struct Email(String);

---

Issue 3: cargo sqlx prepare — relative paths in migrate!() macro are NOT supported with paths relative to the current file's directory

Location in Skill

• references/transactions-migrations.md — Embedding Migrations in Code
• references/transactions-migrations.md — #[sqlx::test] With Migrations

Original Skill Code (Buggy)

let migrator = sqlx::migrate!("migrations/");

Actual Error

error: paths relative to the current file's directory are not currently supported

Root Cause

The migrate!() macro requires the migration path to be relative to the crate root (the directory containing Cargo.toml), not the current source file's directory.

Verified Fix

Pass the path relative to the crate root:

let migrator = sqlx::migrate!("./migrations");

Note: The trailing slash is optional, but a leading ./ is recommended for crate-relative resolution.

---

Issue 4: PgCopyIn::send() requires impl Deref<Target = [u8]>

Location in Skill

• references/database-specifics.md — PostgreSQL COPY (Bulk Import)

Original Skill Code (Buggy)

let mut writer = pool.copy_in_raw("...").await?;
writer.send(b"1,Alice,alice@example.com\n").await?;

Actual Error

error[E0271]: type mismatch resolving `<&[u8; 26] as Deref>::Target == [u8]`

Root Cause

Byte string literals (b"...") have type &[u8; N], which does not implement Deref<Target = [u8]> directly in the context send() expects. You must cast to a slice.

Verified Fix

writer.send(b"1,Alice,alice@example.com\n" as &[u8]).await?;

Alternatively, use a Vec<u8> or string converted to bytes.

---

Issue 5: query_scalar!("SELECT COUNT(*) ...") may return Option<T> depending on database inference

Location in Skill

• references/compile-time-checking.md — query_scalar! section
• references/testing-mocking.md — With Fixtures section

Original Skill Code (Buggy)

let count: i64 = sqlx::query_scalar!("SELECT COUNT(*) FROM users")
    .fetch_one(&pool)
    .await?;

Actual Error

error[E0308]: `?` operator has incompatible types
expected `i64`, found `Option<i64>`

Root Cause

The compile-time macros infer the return type based on the database metadata. For aggregate functions like COUNT(*), some PostgreSQL versions may infer the result as nullable (Option<i64>). This is especially common with PostgreSQL.

Verified Fix

Either use Option<i64> as the type, or use a type override with COUNT(*)::BIGINT:

let count: i64 = sqlx::query_scalar!("SELECT COUNT(*)::BIGINT FROM users")
    .fetch_one(&pool)
    .await?;

Or accept Option:

let count: Option<i64> = sqlx::query_scalar!("SELECT COUNT(*) FROM users")
    .fetch_one(&pool)
    .await?;

---

Issue 6: #[sqlx::test(migrations = "...")] — relative path from test file directory NOT supported

Location in Skill

• references/testing-mocking.md — With Migrations section

Original Skill Code (Buggy)

#[sqlx::test(migrations = "migrations/")]
async fn test_with_schema(pool: PgPool) -> sqlx::Result<()> { ... }

Actual Error

error: paths relative to the current file's directory are not currently supported

Root Cause

Same as Issue 3 — the migrate!() macro (internally used by #[sqlx::test]) requires paths relative to the crate root, not the test file's directory.

Verified Fix

Use a crate-root-relative path, or (safer) don't specify migrations if your schema is already present in the test DB. If migrations are needed, place them at the crate root and use:

#[sqlx::test(migrations = "./migrations")]
async fn test_with_schema(pool: PgPool) -> sqlx::Result<()> { ... }

Alternatively, create tables inline in the test without relying on migrations for compile-time checked macros.

---

Issue 7: chrono needs serde feature for #[derive(Serialize, Deserialize)] on structs containing DateTime<Utc>

Location in Skill

• references/compile-time-checking.md — query!() examples with chrono::DateTime<chrono::Utc>

Problem

The skill frequently shows structs with chrono::DateTime<chrono::Utc> and #[derive(Serialize, Deserialize)], but does not mention that chrono must be configured with the serde feature:

chrono = { version = "0.4", features = ["serde"] }

Actual Error (without serde feature)

error[E0277]: the trait bound `DateTime<Utc>: serde::Serialize` is not satisfied
error[E0277]: the trait bound `DateTime<Utc>: serde::Deserialize<'_>` is not satisfied

Verified Fix

Add the serde feature to chrono in Cargo.toml:

chrono = { version = "0.4", features = ["serde"] }

---

Issue 8: uuid requires v4 feature for Uuid::new_v4()

Location in Skill

• references/database-specifics.md — UUID Type section

Problem

The skill shows Uuid::new_v4() but does not mention that the uuid crate must have the v4 feature enabled.

Actual Error (without v4 feature)

error[E0599]: no function or associated item named `new_v4` found for struct `Uuid`

Verified Fix

uuid = { version = "1", features = ["v4"] }

---

Issue 9: ipnet type needs ipnet dependency in Cargo.toml, not just sqlx feature

Location in Skill

• references/database-specifics.md — Network Types section
• references/type-mapping.md — Network Types

Problem

The skill says ipnet::IpNet requires the ipnet feature on sqlx, but in practice the ipnet crate must also be added as an explicit dependency because the Rust code directly uses ipnet::IpNet.

Actual Error

error[E0433]: cannot find module or crate `ipnet` in this scope

Verified Fix

Add ipnet as a direct dependency in addition to enabling the sqlx feature:

[dependencies]
sqlx = { version = "0.8", features = ["ipnet", "postgres", ...] }
ipnet = "2"

---

Summary Table

sqlx-skills

A comprehensive AI skill for the sqlx Rust library — the async-first, pure-Rust SQL toolkit with compile-time query checking. Supports PostgreSQL, MySQL, and SQLite.

Overview

This skill enables an LLM to generate correct, production-ready Rust code using sqlx. It covers every major feature of the library with practical examples, type references, and common-pitfall documentation so that the LLM developer can build reliable database solutions without trial-and-error errors.

The skill uses a progressive disclosure architecture: the main SKILL.md file provides a concise quick-reference with 11 essential patterns, while 8 deep-dive reference files cover each topic in full detail (type tables, error code catalogs, Cargo.toml recipes, and more).

Installation

npx skills add melonask/sqlx-skills

What This Skill Covers

File Structure

sqlx/
├── SKILL.md                              # Main guide (410 lines)
└── references/
    ├── compile-time-checking.md          # Query macros, type overrides, offline mode (225 lines)
    ├── connection-pooling.md             # Pool config, connect options, lifecycle (178 lines)
    ├── type-mapping.md                   # Full type table, FromRow, custom types (230 lines)
    ├── transactions-migrations.md        # Transactions, migrations, CLI commands (246 lines)
    ├── testing-mocking.md                # #[sqlx::test], fixtures, repository pattern (271 lines)
    ├── database-specifics.md             # PostgreSQL/MySQL/SQLite specifics (303 lines)
    ├── error-handling.md                 # sqlx::Error, error codes, patterns (228 lines)
    └── feature-flags.md                  # Complete flag reference, Cargo.toml recipes (180 lines)

Total: 2,271 lines of documentation.

How It Works

1. Triggering — The skill description is designed to activate whenever the user mentions sqlx, Rust database operations, compile-time query checking, async database Rust, or any SQL database interaction in Rust. 2. Core guide — SKILL.md loads first with a quick-start setup, 11 essential patterns, placeholder syntax reference, key imports, and common pitfalls. 3. Deep dives — The LLM reads reference files on demand based on the specific task. For example, a question about error handling triggers loading references/error-handling.md.

Trigger Phrases

This skill activates on mentions of: sqlx, Rust database, Rust PostgreSQL, Rust MySQL, Rust SQLite, Rust SQL toolkit, compile-time query checking, async database Rust, database migrations Rust, sqlx pool, sqlx macros, query!, query_as!, FromRow, PgPool, #[sqlx::test], PgListener, cargo sqlx prepare, SQLX_OFFLINE, or any SQL database interaction in Rust.

Requirements

• sqlx version 0.8.x (the skill references 0.8 APIs and feature flags)
• Rust edition 2021+
• An async runtime: Tokio or async-std
• For compile-time checking: a running database or prepared .sqlx/ metadata

License

This skill is provided as-is for use with LLM-powered development environments.

Compile-Time Query Checking

Compile-time query checking is sqlx's flagship feature. Proc macros connect to a live database during cargo build, parse the SQL, describe the query with PREPARE, and verify that parameter types and return column types match your Rust code.

How It Works

When cargo build runs, each query!() macro:

1. Parses the SQL string for syntax validity 2. Reads DATABASE_URL from the environment 3. Connects to the database and issues a PREPARE statement 4. Retrieves parameter type descriptions and return column type descriptions 5. Checks that the Rust types you bind match the SQL parameter types 6. Generates an anonymous struct with properly typed fields for the return columns

If anything is wrong — syntax error, wrong parameter type, non-existent table — you get a compile error, not a runtime error.

Available Macros

query!() — Anonymous Record Type

The query!() macro returns an anonymous struct where each selected column becomes a typed field:

let record = sqlx::query!(
    "SELECT id, name, email, created_at FROM users WHERE id = $1",
    42i64
)
.fetch_one(&pool)
.await?;

// Fields are automatically typed based on the database schema:
// record.id: i64
// record.name: String
// record.email: String
// record.created_at: chrono::NaiveDateTime  (or DateTime<Utc> for TIMESTAMPTZ)
println!("User #{}: {} ({})", record.id, record.name, record.email);

The column names in the generated struct match the SQL column names exactly (lowercase).

query_as!() — Named Struct

Use when you need a reusable type, or when working with existing structs:

#[derive(Debug, sqlx::FromRow)]
struct User {
    id: i64,
    name: String,
    email: String,
    created_at: chrono::DateTime<chrono::Utc>,
}

let users: Vec<User> = sqlx::query_as!(
    User,
    "SELECT id, name, email, created_at FROM users WHERE active = $1",
    true
)
.fetch_all(&pool)
.await?;

The struct fields must match the SQL column names. Use #[sqlx(rename = "...")] when they differ.

query_scalar!() — Single Value

Returns just one column value from one row. Ideal for COUNT, EXISTS, or looking up a single field:

let count: i64 = sqlx::query_scalar!("SELECT COUNT(*)::BIGINT FROM users")
    .fetch_one(&pool)
    .await?;

let name: Option<String> = sqlx::query_scalar!(
    "SELECT name FROM users WHERE id = $1",
    user_id
)
.fetch_optional(&pool)
.await?; // Returns Option<String>, None if no user found

let exists: bool = sqlx::query_scalar!(
    "SELECT EXISTS(SELECT 1 FROM users WHERE email = $1)",
    "alice@example.com"
)
.fetch_one(&pool)
.await?;

Type Overrides in Macros

Sometimes the inferred type doesn't match what you need. Use the as syntax to override.

Important: The override syntax is different for MySQL and PostgreSQL/SQLite because it is embedded in the SQL string itself:

// Override parameter type
let record = sqlx::query!(
    "SELECT * FROM users WHERE id = $1",
    user_id as i64  // Override what type the macro expects for $1
)
.fetch_one(&pool)
.await?;

PostgreSQL / SQLite — double-quoted identifiers

// Force NOT NULL (useful for expressions Postgres can't infer)
let record = sqlx::query!(r#"SELECT 1 as "id!""#)
    .fetch_one(&pool)
    .await?;

// Override return column type
let record = sqlx::query!(
    r#"SELECT id, created_at as "created_at: chrono::DateTime<chrono::Utc>" FROM users WHERE id = $1"#,
    1i64
)
.fetch_one(&pool)
.await?;

MySQL — backtick-quoted identifiers

let record = sqlx::query!(
    "SELECT id, created_at as `created_at: chrono::DateTime<chrono::Utc>` FROM users WHERE id = ?",
    1i64
)
.fetch_one(&pool)
.await?;

Common cases where overrides are needed:

• TIMESTAMP columns can map to NaiveDateTime or DateTime<Utc> — override to pick
• JSON/JSONB columns can map to serde_json::Value or Json<T> — override for typed JSON
• NUMERIC columns can map to f64, BigDecimal, or Decimal — override based on precision needs
• Aggregate functions like COUNT(*) may be inferred as nullable depending on the database — use ! to force non-null

Unchecked Variants

Use query_unchecked!(), query_as_unchecked!(), or query_scalar_unchecked!() when you want SQL syntax validation but cannot connect to the database. These macros:

• Parse the SQL for syntax errors at compile time
• Do NOT connect to the database
• Do NOT check type compatibility
• Generate the same code but without type verification

// This will catch SQL syntax errors at compile time but won't check types
let users = sqlx::query_as_unchecked!(
    User,
    "SELECT id, name FROM users WHERE active = $1"
)
.bind(true)
.fetch_all(&pool)
.await?;

Query Files

Store SQL in separate .sql files for better organization, especially for complex queries:

// queries/list_active_users.sql
// SELECT id, name, email FROM users WHERE active = true ORDER BY created_at DESC

// src/main.rs
let users = sqlx::query_file_as!(User, "queries/list_active_users.sql")
    .fetch_all(&pool)
    .await?;

The file path is relative to the crate root (where Cargo.toml is). For workspace members, it's relative to the member crate's root.

Offline Mode

Offline mode lets you build without a live database. The workflow:

Step 1: Prepare with a live database

# Ensure DATABASE_URL is set, then prepare
DATABASE_URL=postgres://user:pass@localhost/mydb cargo sqlx prepare

# For Cargo workspaces, use --workspace
DATABASE_URL=postgres://user:pass@localhost/mydb cargo sqlx prepare --workspace

# Verify the .sqlx/ directory is up-to-date (CI use)
cargo sqlx prepare --check

Step 2: Commit .sqlx/ to version control

.sqlx/
├── query-a1b2c3d4.json
├── query-e5f6g7h8.json
└── ...

Each JSON file contains the query text, parameter type descriptions, and return column type descriptions. These are keyed by a hash of the query text so they survive code refactoring.

Step 3: Build without a database

# Set SQLX_OFFLINE=true to use cached metadata
SQLX_OFFLINE=true cargo build

The macros will read from .sqlx/ instead of connecting to the database. This is essential for CI/CD pipelines.

Legacy: sqlx-data.json

Pre-0.7 used a single sqlx-data.json file. Sqlx 0.8+ uses the .sqlx/ directory. Do not use the old format.

DATABASE_URL Format

The DATABASE_URL environment variable must be a valid connection string for the target database:

# PostgreSQL
DATABASE_URL=postgres://user:pass@localhost:5432/mydb
DATABASE_URL=postgresql://user:pass@localhost:5432/mydb?sslmode=require

# MySQL
DATABASE_URL=mysql://user:pass@localhost:3306/mydb

# SQLite
DATABASE_URL=sqlite:./mydb.sqlite
DATABASE_URL=sqlite::memory:

The macros read this at compile time to connect and describe queries.

Connection Pooling

sqlx provides built-in connection pooling powered by deadpool. Pools manage a set of reusable connections, handle reconnection, and allow tuning for performance.

Pool Types

Simple Connection

// Simple — uses default pool options
let pool = PgPool::connect("postgres://user:pass@localhost/db").await?;

// From environment variable
let pool = PgPool::connect(&std::env::var("DATABASE_URL")?).await?;

Pool Configuration

use sqlx::postgres::PgPoolOptions;
use std::time::Duration;

let pool = PgPoolOptions::new()
    .max_connections(20)           // Maximum concurrent connections (default: 10)
    .min_connections(5)            // Minimum idle connections to maintain (default: 0)
    .acquire_timeout(Duration::from_secs(3))  // Max wait to get a connection (default: 30s)
    .idle_timeout(Duration::from_secs(600))   // Close idle connections after this (default: 10min)
    .max_lifetime(Duration::from_secs(1800))  // Max total lifetime per connection (default: 30min)
    .connect("postgres://user:pass@localhost/db")
    .await?;

Pool Options Reference

After Connect Hook

Use after_connect to run setup commands on every new connection (e.g., setting search_path, timezone, or session variables):

let pool = PgPoolOptions::new()
    .after_connect(|conn, _meta| Box::pin(async move {
        // Run SET commands on each new connection
        sqlx::query("SET search_path TO 'my_schema'")
            .execute(&mut *conn)
            .await?;
        sqlx::query("SET timezone = 'UTC'")
            .execute(&mut *conn)
            .await?;
        Ok(())
    }))
    .connect("postgres://user:pass@localhost/db")
    .await?;

Connect Options (Per-Connection)

For fine-grained connection configuration, use the database-specific ConnectOptions:

PostgreSQL Connect Options

use sqlx::postgres::{PgConnectOptions, PgSslMode};
use sqlx::ConnectOptions;

let options = PgConnectOptions::new()
    .host("localhost")
    .port(5432)
    .username("user")
    .password("password")
    .database("mydb")
    .ssl_mode(PgSslMode::Prefer)          // Prefer, Require, Disable, NoTls
    .statement_cache_capacity(100)        // Prepared statement cache size
    .application_name("my-app")           // Shows in pg_stat_activity
    .options([("search_path", "my_schema,public")]);

let pool = PgPoolOptions::new()
    .connect_with(options)
    .await?;

SQLite Connect Options

use sqlx::sqlite::{SqliteConnectOptions, SqliteJournalMode, SqlitePoolOptions};
use std::time::Duration;

let options = SqliteConnectOptions::new()
    .filename("./mydb.sqlite")
    .journal_mode(SqliteJournalMode::Wal)   // WAL mode for better concurrency
    .busy_timeout(Duration::from_secs(5))   // Wait up to 5s if DB is locked
    .create_if_missing(true)                 // Create the file if it doesn't exist
    .foreign_keys(true);                     // Enable foreign key enforcement
    // .pragma("synchronous", "normal")     // Set any PRAGMA value

let pool = SqlitePoolOptions::new()
    .connect_with(options)
    .await?;

MySQL Connect Options

use sqlx::mysql::{MySqlConnectOptions, MySqlPoolOptions, MySqlSslMode};

let options = MySqlConnectOptions::new()
    .host("localhost")
    .port(3306)
    .username("user")
    .password("password")
    .database("mydb")
    .ssl_mode(MySqlSslMode::Preferred);

let pool = MySqlPoolOptions::new()
    .connect_with(options)
    .await?;

Acquiring Connections

// Get a connection from the pool (returns PoolConnection)
let mut conn = pool.acquire().await?;

// Begin a transaction (returns Transaction, which impls Executor)
let mut tx = pool.begin().await?;

// The pool itself implements Executor (most common usage)
sqlx::query("SELECT 1").fetch_one(&pool).await?;

Pool Lifecycle

• Startup: min_connections connections are created immediately
• On demand: New connections are created up to max_connections when needed
• Idle cleanup: Connections idle longer than idle_timeout are closed (but pool retains at least min_connections)
• Lifetime recycling: Connections older than max_lifetime are closed and replaced
• Health check: If test_before_acquire is true (default), connections are pinged before reuse

Pool Exhaustion

If all max_connections are in use and a new acquire request comes in, it waits up to acquire_timeout. If the timeout elapses, it returns Error::PoolTimedOut.

Common solutions:

• Increase max_connections
• Decrease query execution time
• Use transactions to batch multiple operations
• Use streaming (fetch()) instead of fetch_all() to avoid holding connections while processing large result sets

Graceful Shutdown

// Close the pool gracefully (waits for all connections to be returned)
pool.close().await;

This is important in application shutdown to avoid leaving transactions in progress.

Database-Specific Features

PostgreSQL

PostgreSQL is the most feature-rich backend in sqlx.

PgListener (LISTEN/NOTIFY)

PgListener provides async PostgreSQL LISTEN/NOTIFY functionality with automatic reconnection:

use sqlx::postgres::PgListener;
use futures::StreamExt;

// Connect and subscribe to a channel
let mut listener = PgListener::connect_with(&pool).await?;
listener.listen("user_events").await?;

// Blocking receive
let notification = listener.recv().await?;
println!("Channel: {}", notification.channel());
println!("Payload: {}", notification.payload());

// Stream-based (using futures::StreamExt)
while let Some(notification) = listener.next().await {
    let notification = notification?;
    println!("Received '{}' on '{}': {}",
        notification.payload(), notification.channel(), notification.payload());
}

Sending notifications:

sqlx::query("NOTIFY user_events, 'user_created:42'")
    .execute(&pool)
    .await?;

Key behavior: PgListener automatically reconnects if the connection dies. You do not need to handle reconnection manually.

PostgreSQL Arrays

Vec<T> maps directly to PostgreSQL array types:

// Reading arrays
let tags: Vec<String> = sqlx::query_scalar!(
    "SELECT array_agg(tag) FROM posts"
).fetch_one(&pool).await?;

// Writing arrays
sqlx::query!("INSERT INTO posts (tags) VALUES ($1)", &vec!["rust", "sqlx"] as &[&str])
    .execute(&pool)
    .await?;

// Array of a custom type (requires #[sqlx(type_name = "...")])
let roles: Vec<UserRole> = sqlx::query_scalar!(
    "SELECT array_agg(role) FROM users"
).fetch_one(&pool).await?;

PostgreSQL COPY (Bulk Import)

For high-performance bulk data loading:

use sqlx::postgres::PgPoolCopyExt;

// Binary COPY
let mut writer = pool.copy_in_raw("COPY users FROM STDIN WITH (FORMAT binary)").await?;
// Write binary-encoded data with .send()
writer.finish().await?;

// CSV COPY
let mut writer = pool.copy_in_raw("COPY users (id, name, email) FROM STDIN WITH (FORMAT csv)").await?;
writer.send(b"1,Alice,alice@example.com\n" as &[u8]).await?;
writer.send(b"2,Bob,bob@example.com\n" as &[u8]).await?;
let rows = writer.finish().await?;
println!("Imported {} rows", rows);

PostgreSQL Ranges

Range types (int4range, int8range, numrange, tsrange, tstzrange, daterange) are supported via sqlx::postgres::types::PgRange:

use sqlx::postgres::types::PgRange;

// Reading a range
let range: PgRange<chrono::NaiveDateTime> = sqlx::query_scalar!(
    "SELECT valid_during FROM events WHERE id = $1",
    event_id
).fetch_one(&pool).await?;

PostgreSQL Composite Types

Custom composite types are supported with #[sqlx(type_name = "...")]:

#[derive(Debug, sqlx::Type)]
#[sqlx(type_name = "address")]
struct Address {
    street: String,
    city: String,
    zip: String,
}

#[derive(Debug, FromRow)]
struct User {
    id: i64,
    name: String,
    address: Address,
}

PostgreSQL SSL Modes

use sqlx::postgres::PgSslMode;

// Available modes:
PgSslMode::Disable      // No SSL
PgSslMode::Allow        // Try plaintext first; if that fails, try SSL
PgSslMode::Prefer       // Try SSL first; if that fails, try plaintext (default)
PgSslMode::Require     // Require SSL, but don't verify certificate
PgSslMode::VerifyCa    // Require SSL + verify certificate authority
PgSslMode::VerifyFull  // Require SSL + verify certificate + hostname match

MySQL

MySQL-Specific Connection

use sqlx::mysql::{MySqlConnectOptions, MySqlPoolOptions, MySqlSslMode};

let pool = MySqlPoolOptions::new()
    .max_connections(10)
    .connect_with(
        MySqlConnectOptions::new()
            .host("localhost")
            .port(3306)
            .username("root")
            .password("password")
            .database("mydb")
            .ssl_mode(MySqlSslMode::Preferred)
            .charset("utf8mb4")  // Set character set
    )
    .await?;

MySQL Placeholder Syntax

MySQL uses ? instead of $1:

// Correct for MySQL
sqlx::query("SELECT * FROM users WHERE id = ? AND active = ?")
    .bind(1)
    .bind(true)
    .fetch_one(&pool)
    .await?;

// WRONG — $1 is PostgreSQL syntax
// sqlx::query("SELECT * FROM users WHERE id = $1")  // Will fail on MySQL

MySQL INSERT and Auto-Increment

MySQL does not support RETURNING *. Use LAST_INSERT_ID() instead:

sqlx::query("INSERT INTO users (name, email) VALUES (?, ?)")
    .bind("Alice")
    .bind("alice@example.com")
    .execute(&pool)
    .await?;

let id: u64 = sqlx::query_scalar("SELECT LAST_INSERT_ID()")
    .fetch_one(&pool)
    .await?;

MySQL Stored Procedures

let result = sqlx::query("CALL my_procedure(?, ?)")
    .bind(param1)
    .bind(param2)
    .execute(&pool)
    .await?;

SQLite

In-Memory Database

Ideal for testing and temporary data:

// In-memory (fastest, data lost when connection closes)
let pool = SqlitePool::connect("sqlite::memory:").await?;

// File-based
let pool = SqlitePool::connect("sqlite:./mydb.sqlite").await?;

SQLite Connect Options

use sqlx::sqlite::{SqliteConnectOptions, SqliteJournalMode, SqlitePoolOptions};
use std::time::Duration;

let pool = SqlitePoolOptions::new()
    .max_connections(5)  // Keep low for SQLite
    .connect_with(
        SqliteConnectOptions::new()
            .filename("./mydb.sqlite")
            .journal_mode(SqliteJournalMode::Wal)    // WAL for better concurrency
            .busy_timeout(Duration::from_secs(5))     // Wait if DB is locked
            .create_if_missing(true)                   // Create file if missing
            .foreign_keys(true)                       // Enforce FK constraints
    )
    .await?;

SQLite PRAGMA Settings

Set PRAGMA values through connect options or raw queries:

// Via connect options
let options = SqliteConnectOptions::new()
    .filename("./mydb.sqlite")
    .foreign_keys(true)
    .journal_mode(SqliteJournalMode::Wal)
    .synchronous(SqliteSynchronous::Normal)
    .pragma("cache_size", "-8000");  // 8MB cache

// Via raw query
sqlx::query("PRAGMA journal_mode = WAL")
    .execute(&pool)
    .await?;

SQLite Journal Modes

SQLite Boolean Pitfall

SQLite has no native boolean type. Booleans are stored as 0 (false) and 1 (true):

// Inserting boolean (sqlx handles the conversion)
sqlx::query("INSERT INTO users (name, active) VALUES ($1, $2)")
    .bind("Alice")
    .bind(true)  // Stored as 1
    .execute(&pool)
    .await?;

// Querying boolean (sqlx handles the conversion back)
let active: bool = sqlx::query_scalar("SELECT active FROM users WHERE id = $1")
    .bind(1)
    .fetch_one(&pool)
    .await?;  // Reads 1 as true

// But in raw SQL, compare with integers:
sqlx::query("SELECT * FROM users WHERE active = 1")  // Not WHERE active = TRUE

SQLite Feature Flags

SQLite No Native Array Type

SQLite has no native array type. Use JSON serialization as a workaround:

// Store array as JSON text
let tags = vec!["rust", "sqlx"];
sqlx::query("INSERT INTO posts (tags) VALUES ($1)")
    .bind(serde_json::to_string(&tags).unwrap())
    .execute(&pool)
    .await?;

// Read back
let tags_str: String = sqlx::query_scalar("SELECT tags FROM posts WHERE id = $1")
    .bind(1)
    .fetch_one(&pool)
    .await?;
let tags: Vec<String> = serde_json::from_str(&tags_str).unwrap();

Error Handling

sqlx::Error Enum

sqlx provides a comprehensive error type through sqlx::Error. Understanding its variants is essential for writing robust database code.

use sqlx::Error;

match result {
    Ok(user) => println!("Found user: {}", user.name),
    Err(Error::RowNotFound) => println!("No user found"),
    Err(Error::Database(db_err)) => {
        println!("DB error: {}", db_err.message());
        if let Some(code) = db_err.code() {
            println!("Error code: {}", code);
        }
    }
    Err(Error::PoolTimedOut) => println!("Timed out waiting for a connection"),
    Err(Error::PoolClosed) => println!("Connection pool was closed"),
    Err(Error::Io(e)) => println!("IO error: {}", e),
    Err(Error::Tls(e)) => println!("TLS error: {}", e),
    Err(Error::Configuration(e)) => println!("Config error: {}", e),
    Err(Error::Protocol(e)) => println!("Protocol error: {}", e),
    Err(Error::ColumnNotFound(name)) => println!("Column '{}' not found in row", name),
    Err(Error::Decode(e)) => println!("Failed to decode value: {}", e),
    Err(Error::Encode(e)) => println!("Failed to encode parameter: {}", e),
    Err(Error::TypeNotFound { type_name }) => println!("Unknown SQL type: {}", type_name),
    Err(Error::Migration(e)) => println!("Migration error: {}", e),
    Err(Error::WorkerCrashed) => println!("Background pool worker panicked"),
    Err(e) => println!("Other error: {}", e),
}

Common Error Variants in Detail

RowNotFound

Returned by fetch_one() and fetch_optional() (as None) when no rows match:

// fetch_one returns Error::RowNotFound if 0 rows
let user = sqlx::query_as!(User, "SELECT * FROM users WHERE id = $1", 999)
    .fetch_one(&pool)
    .await
    .unwrap_err(); // Error::RowNotFound

// fetch_optional returns Option<T>
let user: Option<User> = sqlx::query_as!(User, "SELECT * FROM users WHERE id = $1", 999)
    .fetch_optional(&pool)
    .await?;
// user is None — no error

Database Error

Wraps database-specific errors with rich context:

if let Error::Database(db_err) = result.err() {
    // The human-readable error message
    println!("Message: {}", db_err.message());

    // The SQLSTATE error code (PostgreSQL/MySQL)
    if let Some(code) = db_err.code() {
        println!("Code: {}", code);
    }

    // The constraint that was violated (if applicable)
    if let Some(constraint) = db_err.constraint() {
        println!("Constraint: {}", constraint);
    }

    // The table involved (if applicable)
    if let Some(table) = db_err.table() {
        println!("Table: {}", table);
    }

    // The column involved (if applicable)
    if let Some(column) = db_err.column() {
        println!("Column: {}", column);
    }

    // The original error from the database driver
    println!("Original: {:?}", db_err.into_source());
}

PostgreSQL Error Codes

Common SQLSTATE codes to handle:

Practical Error Handling Patterns

Unique Constraint (Duplicate Entry)

async fn create_user(pool: &PgPool, name: &str, email: &str) -> Result<User, CreateUserError> {
    sqlx::query_as!(
        User,
        "INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *",
        name, email
    )
    .fetch_one(pool)
    .await
    .map_err(|e| match e {
        Error::Database(db_err) if db_err.code().as_deref() == Some("23505") => {
            CreateUserError::AlreadyExists(db_err.constraint().unwrap_or("unknown").to_string())
        }
        e => CreateUserError::Database(e),
    })
}

#[derive(Debug)]
enum CreateUserError {
    AlreadyExists(String),
    Database(sqlx::Error),
}

Not Found vs Other Errors

async fn get_user(pool: &PgPool, id: i64) -> Result<User, AppError> {
    sqlx::query_as!(User, "SELECT * FROM users WHERE id = $1", id)
        .fetch_optional(pool)
        .await?
        .ok_or(AppError::NotFound(format!("User {} not found", id)))
}

#[derive(Debug)]
enum AppError {
    NotFound(String),
    Database(sqlx::Error),
}

Pool Timeout

use std::time::Duration;

// Increase acquire timeout if timeouts are frequent
let pool = PgPoolOptions::new()
    .acquire_timeout(Duration::from_secs(10))
    .max_connections(20)
    .connect(&database_url)
    .await?;

// Handle timeout gracefully
match pool.acquire().await {
    Ok(conn) => { /* use connection */ }
    Err(Error::PoolTimedOut) => {
        // Retry or return a service-unavailable response
        eprintln!("Connection pool exhausted");
    }
    Err(e) => return Err(e),
}

Error Conversion

IntoResponse for Axum

use axum::response::{IntoResponse, Response};
use axum::http::StatusCode;

impl IntoResponse for AppError {
    fn into_response(self) -> Response {
        let (status, message) = match &self {
            AppError::NotFound(msg) => (StatusCode::NOT_FOUND, msg.clone()),
            AppError::Unauthorized => (StatusCode::UNAUTHORIZED, "Unauthorized".into()),
            AppError::Validation(msg) => (StatusCode::BAD_REQUEST, msg.clone()),
            AppError::Database(Error::RowNotFound) => (StatusCode::NOT_FOUND, "Not found".into()),
            AppError::Database(Error::Database(db_err))
                if db_err.code().as_deref() == Some("23505") =>
                    (StatusCode::CONFLICT, "Resource already exists".into()),
            AppError::Database(_) => (StatusCode::INTERNAL_SERVER_ERROR, "Database error".into()),
        };
        (status, message).into_response()
    }
}

From<sqlx::Error> for Custom Errors

#[derive(Debug)]
enum MyError {
    Database(sqlx::Error),
    NotFound(String),
}

impl From<sqlx::Error> for MyError {
    fn from(e: sqlx::Error) -> Self {
        match e {
            sqlx::Error::RowNotFound => MyError::NotFound("Resource not found".into()),
            other => MyError::Database(other),
        }
    }
}

This lets you use ? directly:

async fn get_user(pool: &PgPool, id: i64) -> Result<User, MyError> {
    let user = sqlx::query_as!(User, "SELECT * FROM users WHERE id = $1", id)
        .fetch_one(pool)
        .await?;  // RowNotFound automatically converts to MyError::NotFound
    Ok(user)
}

Feature Flags

sqlx uses Cargo feature flags to control which database backends, runtimes, TLS implementations, and extra type integrations are compiled.

Default Features

sqlx = "0.8"
# Equivalent to:
# features = ["any", "json", "macros", "migrate", "derive"]

These defaults are always included unless you use default-features = false.

Common Configurations

PostgreSQL + Tokio + Rustls (Most Common)

sqlx = { version = "0.8", features = [
    "runtime-tokio",
    "tls-rustls",
    "postgres",
    "chrono",
    "uuid",
] }

PostgreSQL + Tokio + All Extras

sqlx = { version = "0.8", features = [
    "runtime-tokio",
    "tls-rustls",
    "postgres",
    "chrono",
    "uuid",
    "bigdecimal",
    "json",
    "ipnet",
    "mac_address",
    "bit-vec",
] }

MySQL + Tokio

sqlx = { version = "0.8", features = [
    "runtime-tokio",
    "tls-rustls",
    "mysql",
    "chrono",
] }

SQLite + Tokio

sqlx = { version = "0.8", features = [
    "runtime-tokio",
    "sqlite",
] }

Minimal (Smallest Binary)

sqlx = { version = "0.8", default-features = false, features = [
    "runtime-tokio",
    "tls-rustls",
    "postgres",
] }

All Databases

sqlx = { version = "0.8", features = [
    "all-databases",
    "runtime-tokio",
    "tls-rustls",
] }

Complete Feature Flag Reference

Database Backends

Runtime

Exactly one runtime feature is required. Without a runtime, pool operations will panic.

Legacy combo features (included runtime + TLS, still work but split is preferred):

• runtime-tokio-native-tls
• runtime-tokio-rustls
• runtime-async-std-native-tls
• runtime-async-std-rustls

TLS (SSL/TLS)

At most one TLS implementation should be enabled.

Extra Type Integrations

These features enable Encode/Decode support for additional Rust types:

Core Features (Defaults)

SQLite-Specific

Choosing TLS

• `tls-rustls` — Recommended. Pure Rust, fast, no C dependencies. Use this unless you have a specific reason not to.
• `tls-native-tls` — Use if you need system certificate stores, or if rustls has compatibility issues with your database server.
• `tls-none` — Use only for local development or trusted internal networks.

Choosing Runtime

• `runtime-tokio` — Most popular choice. If you're building a web server with Axum, Actix-web, or similar, use Tokio.
• `runtime-async-std` — Use if your project already uses async-std, or if you prefer its API.

Build Time Optimization

Compile-time query checking (query!() macros) connects to the database during build, which can slow down compilation. Mitigations:

1. Offline mode: Use cargo sqlx prepare + SQLX_OFFLINE=true 2. Unchecked macros: Use query_unchecked!() for non-critical queries 3. Fewer unique queries: Use parameterized queries instead of building different query strings 4. Query files: Use query_file!() to keep SQL out of Rust code and reduce recompilation

Testing and Mocking

#[sqlx::test] Attribute Macro

The #[sqlx::test] macro is sqlx's primary testing tool. It automatically creates an isolated test database for each test, applies migrations and fixtures, and cleans up afterward.

Basic Usage

use sqlx::PgPool;

#[sqlx::test]
async fn test_create_user(pool: PgPool) -> sqlx::Result<()> {
    // An isolated database is created automatically
    sqlx::query!("INSERT INTO users (name) VALUES ($1)", "Alice")
        .execute(&pool)
        .await?;

    let user = sqlx::query_as!(User, "SELECT * FROM users WHERE name = $1", "Alice")
        .fetch_one(&pool)
        .await?;

    assert_eq!(user.name, "Alice");
    Ok(())
}

With Migrations

Automatically applies migrations before the test runs:

#[sqlx::test(migrations = "./migrations")]
async fn test_with_schema(pool: PgPool) -> sqlx::Result<()> {
    // All tables, indexes, and seed data from migrations/ are ready
    sqlx::query!("INSERT INTO users (name, email) VALUES ($1, $2)", "Bob", "bob@test.com")
        .execute(&pool)
        .await?;
    Ok(())
}

With Fixtures

Apply additional SQL files (test data) after migrations:

// tests/fixtures/users.sql
// INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
// INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com');

#[sqlx::test(migrations = "./migrations", fixtures("tests/fixtures/users.sql"))]
async fn test_with_fixtures(pool: PgPool) -> sqlx::Result<()> {
    // users.sql has been executed — test data is ready
    let count: i64 = sqlx::query_scalar!("SELECT COUNT(*) FROM users")
        .fetch_one(&pool)
        .await?;
    assert_eq!(count, 2);
    Ok(())
}

Multiple Fixtures

#[sqlx::test(
    migrations = "./migrations",
    fixtures("tests/fixtures/users.sql", "tests/fixtures/posts.sql")
)]
async fn test_with_multiple_fixtures(pool: PgPool) -> sqlx::Result<()> {
    Ok(())
}

Custom Pool Options and Connect Options

For tests that need specific pool or connection configuration:

use sqlx::postgres::{PgPoolOptions, PgConnectOptions};

#[sqlx::test]
async fn test_with_custom_options(
    pool_options: PgPoolOptions,
    options: PgConnectOptions,
) -> sqlx::Result<()> {
    let pool = pool_options
        .max_connections(5)
        .connect_with(options)
        .await?;
    Ok(())
}

Supported Function Signatures

The macro recognizes these parameter patterns:

Database Requirements

Test Database Lifecycle

• Before test: A new database is created, migrations are applied, fixtures are executed
• On success: The database is deleted
• On failure: The database is left intact for debugging
• On next run: Previous failed test databases are cleaned up

Repository Pattern for Testability

For unit tests that don't need a real database, abstract SQLx behind a trait:

use async_trait::async_trait;

#[async_trait]
pub trait UserRepository: Send + Sync {
    async fn find_by_id(&self, id: i64) -> Result<Option<User>>;
    async fn create(&self, name: &str, email: &str) -> Result<User>;
    async fn list_active(&self) -> Result<Vec<User>>;
}

// Production implementation
pub struct PgUserRepository {
    pool: PgPool,
}

#[async_trait]
impl UserRepository for PgUserRepository {
    async fn find_by_id(&self, id: i64) -> Result<Option<User>> {
        sqlx::query_as!(User, "SELECT * FROM users WHERE id = $1", id)
            .fetch_optional(&self.pool)
            .await
            .map_err(Into::into)
    }

    async fn create(&self, name: &str, email: &str) -> Result<User> {
        sqlx::query_as!(
            User,
            "INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id, name, email, created_at",
            name, email
        )
        .fetch_one(&self.pool)
        .await
        .map_err(Into::into)
    }

    async fn list_active(&self) -> Result<Vec<User>> {
        sqlx::query_as!(User, "SELECT * FROM users WHERE active = true")
            .fetch_all(&self.pool)
            .await
            .map_err(Into::into)
    }
}

// Mock implementation for unit tests
pub struct MockUserRepository {
    users: std::sync::Mutex<Vec<User>>,
}

#[async_trait]
impl UserRepository for MockUserRepository {
    async fn find_by_id(&self, id: i64) -> Result<Option<User>> {
        let users = self.users.lock().unwrap();
        Ok(users.iter().find(|u| u.id == id).cloned())
    }

    async fn create(&self, name: &str, email: &str) -> Result<User> {
        let mut users = self.users.lock().unwrap();
        let user = User { id: users.len() as i64 + 1, name: name.to_string(), email: email.to_string(), active: true };
        users.push(user.clone());
        Ok(user)
    }

    async fn list_active(&self) -> Result<Vec<User>> {
        let users = self.users.lock().unwrap();
        Ok(users.iter().filter(|u| u.active).cloned().collect())
    }
}

// Usage in a service
pub struct UserService<U: UserRepository> {
    repo: U,
}

impl<U: UserRepository> UserService<U> {
    pub async fn get_user(&self, id: i64) -> Result<User> {
        self.repo.find_by_id(id).await?
            .ok_or_else(|| anyhow::anyhow!("User not found"))
    }
}

In-Memory SQLite for Tests

SQLite in-memory databases are excellent for fast, isolated tests when your SQL is database-agnostic:

use sqlx::SqlitePool;

async fn setup_test_db() -> SqlitePool {
    let pool = SqlitePool::connect("sqlite::memory:").await.unwrap();
    sqlx::query("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL)")
        .execute(&pool)
        .await
        .unwrap();
    pool
}

#[tokio::test]
async fn test_user_creation() {
    let pool = setup_test_db().await;
    sqlx::query("INSERT INTO users (name) VALUES ($1)")
        .bind("Alice")
        .execute(&pool)
        .await
        .unwrap();
    // ... assertions
}

Caveat: In-memory SQLite databases are connection-specific. If the pool creates multiple connections, they won't share the same in-memory database. Use max_connections(1) or a file-based database.

Common Testing Patterns

Setup/Teardown with Fixtures

async fn seed_users(pool: &PgPool) -> sqlx::Result<()> {
    sqlx::query!("INSERT INTO users (name, email) VALUES ($1, $2)", "Alice", "alice@test.com")
        .execute(pool).await?;
    sqlx::query!("INSERT INTO users (name, email) VALUES ($1, $2)", "Bob", "bob@test.com")
        .execute(pool).await?;
    Ok(())
}

#[sqlx::test(migrations = "./migrations")]
async fn test_list_users(pool: PgPool) -> sqlx::Result<()> {
    seed_users(&pool).await?;
    let users = sqlx::query_as!(User, "SELECT * FROM users")
        .fetch_all(&pool).await?;
    assert_eq!(users.len(), 2);
    Ok(())
}

Integration Test File Structure

tests/
├── common/
│   └── mod.rs          # Shared helpers (setup, seed functions)
├── user_tests.rs        # #[sqlx::test] tests for user operations
├── post_tests.rs        # #[sqlx::test] tests for post operations
└── fixtures/
    ├── users.sql        # Test data for users
    └── posts.sql        # Test data for posts

Transactions and Migrations

Transactions

Transactions group multiple queries into an atomic unit. If any query fails, all changes are rolled back. If all succeed, changes are committed.

Manual Transactions

let mut tx = pool.begin().await?;

sqlx::query!("INSERT INTO users (name) VALUES ($1)", "Alice")
    .execute(&mut *tx)  // Note: &mut *tx, not &tx
    .await?;

sqlx::query!("INSERT INTO audit_log (action) VALUES ($1)", "user created")
    .execute(&mut *tx)
    .await?;

// Explicitly commit
tx.commit().await?;

// If you don't call commit(), the Transaction drops and auto-rollbacks.
// This is by design: you must explicitly commit for changes to persist.

Closure-Based Transactions

The closure pattern auto-commits on Ok(()) and auto-rollbacks on Err. The method is on Connection, not Pool, so you must acquire a connection first:

use sqlx::{Connection, PgPool};

let mut conn = pool.acquire().await?;
let result = conn.transaction::<_, _, sqlx::Error>(|tx| {
    Box::pin(async move {
        sqlx::query!("INSERT INTO users (name) VALUES ($1)", "Bob")
            .execute(&mut **tx)
            .await?;

        let user_id = sqlx::query_scalar!("SELECT lastval()")
            .fetch_one(&mut **tx)
            .await?;

        sqlx::query!("INSERT INTO profiles (user_id, bio) VALUES ($1, $2)")
            .bind(user_id)
            .bind("Hello world")
            .execute(&mut **tx)
            .await?;

        Ok(user_id)
    })
}).await?;
// result is i64 — the value returned from inside the closure

Note the double dereference **tx: the closure receives &mut Transaction, and you need to pass &mut *<deref> to execute().

Nested Transactions (SAVEPOINTs)

Nested transactions use database SAVEPOINTs. Rolling back a nested transaction only rolls back to the savepoint, not the outer transaction. Requires `sqlx::Acquire` to bring `begin()` into scope on a `Transaction`.

use sqlx::Acquire;

let mut tx = pool.begin().await?;

sqlx::query!("INSERT INTO users (name) VALUES ($1)", "Charlie")
    .execute(&mut *tx)
    .await?;

// Start a nested transaction
let mut nested = tx.begin().await?;

sqlx::query!("INSERT INTO orders (user_id, total) VALUES ($1, $2)")
    .bind(1i64)
    .bind(99.99f64)
    .execute(&mut *nested)
    .await?;

// Something went wrong — rollback only the nested transaction
// The user "Charlie" still exists
nested.rollback().await?;

// Outer transaction commits normally
tx.commit().await?;

Transactions with Generic Executor

Functions that accept an executor can be used with pools, connections, or transactions:

use sqlx::PgExecutor;

async fn transfer_funds<'e, E>(executor: E, from: i64, to: i64, amount: f64) -> sqlx::Result<()>
where
    E: PgExecutor<'e>,
{
    sqlx::query!("UPDATE accounts SET balance = balance - $1 WHERE id = $2", amount, from)
        .execute(executor)
        .await?;

    sqlx::query!("UPDATE accounts SET balance = balance + $1 WHERE id = $2", amount, to)
        .execute(executor)
        .await?;

    Ok(())
}

// Use with a pool (no transaction)
transfer_funds(&pool, 1, 2, 100.0).await?;

// Use within a transaction (atomic)
let mut conn = pool.acquire().await?;
conn.transaction(|tx| {
    Box::pin(async move {
        transfer_funds(&mut **tx, 1, 2, 100.0).await?;
        Ok(())
    })
}).await?;

Migrations

Migration File Structure

Migrations live in a migrations/ directory (by default). Each migration is a SQL file with a timestamp prefix:

migrations/
├── 20240101000000_create_users.sql
├── 20240102000000_create_posts.sql
├── 20240103000000_add_email_index.sql
└── 20240104000000_create_comments.sql

Creating Migrations

# Create a new migration file (simple, up only)
sqlx migrate add create_users
# Creates: migrations/20240101000000_create_users.sql

# Create a reversible migration (with down.sql)
sqlx migrate add create_users -r
# Creates: migrations/20240101000000_create_users.up.sql
#          migrations/20240101000000_create_users.down.sql

Writing Migrations

Simple (up-only):

-- migrations/20240101000000_create_users.sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users(email);

Reversible (separator-based):

-- migrations/20240101000000_create_users.sql

-- This is the "up" migration (applied when running forward)
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- This is the "down" migration (applied when reverting)
-- down
DROP TABLE users;

The -- down separator splits the file into up and down sections.

Running Migrations

# Apply all pending migrations
sqlx migrate run

# Rollback the last applied migration
sqlx migrate revert

# Show migration status (applied vs pending)
sqlx migrate info

# Create the database first (if it doesn't exist)
sqlx database create

# Drop and recreate the database (dangerous!)
sqlx database reset

Embedding Migrations in Code

Embed migrations into the binary using the migrate!() macro:

// Reads from ./migrations by default
let migrator = sqlx::migrate!();

// Custom path (relative to the crate root where Cargo.toml is located)
let migrator = sqlx::migrate!("./migrations");

// Run all pending migrations
migrator.run(&pool).await?;

This is useful for applications that self-migrate on startup (e.g., CLI tools, embedded databases).

Migrations in Tests

The #[sqlx::test] macro automatically applies migrations before each test:

#[sqlx::test(migrations = "./migrations")]
async fn test_user_creation(pool: PgPool) -> sqlx::Result<()> {
    // The database is fully migrated at this point
    sqlx::query!("INSERT INTO users (name) VALUES ($1)", "Test User")
        .execute(&pool).await?;
    Ok(())
}

Migration Configuration (sqlx.toml)

For more complex setups (e.g., multiple migration sources), create a sqlx.toml:

# sqlx.toml
[migrate]
# Directory containing migration files (relative to sqlx.toml location)
migrations_dir = "./migrations"

Migration Best Practices

1. Always make migrations reversible — Use the -- down separator or separate .up.sql/.down.sql files 2. Never modify existing migrations — Create new ones instead. Modified migrations won't match what's already been applied. 3. Test migrations against a clean database — sqlx database reset drops and recreates, then runs all migrations 4. Order matters — Migrations run in timestamp order. Ensure dependencies are respected. 5. Use transactions where possible — Wrapping DDL in transactions (PostgreSQL supports this) ensures atomicity

Type Mapping

sqlx maps between Rust types and SQL types. This reference covers all built-in mappings, feature-flagged types, and how to define custom type mappings.

Built-in Type Mappings

Numeric Types

String and Binary Types

Boolean

SQLite pitfall: SQLite stores booleans as 0/1 integers. In SQL queries, compare with = 1 or = 0, not = TRUE.

Date and Time Types

Requires the chrono or time feature flag.

UUID

Requires the uuid feature flag.

JSON Types

The json feature is enabled by default.

Decimal Types

Nullable Types

When reading, a NULL column always maps to Option<T>::None. When binding, None inserts NULL.

Network Types (PostgreSQL only)

Array Types (PostgreSQL only)

Array support requires the element type T to implement Encode/Decode.

FromRow Derive Attributes

use sqlx::FromRow;

#[derive(Debug, FromRow)]
struct User {
    id: i64,
    // Rename: map a SQL column to a differently-named Rust field
    #[sqlx(rename = "full_name")]
    name: String,

    // Default: provide a default value if the column is missing from the query
    #[sqlx(default)]
    nickname: String,

    // Skip: ignore this field entirely (field must have a Default impl)
    #[sqlx(skip)]
    computed_field: String,

    // Flatten: embed another FromRow struct (for JOINs)
    #[sqlx(flatten)]
    address: Address,

    // JSON: deserialize a JSON/JSONB column into a typed struct
    #[sqlx(json)]
    metadata: Json<Metadata>,

    // Try From: convert using TryFrom<&str> or similar
    #[sqlx(try_from = "String")]
    email: Email,
}

Custom Type Implementations

Transparent Wrapper (Newtype Pattern)

The simplest way to map a custom Rust type to a SQL type:

#[derive(Debug, sqlx::Type)]
#[sqlx(transparent)]
struct Email(String);

Enum Mapping (PostgreSQL)

Map a Rust enum to a PostgreSQL enum type:

#[derive(Debug, sqlx::Type)]
#[sqlx(type_name = "user_role", rename_all = "lowercase")]
enum UserRole {
    Admin,
    User,
    Guest,
}

The corresponding PostgreSQL type must exist:

CREATE TYPE user_role AS ENUM ('admin', 'user', 'guest');

Composite Type (PostgreSQL)

#[derive(Debug, sqlx::Type)]
#[sqlx(type_name = "address")]
struct Address {
    street: String,
    city: String,
    zip: String,
}

The corresponding PostgreSQL type:

CREATE TYPE address AS (
    street TEXT,
    city TEXT,
    zip TEXT
);

Manual Encode/Decode Implementation

For types that need custom serialization logic:

impl<'q> sqlx::Encode<'q, sqlx::Postgres> for MyType {
    fn encode_by_ref(&self, buf: &mut sqlx::postgres::PgArgumentBuffer) -> sqlx::encode::IsNull {
        // Write to buffer
        sqlx::encode::IsNull::No
    }
}

impl<'r> sqlx::Decode<'r, sqlx::Postgres> for MyType {
    fn decode(value: sqlx::postgres::PgValueRef<'r>) -> Result<Self, Box<dyn std::error::Error + Send + Sync>> {
        // Read from value
        Ok(MyType { /* ... */ })
    }
}

impl sqlx::Type<sqlx::Postgres> for MyType {
    fn type_info() -> sqlx::postgres::PgTypeInfo {
        // Return the SQL type this maps to
        <String as sqlx::Type<sqlx::Postgres>>::type_info()
    }
}

Text<T> Wrapper

sqlx::types::Text<T> maps any type that implements Display and FromStr to/from a SQL text column:

use sqlx::types::Text;

// If Email impl Display + FromStr
let email: Text<Email> = row.get("email");
// Read: Text(Email { ... })
// Write: binds as the Display string

This is useful for custom string types without writing full Encode/Decode implementations.