Patterns & Best Practices

Once you can connect, query, and migrate, the next question is how to structure your database code for maintainability and testability. This topic covers the patterns that keep database code clean as your application grows.

The Repository Pattern

Keep database queries behind a trait so you can swap implementations for testing:

use async_trait::async_trait;

#[derive(Debug, sqlx::FromRow)]
pub struct User {
    pub id: i64,
    pub name: String,
    pub email: String,
    pub active: bool,
}

pub struct CreateUserInput {
    pub name: String,
    pub email: String,
}

#[async_trait]
pub trait UserRepository: Send + Sync {
    async fn find_by_id(&self, id: i64) -> Result<Option<User>, sqlx::Error>;
    async fn find_all_active(&self) -> Result<Vec<User>, sqlx::Error>;
    async fn create(&self, input: CreateUserInput) -> Result<User, sqlx::Error>;
    async fn deactivate(&self, id: i64) -> Result<bool, sqlx::Error>;
}

The concrete implementation uses the connection pool:

pub struct PgUserRepository {
    pool: sqlx::PgPool,
}

impl PgUserRepository {
    pub fn new(pool: sqlx::PgPool) -> Self {
        Self { pool }
    }
}

#[async_trait]
impl UserRepository for PgUserRepository {
    async fn find_by_id(&self, id: i64) -> Result<Option<User>, sqlx::Error> {
        sqlx::query_as::<_, User>(
            "SELECT id, name, email, active FROM users WHERE id = $1"
        )
        .bind(id)
        .fetch_optional(&self.pool)
        .await
    }

    async fn find_all_active(&self) -> Result<Vec<User>, sqlx::Error> {
        sqlx::query_as::<_, User>(
            "SELECT id, name, email, active FROM users WHERE active = true"
        )
        .fetch_all(&self.pool)
        .await
    }

    async fn create(&self, input: CreateUserInput) -> Result<User, sqlx::Error> {
        sqlx::query_as::<_, User>(
            "INSERT INTO users (name, email) VALUES ($1, $2)
             RETURNING id, name, email, active"
        )
        .bind(&input.name)
        .bind(&input.email)
        .fetch_one(&self.pool)
        .await
    }

    async fn deactivate(&self, id: i64) -> Result<bool, sqlx::Error> {
        let result = sqlx::query(
            "UPDATE users SET active = false WHERE id = $1 AND active = true"
        )
        .bind(id)
        .execute(&self.pool)
        .await?;

        Ok(result.rows_affected() > 0)
    }
}

Handlers receive a dyn UserRepository instead of touching the pool directly. This separation makes testing straightforward and keeps SQL out of your business logic.

Mapping Between Database Rows & Domain Types

Sometimes your database schema does not match your domain model. Map explicitly:

#[derive(sqlx::FromRow)]
struct UserRow {
    id: i64,
    name: String,
    email: String,
    active: bool,
    metadata: serde_json::Value,
    created_at: chrono::DateTime<chrono::Utc>,
}

// Domain type with richer semantics
struct User {
    id: UserId,
    name: String,
    email: Email,
    status: UserStatus,
    preferences: UserPreferences,
    created_at: chrono::DateTime<chrono::Utc>,
}

impl TryFrom<UserRow> for User {
    type Error = ConversionError;

    fn try_from(row: UserRow) -> Result<Self, Self::Error> {
        Ok(User {
            id: UserId(row.id),
            name: row.name,
            email: Email::parse(&row.email)?,
            status: if row.active {
                UserStatus::Active
            } else {
                UserStatus::Inactive
            },
            preferences: serde_json::from_value(row.metadata)?,
            created_at: row.created_at,
        })
    }
}

The database row is a flat representation. The domain type has validation, newtypes, and enums. Convert at the boundary.

JSON Columns with Serde

PostgreSQL's jsonb type pairs beautifully with serde:

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

#[derive(Debug, Serialize, Deserialize)]
struct UserPreferences {
    theme: String,
    notifications_enabled: bool,
    language: String,
}

#[derive(Debug, sqlx::FromRow)]
struct UserWithPrefs {
    id: i64,
    name: String,
    preferences: Json<UserPreferences>,
}

// Reading
let user = sqlx::query_as::<_, UserWithPrefs>(
    "SELECT id, name, preferences FROM users WHERE id = $1"
)
.bind(user_id)
.fetch_one(&pool)
.await?;

println!("Theme: {}", user.preferences.theme);

// Writing
let prefs = UserPreferences {
    theme: "dark".to_string(),
    notifications_enabled: true,
    language: "en".to_string(),
};

sqlx::query(
    "UPDATE users SET preferences = $1 WHERE id = $2"
)
.bind(Json(&prefs))
.bind(user_id)
.execute(&pool)
.await?;

sqlx::types::Json<T> serializes to jsonb on write and deserializes on read. Your Rust struct becomes the schema for the JSON column.

Batch Operations

For inserting many rows, use UNNEST or build a multi-row INSERT:

async fn insert_tags(
    pool: &sqlx::PgPool,
    task_id: i64,
    tags: &[String],
) -> Result<(), sqlx::Error> {
    if tags.is_empty() {
        return Ok(());
    }

    // PostgreSQL UNNEST approach — single round trip
    sqlx::query(
        "INSERT INTO task_tags (task_id, tag)
         SELECT $1, UNNEST($2::text[])"
    )
    .bind(task_id)
    .bind(tags)
    .execute(pool)
    .await?;

    Ok(())
}

For upserts:

