Drizzle RS

A type-safe SQL query builder and ORM for Rust, inspired by Drizzle ORM.

[!WARNING] This project is still evolving. Expect breaking changes.

Contents

• Getting Started
  • 1. Install
  • 2. Initialize
  • 3. Define Your Schema
  • 4. Connect & Query
• Feature Flags
• Migrations
  • Manual: Generate with the CLI
  • Automatic: Generate from build.rs
  • Applying Migrations
  • Push (Dev Only)
• Porting from drizzle-orm (TypeScript)
  • Keep the Migration History
  • What to Port by Hand
  • Schema Cheat Sheet
• Generated Models
  • Insert
  • Update
  • JSON Columns
• Querying
  • Select
    • Ordering, Limiting, Pagination
    • Group By
  • Insert
  • Update
  • Delete
  • Joins
  • Subqueries & Set Operations
  • Aliases
• Expressions
  • Type Casting
• Relational Queries
  • Relation Names
  • Selecting Specific Columns
  • Result Types
• Transactions
• Prepared Statements
• Seeding
• PostgreSQL
• MySQL
• CLI Reference
  • Renames
• License

Getting Started

1. Install

[dependencies]
drizzle = { version = "0.3", features = ["rusqlite"] }
rusqlite = { version = "0.39", features = ["bundled"] }

Pick the driver feature that matches the client your application already uses. You create and own the connection; drizzle wraps it.

Install the CLI with the drivers it should connect through:

cargo install drizzle-cli --locked --features sqlite-all   # or postgres-all, mysql-all

Individual driver features (rusqlite, postgres-sync, mysql-async, ...) work too. Without a driver, generate still works, but migrate, push, and introspect stop with a "No driver available" error.

See Feature Flags for every driver and optional column type.

2. Initialize

drizzle init --dialect sqlite

This creates drizzle.config.toml. Point it at your schema and database:

dialect = "sqlite"
schema = "src/schema.rs"
out = "./drizzle"

[dbCredentials]
url = "./dev.db"

3. Define Your Schema

# #[cfg(feature = "rusqlite")]
# fn main() {
use drizzle::sqlite::prelude::*;

#[SQLiteTable]
pub struct Users {
    #[column(primary, autoincrement)]
    pub id: i64,
    pub name: String,
    pub email: Option<String>,
    pub age: i64,
}

#[SQLiteTable]
pub struct Posts {
    #[column(primary, autoincrement)]
    pub id: i64,
    pub title: String,
    pub content: Option<String>,
    #[column(references = Users::id)]
    pub author_id: i64,
}

#[SQLiteTable]
pub struct Comments {
    #[column(primary, autoincrement)]
    pub id: i64,
    pub body: String,
    #[column(references = Posts::id)]
    pub post_id: i64,
}

#[derive(SQLiteSchema)]
pub struct Schema {
    pub users: Users,
    pub posts: Posts,
    pub comments: Comments,
}
# }
# #[cfg(not(feature = "rusqlite"))]
# fn main() {}

Each table also gets a module of column types named after it: users::Name is the type of Users::name, should you need to name it. The module keeps these types apart from your own, so a User table can have a role: UserRole column.

If you already have a database, run drizzle introspect to reverse-engineer the schema instead of writing it by hand.

4. Connect & Query

# #[cfg(feature = "rusqlite")]
# fn main() -> drizzle::Result<()> {
# mod readme {
#     include!(concat!(env!("CARGO_MANIFEST_DIR"), "/tests/readme/sqlite.rs"));
# }
# use readme::Schema;
use drizzle::sqlite::rusqlite::Drizzle;

let conn = rusqlite::Connection::open("app.db")?;
let (db, Schema { users, posts, comments }) = Drizzle::new(conn);
# Ok(())
# }
# #[cfg(not(feature = "rusqlite"))]
# fn main() {}

Drizzle::new builds the schema value itself, and the pattern that destructures it names its type. When nothing else names it, put the type on the call: Drizzle::<Schema>::new(conn) (MySQL: Drizzle::<_, Schema>::new(conn)). Use let (db, ()) = Drizzle::new(conn) to run queries with no schema.

[!NOTE] See examples/rusqlite.rs for a full runnable example.

Feature Flags

Migrations

You have two workflows for keeping migration files in sync with your schema. Pick one — both produce the same committed SQL; the difference is whether you regenerate by hand or let cargo do it.

