Query Builder

When you want to query a table without modelling it as a typed #[suprnova::model] struct, reach for DB::table(name). It returns a chainable builder shaped like the typed Eloquent Builder<M>, but materialises rows as DynamicRow - a serde_json::Map newtype with typed accessors. This is the chapter for audit logs, ad-hoc reports, dashboard aggregates, and any table you haven't bothered to model. For the typed equivalent, see Eloquent. For raw DB::select inside transactions or with DB::listen observation, see Database.

use suprnova::DB;

let rows = DB::table("audit_log")
    .select(["id", "event", "actor_id"])
    .filter("actor_id", 42i64)
    .filter_op("created_at", ">=", "2026-01-01")
    .order_by_desc("id")
    .limit(50)
    .get()
    .await?;

for row in rows.iter() {
    let id: i64 = row.get_int("id")?;
    let event: String = row.get_string("event")?;
    println!("{id}: {event}");
}

When to use which surface

Three query surfaces overlap; pick the right one for the table.

DbTableBuilder exists for the middle case. You get the WHERE / ORDER / LIMIT chain without committing to a #[suprnova::model] struct and without dropping all the way to raw SQL strings.

The chainable surface

DB::table(name) returns a DbTableBuilder. Build it up, then call a terminal method to execute.

Filtering

// Equality.
DB::table("users").filter("email", "alice@example.com").get().await?;

// Arbitrary operator. Allowlist: =, <>, <, <=, >, >=, LIKE, NOT LIKE,
// ILIKE, NOT ILIKE, IS, IS NOT.
DB::table("orders").filter_op("total", ">=", 100i64).get().await?;
DB::table("posts").filter_op("title", "LIKE", "%rust%").get().await?;

// Multiple filters AND together.
DB::table("audit_log")
    .filter("actor_id", 42i64)
    .filter_op("event", "<>", "noop")
    .get()
    .await?;

filter and filter_op both accept any Into<SeaValue> for the right-hand side, which covers i64, String, &str, bool, f64, Option<T>, chrono::*, uuid::Uuid, and serde_json::Value - every column type the backend understands.

Byte-exact comparison

where_binary compares the raw bytes of a column instead of matching under its collation, so "Alice" does not match "alice" or "ALICE":

DB::table("users").where_binary("email", submitted).get().await?;
DB::table("users").where_not_binary("email", submitted).get().await?;

This is a MySQL and MariaDB feature - it emits their binary operator modifier, email = binary ?. Postgres and SQLite have no equivalent, so on those backends every terminal returns an error when the statement renders, before any query runs. Suprnova refuses rather than falling back to a plain =, because a fallback would compare under the column's collation and return rows you asked to exclude.

If you need case-sensitive matching on Postgres or SQLite, set a case-sensitive collation on the column, or use DB::select with a backend-specific expression.

Selecting columns

// Default is SELECT *.
DB::table("users").get().await?;

// Restrict columns when you only need some.
DB::table("users").select(["id", "email"]).get().await?;

Ordering and windowing

DB::table("posts")
    .order_by_desc("created_at")
    .order_by_asc("title")
    .limit(20)
    .offset(40)
    .get()
    .await?;

order_by_desc and order_by_asc chain in insertion order; the generated SQL preserves it.

Terminals

// All matching rows.
let rows: Collection<DynamicRow> = DB::table("audit_log")
    .filter("actor_id", 42i64)
    .get()
    .await?;

// First row or None.
let first: Option<DynamicRow> = DB::table("audit_log")
    .filter("event", "user.deleted")
    .first()
    .await?;

// Just the count (clears any select/order/limit/offset before
// rendering - count semantics don't care about those).
let n: u64 = DB::table("audit_log")
    .filter("actor_id", 42i64)
    .count()
    .await?;

get() returns Collection<DynamicRow> - the same collection wrapper typed models use, with the same .iter(), .len(), .into_vec() surface. See Eloquent Collections.

Inserts, updates, deletes

use suprnova::attrs;

// INSERT, returns the new row's auto-increment id.
let id: i64 = DB::table("audit_log")
    .insert(attrs! { event: "user.created", actor_id: 42 })
    .await?;

// UPDATE, returns rows affected.
let updated: u64 = DB::table("audit_log")
    .filter("id", id)
    .update(attrs! { event: "user.created.v2" })
    .await?;

// DELETE, returns rows affected.
let deleted: u64 = DB::table("audit_log")
    .filter("actor_id", 42i64)
    .delete()
    .await?;

The attrs! macro builds the column-to-value map at the call site. Keys are SQL identifiers (validated) and values are bound as parameters. An explicit null is emitted as SQL NULL because the JSON attribute map no longer carries its original Rust type; all non-null values remain parameter-bound. The same rule applies to typed Eloquent mass writes and many-to-many pivot extras.

update_all and delete_all aliases

update and delete are the Laravel-faithful names. The Builder<M>-style aliases - update_all and delete_all - call the same implementation. Prefer the _all form when the table-wide intent is the point of the call site; it makes a missing filter visible to reviewers:

