Databases Basics

10 examples for Rust database fundamentals - 7 basic and 3 intermediate.

Prerequisites

[dependencies]
sqlx = { version = "0.8", features = ["runtime-tokio", "postgres", "macros"] }
tokio = { version = "1", features = ["full"] }

• sqlx 0.8 provides async PostgreSQL access with optional compile-time query checking.
• Set DATABASE_URL for local development.

Basic Examples

1. Connect with sqlx

Open a single connection for scripts.

let conn = sqlx::PgConnection::connect(&std::env::var("DATABASE_URL")?).await?;

• PgConnection is one connection, not a pool.
• Prefer pools for servers.
• Connection string format: postgres://user:pass@host/db.

Related: sqlx - Full sqlx guide

2. Connection Pool

Share connections across async tasks.

let pool = sqlx::postgres::PgPoolOptions::new()
    .max_connections(10)
    .connect(&database_url)
    .await?;

• Pool hands out connections and returns them when done.
• Size from CPU workers and DB max_connections.
• Clone PgPool cheaply - inner Arc.

Related: Connection Pooling - Sizing guidance

3. Parameterized Query

Prevent SQL injection.

let row: (i64,) = sqlx::query_as("SELECT COUNT(*) FROM users WHERE active = $1")
    .bind(true)
    .fetch_one(&pool)
    .await?;

• Always use $1 placeholders, never format strings.
• query_as maps columns to tuple or struct.
• Errors are sqlx::Error.

4. Insert Returning

Get generated IDs from PostgreSQL.

let rec: (i64,) = sqlx::query_as(
    "INSERT INTO items (name) VALUES ($1) RETURNING id"
)
.bind("widget")
.fetch_one(&pool)
.await?;

• RETURNING avoids second round trip.
• Works with query_scalar for single values.

5. Fetch Optional Row

Handle missing rows without error.

let user = sqlx::query_as::<_, User>("SELECT id, email FROM users WHERE id = $1")
    .bind(id)
    .fetch_optional(&pool)
    .await?;

• fetch_optional returns Option<Row>.
• Map None to 404 in HTTP handlers.

6. Migrations on Startup

Apply schema before serving traffic.

sqlx::migrate!("./migrations").run(&pool).await?;

• SQL files in migrations/ directory.
• Versioned filenames with timestamps.
• Run in CI and deploy pipeline too.

Related: Migrations - Schema evolution

7. Graceful Pool Shutdown

Close pool on shutdown signal.

pool.close().await;

• Waits for in-flight queries to finish (with timeout).
• Pair with Axum graceful shutdown.

Intermediate Examples

8. Transaction Block

Atomic multi-statement updates.

let mut tx = pool.begin().await?;
sqlx::query("UPDATE accounts SET balance = balance - $1 WHERE id = $2")
    .bind(100_i64).bind(1_i64).execute(&mut *tx).await?;
sqlx::query("UPDATE accounts SET balance = balance + $1 WHERE id = $2")
    .bind(100_i64).bind(2_i64).execute(&mut *tx).await?;
tx.commit().await?;

• rollback happens automatically on ? drop without commit.
• Keep transactions short.

Related: Transactions & Isolation - Isolation levels

9. Store Pool in Axum State

Wire database into HTTP service.

#[derive(Clone)]
struct AppState { pool: sqlx::PgPool }
 
let state = AppState { pool: pool.clone() };
let app = Router::new().route("/users", get(list_users)).with_state(state);

• PgPool is Clone cheaply.
• One pool per process typically.

Related: Extractors & State - Axum state

10. Health Check Query

Verify DB connectivity for /ready.

sqlx::query("SELECT 1").fetch_one(&pool).await?;

• Lightweight probe for orchestrators.
• Distinguish /health (process up) from /ready (DB up).

Stack versions: This page was written for Rust 1.97.0 (edition 2024), Tokio 1.x, Axum 0.8, serde 1.0, sqlx 0.8, clap 4, and Polars 0.46+.