Both workflows apply migrations the same way — either with the CLI at deploy time, or from your app at startup. For local iteration without committed files at all, see Push (Dev Only).

Manual: Generate with the CLI

Run drizzle generate whenever you change your schema, then commit the resulting SQL files:

drizzle generate              # diff schema -> SQL migration files
drizzle generate --name init  # optional: name the migration

After a git merge brings in migrations generated on another branch, generate (like drizzle-kit) diffs against the combined result of both branches and records both as the new migration's parents (prevIds). If the branches changed the same objects it stops with a conflict report; regenerate one branch's migration on top of the other, or pass --ignore-conflicts to diff against the newest migration only.

If a change could be a rename, drizzle generate asks (or, without a terminal, reads --hints); see Renames.

Automatic: Generate from build.rs

Add drizzle-migrations as a build dependency, then point it at your existing drizzle.config.toml. Migration files regenerate themselves whenever your schema changes — you commit them the same way as the manual workflow, you just never run drizzle generate by hand.

[build-dependencies]
drizzle = { version = "0.3", features = ["rusqlite"] }
drizzle-migrations = "0.3"
rusqlite = { version = "0.39", features = ["bundled"] }

use drizzle_migrations::build::{Config, Output, run};

fn main() -> Result<(), Box<dyn std::error::Error>> {
    let cfg = Config::from_toml("drizzle.config.toml")?;
    cfg.watch();

    if let Output::Generated { tag, .. } = run(&cfg)? {
        println!("cargo:warning=generated migration {tag}");
    }

    Ok(())
}

cfg.watch() tells cargo to rerun build.rs whenever a schema file, drizzle.config.toml, or a referenced env var changes.

Applying Migrations

Once migration files exist, apply them one of three ways. They all use the same SQL files and tracking table — pick whichever fits your environment.

At deploy time, with the CLI:

drizzle migrate

At app startup, from your code:

use drizzle::migrations::Tracking;

let migrations = drizzle::include_migrations!("./drizzle");
db.migrate(&migrations, Tracking::SQLITE)?;

Use Tracking::POSTGRES for PostgreSQL and Tracking::MYSQL for MySQL. All three record applied migrations in a __drizzle_migrations table, which PostgreSQL keeps in a drizzle schema. Override the tracking table or schema when you need to:

db.migrate(
    &migrations,
    Tracking::POSTGRES
        .schema("ops")
        .table("schema_migrations"),
)?;

[!NOTE] PostgreSQL transaction scope differs from drizzle-orm. drizzle-orm's PostgreSQL migrate runs every pending migration inside one transaction, so a failure leaves none of them applied. drizzle-rs's PostgreSQL db.migrate (postgres-sync, tokio-postgres) commits each migration in its own transaction together with its tracking row: when a later migration fails, the earlier ones from the same call stay applied, and the next call resumes at the failed one. drizzle migrate on PostgreSQL keeps drizzle-orm's single transaction. CREATE/DROP INDEX CONCURRENTLY cannot run in a transaction at all: db.migrate runs a migration containing it statement by statement behind a dirty marker (and drizzle migrate does that for every migration in such a run); finish an interrupted one with drizzle migrate --repair or migrate_with_repair. On SQLite, rusqlite's db.migrate also uses one transaction for the batch.

Migration files are split the way drizzle-orm splits them: a file with --> statement-breakpoint markers runs one chunk at a time, each chunk exactly as written (PostgreSQL and MySQL accept several statements in one chunk; SQLite takes one statement per chunk). A hand-written file without markers is split on top-level semicolons, honoring the dialect's quoting, comment, and stored-program syntax.

MySQL DDL implicitly commits, so MySQL migrations do not pretend to be transactional. The CLI takes a database-scoped advisory lock, writes a durable dirty marker before the first statement, and marks the migration complete only after every statement succeeds. If a migration fails or is interrupted, inspect the partially applied DDL before retrying; automatic MySQL repair is deliberately unsupported because the server may already have committed some statements.

During cargo build, by extending the build.rs from above. Set DRIZZLE_MIGRATE=1 in your dev environment and your local database stays in lockstep with the schema:

use drizzle::sqlite::rusqlite::Drizzle;
use drizzle_migrations::{MigrateOutcome, MigrationDir};