async fn upsert_settings(
    pool: &sqlx::PgPool,
    user_id: i64,
    key: &str,
    value: &str,
) -> Result<(), sqlx::Error> {
    sqlx::query(
        "INSERT INTO user_settings (user_id, key, value)
         VALUES ($1, $2, $3)
         ON CONFLICT (user_id, key)
         DO UPDATE SET value = EXCLUDED.value, updated_at = NOW()"
    )
    .bind(user_id)
    .bind(key)
    .bind(value)
    .execute(pool)
    .await?;

    Ok(())
}

Transaction-Based Testing

The gold standard for database tests: wrap each test in a transaction and roll back at the end. The test runs against the real database but leaves no trace.

#[cfg(test)]
mod tests {
    use super::*;

    async fn setup_pool() -> sqlx::PgPool {
        let pool = sqlx::PgPool::connect("postgres://localhost/test_db")
            .await
            .expect("Failed to connect to test database");

        sqlx::migrate!().run(&pool).await.expect("Failed to migrate");
        pool
    }

    #[tokio::test]
    async fn test_create_user() {
        let pool = setup_pool().await;
        let mut tx = pool.begin().await.unwrap();

        // Insert a user within the transaction
        let user = sqlx::query_as::<_, User>(
            "INSERT INTO users (name, email) VALUES ($1, $2)
             RETURNING id, name, email, active"
        )
        .bind("Test User")
        .bind("test@example.com")
        .fetch_one(&mut *tx)
        .await
        .unwrap();

        assert_eq!(user.name, "Test User");
        assert_eq!(user.email, "test@example.com");
        assert!(user.active);

        // Transaction is dropped here — automatic rollback
        // No test data pollutes the database
    }

    #[tokio::test]
    async fn test_deactivate_user() {
        let pool = setup_pool().await;
        let mut tx = pool.begin().await.unwrap();

        // Setup: create a user
        let user = sqlx::query_as::<_, User>(
            "INSERT INTO users (name, email) VALUES ($1, $2)
             RETURNING id, name, email, active"
        )
        .bind("Alice")
        .bind("alice@example.com")
        .fetch_one(&mut *tx)
        .await
        .unwrap();

        // Act: deactivate
        sqlx::query("UPDATE users SET active = false WHERE id = $1")
            .bind(user.id)
            .execute(&mut *tx)
            .await
            .unwrap();

        // Assert
        let updated = sqlx::query_as::<_, User>(
            "SELECT id, name, email, active FROM users WHERE id = $1"
        )
        .bind(user.id)
        .fetch_one(&mut *tx)
        .await
        .unwrap();

        assert!(!updated.active);
    }
}

Each test gets a fresh transaction. Tests run in parallel without interfering with each other because each sees only its own uncommitted data.

Test Fixtures

For complex tests, build reusable fixture functions:

#[cfg(test)]
mod fixtures {
    use super::*;

    pub async fn create_test_user(
        tx: &mut sqlx::Transaction<'_, sqlx::Postgres>,
        name: &str,
    ) -> User {
        sqlx::query_as::<_, User>(
            "INSERT INTO users (name, email) VALUES ($1, $2)
             RETURNING id, name, email, active"
        )
        .bind(name)
        .bind(format!("{}@test.com", name.to_lowercase()))
        .fetch_one(&mut **tx)
        .await
        .unwrap()
    }

    pub async fn create_test_task(
        tx: &mut sqlx::Transaction<'_, sqlx::Postgres>,
        user_id: i64,
        title: &str,
    ) -> Task {
        sqlx::query_as::<_, Task>(
            "INSERT INTO tasks (user_id, title) VALUES ($1, $2)
             RETURNING id, user_id, title, description, completed, created_at, updated_at"
        )
        .bind(user_id)
        .bind(title)
        .fetch_one(&mut **tx)
        .await
        .unwrap()
    }
}

Tests become concise:

#[tokio::test]
async fn test_user_tasks() {
    let pool = setup_pool().await;
    let mut tx = pool.begin().await.unwrap();

    let user = fixtures::create_test_user(&mut tx, "Alice").await;
    let _task1 = fixtures::create_test_task(&mut tx, user.id, "Task 1").await;
    let _task2 = fixtures::create_test_task(&mut tx, user.id, "Task 2").await;

    let tasks = sqlx::query_as::<_, Task>(
        "SELECT * FROM tasks WHERE user_id = $1"
    )
    .bind(user.id)
    .fetch_all(&mut *tx)
    .await
    .unwrap();

    assert_eq!(tasks.len(), 2);
}

Common Pitfalls

• Putting SQL in handlers. Handlers should call repository methods, not contain raw SQL. SQL scattered across handlers is hard to audit and refactor.
• Mapping domain types too early. Fetch the database row first, then convert. Trying to make SQLx deserialize directly into complex domain types leads to fighting the FromRow derive.
• Not testing database code. If you skip database tests because setup is hard, transaction-based testing solves that problem. Every query deserves a test.
• Batch inserts in a loop. Inserting one row at a time in a for loop means N round trips. Use UNNEST, multi-row INSERT, or COPY for bulk operations.
• Ignoring NULL semantics. In Rust, a nullable column maps to Option<T>. If your struct field is String but the column is nullable, the query will fail on NULL rows. Always match nullability.

Key Takeaways

• Use the repository pattern to isolate database queries behind a trait. This enables testing and keeps SQL out of business logic.
• Map between database rows and domain types explicitly with TryFrom.
• PostgreSQL jsonb columns work seamlessly with serde via sqlx::types::Json<T>.
• Use UNNEST or multi-row INSERT for batch operations instead of loops.
• Test database code with transactions that roll back. Each test is isolated and leaves no trace.
• Build reusable fixture functions for test setup to keep tests concise and readable.