# 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:
```rust
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:
```rust
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:
```rust
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:
```rust
// 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:
```rust
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:
```rust
use sqlw::query_qmark; // ? placeholders
use sqlw::query_numbered; // $1, $2 placeholders
```
Variables in `{curly braces}` are automatically bound:
```rust
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:
```rust
// 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:
```rust
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:
```rust
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:
```rust
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:
```rust
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`:
```rust
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:
```rust
#[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]`:
```rust
#[derive(FromRow)]
struct User {
id: i64,
#[field = "full_name"]
name: String,
}
```
Or reference a schema constant directly:
```rust
#[derive(FromRow)]
struct User {
id: i64,
#[field(User::NAME)]
name: String,
}
```
For columns that may not exist in every query, mark them `#[optional]`:
```rust
#[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):
```rust
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:
```rust
// 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`:
```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.