// `cfg.watch()` does not watch this flag; without this line, cargo would not
// rerun build.rs when you set or unset it.
println!("cargo:rerun-if-env-changed=DRIZZLE_MIGRATE");

if std::env::var("DRIZZLE_MIGRATE").is_ok() {
    let conn = rusqlite::Connection::open(cfg.url()?)?;
    let (db, ()) = Drizzle::new(conn);
    let migrations = MigrationDir::new(cfg.out_dir()).discover()?;

    if let MigrateOutcome::Applied { tags } = db.migrate(&migrations, cfg.tracking())? {
        println!("cargo:warning=applied {} migration(s)", tags.len());
    }
}

cfg.tracking() returns the same Tracking value the runtime path uses — just sourced from drizzle.config.toml instead of hardcoded.

migrate creates the tracking schema/table if needed and skips migrations that have already been applied. Without DRIZZLE_MIGRATE, cargo build only generates files and never touches the database.

Push (Dev Only)

# #[cfg(feature = "rusqlite")]
# fn main() -> drizzle::Result<()> {
# mod readme {
#     include!(concat!(env!("CARGO_MANIFEST_DIR"), "/tests/readme/sqlite.rs"));
# }
# use readme::*;
# let (mut db, _) = readme::database()?;
let schema = Schema::new();
db.push(&schema)?;
# Ok(())
# }
# #[cfg(not(feature = "rusqlite"))]
# fn main() {}

push skips migration files entirely and applies the live schema diff directly.

[!CAUTION] push is for local iteration only. It bypasses the migration tracking table and offers no audit trail. Never run it against a production database.

Porting from drizzle-orm (TypeScript)

drizzle-kit records your TypeScript schema in every migration's snapshot, so drizzle import writes the Rust schema from those snapshots. It reads no TypeScript. Point it at the folder drizzle-kit writes to (out in drizzle.config.ts):

drizzle import ./drizzle                        # writes src/schema.rs
drizzle import ./drizzle --out src/db/schema.rs
drizzle import ./drizzle/meta/0007_snapshot.json --out -   # one snapshot, to stdout

It takes either migration layout drizzle-kit writes (meta/_journal.json from drizzle-kit 0.x, or one folder per migration from 1.x) and uses the newest snapshot. The dialect comes from the snapshot; --dialect turso imports a SQLite snapshot for Turso. The schema goes to --out, else the config's schema, else src/schema.rs, and an existing file is only replaced with --force. Field names are snake_case unless --casing camel (or [introspect] casing in the config) says otherwise; the SQL names are kept with name = "..." wherever they differ. The command prints what it could not express and the steps below.

Keep the Migration History

drizzle-rs uses drizzle-kit's migration folders and tracking table, so the migrations you already ran stay applied:

1. Point out in drizzle.config.toml at the existing folder (or pass --init-config to drizzle import to write a starter config that does):

   dialect = "postgresql"
   schema = "src/schema.rs"
   out = "./drizzle"
   

2. If the folder has meta/_journal.json (drizzle-kit 0.x), convert it to the folder layout with drizzle up (or pass --upgrade to drizzle import). This moves the files in place; the SQL is not changed.

3. drizzle migrate reads the __drizzle_migrations table drizzle-orm wrote (in the drizzle schema on PostgreSQL) and runs only migrations that are not recorded there. If drizzle.config.ts set migrations.table or migrations.schema, set the same under [migrations].

4. drizzle generate should now report no changes. From here on, edit the Rust schema and generate migrations as usual.

What to Port by Hand

Snapshots describe the database, not the TypeScript around it:

• relations(): drizzle-rs derives relations from foreign keys, including one-to-one and many-to-many, and names them (see Relation Names). An author_id column with #[column(references = Users::id)] gives posts.author() and users.author_posts() in the relational query API.
• $type<T>() and column modes ({ mode: 'json' | 'timestamp' | 'bigint' }): fields get the column's storage type. Switch to your Rust type, for example #[column(json)] with a serde type, or an enum deriving SQLiteEnum.
• $default(), $defaultFn(), $onUpdate(): values computed in JavaScript. Use #[column(default_fn = path)] or set them in code.
• customType(): the column keeps its SQL type; give it a Rust type that implements the dialect's column trait (DrizzlePostgresColumn and friends).
• PostgreSQL sequences, numeric and interval columns, and index ordering (.desc(), .nullsFirst()) have no Rust schema equivalent yet; the import warns about each one, and drizzle generate would drop or change them.