// Same behaviour as DB::table("rate_limits").delete().await? but the
// _all suffix tells reviewers "yes, I meant to truncate the table".
DB::table("rate_limits").delete_all().await?;

// Mass update with a WHERE - the _all suffix here matches the typed
// Builder<M> convention for the same operation.
DB::table("sessions")
    .filter_op("expires_at", "<", chrono::Utc::now())
    .update_all(attrs! { status: "expired" })
    .await?;

Empty WHERE on update or delete operates on every row

DB::table("x").delete().await? removes every row in the table. That is supported by design - sometimes you really do want to truncate - but it's rarely correct. Always look at a delete() / delete_all() call and check whether there's a filter in front of it. The same is true of update / update_all.

Insert backend split

RETURNING id is used on Postgres and SQLite. MySQL doesn't support RETURNING, so the builder runs the INSERT and reads the driver's per-connection last_insert_id() from the result. The model-less builder assumes a standard id auto-increment primary key. UUID, composite, renamed, or non-integer primary keys aren't supported on this surface - use the typed Eloquent Model interface instead, which consults the model definition for primary-key shape.

DynamicRow - typed accessors over a JSON map

Every row returned by DB::table or DB::select materialises as DynamicRow, a serde_json::Map<String, Value> newtype with typed accessors. Each getter returns Result<T, FrameworkError> with a clear error message on missing key or type mismatch.

for row in rows.iter() {
    let id: i64                 = row.get_int("id")?;
    let event: String           = row.get_string("event")?;
    let active: bool            = row.get_bool("active")?;
    let weight: f64             = row.get_float("weight")?;
    let payload: serde_json::Value = row.get_value("payload")?;
}

For nullable columns, use get_optional_*. These distinguish "column missing" (error - schema mismatch) from "column present, value SQL NULL" (Ok(None)):

let title: Option<String> = row.get_optional_string("title")?;
let score: Option<i64>    = row.get_optional_int("score")?;

The optional family covers String and i64. For other nullable types, use get_value and match on serde_json::Value::Null yourself, or read the column through get_as::<Option<T>> (any T: DeserializeOwned).

To deserialise a column into any struct or container type, use get_as. The full serde_json deserialisation surface is available:

#[derive(serde::Deserialize)]
struct UserPrefs {
    theme: String,
    notifications: bool,
}

let prefs: UserPrefs    = row.get_as("prefs")?;
let tags: Vec<String>   = row.get_as("tags")?;
let when: chrono::DateTime<chrono::Utc> = row.get_as("created_at")?;

DynamicRow derefs to Map<String, Value>, so iteration and key-existence checks work directly:

for (key, value) in row.iter() {
    println!("{key} = {value}");
}

if row.contains_key("deleted_at") { /* … */ }

Identifier trust boundary

