Skip to content
rust-web-server

Raw SQL

The query builder covers most common queries, but sometimes you need raw SQL — complex joins, CTEs, window functions, or database-specific syntax. DbConnection exposes three methods for this.

Typed query: query::<T>

conn.query::<T>(sql, params) executes a SQL statement and deserialises each row into T using T::from_row. T must implement Model (typically via #[derive(Model)]).

use rust_web_server::model::{DbConfig, DbConnection, Value};


let config = DbConfig::from_env()?;
let mut conn = DbConnection::open(&config)?;


let users: Vec<User> = conn.query::<User>(
    "SELECT * FROM users WHERE role = ? AND active = ?",
    &[Value::Text("admin".into()), Value::Bool(true)],
)?;

For PostgreSQL replace ? with $1, $2, etc.:

let users: Vec<User> = conn.query::<User>(
    "SELECT * FROM users WHERE role = $1 AND active = $2",
    &[Value::Text("admin".into()), Value::Bool(true)],
)?;

Untyped query: query_raw

conn.query_raw(sql, params) returns Vec<ModelRow> — column-name/value pairs without deserialisation. Use this for ad-hoc queries, reporting, or when no matching Model type exists.

let rows: Vec<ModelRow> = conn.query_raw(
    "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, u.name \
     ORDER BY post_count DESC",
    &[],
)?;


for row in &rows {
    let name: String = row.get::<String>("name")?;
    let count: i64  = row.get::<i64>("post_count")?;
    println!("{name}: {count} posts");
}

Execute: execute

conn.execute(sql, params) runs INSERT, UPDATE, DELETE, or DDL statements and returns the number of rows affected.

let affected: u64 = conn.execute(
    "UPDATE users SET last_login = ? WHERE id = ?",
    &[Value::Text("2026-07-02T10:00:00Z".into()), Value::Int(42)],
)?;
println!("{affected} row(s) updated");

Extracting columns from ModelRow

ModelRow::get::<T>(col) performs typed column extraction by name (case-insensitive). It returns Result<T, DbError>.

let name: String       = row.get::<String>("name")?;
let age: i64           = row.get::<i64>("age")?;
let score: f64         = row.get::<f64>("score")?;
let verified: bool     = row.get::<bool>("verified")?;
let avatar: Vec<u8>    = row.get::<Vec<u8>>("avatar")?;
let bio: Option<String> = row.get::<Option<String>>("bio")?;

The Value enum

All parameters and raw column values are represented as Value:

VariantRust typeUse for
Value::Null—SQL NULL
Value::Bool(bool)boolboolean columns
Value::Int(i64)integer typesINTEGER, BIGINT, SMALLINT
Value::Float(f64)float typesREAL, DOUBLE PRECISION
Value::Text(String)String / &strTEXT, VARCHAR, dates as strings
Value::Bytes(Vec<u8>)Vec<u8>BLOB, BYTEA
use rust_web_server::model::Value;


let params = &[
    Value::Text("alice@example.com".into()),
    Value::Int(30),
    Value::Bool(true),
    Value::Null,                               // e.g. an optional field
];

The FromColumn trait

Built-in FromColumn implementations cover i16, i32, i64, u32, u64, f32, f64, bool, String, Vec<u8>, and Option<T> for any T: FromColumn.

Implement it for custom types to enable row.get::<MyType>("col"):

use rust_web_server::model::{FromColumn, Value, DbError};


#[derive(Debug)]
pub enum UserRole { Admin, Member, Guest }


impl FromColumn for UserRole {
    fn from_column(v: Value) -> Result<Self, DbError> {
        match v {
            Value::Text(s) => match s.as_str() {
                "admin"  => Ok(UserRole::Admin),
                "member" => Ok(UserRole::Member),
                "guest"  => Ok(UserRole::Guest),
                other    => Err(DbError::new(format!("unknown role: {other}"))),
            },
            other => Err(DbError::new(format!("expected Text for UserRole, got {other:?}"))),
        }
    }
}


// Usage
let role: UserRole = row.get::<UserRole>("role")?;

When to use raw SQL vs QueryBuilder

SituationPrefer
Simple equality filters, pagination, orderingQueryBuilder
Multi-table joinsRaw SQL
CTEs (WITH …)Raw SQL
Window functions (OVER PARTITION BY …)Raw SQL
Database-specific functions (DATE_TRUNC, JSON_EXTRACT, …)Raw SQL
INSERT / UPDATE / DELETE with complex expressionsRaw SQL via execute
Schema mutations (DDL)Raw SQL via execute