Musq

Musq is an asynchronous SQLite toolkit for Rust.

Musq bundles its own SQLite, runs each connection on a dedicated worker thread behind an async API, enables foreign key enforcement by default, and ships with sqlite-vec vector search built in.

Quickstart

//! Quickstart example from the README.

use musq::{FromRow, Musq, sql, sql_as, values};

/// User record fetched from the database.
#[derive(Debug, FromRow)]
pub struct User {
    /// User ID.
    pub id: i32,
    /// User name.
    pub name: String,
}

#[tokio::main]
async fn main() -> musq::Result<()> {
    // Create an in-memory database pool
    let pool = Musq::new().open_in_memory().await?;

    // Create a table
    sql!("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT NOT NULL);")?
        .execute(&pool)
        .await?;

    // Insert a user
    let id = 1;
    let name = "Alice";
    let user_values = values! { "id": id, "name": name }?;
    sql!("INSERT INTO users {insert:user_values}")?
        .execute(&pool)
        .await?;

    // Fetch the user and map it to our struct
    let user: User = sql_as!("SELECT id, name FROM users WHERE id = {id}")?
        .fetch_one(&pool)
        .await?;

    println!("Fetched user: {user:?}");

    Ok(())
}

Opening a database

Musq is the options builder. open() and open_in_memory() return a Pool; queries execute directly on the pool, on a Connection, a PoolConnection, or a Transaction — all interchangeably.

use musq::{JournalMode, Musq};

let pool = Musq::new()
    .max_connections(10)
    .create_if_missing(true)
    .journal_mode(JournalMode::Wal)
    .open("app.db")
    .await?;

Defaults: foreign keys on, busy timeout 5s, 10 pool connections, journal mode left unchanged (set JournalMode::Wal explicitly for WAL), double-quoted string literals off, trusted schema off, no statement timeout. Any pragma can be set with .pragma(key, value).

Queries

sql! builds a query from a format!-like string. sql_as! does the same and maps rows to a FromRow type. Interpolated values are always bound as parameters, never spliced into the SQL.

use musq::{FromRow, sql, sql_as};

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

let id = 1;
let name = "Bob";

sql!("INSERT INTO users (id, name) VALUES ({id}, {name})")?
    .execute(&pool)
    .await?;

let user: User = sql_as!("SELECT id, name FROM users WHERE id = {id}")?
    .fetch_one(&pool)
    .await?;

Placeholders:

Placeholder	Expansion
{expr}, {}	one bound parameter
{values:list}	?, ?, ? — one parameter per element, for IN (...). An empty list yields IN () (false) and NOT IN () (true for every row)
{ident:expr}	one quoted identifier
{idents:list}	comma-separated quoted identifiers
{insert:values}	(col, ...) VALUES (?, ...)
{set:values}	col = ?, ...
{where:values}	col = ? AND ...; col IS NULL for NULLs; 1=1 when empty
{upsert:values, exclude: a, b}	col = excluded.col, ... for ON CONFLICT ... DO UPDATE SET
{raw:expr}	verbatim SQL; taints the query

let table_name = "users";
let user_ids = vec![1, 2, 3];
let columns = ["id", "name"];

let users: Vec<User> = sql_as!(
    "SELECT {idents:columns} FROM {ident:table_name} WHERE id IN ({values:user_ids})"
)?
.fetch_all(&pool)
.await?;

Run queries with .execute(), .fetch_one(), .fetch_optional(), .fetch_all(), or .fetch() (a Stream). Compose dynamic queries with Query::join, or drop down to QueryBuilder for full control.

Values

Values is an insertion-ordered column/value map consumed by the {insert:}, {set:}, {where:}, and {upsert:} placeholders. Build one with the values! macro or fluently with Values::new().val(k, v)?. Each value is encoded immediately, so construction returns Result.

use musq::{QueryBuilder, Values, sql, sql_as, values};

let user_data = values! { "id": 1, "name": "Alice", "status": "active" }?;

sql!("INSERT INTO users {insert:user_data}")?
    .execute(&pool)
    .await?;

let changes = Values::new()
    .val("name", "Alicia")?
    .val("status", "inactive")?;

sql!("UPDATE users SET {set:changes} WHERE id = 1")?
    .execute(&pool)
    .await?;