Schema Cheat Sheet

Generated Models

Given the schema above, each #[SQLiteTable], #[PostgresTable], or #[MySQLTable] generates four helper types:

Insert

new() takes only the required fields (columns without a default or autoincrement). Chain with_* for optional fields:

# #[cfg(feature = "rusqlite")]
# fn main() {
# mod readme {
#     include!(concat!(env!("CARGO_MANIFEST_DIR"), "/tests/readme/sqlite.rs"));
# }
# use readme::InsertUsers;
InsertUsers::new("Alex Smith", 26i64)
    .with_email("alex@example.com");
# }
# #[cfg(not(feature = "rusqlite"))]
# fn main() {}

Update

Start from default() and set only the fields you want to change. The query won't compile unless at least one field is set:

# #[cfg(feature = "rusqlite")]
# fn main() {
# mod readme {
#     include!(concat!(env!("CARGO_MANIFEST_DIR"), "/tests/readme/sqlite.rs"));
# }
# use readme::UpdateUsers;
UpdateUsers::default()
    .with_age(27)
    .with_email("new@example.com");
# }
# #[cfg(not(feature = "rusqlite"))]
# fn main() {}

JSON Columns

With the serde feature, any Serialize + Deserialize type can be stored in a JSON column (json; json or jsonb on PostgreSQL; JSON on MySQL). The field keeps its own type in the generated models, and one payload type can back columns in several tables. The macro implements nothing on the payload type, and your crate does not need a serde_json dependency. Generated models implement Debug, Clone, PartialEq and Default whenever every field type does, so a payload type only needs the traits you actually use.

# #[cfg(all(feature = "rusqlite", feature = "serde"))]
# fn main() -> drizzle::Result<()> {
use drizzle::core::Json;
use drizzle::core::expr::eq;
use drizzle::sqlite::prelude::*;
# use drizzle::sqlite::rusqlite::Drizzle;

#[derive(serde::Serialize, serde::Deserialize, Debug, Clone, PartialEq)]
pub struct Settings {
    pub theme: String,
}

#[SQLiteTable]
pub struct Profiles {
    #[column(primary)]
    pub id: i64,
    #[column(json)]
    pub settings: Settings,
    #[column(json)]
    pub tags: Vec<String>,
}

#[derive(SQLiteSchema)]
pub struct Schema {
    pub profiles: Profiles,
}

# let conn = rusqlite::Connection::open_in_memory()?;
# let (db, Schema { profiles }) = Drizzle::new(conn);
# db.create()?;
let dark = Settings { theme: "dark".into() };
db.insert(profiles)
    .value(InsertProfiles::new(dark.clone(), vec!["admin".into()]))
    .execute()?;

// Compare a JSON column with a `Json(..)`-wrapped payload.
let rows: Vec<SelectProfiles> = db
    .select(())
    .from(profiles)
    .r#where(eq(profiles.settings, Json(dark.clone())))
    .all()?;
assert_eq!(rows[0].settings, dark);
# Ok(())
# }
# #[cfg(not(all(feature = "rusqlite", feature = "serde")))]
# fn main() {}

Selecting a JSON column on its own (db.select(profiles.settings)) yields Json<Settings>; use .0 or .into_inner() to get the payload.

Querying

Comparison and expression functions such as eq, gt, and, and count live in drizzle::core::expr. Ordering helpers such as asc and desc live in drizzle::core.

Select

# #[cfg(feature = "rusqlite")]
# fn main() -> drizzle::Result<()> {
# mod readme {
#     include!(concat!(env!("CARGO_MANIFEST_DIR"), "/tests/readme/sqlite.rs"));
# }
# use drizzle::core::expr::*;
# use readme::*;
# let (db, Schema { users, .. }) = readme::database()?;
// All rows
let all: Vec<SelectUsers> = db.select(()).from(users).all()?;

// Single row with filter
let user: SelectUsers = db
    .select(())
    .from(users)
    .r#where(eq(users.name, "Alex Smith"))
    .get()?;

// Specific columns
let names: Vec<(i64, String)> = db
    .select((users.id, users.name))
    .from(users)
    .all()?;

