Sea-query Type-Safe Query Building

Sea-query is a Rust query builder that constructs SQL as an AST (Abstract Syntax Tree) and renders it to database-specific dialects. It sits between raw SQL strings (error-prone, no compile-time checks) and full ORMs like Diesel (heavy, opinionated, entity-tracked). Sea-query gives you programmatic SQL construction with dialect portability, without the magic of an ORM.

Prerequisites

• Persistence libraries — broader landscape of Rust database access (sqlx, Diesel, SeaORM)
• Row-Level Security — the multi-tenant pattern that sea-query implements

Why Sea-query Over Alternatives

Tool	Approach	Tradeoff
Raw sqlx::query!()	Compile-time SQL verification against DB	No programmatic composition. Hard to build dynamic queries.
sea-query	AST builder → rendered SQL string	Dialect-portable. Composable. No compile-time DB check.
Diesel	Full ORM with schema DSL	Entity tracking, migrations, type safety. Heavy, opinionated.
SeaORM	ORM built on top of sea-query	Higher-level than sea-query. Adds entity models and relations.

Sea-query is the right choice when you want:

• Programmatic query construction (dynamic WHERE clauses, conditional JOINs)
• Multi-database support from the same code (Postgres + SQLite)
• No ORM overhead (no identity map, no lazy loading, no N+1 surprises)
• sqlx as the execution layer (you control connection pooling and transactions)

#[derive(Iden)]: Compile-Time Column Safety

The Iden trait maps Rust enum variants to SQL identifiers. The derive macro converts PascalCase to snake_case:

#[derive(Iden)]
enum Areas {
    Table,        // → "areas" (the table name)
    Id,           // → "id"
    TenantId,     // → "tenant_id"
    Name,         // → "name"
    CreatedAt,    // → "created_at"
}

The compiler enforces that every query references valid column names:

// If you rename `CreatedAt` to `InsertedAt`, the compiler shows
// every query in the codebase that references it. With raw strings,
// you grep and pray.
Query::select()
    .columns([Areas::Id, Areas::Name, Areas::CreatedAt])
    .from(Areas::Table)

One Iden enum per table, used in all queries against that table. The special Table variant maps to the enum name itself (lowercased).

The Query Builder Pattern

Sea-query builds an AST representing the SQL query, then renders it to a specific dialect:

let (sql, values) = Query::insert()
    .into_table(Areas::Table)
    .columns([Areas::Id, Areas::TenantId, Areas::Name])
    .values_panic([
        area.id.into(),
        self.tenant_id.into(),
        area.name.clone().into(),
    ])
    .build_sqlx(PostgresQueryBuilder);
 
// sql = "INSERT INTO areas (id, tenant_id, name) VALUES ($1, $2, $3)"
// values = SqlxValues containing the bound parameters
 
sqlx::query_with(&sql, values)
    .execute(&mut *tx)
    .await
    .map_err(db_err)?;

Dialect portability

Swap PostgresQueryBuilder for SqliteQueryBuilder and the same Rust code emits SQLite-flavored SQL. The abstraction layer is the query builder, not an ORM — no entity tracking, no implicit queries, no lazy loading.

values_panic() — when it’s safe

values_panic() panics if the number of values doesn’t match the number of columns. This is safe when columns and values are hardcoded together in the same function — it guards against programmer error (forgetting to add a value for a new column), not user input. A panic here means a code bug, not a runtime condition.

Expr::cust_with_values(): The Escape Hatch

Sea-query can’t model every SQL operator natively. Expr::cust_with_values() injects raw SQL fragments with bound parameters:

// The `?` is sea-query's placeholder (rendered as $N for Postgres)
// `::vector` is Postgres CAST syntax
Expr::cust_with_values("?::vector", [embedding_str.into()])

This is used for:

pgvector embeddings

pgvector stores vectors as a custom Postgres type, but sqlx has no built-in encoder for it. The workaround: format the vector as a string "[0.1,0.2,...]", pass as a text parameter, and cast server-side with ::vector:

let embedding_str = format!(
    "[{}]",
    chunk.embedding.iter()
        .map(ToString::to_string)
        .collect::<Vec<_>>()
        .join(",")
);
 
// Becomes: INSERT INTO content_chunks (..., embedding) VALUES (..., $6::vector)
Expr::cust_with_values("?::vector", [embedding_str.into()])

The pgvector crate adds a native Vec<f32> type for sqlx, but sea-query’s Expr doesn’t support custom sqlx types in cust_with_values. So the string-encoding workaround is necessary at the intersection of these two libraries.

pgvector cosine distance

// <=> is pgvector's cosine distance operator (0 = identical, 2 = opposite)
// ORDER BY distance ASC = most similar first
Expr::cust_with_values("embedding <=> ?::vector", [embedding_str.into()])
 
// For the SELECT column, convert distance to similarity (1.0 = identical)
Expr::cust_with_values("1.0 - (embedding <=> ?::vector)", [embedding_str.into()])

The ORDER BY uses raw distance (for HNSW index compatibility) while the SELECT computes similarity (for the API response). They’re monotonically inverse so sorting is equivalent.

ParadeDB BM25 full-text search

// @@@ is ParadeDB's match operator (like tsvector's @@ but with real BM25 scoring)
// paradedb.parse(?) parses the query string into a Tantivy query AST
Expr::cust_with_values("content_chunks @@@ paradedb.parse(?)", [query_text.into()])
 
// paradedb.score(id) returns the BM25 relevance score for the matched row
// `id` here is the internal row identifier (the primary key column), not a semantic concept —
// ParadeDB uses it to look up the score from its internal Tantivy index
Expr::cust("paradedb.score(id)::float8")

When to use cust_with_values

Use it only for operators or functions that sea-query doesn’t model natively. For standard SQL (WHERE, JOIN, GROUP BY, subqueries), use the typed API — it catches structural errors at compile time. cust_with_values is the escape hatch, not the default.

The Repository Method Pattern

Every repository method in a multi-tenant sea-query codebase follows three steps:

async fn create(&self, area: &Area) -> Result<(), LearnOSError> {
    // 1. Start transaction + set tenant context (RLS)
    let mut tx = scoped_tx(&self.pool, &self.tenant_id).await?;
 
    // 2. Build and execute the query
    let (sql, values) = Query::insert()
        .into_table(Areas::Table)
        .columns([Areas::Id, Areas::TenantId, Areas::Name])
        .values_panic([...])
        .build_sqlx(PostgresQueryBuilder);
 
    sqlx::query_with(&sql, values)
        .execute(&mut *tx)  // &mut *tx is deref-reborrow (see below)
        .await
        .map_err(db_err)?;
 
    // 3. Commit (if we get here, no ? returned early)
    tx.commit().await.map_err(db_err)?;
    Ok(())
}

If step 2 fails (via ?), the function returns early. The Transaction is dropped without .commit(), triggering Rust’s Drop impl which sends ROLLBACK to Postgres. No try/finally, no cleanup code to forget.

The &mut *tx deref-reborrow pattern

sqlx’s Transaction<'_, Postgres> implements DerefMut to the underlying connection. &mut *tx dereferences the Transaction wrapper and re-borrows the inner connection. This is needed because sqlx’s execute() expects &mut PgConnection, not &mut Transaction.

See also

• Row-Level Security — the tenant isolation pattern that scoped_tx enables
• Persistence libraries — the broader Rust database ecosystem
• Reciprocal Rank Fusion — the ranking algorithm applied to sea-query search results
• pgvector — the vector similarity extension accessed via cust_with_values
• Full-Text Search and ParadeDB — BM25 search accessed via @@@ operator