let filters = values! { "status": "inactive" }?;
let user: User = sql_as!("SELECT id, name FROM users WHERE {where:filters}")?
    .fetch_one(&pool)
    .await?;

let upsert = values! { "id": 1, "name": "Alicia", "status": "active" }?;
sql!(
    "INSERT INTO users {insert:upsert} ON CONFLICT(id) DO UPDATE SET {upsert:upsert, exclude: id}"
)?
.execute(&pool)
.await?;

let runtime_values = Values::new()
    .val("id", 1)?
    .val("name", "Alice")?
    .val("status", "active")?;
let mut builder = QueryBuilder::new();
builder.push_sql("INSERT INTO users ");
builder.push_insert(&runtime_values)?;
builder.push_sql(" ON CONFLICT(id) DO UPDATE SET ");
builder.push_upsert(&runtime_values, &["id"])?;
builder.build().execute(&pool).await?;

Option::None encodes as SQL NULL everywhere ({where:} renders it as col IS NULL), and musq::Null is an untyped NULL literal:

async fn add_user(
    pool: &musq::Pool,
    name: &str,
    phone: Option<String>,
) -> musq::Result<()> {
    let user_data = values! {
        "name": name,
        "phone": phone,      // Option: None encodes as NULL
        "email": musq::Null, // untyped NULL literal
    }?;
    sql!("INSERT INTO users {insert:user_data}")?
        .execute(pool)
        .await?;
    Ok(())
}

DB-side computed values come from musq::expr (now_rfc3339_utc, jsonb, jsonb_serde with the json feature, raw):

