zeph-db

Database abstraction layer for Zeph — unified SQLite and PostgreSQL backends with compile-time backend selection, automatic migrations, dialect-aware SQL helpers, and FTS support.

Important:

Exactly one of the sqlite or postgres features must be enabled. The default is sqlite, so plain cargo build/cargo build --features full (no --no-default-features) always produce a working sqlite build. Enabling both simultaneously triggers a compile_error!. Using --all-features is intentionally unsupported. For a PostgreSQL build, disable default features explicitly — cargo build --no-default-features --features full,postgres — since the default sqlite backend cannot be "overridden" by additively requesting postgres on top of it.

Features

• Compile-time backend selection — DbPool, DbRow, DbTransaction, and DbQueryResult resolve to the correct sqlx types based on the active feature
• sql! macro — write ? placeholders once; the macro rewrites them to $1, $2, ... for PostgreSQL and is a zero-cost no-op for SQLite
• Dialect trait — backend-specific SQL constants (AUTO_PK, INSERT_IGNORE, EPOCH_NOW, etc.) and helpers (ilike, epoch_from_col) via zero-sized marker types
• Automatic migrations — DbConfig::connect runs migrations/sqlite/ or migrations/postgres/ on startup; WAL checkpoint applied after SQLite migrations
• FullDriver super-trait — reduces sqlx bound repetition in generic impl blocks across consumer crates
• FTS helpers — backend-aware WHERE/JOIN/rank fragments for messages and graph entity full-text search
• limit_clause() helper — cross-backend "0 means unlimited" LIMIT fragment; omits the clause entirely instead of relying on the SQLite-only LIMIT -1 sentinel, which PostgreSQL rejects
• Safe URL logging — redact_url strips credentials from connection strings before they appear in logs
• Write transactions — begin_write issues BEGIN IMMEDIATE on SQLite (prevents SQLITE_BUSY); falls back to standard BEGIN on PostgreSQL

Connection URL configuration

The active backend is chosen at compile time by feature flag; the connection URL is resolved at runtime. Set database_url under the [memory] section of config.toml:

[memory]
database_url = "postgres://user:pass@localhost/zeph"

Because the URL usually embeds credentials, it is also resolvable from the age vault, which takes precedence over the config file:

zeph vault set ZEPH_DATABASE_URL "postgres://user:pass@localhost:5432/zeph"

Important:

The URL scheme must match the compiled feature. A postgres:// URL on a sqlite build (or a non-postgres URL on a postgres build) fails at startup with an explicit error; the URL is redacted before it reaches the message.

CLI migrations

Run pending migrations without starting the agent:

zeph db migrate                         # apply pending migrations using the resolved database_url
zeph --config path/to/config.toml db migrate

Installation

cargo add zeph-db

For a PostgreSQL build, the default sqlite feature must be disabled explicitly — the two backends are mutually exclusive, so postgres cannot be requested additively on top of the default:

[dependencies]
zeph-db = { version = "0.22", default-features = false, features = ["postgres"] }

Feature Flags

Usage

Connect and run migrations

use zeph_db::{DbConfig, DbPool};

let config = DbConfig {
    url: "path/to/zeph.db".into(),
    pool_size: 5,
};

let pool: DbPool = config.connect().await?;

For in-memory SQLite (useful in tests):

let pool = DbConfig { url: ":memory:".into(), ..Default::default() }
    .connect()
    .await?;

Write portable SQL with the sql! macro

use zeph_db::sql;

let rows = sqlx::query(sql!("SELECT id FROM messages WHERE conversation_id = ?"))
    .bind(conversation_id)
    .fetch_all(&pool)
    .await?;

Note:

Do not use the sql! macro for PostgreSQL JSONB queries that contain ?, ?|, or ?& operators — use $N placeholders directly for those.

Dialect-aware SQL fragments

use zeph_db::{ActiveDialect, Dialect};

let ddl = format!("CREATE TABLE t (id {}, name TEXT)", ActiveDialect::AUTO_PK);
let insert = format!("{} INTO t (name) VALUES (?){}", ActiveDialect::INSERT_IGNORE, ActiveDialect::CONFLICT_NOTHING);

Transactions

use zeph_db::{begin, begin_write};

// Standard deferred transaction
let mut tx = begin(&pool).await?;

// Write-intent transaction (BEGIN IMMEDIATE on SQLite)
let mut tx = begin_write(&pool).await?;
sqlx::query("INSERT INTO t (name) VALUES (?)").bind("foo").execute(&mut *tx).await?;
tx.commit().await?;

Cross-backend LIMIT clause

use zeph_db::limit_clause;

let (fragment, bind) = limit_clause(page_size); // 0 => unlimited, omits the clause entirely
let sql = format!("SELECT id FROM messages WHERE conversation_id = ?{fragment}");
let mut query = sqlx::query(&sql).bind(conversation_id);
if let Some(limit) = bind {
    query = query.bind(limit);
}

FTS helpers

use zeph_db::fts::{sanitize_fts_query, messages_fts_where, messages_fts_join, messages_fts_rank_select, messages_fts_order_by};

let q = sanitize_fts_query(user_input);
let sql = format!(
    "SELECT m.id, {} FROM messages m {} WHERE {} ORDER BY {}",
    messages_fts_rank_select(),
    messages_fts_join(),
    messages_fts_where(),
    messages_fts_order_by(),
);

Generic consumer crates

Use D: DatabaseDriver + FullDriver as the single generic bound when you need both sqlx pool access and SQL dialect fragments:

use zeph_db::{DatabaseDriver, FullDriver, DbConfig};

async fn init_store<D: DatabaseDriver + FullDriver>(config: DbConfig) -> sqlx::Pool<D::Database> {
    config.connect().await.expect("db init")
}

Migrations

SQL migration files live in:

• migrations/sqlite/ — SQLite DDL (FTS5 virtual tables, triggers, indexes)
• migrations/postgres/ — PostgreSQL DDL (tsvector columns, GIN indexes, plainto_tsquery setup)

Migrations run automatically on first DbConfig::connect call. The active backend's directory is embedded at compile time via sqlx::migrate!.

MSRV

Rust 1.97 (Edition 2024, resolver 3).

License

Licensed under either of MIT or Apache License, Version 2.0 at your option.