Great question—this kind of repetitive boilerplate is super common when working with low-level database drivers like rusqlite, and there are several solid patterns you can apply to clean this up while making your code more maintainable, performant, and scalable. Let’s break down the key solutions and a reasonable architecture for Rust database queries:

1. Abstract Reusable Query Execution Logic #

Your current code repeats the same prepare → query → map results flow for every database call. The first step is to wrap this boilerplate into generic functions that handle the common work, leaving only the SQL and result mapping to your business-specific functions.

Example Generic Helpers #

use rusqlite::{Connection, Row, Result as SqliteResult};
use crate::{constants::DEFAULT_DB_PATH, database::error::CustomError};

// First, fix your connection function to return a Result instead of panicking
fn get_connection() -> Result<Connection, CustomError> {
    Connection::open(DEFAULT_DB_PATH)
        .map_err(|_| CustomError::DatabaseError) // Update your CustomError to wrap SQLite errors!
}

// Generic helper for single-row queries (with fallback to default if no rows)
fn query_single<T, F>(sql: &str, params: impl rusqlite::Params, mapper: F, default: T) -> Result<T, CustomError>
where
    F: FnOnce(&Row) -> SqliteResult<T>,
{
    let conn = get_connection()?;
    let mut stmt = conn.prepare(sql)?; // Use ? since we implemented From<SqliteError> for CustomError
    
    stmt.query_row(params, mapper)
        .or_else(|e| {
            // Handle "no rows found" case gracefully
            if matches!(e, rusqlite::Error::QueryReturnedNoRows) {
                Ok(default)
            } else {
                Err(e.into())
            }
        })
}

// Refactored version of your original function
fn get_payments_for_user(
    group_id: i64,
    user_id: u64,
    week: u32,
) -> Result<TotalPayment, CustomError> {
    let default = TotalPayment {
        group_id,
        user_id: user_id as i64,
        week,
        amount: 0.0,
    };

    query_single(
        GET_TOTAL_PAYMENT_FOR_USER_PER_WEKK,
        (group_id, user_id, week),
        |row| {
            Ok(TotalPayment {
                group_id: row.get(0)?,
                user_id: row.get(1)?,
                week: row.get(2)?,
                amount: row.get(3)?,
            })
        },
        default
    )
}

This cuts out most of the repetitive code—now each business query only needs to define the SQL, parameters, result mapper, and fallback default.

2. Use a Connection Pool #

Creating a new SQLite connection for every query is inefficient, especially as your app scales. Replace one-off connections with a connection pool (like r2d2 + r2d2_sqlite) to reuse connections and manage lifecycle automatically.

Connection Pool Setup #

// In database/mod.rs
use r2d2::Pool;
use r2d2_sqlite::SqliteConnectionManager;
use crate::{constants::DEFAULT_DB_PATH, database::error::CustomError};

pub struct DbPool {
    inner: Pool<SqliteConnectionManager>,
}

impl DbPool {
    pub fn new() -> Result<Self, CustomError> {
        let manager = SqliteConnectionManager::file(DEFAULT_DB_PATH);
        let pool = Pool::new(manager)?;
        Ok(Self { inner })
    }

    pub fn get(&self) -> Result<r2d2::PooledConnection<SqliteConnectionManager>, CustomError> {
        self.inner.get().map_err(|_| CustomError::DatabaseError)
    }
}

// Update your generic helper to use the pool instead of creating connections
fn query_single_with_pool<T, F>(
    pool: &DbPool,
    sql: &str,
    params: impl rusqlite::Params,
    mapper: F,
    default: T,
) -> Result<T, CustomError>
where
    F: FnOnce(&Row) -> SqliteResult<T>,
{
    let conn = pool.get()?;
    let mut stmt = conn.prepare(sql)?;
    
    stmt.query_row(params, mapper)
        .or_else(|e| {
            if matches!(e, rusqlite::Error::QueryReturnedNoRows) {
                Ok(default)
            } else {
                Err(e.into())
            }
        })
}

Inject the DbPool into your components instead of using global connections—this makes testing easier (you can pass an in-memory SQLite pool for tests).

3. Upgrade to a Type-Safe Library (ORM/Query Builder) #

For medium-to-large apps, writing raw SQL manually becomes error-prone and hard to maintain. Rust has great options for type-safe database access:

• SQLx: Async-first, with compile-time SQL validation. It auto-maps rows to your structs and supports SQLite natively.
• Diesel: A mature ORM with strong type safety, perfect for sync apps that want compile-time query checks.
• SeaORM: A modern, async ORM with a clean API, great for scalable apps.

Example with SQLx #

SQLx eliminates manual row mapping and adds compile-time checks for your SQL:

// Add to Cargo.toml: sqlx = { version = "0.7", features = ["sqlite", "runtime-tokio-native-tls"] }
#[derive(sqlx::FromRow, Debug)]
struct TotalPayment {
    group_id: i64,
    user_id: i64,
    week: u32,
    amount: f64,
}

async fn get_payments_for_user(
    pool: &sqlx::SqlitePool,
    group_id: i64,
    user_id: u64,
    week: u32,
) -> Result<TotalPayment, CustomError> {
    let result = sqlx::query_as!(
        TotalPayment,
        r#"SELECT group_id, user_id, week, amount FROM fees WHERE group_id = ? AND user_id = ? AND week = ?"#,
        group_id,
        user_id as i64,
        week
    )
    .fetch_optional(pool)
    .await?;

    Ok(result.unwrap_or(TotalPayment {
        group_id,
        user_id: user_id as i64,
        week,
        amount: 0.0,
    }))
}

SQLx will even fail to compile if your SQL has syntax errors or mismatched column names—no more runtime surprises!

4. Refine Error Handling #

Your current CustomError is a good start, but you should wrap underlying SQLite errors instead of using a generic QueryError. This makes debugging way easier:

// In database/error.rs
use rusqlite::Error as SqliteError;
use std::{error::Error, fmt};

#[derive(Debug)]
pub enum CustomError {
    UserExistsAlready,
    DatabaseError(SqliteError),
    // Add other app-specific errors here
}

impl Error for CustomError {}

impl fmt::Display for CustomError {
    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
        match self {
            CustomError::UserExistsAlready => write!(f, "This user already exists in the system"),
            CustomError::DatabaseError(e) => write!(f, "Database operation failed: {}", e),
        }
    }
}

// Implement conversion from SqliteError to CustomError
impl From<SqliteError> for CustomError {
    fn from(e: SqliteError) -> Self {
        CustomError::DatabaseError(e)
    }
}

Now you can use ? to automatically convert SQLite errors to your custom type, no manual mapping needed.

5. Recommended Code Architecture #

Based on your current src structure, here’s how to evolve it for production:

• database/mod.rs: Make this the entry point for all database operations. Export the DbPool type and any generic helper functions here.
• database/db.rs: Move all business-specific query functions here (like get_payments_for_user), each using the pool or generic helpers.
• database/queries.rs: Keep static SQL constants here if you’re sticking with raw SQL, or remove it if you switch to an ORM/query builder.
• database/error.rs: Maintain your refined CustomError type here, ensuring all database-related errors are unified.
• Dependency Injection: Pass the DbPool to any component that needs database access (instead of using globals). This makes testing straightforward—you can spin up an in-memory SQLite pool for unit tests.

内容的提问来源于stack exchange，提问作者Marc