sqlw - SQL Writer

The goal of this library is to enable fearless raw SQL queries, embracing SQL in an ergonomic & rusty way of doing things. sqlw gives you compile-time SQL query building with schema-safe field references, automatic parameter binding, and seamless integration with popular databases.

Quick Start

Define your schema, write a query, execute it. Here's the full flow:

use sqlw::{schema, query_qmark, QueryExecutor, FromRow};

// 1. Define your table schema
schema!(User "users" {
    ID: i64 "id",
    NAME: String "name",
    EMAIL: String "email",
    AGE: i64 "age",
});

// 2. Write a query with parameter binding
let min_age = 18;
let query = query_qmark!(
    SELECT User::NAME, User::EMAIL
    FROM User::TABLE
    WHERE User::AGE >= {min_age}
);

assert_eq!(query.sql(), "SELECT name, email FROM users WHERE age >= ?");
assert_eq!(query.args(), &[sqlw::Value::Int(18)]);

// 3. Map results to a struct
#[derive(FromRow)]
struct UserInfo {
    name: String,
    email: String,
}

// 4. Execute against a database
let executor = sqlw_backend::turso::TursoExecutor::new(|| async {
    turso::Builder::new_local("my.db").build().await?.connect()
}).await?;

let users: Vec<UserInfo> = executor.query_list(query).await?;

Defining Schemas with schema!

The schema! macro generates a struct with typed column constants. Each entry maps a column name to a Rust field:

use sqlw::schema;

schema!(Product "products" {
    ID: i64 "id",
    NAME: String "name",
    PRICE: f64 "price",
    IN_STOCK: bool "in_stock",
});

This gives you a Product struct with named fields:

let widget = Product {
    id: 1,
    name: "Widget".into(),
    price: 9.99,
    in_stock: true,
};

let draft = Product::default(); // zero values

And column constants that carry type information into your queries:

// Product::TABLE.desc()  -> "products"
// Product::ID.desc()     -> "id"
// Product::ID            -> Def<Product, Typed<i64>>

You can also define column-only constants (no struct field) by omitting the type:

schema!(User "users" {
    ID: i64 "id",
    FULL_NAME "full_name", // constant only, no struct field
});

Writing Queries

sqlw provides two query macros that differ only in their placeholder style:

use sqlw::query_qmark;   // ? placeholders 
use sqlw::query_numbered; // $1, $2 placeholders 

Variables in {curly braces} are automatically bound:

let name = "Laptop";
let price = 999.99;

let insert = query_qmark!(
    INSERT INTO Product::TABLE (Product::NAME, Product::PRICE)
    VALUES ({name}, {price})
);

assert_eq!(insert.sql(), "INSERT INTO products(name, price) VALUES(?, ?)");
assert_eq!(insert.args(), &[Value::Text("Laptop".into()), Value::Float(999.99)]);

The query macros work with all standard CRUD operations:

// SELECT
let users = query_qmark!(
    SELECT User::NAME, User::EMAIL
    FROM User::TABLE
    ORDER BY User::NAME
);

// UPDATE
let new_email = "alice@example.com";
let update = query_qmark!(
    UPDATE User::TABLE
    SET User::EMAIL = {new_email}
    WHERE User::ID = {1}
);

// DELETE
let delete = query_qmark!(
    DELETE FROM User::TABLE
    WHERE User::ID = {1}
);

Joins, aggregates, and subqueries work naturally since you're writing raw SQL:

schema!(Order "orders" {
    ID: i64 "id",
    USER_ID: i64 "user_id",
    TOTAL: f64 "total",
});

let user_id = 42;
let orders = query_qmark!(
    SELECT User::NAME, Order::TOTAL
    FROM User::TABLE
    INNER JOIN Order::TABLE ON User::ID = Order::USER_ID
    WHERE User::ID = {user_id}
    ORDER BY Order::TOTAL DESC
);

Optional Values

Option<T> becomes NULL automatically:

let nickname: Option<String> = None;
let height: Option<f64> = Some(1.85);

let query = query_qmark!(
    INSERT INTO User::TABLE (User::NICKNAME, User::HEIGHT)
    VALUES ({nickname}, {height})
);

assert_eq!(query.args(), &[Value::Null, Value::Float(1.85)]);

Running Queries

The QueryExecutor trait provides methods for executing your queries:

use sqlw::{QueryExecutor, query_qmark};

let users: Vec<User> = executor.query_list(query_qmark!(
    SELECT * FROM User::TABLE
)).await?; // all matching rows

let user: Option<User> = executor.query_one(query_qmark!(
    SELECT * FROM User::TABLE WHERE User::ID = {1}
)).await?; // at most one row

executor.query_void(query_qmark!(
    DELETE FROM User::TABLE WHERE User::ID = {1}
)).await?; // discard results

Transactions

Turso executors support transactions:

let tx = executor.transaction().await?;

tx.query_void(query_qmark!(
    UPDATE Account::TABLE
    SET Account::BALANCE = Account::BALANCE - {100}
    WHERE Account::ID = {1}
)).await?;

tx.query_void(query_qmark!(
    UPDATE Account::TABLE
    SET Account::BALANCE = Account::BALANCE + {100}
    WHERE Account::ID = {2}
)).await?;

tx.commit().await?;

For a static sequence, use batch:

fn debit() -> Query {
    query_qmark!(UPDATE Account::TABLE
        SET Account::BALANCE = Account::BALANCE - {100}
        WHERE Account::ID = {1}
    )
}

fn credit() -> Query {
    query_qmark!(UPDATE Account::TABLE
        SET Account::BALANCE = Account::BALANCE + {100}
        WHERE Account::ID = {2}
    )
}

executor.batch(&[debit, credit]).await?;

Mapping Results with FromRow

Derive FromRow to map database rows to your structs. Column names match field names by default:

#[derive(FromRow)]
struct User {
    id: i64,
    name: String,
}

let users: Vec<User> = executor.query_list(query_qmark!(
    SELECT User::ID, User::NAME FROM User::TABLE
)).await?;

Need to map to a different column name? Use #[field]:

#[derive(FromRow)]
struct User {
    id: i64,
    #[field = "full_name"]
    name: String,
}

Or reference a schema constant directly:

#[derive(FromRow)]
struct User {
    id: i64,
    #[field(User::NAME)]
    name: String,
}

For columns that may not exist in every query, mark them #[optional]:

#[derive(FromRow)]
struct User {
    id: i64,
    #[optional]
    bio: Option<String>,
}

Custom Types

Use custom types in queries by implementing From<T> for Value (already covered by the blanket Into<Value> conversion):

use sqlw::Value;

struct Timestamp(i64);

impl From<Timestamp> for Value {
    fn from(ts: Timestamp) -> Value {
        Value::Int(ts.0)
    }
}

let ts = Timestamp(1_736_880_000);
query_qmark!(
    INSERT INTO Log::TABLE (Log::TS)
    VALUES ({ts})
);

Backends

sqlw supports multiple databases through the sqlw-backend crate. Each backend has its own executor:

// Turso / LibSQL
use sqlw_backend::turso::TursoExecutor;

// SQLite
use sqlw_backend::sqlite::SqliteExecutor;

// PostgreSQL
use sqlw_backend::postgres::PostgresExecutor;

// MySQL
use sqlw_backend::mysql::MySqlExecutor;

Installation

Add both crates to your Cargo.toml:

[dependencies]
sqlw = "0.1"
sqlw-backend = { version = "0.1", features = ["turso"] }

Pick your backend feature: turso (default), sqlite, postgres, or mysql. Enable chrono for chrono date/time support.