use musq::expr;
let changes = values! {
    "updated_at": expr::now_rfc3339_utc(),
    "payload": expr::jsonb(r#"{"event":"hello"}"#),
}?;

sql!("UPDATE events SET {set:changes} WHERE id = 1")?
    .execute(&pool)
    .await?;

expr::raw(...) taints the resulting query; prefer the curated helpers.

Transactions

Pool::begin and Connection::begin return a Transaction. Top-level transactions start with BEGIN IMMEDIATE so a reserved lock is taken at begin. That avoids SQLITE_BUSY_SNAPSHOT when another connection commits between a deferred read and a later write. Use Musq::default_transaction_behavior to change the default, or begin_with(TransactionBehavior::Deferred) / Exclusive for one call.

Calling begin on an existing transaction creates a savepoint. commit and rollback consume the transaction. Dropping an uncommitted transaction rolls it back. Pool::transaction and Connection::transaction run an async closure and commit or roll back based on its result. The closure may borrow locals across await.

Connection::interrupt cancels the statement that is currently running. An interrupted write inside a transaction rolls that transaction back, and the next commit or rollback returns Error::TransactionAborted. Later statements run normally. Musq::statement_timeout sets a per-statement deadline through sqlite3_progress_handler.

let mut tx = pool.begin().await?;
sql!("INSERT INTO users (id, name) VALUES ({id}, {name})")?
    .execute(&tx)
    .await?;
tx.commit().await?;

pool.transaction(async |tx| {
    sql!("INSERT INTO users (id, name) VALUES (1, {name})")?
        .execute(&*tx)
        .await?;
    Ok::<_, musq::Error>(())
})
.await?;

Types

The Encode and Decode traits convert between Rust and SQLite types:

Rust type	SQLite type
bool	BOOLEAN
i8, i16, i32, i64	INTEGER
u8, u16, u32, u64, usize	INTEGER
f32, f64	REAL
&str, String, Arc<String>	TEXT
&[u8], Vec<u8>, Arc<Vec<u8>>	BLOB
bstr::BString	BLOB
Path, PathBuf	TEXT
serde_json::Value	TEXT
time::OffsetDateTime	DATETIME
time::PrimitiveDateTime	DATETIME
time::Date	DATE
time::Time	TIME
VecF32, VecInt8, VecBit	BLOB

bstr::BString needs the bstr feature. serde_json::Value and #[derive(musq::Json)] need the json feature. The time:: types need the time feature. VecF32, VecInt8, and VecBit need the vec feature. These four features are on by default.

Option<T> maps None to NULL. For large strings and blobs, prefer owned or shared types (String, Vec<u8>, Arc<T>) to avoid copies; blobs can be decoded directly into Arc<Vec<u8>>. bstr::BString handles text-like BLOBs that may not be valid UTF-8.

Encoding a u64 or usize above i64::MAX returns an error. Decode rejects negative values for all unsigned integer types. Use try_bind when an input can exceed SQLite's signed integer range.

With the time feature, OffsetDateTime preserves its RFC 3339 offset and fractional precision. Its TEXT encoding does not provide chronological lexical order. Use a normalized, fixed-width domain type for a sortable database key.

Derived types

#[derive(musq::Codec)] implements both Encode and Decode for enums and newtype structs (Encode and Decode can also be derived individually).

Enums store as snake-cased strings ("open", "closed") by default:

#[derive(musq::Codec, Debug, PartialEq)]
enum Status {
    Open,
    Closed,
}

Or as integers with repr:

#[derive(musq::Codec, Debug, PartialEq)]
#[musq(repr = "i32")]
enum Priority {
    Low = 1,
    Medium = 2,
    High = 3,
}

Newtype structs store as their inner value:

#[derive(musq::Codec, Debug, PartialEq)]
struct UserId(i32);

A checked newtype can decode another supported type through TryFrom:

#[derive(musq::Codec, Debug, PartialEq)]
#[musq(try_from = "String")]
struct CheckedUserId(String);

impl TryFrom<String> for CheckedUserId {
    type Error = &'static str;

    fn try_from(value: String) -> Result<Self, Self::Error> {
        value.starts_with("user-").then_some(Self(value)).ok_or("invalid user ID")
    }
}

#[derive(musq::Json)] stores any serde-compatible type as JSON text. This derive needs the json feature:

#[derive(musq::Json, serde::Serialize, serde::Deserialize, Debug, PartialEq)]
struct Metadata {
    tags: Vec<String>,
    version: i32,
}

Row mapping

#[derive(FromRow)] maps columns to fields by name. Attributes:

• #[musq(rename = "...")] — map a field to a differently named column
• #[musq(rename_all = "...")] — on the struct; case-convert all field names
• #[musq(default)] — use Default::default() when the column is absent
• #[musq(skip)] — always use Default::default()
• #[musq(try_from = "T")] — decode as T, then convert with TryFrom
• #[musq(deserialize_with = "path")] — decode with a custom fn(prefix: &str, row: &Row) -> Result<T>
• #[musq(flatten)], #[musq(flatten, prefix = "...")] — embed a nested FromRow struct, optionally prefixing its column names; an Option nested struct is None iff all of its columns are NULL

#[derive(FromRow)]
struct Address {
    street: String,
    city: String,
}

#[derive(FromRow)]
struct User {
    id: i32,
    // Reads the `street` and `city` columns.
    #[musq(flatten)]
    address: Address,
    // Reads `billing_street` and `billing_city`; None iff both are NULL.
    #[musq(flatten, prefix = "billing_")]
    billing: Option<Address>,
}

Features

Default features: vec, time, bstr, and json. A consumer that stores only integers and text can turn them off:

musq = { version = "0.0.4", default-features = false }

• vec registers sqlite-vec on every connection and provides VecF32, VecInt8, and VecBit. VecF32 binds directly to vector functions and vec0 tables; wrap VecInt8 and VecBit in SQL as vec_int8(?) and vec_bit(?). Example: cargo run -p musq --example vec.
• time implements Encode and Decode for time crate date and time types.
• bstr implements Encode and Decode for bstr::BString.
• json implements Encode and Decode for serde_json::Value, enables #[derive(musq::Json)], and exposes expr::jsonb_serde.

SQLite runtime

Musq supports the SQLite release bundled by the pinned libsqlite3-sys crate. Read the version from musq::BUNDLED_SQLITE_VERSION or runtime_info(). Linking against older or system SQLite libraries is not supported; leave LIBSQLITE3_SYS_USE_PKG_CONFIG and SQLITE3_* environment variables unset.

Runtime introspection and control:

• runtime_info() — SQLite version, source ID, and compile options
• db_status(kind, reset) — per-connection status counters (page cache, lookaside, schema, statements, ...)
• wal_checkpoint(schema, mode) — run or inspect WAL checkpoints

Community

Questions, ideas, feature requests: Discord.

Just like whales once used to be land-dwelling quadrupeds, Musq started life as a focused fork of SQLx.