Updated for SQLx 0.9.x & Rust 2024 Edition

The Guide to SQLx in Rust

If you are building a backend service in Rust, you need to talk to a database. Master SQLx—the industry-standard async SQL toolkit that validates your queries at compile time without forcing you into an ORM DSL. This site is a tutorial not associated with the project maintainers.

Get Started Jump to SeaORM Integration

1. What is SQLx and Why Use It?

SQLx is a 100% asynchronous, pure-Rust database driver supporting PostgreSQL, MySQL, and SQLite. It is not an Object-Relational Mapper (ORM). It is an async SQL toolkit that stays out of your way, lets you write pure SQL, and validates your queries at compile time.

Compile-Time Safety

Catch syntax errors, misspelled columns, and type mismatches before your code even runs. Bad queries break the build.

No DSL to Learn

If you know SQL, you know SQLx. There is no domain-specific language to wrestle with when writing complex JOINs or window functions.

Zero-Cost Abstraction

Because queries are checked at compile time and mapped directly to structs, there is no heavy runtime ORM translation overhead.

2. Alternatives & Tradeoffs

How does SQLx compare to the rest of the Rust database ecosystem?

Framework	Type	Tradeoffs vs SQLx
Diesel	Sync / Async DSL ORM	The most mature ORM. Diesel uses a heavily-typed Rust DSL to build queries safely. Tradeoff: Steeper learning curve; hard to write hyper-complex SQL.
SeaORM	Async Active Record ORM	Built on top of SQLx. Fantastic if you want an Active Record experience (auto-generating queries, relational mapping). Tradeoff: Introduces more runtime overhead and "magic" (See Section 6).
Rusqlite	Sync SQLite Driver	Excellent for local CLI/desktop apps. Tradeoff: If building an async web API (e.g., Axum/Actix), SQLx's async native driver is superior for preventing thread-blocking.

3. Project Setup (v0.9.x)

Let’s build a ticketing system backend using PostgreSQL and Tokio.

Dependencies

Add the following to your Cargo.toml. Notice we enable `runtime-tokio`, `postgres`, and `time` for datetime handling.

[dependencies]
tokio = { version = "1", features = ["full"] }
sqlx = { version = "0.9", features = ["postgres", "runtime-tokio", "macros", "time"] }
anyhow = "1.0"   # For clean error propagation
dotenvy = "0.15" # For loading .env variables

The CLI & Migrations

You need the SQLx CLI to manage databases and migrations. As of v0.9.x, the repository removed `Cargo.lock`, so `cargo binstall` is recommended.

Terminal

# Install the CLI
cargo binstall sqlx-cli

# Create database & initial migration
sqlx database create
sqlx migrate add create_tickets

migrations/..._create_tickets.sql

