Rust database

Rust Database Patterns

sqlx: Async SQL with Compile-Time Verification

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

use sqlx::{PgPool, postgres::PgPoolOptions};
use uuid::Uuid;

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

async fn create_pool(database_url: &str) -> anyhow::Result<PgPool> {
    let pool = PgPoolOptions::new()
        .max_connections(20)
        .acquire_timeout(std::time::Duration::from_secs(5))
        .connect(database_url)
        .await?;

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

// Compile-time checked query
async fn find_user(pool: &PgPool, id: Uuid) -> anyhow::Result<Option<User>> {
    Ok(sqlx::query_as!(
        User,
        "SELECT id, name, email, created_at FROM users WHERE id = $1",
        id
    )
    .fetch_optional(pool)
    .await?)
}

async fn create_user(pool: &PgPool, name: &str, email: &str) -> anyhow::Result<User> {
    Ok(sqlx::query_as!(
        User,
        "INSERT INTO users (id, name, email, created_at)
         VALUES ($1, $2, $3, NOW())
         RETURNING id, name, email, created_at",
        Uuid::new_v4(), name, email
    )
    .fetch_one(pool)
    .await?)
}

sqlx: Transactions

async fn transfer_funds(pool: &PgPool, from: Uuid, to: Uuid, amount: i64) -> anyhow::Result<()> {
    let mut tx = pool.begin().await?;

    sqlx::query!(
        "UPDATE accounts SET balance = balance - $1 WHERE id = $2 AND balance >= $1",
        amount, from
    )
    .execute(&mut *tx)
    .await?;

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

    tx.commit().await?;
    Ok(())
}

sqlx: Dynamic Queries

use sqlx::QueryBuilder;

async fn search_users(
    pool: &PgPool,
    name_filter: Option<&str>,
    limit: i64,
) -> anyhow::Result<Vec<User>> {
    let mut qb = QueryBuilder::new(
        "SELECT id, name, email, created_at FROM users WHERE 1=1"
    );

    if let Some(name) = name_filter {
        qb.push(" AND name ILIKE ").push_bind(format!("%{name}%"));
    }

    qb.push(" ORDER BY created_at DESC LIMIT ").push_bind(limit);

    Ok(qb.build_query_as::<User>().fetch_all(pool).await?)
}

Diesel: ORM with Schema DSL

[dependencies]
diesel = { version = "2", features = ["postgres", "uuid", "r2d2"] }

diesel::table! {
    users (id) {
        id -> Uuid,
        name -> Text,
        email -> Text,
    }
}

use diesel::prelude::*;

#[derive(Debug, Queryable, Selectable)]
#[diesel(table_name = users)]
struct User { id: uuid::Uuid, name: String, email: String }

#[derive(Insertable)]
#[diesel(table_name = users)]
struct NewUser<'a> { name: &'a str, email: &'a str }

fn find_user(conn: &mut PgConnection, user_id: uuid::Uuid) -> QueryResult<Option<User>> {
    users::table
        .filter(users::id.eq(user_id))
        .select(User::as_select())
        .first(conn)
        .optional()
}

Repository Pattern

use async_trait::async_trait;

#[async_trait]
trait UserRepository: Send + Sync {
    async fn find(&self, id: Uuid) -> anyhow::Result<Option<User>>;
    async fn create(&self, name: &str, email: &str) -> anyhow::Result<User>;
}

struct PgUserRepository { pool: PgPool }

#[async_trait]
impl UserRepository for PgUserRepository {
    async fn find(&self, id: Uuid) -> anyhow::Result<Option<User>> {
        find_user(&self.pool, id).await
    }
    async fn create(&self, name: &str, email: &str) -> anyhow::Result<User> {
        create_user(&self.pool, name, email).await
    }
}

Migration Files

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

CREATE INDEX idx_users_email ON users(email);

Common Anti-Patterns

• Opening new connections per request — always use a connection pool
• N+1 queries — use JOINs or batch loading; never query in a loop
• String interpolation in SQL — always use parameterized queries
• Ignoring transaction rollback — explicitly rollback or commit in all error paths
• Unbounded queries — always add LIMIT to queries returning multiple rows