SQLite and the Query Builder

Vantage is a big framework. It covers SQL databases, SurrealDB, MongoDB, CSV files, REST APIs — and ties them all together with a shared type system, expression engine, and data abstraction layer.

We’ll get to all of that. But right now, let’s start with something familiar: building SQL queries.

For this chapter we’ll use SQLite. It’s lightweight, needs no server, and works with a plain file on disk.

Set up

Create a new project:

cargo init learn-1 && cd learn-1
cargo add vantage-sql --features sqlite
cargo add vantage-expressions
cargo add tokio --features full

Three dependencies — vantage-sql gives us the SQLite query builder and connection pool, vantage-expressions is needed by the sqlite_expr! macro, and tokio provides the async runtime because all database operations are async.

Create and populate a database

We’ll make a small product catalog from scratch. Create seed.sql in your project root:

CREATE TABLE product (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price INTEGER NOT NULL,
    category_id INTEGER,
    is_deleted BOOLEAN NOT NULL DEFAULT 0
);

INSERT INTO product VALUES (1, 'Cupcake',           120, 1, 0);
INSERT INTO product VALUES (2, 'Doughnut',          135, 1, 0);
INSERT INTO product VALUES (3, 'Tart',              220, 2, 0);
INSERT INTO product VALUES (4, 'Pie',               299, 2, 0);
INSERT INTO product VALUES (5, 'Cookies',           199, 1, 0);
INSERT INTO product VALUES (6, 'A Stale Cake',       80, 1, 1);
INSERT INTO product VALUES (7, 'Sourdough Loaf',    350, 3, 0);