// Multiple conditions — a tuple is an AND of its elements
let active_adults: Vec<SelectUsers> = db
    .select(())
    .from(users)
    .r#where((gt(users.age, 18), eq(users.name, "Alex Smith")))
    .all()?;

// Or
let rows: Vec<SelectUsers> = db
    .select(())
    .from(users)
    .r#where(eq(users.name, "Alice") | eq(users.name, "Bob"))
    .all()?;
# Ok(())
# }
# #[cfg(not(feature = "rusqlite"))]
# fn main() {}

Combining Conditions

A tuple of conditions is a condition, so lists stay flat instead of nesting and(a, and(b, c)). all and any combine the same lists explicitly.

# #[cfg(feature = "rusqlite")]
# fn main() -> drizzle::Result<()> {
# mod readme {
#     include!(concat!(env!("CARGO_MANIFEST_DIR"), "/tests/readme/sqlite.rs"));
# }
use drizzle::core::expr::{all, any, eq, gt, is_not_null};
# use readme::*;
# let (db, Schema { users, posts, .. }) = readme::database()?;

let adults: Vec<SelectUsers> = db
    .select(())
    .from(users)
    .r#where((gt(users.age, 18), eq(users.name, "Alex"), is_not_null(users.email)))
    .all()?;

let staff: Vec<SelectUsers> = db
    .select(())
    .from(users)
    .r#where(any((eq(users.name, "Alice"), eq(users.name, "Bob"))))
    .all()?;

let posts: Vec<(i64, i64)> = db
    .select((users.id, posts.id))
    .from(users)
    .inner_join((posts, all((eq(posts.author_id, users.id), is_not_null(posts.content)))))
    .all()?;
# Ok(())
# }
# #[cfg(not(feature = "rusqlite"))]
# fn main() {}

Elements may be Options — None contributes nothing, which makes dynamic filters composable:

# #[cfg(feature = "rusqlite")]
# fn main() -> drizzle::Result<()> {
# mod readme {
#     include!(concat!(env!("CARGO_MANIFEST_DIR"), "/tests/readme/sqlite.rs"));
# }
# use drizzle::core::expr::{eq, gt};
# use readme::*;
# let (db, Schema { users, .. }) = readme::database()?;
# let name = Some("Alex");
let by_name = name.map(|n| eq(users.name, n));

// Some("Alex") => WHERE ("users"."age" > ? AND "users"."name" = ?)
// None         => WHERE ("users"."age" > ?)
let rows: Vec<SelectUsers> = db
    .select(())
    .from(users)
    .r#where((gt(users.age, 18), by_name))
    .all()?;
# Ok(())
# }
# #[cfg(not(feature = "rusqlite"))]
# fn main() {}

When every element is absent the list renders as its operator's identity: TRUE for a tuple or all (matches everything, like an absent WHERE) and FALSE for any (matches nothing, so a fully-optional any fails closed).

Bare tuples hold up to 8 conditions; all/any and nesting cover longer lists.

Ordering, Limiting, Pagination

# #[cfg(feature = "rusqlite")]
# fn main() -> drizzle::Result<()> {
# mod readme {
#     include!(concat!(env!("CARGO_MANIFEST_DIR"), "/tests/readme/sqlite.rs"));
# }
# use drizzle::core::{asc, desc};
# use readme::*;
# let (db, Schema { users, .. }) = readme::database()?;
let rows: Vec<SelectUsers> = db
    .select(())
    .from(users)
    .order_by((asc(users.name), desc(users.age)))
    .limit(10)
    .offset(20)
    .all()?;
# Ok(())
# }
# #[cfg(not(feature = "rusqlite"))]
# fn main() {}

Group By

# #[cfg(feature = "rusqlite")]
# fn main() -> drizzle::Result<()> {
# mod readme {
#     include!(concat!(env!("CARGO_MANIFEST_DIR"), "/tests/readme/sqlite.rs"));
# }
# use drizzle::core::expr::{alias, count, gt};
# use readme::*;
# let (db, Schema { users, .. }) = readme::database()?;
let totals: Vec<(String, i64)> = db
    .select((users.name, alias(count(users.id), "total")))
    .from(users)
    .group_by(users.name)
    .having(gt(count(users.id), 1))
    .all()?;

let totals_by_age: Vec<(String, i64, i64)> = db
    .select((users.name, users.age, alias(count(users.id), "total")))
    .from(users)
    .group_by((users.name, users.age))
    .all()?;