CREATE TABLE tickets (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

4. Core Tutorial: Writing Production Code

Connecting Properly (Pool Configuration)

Never create a single connection for a web service. Always configure a connection pool with proper limits and timeouts.

use sqlx::postgres::{PgPool, PgPoolOptions};
use std::time::Duration;

pub async fn establish_connection(db_url: &str) -> anyhow::Result<PgPool> {
    // Production-ready pool configuration
    let pool = PgPoolOptions::new()
        .max_connections(50)
        .acquire_timeout(Duration::from_secs(3))
        .idle_timeout(Duration::from_secs(10))
        .connect(db_url)
        .await?;

    Ok(pool)
}

Inserting and Fetching Data

SQLx uses parameter binding ($1, $2 in Postgres) to completely prevent SQL injection. The query_as! macro automatically maps fields at compile time without needing #[derive(FromRow)].

use time::OffsetDateTime;

#[derive(Debug)]
pub struct Ticket {
    pub id: i32,
    pub title: String,
    pub description: Option<String>, // Maps perfectly to NULLable TEXT
    pub created_at: OffsetDateTime,
}

pub async fn create_ticket(pool: &PgPool, title: &str, desc: &str) -> anyhow::Result<i32> {
    let rec = sqlx::query!(
        r#"
        INSERT INTO tickets (title, description)
        VALUES ($1, $2)
        RETURNING id
        "#,
        title,
        desc
    )
    .fetch_one(pool)
    .await?;

    Ok(rec.id)
}

pub async fn get_all_tickets(pool: &PgPool) -> anyhow::Result<Vec<Ticket>> {
    let tickets = sqlx::query_as!(
        Ticket,
        r#"SELECT id, title, description, created_at FROM tickets ORDER BY created_at DESC"#
    )
    .fetch_all(pool)
    .await?;

    Ok(tickets)
}

Transaction Handling

Transactions in SQLx automatically ROLLBACK if they are dropped from scope before .commit() is called. This is a brilliant use of Rust's ownership model.

pub async fn update_ticket_securely(pool: &PgPool, ticket_id: i32) -> anyhow::Result<()> {
    let mut tx = pool.begin().await?;

    // Pass `&mut *tx` to the executor
    sqlx::query!("UPDATE tickets SET title = 'Reviewed' WHERE id = $1", ticket_id)
        .execute(&mut *tx)
        .await?;

    // If we return an error before this line, the transaction safely rolls back!
    tx.commit().await?;
    Ok(())
}

5. Advanced SQLx Features

Custom Types & Database ENUMs

You can map Postgres ENUMs directly to Rust enums using the sqlx::Type macro. This provides incredibly strict type safety across your domain layer.

#[derive(sqlx::Type, Debug, Clone)]
#[sqlx(type_name = "ticket_status", rename_all = "lowercase")]
pub enum TicketStatus {
    Open,
    InProgress,
    Resolved,
}

// Now use it inside your structs normally!
#[derive(Debug)]
pub struct Ticket {
    pub id: i32,
    pub status: TicketStatus, // SQLx handles the mapping transparently
}

JSON & JSONB Support

Storing unstructured data? Enable the "json" feature flag on your SQLx dependency to seamlessly map Postgres JSONB columns to serde_json::Value or custom Serde structs.

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

#[derive(Serialize, Deserialize, Debug)]
pub struct Metadata {
    pub source: String,
    pub tags: Vec<String>,
}

// Wrap your custom struct in `Json`
let tags = vec!["bug".to_string()];
let meta = Json(Metadata { source: "web".into(), tags });

sqlx::query!(
    "INSERT INTO events (payload) VALUES ($1)", 
    meta as Json<Metadata>
).execute(pool).await?;

Automated Code Migrations

Instead of relying on the CLI to apply migrations on the production server, embed them directly into your binary. When your app boots up, it updates the database schema automatically.

pub async fn run_migrations(pool: &PgPool) -> anyhow::Result<()> {
    // Reads the `./migrations` folder at compile time and bundles it!
    sqlx::migrate!("./migrations")
        .run(pool)
        .await?;
        
    Ok(())
}

6. Leveling Up with SeaORM

As your application grows, writing raw SQL for every single endpoint—especially endpoints with highly dynamic filters, sorting, and pagination—becomes incredibly tedious. This is where SeaORM shines.

How it extends SQLx

SeaORM is an async, dynamic Active Record ORM built directly on top of SQLx. It uses SQLx under the hood for connection pooling and executing queries, but abstracts away the SQL string manipulation. You get the raw performance of SQLx drivers with the developer experience of a modern ORM.

When to use SeaORM instead of raw SQLx?

Dynamic Query Building: If your API users can filter by 10 different optional fields, concatenating strings in raw SQLx is miserable. SeaORM handles this beautifully.
CRUD Heavy Applications: When you have dozens of tables and just need basic Create/Read/Update/Delete endpoints, writing raw SQL is repetitive.
Database Agnosticism: SeaORM allows you to write queries that compile down to Postgres, MySQL, or SQLite dynamically without changing your syntax.

The Pain of Dynamic SQLx vs The Joy of SeaORM

Raw SQLx (Painful string building)

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

if let Some(name) = search_name {
    qb.push(" AND name LIKE ");
    qb.push_bind(format!("%{}%", name));
}

if let Some(min_age) = age_filter {
    qb.push(" AND age >= ");
    qb.push_bind(min_age);
}

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

SeaORM (Clean fluent API)

let mut query = User::find();

if let Some(name) = search_name {
    query = query.filter(user::Column::Name.contains(name));
}

if let Some(min_age) = age_filter {
    query = query.filter(user::Column::Age.gte(min_age));
}

let users = query.all(db).await?;

Entity Generation (Entity-First)

If you already built your schema using SQLx migrations, SeaORM can automatically generate your Rust Structs (Entities). By running:

sea-orm-cli generate entity -u postgres://user:pass@localhost/db -o src/entities

This creates fully typed models for your existing database, instantly allowing you to leverage the ORM without manually writing structural boilerplate.

7. Top Production Gotchas & Pain Points

After running SQLx (and SeaORM) in production, these are the pain points you will inevitably hit and how to solve them.

The Problem: Because query! checks against a live database during cargo build, your GitHub Actions / CI pipelines will fail unless you spin up a sidecar database in CI.

The Fix: Offline Mode

Run cargo sqlx prepare locally. This creates a .sqlx/ directory caching the queries.
Commit .sqlx/ to version control.
In CI, SQLx detects the directory and bypasses the live database check entirely.

Pro-tip: Use cargo sqlx prepare --workspace -- --all-targets to ensure queries inside #[tokio::test] blocks are captured.

The Problem: Sometimes a column is NOT NULL in the DB, but because of a LEFT JOIN or MAX(), the result could be NULL. SQLx strictly enforces the schema and will break your Rust struct.

The Fix: Type Overrides

Force SQLx's inference by appending ! (force NOT NULL) or ? (force Nullable) to the column alias.

let tickets = sqlx::query!(
    r#"SELECT MAX(id) as "max_id!", description as "description?" FROM tickets GROUP BY description"#
).fetch_all(pool).await?;

The Problem: SQLx heavily utilizes Prepared Statements. If you use PgBouncer in Transaction Mode, connections are randomly reassigned, breaking the prepared statements and throwing errors.

The Fix: Disable Statement Caching

Use PgConnectOptions to set statement cache capacity to 0.

let options = PgConnectOptions::from_str(&db_url)?
    .statement_cache_capacity(0);

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

No sections found