CREATE TABLE category (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

INSERT INTO category VALUES (1, 'Sweet Treats');
INSERT INTO category VALUES (2, 'Pastries');
INSERT INTO category VALUES (3, 'Breads');

Run it:

sqlite3 products.db < seed.sql

You now have products.db — 7 products (6 active, 1 deleted) across 3 categories. Quick check:

sqlite3 products.db "SELECT name, price FROM product WHERE is_deleted = 0"

Start with an async main

All database operations in Vantage are async, so we need a Tokio runtime. Replace src/main.rs with:

use vantage_sql::prelude::*;

#[tokio::main]
async fn main() {
    if let Err(e) = run().await {
        e.report();
    }
}

async fn run() -> VantageResult<()> {
    println!("Ready!");
    Ok(())
}

A few things going on here:

• use vantage_sql::prelude::* brings in everything we need for this chapter — SqliteDB, SqliteSelect, the sqlite_expr! macro, error types, and the traits that make builder and execution methods work.
• VantageResult<()> is Vantage’s own Result type. It uses VantageError, which tracks context and error chains for readable diagnostics.
• e.report() prints the error in a structured format. We call it from main() because Rust’s default Result-returning main uses Debug formatting, which is ugly. This pattern gives us clean error output instead.

Run cargo run to make sure it compiles.

Connect to SQLite

Add this inside run():

#![allow(unused)]
fn main() {
let db = SqliteDB::connect("sqlite:products.db?mode=ro")
    .await
    .context("Failed to connect to products.db")?;
}

SqliteDB wraps an sqlx connection pool. The connection string is an sqlx URL — ?mode=ro opens read-only, which is all we need for now.

#![allow(unused)]
fn main() {
let db = SqliteDB::new(existing_pool);
}

Error: Failed to connect to products.db
│
╰─▶ error returned from database: (code: 14) unable to open database file

Build a SELECT

SqliteSelect is the query builder for SQLite. Other persistences have their own — PostgresSelect, MongoSelect — and they all implement the Selectable trait, so the interface is identical apart from vendor-specific extensions. None of them need a database connection — they’re just structs that accumulate query parts. You build them with a chain of .with_*() calls:

#![allow(unused)]
fn main() {
let select = SqliteSelect::new()
    .with_source("product")
    .with_field("name")
    .with_field("price");

println!("{}", select.preview());
// SELECT "name", "price" FROM "product"
}

.preview() renders the final SQL as a string — handy for debugging, but never used for execution.

#![allow(unused)]
fn main() {
// Builder style
let select = SqliteSelect::new()
    .with_source("product")
    .with_field("name");

// Mutable style
let mut select = SqliteSelect::new();
select.add_source("product", None);
select.add_field("name");
}

Execute it

#![allow(unused)]
fn main() {
let result = db.execute(&select.expr()).await?;
println!("{:?}", result);
}

Two steps here: .expr() turns the builder into an Expression — Vantage’s internal representation that keeps parameters separate from the SQL template. Then db.execute() sends it to the database.

The result is AnySqliteType — a type-tagged wrapper around whatever came back. The Debug output isn’t pretty, but you should see all 6 product rows in there.

Adding conditions

Our database has a soft-delete flag — “A Stale Cake” has is_deleted = 1. Let’s filter it out:

#![allow(unused)]
fn main() {
let condition = sqlite_expr!("\"is_deleted\" = {}", false);

let select = SqliteSelect::new()
    .with_source("product")
    .with_field("name")
    .with_field("price")
    .with_condition(condition);

println!("{}", select.preview());
// SELECT "name", "price" FROM "product" WHERE "is_deleted" = 0
}

sqlite_expr! creates an Expression — a SQL template with typed, bound parameters. That {} is not Rust’s format!: the value false is stored separately and bound through sqlx’s parameterized query interface at execution time — no injection risk, ever. The preview shows it inline for readability.

In SQL persistences, a condition is just an expression. When you pass it to .with_condition(), it’s nested inside the select’s own expression tree. At execution time the whole tree is flattened into a single template + parameter list that the database driver can bind safely.

Run it — you should see 5 rows, with “A Stale Cake” filtered out.

Typed columns and operators

Writing \"is_deleted\" in a raw expression works, but there’s a cleaner way. Column<T> creates a typed column reference, then chain an SqliteOperation like .eq() to build the condition. (Column lives in the vantage-table crate, but the vantage_sql prelude re-exports it — no new dependency needed.)

#![allow(unused)]
fn main() {
let is_deleted = Column::<bool>::new("is_deleted");
let condition = is_deleted.eq(false);
}

Same result, but the type parameter <bool> ensures you can only compare against matching types. Try is_deleted.eq(42) — it won’t compile. Other operators — .gt(), .lt(), .ne(), .in_() — enforce the same type safety.

The result is a SqliteCondition — the backend’s native condition type — ready to be passed directly to .with_condition().

Multiple conditions combine with AND:

#![allow(unused)]
fn main() {
let is_deleted = Column::<bool>::new("is_deleted");
let price = Column::<i64>::new("price");

let select = SqliteSelect::new()
    .with_source("product")
    .with_field("name")
    .with_condition(is_deleted.eq(false))
    .with_condition(price.gt(150));
// ... WHERE "is_deleted" = 0 AND "price" > 150
}

#![allow(unused)]
fn main() {
use vantage_sql::sqlite::sqlite_ident as ident;

let condition = ident("is_deleted").eq(false);
}

Primitives, query builders, Column, conditions — even native values like i64 and bool — all implement Expressive<T>, where T is your database’s any-type (AnySqliteType for SQLite, AnyMongoType for MongoDB). That shared trait is what makes any of them usable as a parameter inside any expression.

Working with Any-types

Record<V> is an ordered map (IndexMap<String, V>) — one record per row, with column names as keys. It lives in the vantage-types crate, so add that dependency and import its prelude:

cargo add vantage-types --features serde

#![allow(unused)]
fn main() {
use vantage_types::prelude::*;
}

So far we’ve been printing raw AnySqliteType with Debug. That works for verifying queries, but it’s useless for real work. When db.execute() returns a multi-row result, the AnySqliteType holds an array of maps internally. Convert it to Vec<Record<AnySqliteType>> to work with individual rows:

#![allow(unused)]
fn main() {
let raw = db.execute(&select.expr()).await?;

let records = Vec::<Record<AnySqliteType>>::try_from(raw)
    .context("Failed to convert to records")?;

for rec in &records {
    let name: String = rec["name"].try_get::<String>().unwrap();
    let price: i64 = rec["price"].try_get::<i64>().unwrap();
    println!("{} — {} cents", name, price);
}
// Cupcake — 120 cents
// Doughnut — 135 cents
// ...
}

Access fields by column name with rec["name"]. Each value is still an AnySqliteType, so you call .try_get::<T>() to extract a typed Rust value. If the type doesn’t match (say you call .try_get::<i64>() on a text column), you get None — no panics, no garbage.

#![allow(unused)]
fn main() {
let json_rec: Record<serde_json::Value> = rec.into_record();
}

Under the hood, each persistence has its own type system for storing values. SQLite uses CBOR — a compact binary format that preserves types like Decimal, NaiveDate, and NaiveDateTime through tagged values. MongoDB uses BSON natively. The AnySqliteType / AnyMongoType wrappers hide these details — you interact with .try_get::<T>() regardless of which persistence you’re using. If you ever need to inspect the raw representation, .value() gives you the underlying CBOR value:

#![allow(unused)]
fn main() {
let price_cbor = rec["price"].value();
println!("{:?}", price_cbor);
// Integer(Integer(120))
}

See Persistence-aligned Type System for the full picture.

Mapping rows to structs

Calling .try_get::<T>() on every field gets tedious. The #[entity] macro generates TryFromRecord<AnySqliteType> for your struct, so conversion happens in one call with no type information lost:

#![allow(unused)]
fn main() {
#[entity(SqliteType)]
struct Product {
    name: String,
    price: i64,
}

let raw = db.execute(&select.expr()).await?;
let records = Vec::<Record<AnySqliteType>>::try_from(raw)?;

for rec in records {
    let product = Product::from_record(rec)?;
    println!("{} — {} cents", product.name, product.price);
}
}

The macro needs vantage-core as a direct dependency (it’s already a transitive dep through vantage-sql, but the generated code references it in your crate):

cargo add vantage-core

The macro also supports multiple type systems in one attribute — #[entity(SqliteType, PostgresType, MongoType)] generates a separate TryFromRecord impl for each persistence. One struct, all backends.

Putting it together

Here’s the complete src/main.rs — connect, query, convert to entities, print:

use vantage_sql::prelude::*;
use vantage_types::prelude::*;

#[entity(SqliteType)]
struct Product {
    name: String,
    price: i64,
}

#[tokio::main]
async fn main() {
    if let Err(e) = run().await {
        e.report();
    }
}

async fn run() -> VantageResult<()> {
    let db = SqliteDB::connect("sqlite:products.db?mode=ro")
        .await
        .context("Failed to connect to products.db")?;

    let select = SqliteSelect::new()
        .with_source("product")
        .with_field("name")
        .with_field("price")
        .with_condition(Column::<bool>::new("is_deleted").eq(false));

    let raw = db.execute(&select.expr()).await?;
    let records = Vec::<Record<AnySqliteType>>::try_from(raw)?;

    for rec in records {
        let p = Product::from_record(rec)?;
        println!("{:<12} {:>3} cents", p.name, p.price);
    }

    Ok(())
}

cargo run
# Cupcake      120 cents
# Doughnut     135 cents
# Tart         220 cents
# Pie          299 cents
# Cookies      199 cents

What we covered