# Ok(())
# }
# #[cfg(not(feature = "rusqlite"))]
# fn main() {}

Insert

# #[cfg(feature = "rusqlite")]
# fn main() -> drizzle::Result<()> {
# mod readme {
#     include!(concat!(env!("CARGO_MANIFEST_DIR"), "/tests/readme/sqlite.rs"));
# }
# use readme::*;
# let (db, Schema { users, .. }) = readme::database()?;
// Single row
db.insert(users)
    .value(InsertUsers::new("Alex Smith", 26i64).with_email("alex@example.com"))
    .execute()?;

// Multiple rows
db.insert(users)
    .values([
        InsertUsers::new("Alex Smith", 26i64).with_email("alex@example.com"),
        InsertUsers::new("Jordan Lee", 30i64).with_email("jordan@example.com"),
    ])
    .execute()?;
# Ok(())
# }
# #[cfg(not(feature = "rusqlite"))]
# fn main() {}

[!IMPORTANT] In a multi-row insert, every row must set the same set of optional fields. Mixing with_email(...) on some rows but not others is a compile error.

Update

# #[cfg(feature = "rusqlite")]
# fn main() -> drizzle::Result<()> {
# mod readme {
#     include!(concat!(env!("CARGO_MANIFEST_DIR"), "/tests/readme/sqlite.rs"));
# }
# use drizzle::core::expr::eq;
# use readme::*;
# let (db, Schema { users, .. }) = readme::database()?;
db.update(users)
    .set(UpdateUsers::default().with_age(27))
    .r#where(eq(users.id, 1))
    .execute()?;
# Ok(())
# }
# #[cfg(not(feature = "rusqlite"))]
# fn main() {}

Delete

# #[cfg(feature = "rusqlite")]
# fn main() -> drizzle::Result<()> {
# mod readme {
#     include!(concat!(env!("CARGO_MANIFEST_DIR"), "/tests/readme/sqlite.rs"));
# }
# use drizzle::core::expr::eq;
# use readme::*;
# let (db, Schema { users, .. }) = readme::database()?;
db.delete(users)
    .r#where(eq(users.id, 3))
    .execute()?;
# Ok(())
# }
# #[cfg(not(feature = "rusqlite"))]
# fn main() {}

A delete or update without .r#where(...) does not compile, so a forgotten condition cannot empty or rewrite a table. To change every row on purpose, write .r#where(true).

Joins

Use #[derive(SQLiteFromRow)] to map columns from multiple tables into a flat struct. #[from(Users)] sets the default source table for unannotated fields:

# #[cfg(feature = "rusqlite")]
# fn main() -> drizzle::Result<()> {
# mod readme {
#     include!(concat!(env!("CARGO_MANIFEST_DIR"), "/tests/readme/sqlite.rs"));
# }
use drizzle::core::expr::eq;
use drizzle::sqlite::prelude::*;
# use readme::*;

#[derive(SQLiteFromRow, Debug)]
#[from(Users)]
struct UserWithPost {
    #[column(Users::id)]
    user_id: i64,
    name: String,
    // LEFT JOIN — every Posts column must be Option<T> in case the user has no
    // posts. A non-Option field here is a compile error.
    #[column(Posts::id)]
    post_id: Option<i64>,
    #[column(Posts::content)]
    content: Option<String>,
}

# let (db, Schema { users, posts, .. }) = readme::database()?;
// Explicit ON condition
let rows: Vec<UserWithPost> = db
    .select(UserWithPost::Select)
    .from(users)
    .left_join((posts, eq(users.id, posts.author_id)))
    .all()?;

// Auto-FK: derives the ON condition from #[column(references = ...)]
let rows: Vec<UserWithPost> = db
    .select(UserWithPost::Select)
    .from(users)
    .left_join(posts)
    .all()?;
# Ok(())
# }
# #[cfg(not(feature = "rusqlite"))]
# fn main() {}

Subqueries & Set Operations

SELECT builders are expressions — pass them directly into comparisons or IN:

# #[cfg(feature = "rusqlite")]
# fn main() -> drizzle::Result<()> {
# mod readme {
#     include!(concat!(env!("CARGO_MANIFEST_DIR"), "/tests/readme/sqlite.rs"));
# }
# use drizzle::core::expr::{eq, gt, in_subquery, min};
# use readme::*;
# let (db, Schema { users, .. }) = readme::database()?;
let min_id = db.select(min(users.id)).from(users);
let newer: Vec<SelectUsers> = db
    .select(())
    .from(users)
    .r#where(gt(users.id, min_id))
    .all()?;