Table names, column names, ORDER BY directions, and SQL operators are interpolated into the SQL string verbatim - they are NOT bound as parameters (SQL doesn't allow placeholder-bound identifiers). Treat every impl Into<String> argument as a trusted, compile-time literal.

// Safe - the column name is a constant; the value is bound.
DB::table("users").filter("email", request.email()).get().await?;

// UNSAFE - never splice user input into a column name.
DB::table("users")
    .filter(request.user_supplied_column(), value)
    .get()
    .await?;

The framework enforces a strict allowlist at the I/O boundary - identifiers must match [A-Za-z_][A-Za-z0-9_]* with one optional schema. prefix, and operators must come from a fixed list. Violations fail closed with a FrameworkError::Database before any SQL is rendered. That's a safety net, not a license: keep identifiers literal in your code.

Values on the right-hand side of filter / filter_op are always bound as parameters and safe to splice through from request data.

Raw queries

When the builder can't express what you need - recursive CTEs, window functions, backend-specific DDL, INSERT … ON CONFLICT DO UPDATE - drop to a raw string. Placeholders match the active backend ($1, $2, … for Postgres, ? for MySQL and SQLite); the framework auto-detects from DatabaseConfig::url.

use suprnova::DB;
use sea_orm::Value;

// SELECT - every row as DynamicRow.
let rows = DB::select(
    "SELECT u.name, COUNT(p.id) AS post_count
     FROM users u LEFT JOIN posts p ON p.user_id = u.id
     GROUP BY u.id
     HAVING COUNT(p.id) > ?",
    vec![Value::from(5i64)],
).await?;

// SELECT - first row only, mirrors Laravel's DB::selectOne.
let alice = DB::select_one(
    "SELECT * FROM users WHERE email = ?",
    vec![Value::from("alice@example.com")],
).await?;

// SELECT - first column of first row as a typed scalar.
let total: i64 = DB::scalar(
    "SELECT COUNT(*) FROM users WHERE active = ?",
    vec![Value::from(true)],
).await?;

// INSERT - true when at least one row was affected.
DB::insert(
    "INSERT INTO users (name, active) VALUES (?, ?)",
    vec![Value::from("bob"), Value::from(true)],
).await?;

// UPDATE / DELETE - return the rows-affected count.
let updated: u64 = DB::update(
    "UPDATE users SET active = ? WHERE id = ?",
    vec![Value::from(false), Value::from(1i64)],
).await?;

let deleted: u64 = DB::delete(
    "DELETE FROM users WHERE active = ?",
    vec![Value::from(false)],
).await?;

// Any prepared statement with bindings.
DB::statement(
    "UPDATE users SET votes = votes + ? WHERE id = ?",
    vec![Value::from(1i64), Value::from(42i64)],
).await?;

// DDL or other no-binding statements that reject placeholder binding.
DB::unprepared("CREATE INDEX idx_users_name ON users(name)").await?;

// Generic "rows affected" path - for upserts and operations that
// don't fit the named helpers.
let n: u64 = DB::affecting_statement(
    "INSERT INTO counters (k, n) VALUES ($1, 1)
     ON CONFLICT (k) DO UPDATE SET n = counters.n + 1",
    vec![Value::from("page_views")],
).await?;

Aggregate-column gotcha

Untyped aggregates like SELECT COUNT(*) AS n FROM t work through the builder's .count() helper but may come back silently dropped from raw DB::select rows on SQLite. The underlying row materialiser walks sqlx's per-column type info, and a bare aggregate carries none. If you need raw DB::select with aggregates on SQLite, either wrap the expression in CAST(… AS BIGINT) to give it a type tag, or use DB::scalar::<i64> which goes through query_one + try_get and doesn't depend on the per-column type detection.

Bridge to typed Eloquent

When the table is worth a #[suprnova::model] struct, the chainable shape carries over. Model::query() returns Builder<M>, which ships the same filter / filter_op / order_by_* / limit / offset / get / first / count surface - plus a much wider WHERE vocabulary (filter_in, filter_between, filter_null, filter_has, filter_raw, …) and Laravel-shape aliases (db_where, where_in, where_between, where_null, where_has, where_raw, …).

use suprnova::Model;

let admins = User::query()
    .filter("role", "admin")
    .filter_op("created_at", ">=", since)
    .order_by_desc("created_at")
    .limit(20)
    .get()
    .await?;     // Collection<User> - typed, not DynamicRow

let alice = User::query().filter("email", &email).first().await?;
let total = User::query().filter("active", true).count().await?;
// Note: Builder<M>::count returns i64 (matches Laravel's Eloquent),
// whereas DbTableBuilder::count returns u64. Both surfaces give you a
// non-negative SQL COUNT - they only differ in their wire type.

The full Builder<M> surface - every WHERE shape, aggregates, relations, eager loading, scopes, paginators, chunk iteration - is in Eloquent. The chainable shape you learned above is the same shape; the differences are typing and reach.

Routing to a named connection

DB::table and the raw helpers default to the primary connection. To target a read replica, shard, or warehouse pool, pin the call:

// Builder pinned to a named connection.
let rows = DB::table("audit_log").on("warehouse").get().await?;

// Equivalent shorthand.
let rows = DB::table_on("warehouse", "audit_log").get().await?;

// Raw escapes have _on variants too.
let rows = DB::select_on("warehouse", "SELECT …", vec![]).await?;
let n    = DB::affecting_statement_on(
    "warehouse",
    "UPDATE …",
    vec![],
).await?;

When __read_replica__ is registered, every read-shape terminal auto-routes through it; writes (insert / update / delete / update_all / delete_all) always target the primary. Inside a DB::transaction closure the active transaction's connection wins absolutely - on(name) is silently ignored to preserve atomicity. See Database - Named connections for the full precedence chain.

Why Suprnova diverges

Laravel's DB::table(...) is its model-less query builder; under the hood it returns a stdClass per row (a PHP object whose properties are the columns). Suprnova returns DynamicRow instead - a serde_json::Map newtype with typed accessors. The accessor shape catches missing-column and wrong-type errors at the boundary instead of panicking deep in user code with a property-access exception.

The dual update/update_all and delete/delete_all names exist because the typed Eloquent Builder<M> surface uses the _all suffix to make table-wide intent explicit at the call site. Rather than pick a side, the model-less builder ships both - update and delete match Laravel's DB::table($t)->update(...) and ->delete() letter for letter; update_all and delete_all match the convention M users will already have in their muscle memory.

where_binary returns an error on Postgres and SQLite where Laravel throws a RuntimeException from the base grammar. The reason is the same and only the mechanism differs: public-surface code in Suprnova returns Result rather than panicking, so the refusal arrives as an Err from the terminal instead of an exception from the grammar.

Next

• Database - DB facade, transactions with savepoints, DB::listen observability, named connections
• Eloquent - typed #[suprnova::model] structs and the full Builder<M> surface
• Pagination - paginate / simple_paginate / cursor_paginate on typed builders
• Eloquent Collections - the Collection<T> returned by get() on both surfaces
• Migrations - defining the schema the builders query