Rust Sea-Query + SQLX + ModQL (Production Web App Series - E02)

This chapter upgrades the production web application's data layer to use Sea-Query (for multi-database SQL building), SQLx (for database execution across PostgreSQL, MySQL, and SQLite), and ModQL (for composable filtering and field mapping).

1. Why Do We Need This? (The Problem with Static Query Builders)

In Episode 1, the architecture relied on a custom or lightweight SQL builder (SQLB) embedded tightly into SQLx. While simple, it suffered from several production limitations:

• Single Database Lock-in: Harder to port seamlessly across PostgreSQL, MySQL, and SQLite without rewriting query structures.
• Limited Filtering: Lacked sophisticated, composable query filters (such as nested AND/OR groups, numerical comparison operators, and MongoDB-style operators).
• Maintenance Overhead: Custom query builders require manual maintenance for field mapping, pagination, and sorting options.

By introducing Sea-Query, SQLx, and ModQL, we achieve database-agnostic query building combined with type-safe, rich filtering capabilities.

2. Core Mental Model & Architecture

The application adopts a strict Separation of Concerns in its model layer (base.rs):

Real-Life Analogy

Think of this architecture like a restaurant ordering system:

1. ModQL acts as the customer filling out a structured order ticket with specific dietary constraints and menu item preferences (filters and sorting).
2. Sea-Query acts as the master chef translating that ticket into a universal kitchen order format, independent of whether the kitchen uses a gas or electric stove (database-agnostic SQL builder).
3. SQLx acts as the runner delivering the exact ingredients to the specific cooking station (driver execution for Postgres, MySQL, or SQLite).

3. Dependency Updates (Cargo.toml)

We remove the old SQLB dependency and introduce Sea-Query (with SQLx PostgreSQL bindings) and ModQL (with Sea-Query features).

[dependencies]
# Remove SQLB, add Sea-Query and ModQL
sea-query = { version = "0.30" }
sea-query-binder = { version = "0.5", features = ["sqlx-postgres", "runtime-tokio-rustls"] }
modql = { version = "0.1", features = ["with-sea-query"] }
sqlx = { version = "0.7", features = ["runtime-tokio-rustls", "postgres", "macros", "chrono"] }

4. Upgrading the Base CRUD Operations (base.rs)

We replace all SQLB calls in our generic CRUD implementations with a clean three-step pipeline: Extract Fields, Build Query with Sea-Query, and Execute with SQLx.

Step 1: Table References (table_ref)

Instead of raw strings, models provide a Sea-Query table reference helper while keeping static string flexibility for upstream code.

// Inside DBBMC trait implementation
fn table_ref() -> sea_query::TableRef {
    sea_query::TableRef::Table(Self::TABLE.into())
}

Step 2: Implementing the Create Operation

pub async fn create<MC, E>(db: &Db, data: E) -> Result<I64>
where
    MC: ModelCrud,
    E: HasFields + marker::ForCreate,
{
    // 1. Extract Fields & Prepare Data
    let fields = data.not_known_fields();
    let c_values = fields.for_sea_insert();

    // 2. Build Query with Sea-Query
    let mut query = sea_query::Query::insert();
    query
        .into_table(MC::table_ref())
        .columns(c_values.columns)
        .values(c_values.values)?;

    // 3. Execute with SQLx
    let (sql, values) = query.build_sqlx(sea_query_binder::SqlxBinder);
    let id = sqlx::query_as::<_, (i64,)>(&sql)
        .bind_values(values)
        .fetch_one(db)
        .await?
        .0;

    Ok(id)
}

5. Adding Dynamic Filters & List Options

We extend our task schema with a done boolean flag, create a strongly-typed TaskFilter struct using ModQL, and integrate pagination/sorting via ListOptions.

Task Filter Struct Definition

#[derive(Debug, Clone, Deserialize, Default)]
pub struct TaskFilter {
    pub id: Option<modql::filter::ValueInt64>,
    pub title: Option<modql::filter::ValueString>,
    pub done: Option<modql::filter::ValueBool>,
}

// Automatically implements conversion into ModQL filter nodes
impl modql::filter::FilterNodes for TaskFilter {}

Base List Implementation with Filters & List Options

pub async fn list<MC, F, E>(
    db: &Db,
    filter: Option<F>,
    list_options: Option<ListOptions>,
) -> Result<Vec<E>>
where
    MC: ModelCrud,
    F: IntoFilterGroups,
    E: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> + Send + Unpin,
{
    let mut query = sea_query::Query::select();
    query.from(MC::table_ref()).columns(MC::fields_ref());

    // Apply Filters if present
    if let Some(f) = filter {
        let filter_groups: FilterGroups = f.into_filter_groups();
        let condition: sea_query::Condition = filter_groups.try_into()?;
        query.cond_where(condition);
    }

    // Apply List Options (Pagination, Sorting)
    let list_options = finalize_list_options(list_options)?;
    list_options.apply_to_sea_query(&mut query);

    let (sql, values) = query.build_sqlx(sea_query_binder::SqlxBinder);
    let items = sqlx::query_as::<_, E>(&sql)
        .bind_values(values)
        .fetch_all(db)
        .await?;

    Ok(items)
}

6. Production Safety: Finalizing List Options

Unbounded SQL queries are a major production risk. We implement a finalize_list_options function to enforce default limits and maximum query boundaries.

const LIST_LIMIT_DEFAULT: i64 = 300;
const LIST_LIMIT_MAX: i64 = 1000;

pub fn finalize_list_options(
    list_options: Option<ListOptions>,
) -> Result<ListOptions> {
    let mut options = list_options.unwrap_or_default();
    
    // Enforce maximum limits
    if let Some(limit) = options.limit {
        if limit > LIST_LIMIT_MAX {
            return Err(Error::ListLimitOverMax { 
                max: LIST_LIMIT_MAX, 
                actual: limit 
            });
        }
    } else {
        options.limit = Some(LIST_LIMIT_DEFAULT);
    }

    // Set default sorting if none provided
    if options.order_by.is_none() {
        options.order_by = Some(vec![sea_query::Order::Desc("id".into())]);
    }

    Ok(options)
}

7. Upgrading the RPC Layer & Handling Flexible Filters

To support both single filter objects and arrays of filters (expressing OR conditions) in JSON-RPC parameters, we leverage the serde_with crate.

Params List Definition (params.rs)

use serde_with::{serde_as, OneOrMany};

#[serde_as]
#[derive(Debug, Clone, Deserialize)]
pub struct ParamsList<F> {
    pub list_options: Option<ListOptions>,
    #[serde_as(as = "Option<OneOrMany<F>>")]
    pub filters: Option<Vec<F>>,
}

8. Complete Flow Walkthrough: Filtered Task List Request

When a client requests a filtered and sorted task list via JSON-RPC:

Summary and Key Takeaways

• Multi-Database Power with Sea-Query: Sea-Query decouples query generation from SQLx execution, enabling seamless support for PostgreSQL, MySQL, and SQLite.
• Composable Filtering via ModQL: ModQL bridges domain structs into robust Sea-Query conditions with support for operators (eq, contains, in, etc.).
• Flexible JSON-RPC Deserialization: Using serde_with::OneOrMany allows API clients to pass either a single filter object or an array of filters interchangeably.
• Production Guardrails: Always enforce list limits (finalize_list_options) to prevent unbounded queries from exhausting database memory in production.
• Type Safety Maintained: Trait bounds (HasFields, IntoFilterGroups, ForCreate) ensure compile-time correctness across all generic CRUD operations.