let exact_rows = db
    .select((users.id, users.name))
    .from(users)
    .r#where(eq(users.name, "Alex Smith"));

let matched: Vec<SelectUsers> = db
    .select(())
    .from(users)
    .r#where(in_subquery((users.id, users.name), exact_rows))
    .all()?;
# Ok(())
# }
# #[cfg(not(feature = "rusqlite"))]
# fn main() {}

Combine queries with union, union_all, intersect, and except. union removes duplicates; union_all keeps them:

# #[cfg(feature = "rusqlite")]
# fn main() -> drizzle::Result<()> {
# mod readme {
#     include!(concat!(env!("CARGO_MANIFEST_DIR"), "/tests/readme/sqlite.rs"));
# }
# use drizzle::core::{asc, desc};
# use drizzle::core::expr::{gte, lte};
# use readme::*;
# let (db, Schema { users, .. }) = readme::database()?;
let results: Vec<(String,)> = db
    .select((users.name,))
    .from(users)
    .r#where(lte(users.age, 25))
    .union(
        db.select((users.name,))
          .from(users)
          .r#where(gte(users.age, 30))
    )
    .order_by(asc(users.name))
    .all()?;
# Ok(())
# }
# #[cfg(not(feature = "rusqlite"))]
# fn main() {}

Aliases

Use a Tag to alias a table for self-joins:

# #[cfg(feature = "rusqlite")]
# fn main() -> drizzle::Result<()> {
# mod readme {
#     include!(concat!(env!("CARGO_MANIFEST_DIR"), "/tests/readme/sqlite.rs"));
# }
use drizzle::sqlite::prelude::*;
# use readme::*;

tag!(U, "u");

# let (db, _) = readme::database()?;
let u = Users::alias::<U>();
let rows: Vec<(i64,)> = db.select((u.id,)).from(u).all()?;
# Ok(())
# }
# #[cfg(not(feature = "rusqlite"))]
# fn main() {}

Expressions

Aggregate functions and common SQL expressions:

# #[cfg(feature = "rusqlite")]
# fn main() -> drizzle::Result<()> {
# mod readme {
#     include!(concat!(env!("CARGO_MANIFEST_DIR"), "/tests/readme/sqlite.rs"));
# }
# use drizzle::core::expr::{coalesce, count, max};
# use readme::*;
# let (db, Schema { users, .. }) = readme::database()?;
// Aggregates
let total: (i64,) = db.select((count(users.id),)).from(users).get()?;
let oldest: (Option<i64>,) = db.select((max(users.age),)).from(users).get()?;

// Coalesce — first non-null value
let rows: Vec<(String,)> = db
    .select((coalesce(users.email, "unknown"),))
    .from(users)
    .all()?;
# Ok(())
# }
# #[cfg(not(feature = "rusqlite"))]
# fn main() {}

Available in drizzle::core::expr:

• Comparisons — eq, neq, gt, gte, lt, lte
• Boolean — and, or, not, all, any (and tuples, which mean AND)
• Aggregates — count, sum, avg, min, max
• Null handling — coalesce, is_null, is_not_null
• Strings — upper, lower, length
• Math — abs, round, sign, mod_; ceil, floor, trunc, sqrt, power, exp, ln, log, log10, log2, pi (see the SQLite note below)

The ordering helpers asc and desc are in drizzle::core, not drizzle::core::expr.

SQLite only has ceil through pi when it is compiled with SQLITE_ENABLE_MATH_FUNCTIONS, so on SQLite those functions compile only with drizzle's math feature. Enabling math is a promise about the SQLite you link:

• rusqlite (with bundled) and libsql compile their own SQLite and read LIBSQLITE3_FLAGS while doing so. Set LIBSQLITE3_FLAGS="-DSQLITE_ENABLE_MATH_FUNCTIONS" in the build environment, for example under [env] in .cargo/config.toml.
• turso implements these functions itself and needs no flag.

If the linked SQLite lacks them, the calls still compile under math, and the query fails at runtime with no such function.

Type Casting

cast(expr, target) takes a type marker from the dialect's types module (drizzle::sqlite::types, drizzle::postgres::types, or drizzle::mysql::types). The marker supplies both the SQL type name and the result type. You can pass a SQL type name as a string instead, but a string carries no result type, so name the marker with a turbofish: cast::<_, _, Real>(expr, "DOUBLE"). SQLite and PostgreSQL only allow casts between compatible types, such as integer to real.

# #[cfg(feature = "rusqlite")]
# fn main() -> drizzle::Result<()> {
# mod readme {
#     include!(concat!(env!("CARGO_MANIFEST_DIR"), "/tests/readme/sqlite.rs"));
# }
# use readme::*;
use drizzle::core::expr::cast;
use drizzle::sqlite::types::Real;

# let (db, Schema { users, .. }) = readme::database()?;
// The marker renders `AS REAL` and types the result as a SQLite REAL (f64)
let ages: Vec<(f64,)> = db.select((cast(users.age, Real),)).from(users).all()?;

// A string only renders the SQL type name; the turbofish supplies the result type
let ages: Vec<(f64,)> = db
    .select((cast::<_, _, Real>(users.age, "DOUBLE"),))
    .from(users)
    .all()?;
# Ok(())
# }
# #[cfg(not(feature = "rusqlite"))]
# fn main() {}

Relational Queries

Requires the query feature. Fetches a table with its relations in a single query — no manual joins.

Relation methods are generated from foreign keys. Given Posts.author_id → Users.id, posts.author() is the forward (many-to-one) and users.author_posts() the reverse (one-to-many). Relation Names explains how the names are chosen.

# #[cfg(all(feature = "rusqlite", feature = "query"))]
# fn main() -> drizzle::Result<()> {
# mod readme {
#     include!(concat!(env!("CARGO_MANIFEST_DIR"), "/tests/readme/sqlite.rs"));
# }
# use readme::*;
# let (db, Schema { users, .. }) = readme::database()?;
let users = db.query(users)
    .with(users.author_posts())
    .find_many()?;

for user in &users {
    println!("{}: {} posts", user.name, user.author_posts.len());
}
# Ok(())
# }
# #[cfg(not(all(feature = "rusqlite", feature = "query")))]
# fn main() {}

.find_first() returns Option<...>:

# #[cfg(all(feature = "rusqlite", feature = "query"))]
# fn main() -> drizzle::Result<()> {
# mod readme {
#     include!(concat!(env!("CARGO_MANIFEST_DIR"), "/tests/readme/sqlite.rs"));
# }
# use drizzle::core::expr::eq;
# use readme::*;
# let (db, Schema { users, .. }) = readme::database()?;
let user = db.query(users)
    .with(users.author_posts())
    .r#where(eq(users.name, "Alice"))
    .find_first()?;
# Ok(())
# }
# #[cfg(not(all(feature = "rusqlite", feature = "query")))]
# fn main() {}

Nest relations:

# #[cfg(all(feature = "rusqlite", feature = "query"))]
# fn main() -> drizzle::Result<()> {
# mod readme {
#     include!(concat!(env!("CARGO_MANIFEST_DIR"), "/tests/readme/sqlite.rs"));
# }
# use readme::*;
# let (db, Schema { users, posts, .. }) = readme::database()?;
let users = db.query(users)
    .with(users.author_posts().with(posts.comments()))
    .find_many()?;

println!("{} comments", users[0].author_posts[0].comments.len());
# Ok(())
# }
# #[cfg(not(all(feature = "rusqlite", feature = "query")))]
# fn main() {}

Filter and paginate the root query:

# #[cfg(all(feature = "rusqlite", feature = "query"))]
# fn main() -> drizzle::Result<()> {
# mod readme {
#     include!(concat!(env!("CARGO_MANIFEST_DIR"), "/tests/readme/sqlite.rs"));
# }
# use drizzle::core::asc;
# use drizzle::core::expr::gt;
# use readme::*;
# let (db, Schema { users, .. }) = readme::database()?;
let users = db.query(users)
    .with(users.author_posts())
    .r#where(gt(users.age, 25))
    .order_by(asc(users.name))
    .limit(10)
    .find_many()?;
# Ok(())
# }
# #[cfg(not(all(feature = "rusqlite", feature = "query")))]
# fn main() {}

Relation Names

Every foreign key gives relation accessors named from the schema, so you rarely name one yourself: