FeatherDB

The embedded SQL database for AI agents — MVCC, encryption, sessions.

Overview

This crate provides the primary user-facing API through Database and Transaction. It re-exports types from all subsystem crates so you only need one dependency.

Key Features

• Full SQL: JOINs, subqueries, CTEs, window functions, 30+ built-in functions — passes all 22 TPC-H queries
• MVCC: Snapshot isolation — readers never block writers
• Durability: Write-ahead logging with crash recovery
• Encryption: AES-256-GCM at rest with hardware acceleration
• Embedded: Single-file database, zero C dependencies
• Prepared Statements: Plan caching for 5–10x speedup on repeated queries

Quick Start

use featherdb::{Database, Result};

fn main() -> Result<()> {
    // Open database (creates if missing)
    let db = Database::open("myapp.db")?;

    // Create a table
    db.execute("CREATE TABLE users (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        email TEXT UNIQUE
    )")?;

    // Insert data
    db.execute("INSERT INTO users (id, name, email) VALUES (1, 'Alice', 'alice@example.com')")?;

    // Query data
    let rows = db.query("SELECT * FROM users WHERE id = 1")?;
    for row in rows {
        println!("Name: {:?}", row.get("name"));
    }

    Ok(())
}

Public API Overview

Database

The main entry point for all operations.

impl Database {
    // Opening
    pub fn open(path: impl AsRef<Path>) -> Result<Self>;
    pub fn open_with_config(config: Config) -> Result<Self>;

    // Auto-commit operations
    pub fn execute(&self, sql: &str) -> Result<usize>;
    pub fn query(&self, sql: &str) -> Result<Vec<Row>>;
    pub fn query_one(&self, sql: &str) -> Result<Row>;
    pub fn query_opt(&self, sql: &str) -> Result<Option<Row>>;

    // Prepared statements
    pub fn prepare(&self, sql: &str) -> Result<PreparedStatement>;
    pub fn execute_prepared(&self, stmt: &PreparedStatement, params: &[Value]) -> Result<QueryResult>;
    pub fn query_prepared(&self, stmt: &PreparedStatement, params: &[Value]) -> Result<Vec<Row>>;
    pub fn execute_prepared_mut(&self, stmt: &PreparedStatement, params: &[Value]) -> Result<usize>;

    // Plan cache management
    pub fn plan_cache(&self) -> &Arc<PlanCache>;
    pub fn invalidate_plan_cache(&self);
    pub fn invalidate_plans_for_table(&self, table_name: &str);

    // Transaction control
    pub fn begin(&self) -> Result<Transaction>;
    pub fn begin_read_only(&self) -> Result<Transaction>;

    // Metadata
    pub fn table_exists(&self, name: &str) -> bool;
    pub fn list_tables(&self) -> Vec<String>;
    pub fn catalog(&self) -> &Arc<Catalog>;

    // Maintenance
    pub fn checkpoint(&self) -> Result<()>;
    pub fn save_catalog(&self) -> Result<()>;

    // Observability
    pub fn metrics(&self) -> DatabaseMetrics;

    // Storage management
    pub fn storage_quota(&self) -> StorageQuota;
    pub fn size(&self) -> u64;
    pub fn max_size(&self) -> Option<u64>;
}

Transaction

Explicit transaction control with ACID guarantees.

impl Transaction {
    // Operations
    pub fn execute(&mut self, sql: &str) -> Result<usize>;
    pub fn query(&self, sql: &str) -> Result<Vec<Row>>;
    pub fn query_one(&self, sql: &str) -> Result<Row>;
    pub fn query_opt(&self, sql: &str) -> Result<Option<Row>>;

    // Prepared statements within transaction
    pub fn prepare(&self, sql: &str) -> Result<PreparedStatement>;
    pub fn execute_prepared(&self, stmt: &PreparedStatement, params: &[Value]) -> Result<QueryResult>;
    pub fn query_prepared(&self, stmt: &PreparedStatement, params: &[Value]) -> Result<Vec<Row>>;
    pub fn execute_prepared_mut(&mut self, stmt: &PreparedStatement, params: &[Value]) -> Result<usize>;

    // Savepoints
    pub fn savepoint(&mut self, name: &str) -> Result<SavepointId>;
    pub fn rollback_to_savepoint(&mut self, name: &str) -> Result<()>;
    pub fn release_savepoint(&mut self, name: &str) -> Result<()>;
    pub fn savepoint_names(&self) -> Vec<&str>;

    // Transaction info
    pub fn mode(&self) -> TransactionMode;
    pub fn is_read_only(&self) -> bool;
    pub fn snapshot(&self) -> &Snapshot;

    // Completion
    pub fn commit(self) -> Result<()>;
    pub fn rollback(self) -> Result<()>;
}

Prepared Statements

Prepared statements parse and plan SQL once, then execute multiple times with different parameters. This provides 5-10x speedup for repeated queries.

use featherdb::{Database, Value};

let db = Database::open("myapp.db")?;
db.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)")?;

// Prepare once - plan is cached
let stmt = db.prepare("SELECT * FROM users WHERE id = $1")?;

// Execute many times with different parameters
let rows1 = db.query_prepared(&stmt, &[Value::Integer(1)])?;
let rows2 = db.query_prepared(&stmt, &[Value::Integer(2)])?;

// For INSERT/UPDATE/DELETE, use execute_prepared_mut
let insert_stmt = db.prepare("INSERT INTO users (id, name) VALUES ($1, $2)")?;
let affected = db.execute_prepared_mut(
    &insert_stmt,
    &[Value::Integer(3), Value::Text("Charlie".into())]
)?;

// Check cache statistics
let stats = db.plan_cache().stats();
println!("Cache hits: {}, misses: {}", stats.hits(), stats.misses());

Parameter Placeholders

Use PostgreSQL-style $1, $2, etc. or SQLite-style ?1, ?2, etc.:

// PostgreSQL style
let stmt = db.prepare("SELECT * FROM items WHERE price > $1 AND price < $2")?;

// Query with two parameters
let rows = db.query_prepared(&stmt, &[
    Value::Integer(100),
    Value::Integer(500),
])?;

Plan Cache Management

The plan cache is automatically invalidated when schema changes occur (CREATE, DROP, ALTER). You can also manually manage it:

// Invalidate all cached plans
db.invalidate_plan_cache();

// Invalidate plans for a specific table
db.invalidate_plans_for_table("users");

// Get cache statistics
let cache = db.plan_cache();
println!("Cached plans: {}", cache.len());

Query Builder

Type-safe query construction without writing raw SQL strings.

use featherdb::{QueryBuilder, QueryOrdering, Value};

// SELECT with conditions
let rows = QueryBuilder::select(&["id", "name"])
    .from("users")
    .where_eq("active", true)
    .where_gt("age", 18i64)
    .order_by("name", QueryOrdering::Asc)
    .limit(10)
    .execute(&db)?;

// SELECT all columns
let rows = QueryBuilder::select_all()
    .from("users")
    .build();

// SELECT DISTINCT
let statuses = QueryBuilder::select(&["status"])
    .distinct()
    .from("orders")
    .execute(&db)?;

// INSERT
QueryBuilder::insert_into("users")
    .columns(&["id", "name", "email"])
    .values(&[
        Value::Integer(1),
        Value::Text("Bob".into()),
        Value::Text("bob@example.com".into())
    ])
    .execute(&db)?;

// UPDATE
QueryBuilder::update("users")
    .set("name", "Robert")
    .set("score", 100i64)
    .where_eq("id", 1i64)
    .execute(&db)?;

// DELETE
QueryBuilder::delete_from("users")
    .where_eq("id", 1i64)
    .execute(&db)?;

Available WHERE Conditions

.where_eq("column", value)      // column = value
.where_ne("column", value)      // column != value
.where_gt("column", value)      // column > value
.where_gte("column", value)     // column >= value
.where_lt("column", value)      // column < value
.where_lte("column", value)     // column <= value
.where_null("column")           // column IS NULL
.where_not_null("column")       // column IS NOT NULL
.where_like("column", "%pat%")  // column LIKE '%pat%'
.where_clause("custom SQL")     // arbitrary WHERE clause

Derive Macros

Type-safe table definitions.

use featherdb::Table;

#[derive(Table, Debug)]
struct User {
    #[primary_key]
    id: i64,
    name: String,
    email: Option<String>,
}

// Automatic schema information
println!("Table: {}", User::table_name());
println!("Columns: {:?}", User::columns());
println!("PK: {:?}", User::primary_key());

// Convert to/from database rows
let user = User { id: 1, name: "Alice".into(), email: None };
let values = user.to_values();
let restored = User::from_values(&values)?;

Configuration

use featherdb::{Config, Database, EvictionPolicyType};

let config = Config::new("myapp.db")
    .create_if_missing(true)     // Create if doesn't exist
    .buffer_pool_size_mb(64);    // Buffer pool size in MB

let db = Database::open_with_config(config)?;

Eviction Policies

Configure the buffer pool eviction policy:

use featherdb::{Config, Database};

// Clock algorithm (default) - good general-purpose performance
let config = Config::new("myapp.db");

// LRU-2 - better for scan-heavy workloads
let config = Config::new("myapp.db").with_lru2_eviction();

// LIRS - better for workloads with recency and frequency patterns
let config = Config::new("myapp.db").with_lirs_eviction();

Transactions

Auto-commit

Single statements are auto-committed:

db.execute("INSERT INTO users (id, name) VALUES (1, 'Alice')")?;
// Automatically committed

Explicit Transactions

Multiple statements in one transaction:

let mut txn = db.begin()?;
txn.execute("INSERT INTO accounts (id, balance) VALUES (1, 1000)")?;
txn.execute("INSERT INTO accounts (id, balance) VALUES (2, 500)")?;
txn.commit()?;  // Both inserts committed atomically

Savepoints

Partial rollback within transaction:

let mut txn = db.begin()?;
txn.execute("INSERT INTO orders (id) VALUES (1)")?;
txn.savepoint("after_order")?;

txn.execute("INSERT INTO items (order_id) VALUES (1)")?;

// Oops, rollback just the items
txn.rollback_to_savepoint("after_order")?;

// Order is still there, items are gone
// Savepoint is still valid - can be used again
txn.execute("INSERT INTO items (order_id) VALUES (1)")?;  // Try again
txn.commit()?;

You can also release savepoints to remove them:

txn.savepoint("sp1")?;
txn.savepoint("sp2")?;

// Release sp1 (also releases sp2 since it was created after)
txn.release_savepoint("sp1")?;

// List active savepoints
let names = txn.savepoint_names();

Read-Only Transactions

Consistent snapshot reads:

let txn = db.begin_read_only()?;
let count1 = txn.query_one("SELECT COUNT(*) FROM users")?;
// Other transactions can't affect what we see
let count2 = txn.query_one("SELECT COUNT(*) FROM users")?;
// count1 and count2 will be equal - snapshot isolation
txn.commit()?;

Automatic Rollback

Transactions automatically rollback on drop if not committed:

{
    let mut txn = db.begin()?;
    txn.execute("INSERT INTO users (id, name) VALUES (1, 'Alice')")?;
    // Transaction dropped without commit - automatically rolled back
}
// The insert is NOT visible

Error Handling

FeatherDB uses a unified Error type with variants for different error conditions:

use featherdb::{Database, Error};

match db.execute("INSERT INTO users (id) VALUES (1)") {
    Ok(affected) => println!("{} rows affected", affected),
    Err(Error::InvalidQuery { message }) => {
        println!("Invalid query: {}", message);
    }
    Err(Error::TableNotFound { name }) => {
        println!("Table '{}' not found", name);
    }
    Err(Error::ReadOnly) => {
        println!("Cannot modify in read-only transaction");
    }
    Err(Error::TransactionEnded) => {
        println!("Transaction already committed or rolled back");
    }
    Err(Error::SavepointNotFound { name }) => {
        println!("Savepoint '{}' not found", name);
    }
    Err(e) => println!("Error: {}", e),
}

Common Error Types

Error	Description
InvalidQuery	SQL syntax error or semantic error
TableNotFound	Referenced table does not exist
ReadOnly	Write operation in read-only transaction
TransactionEnded	Operation on committed/rolled-back transaction
SavepointNotFound	Referenced savepoint does not exist
IoError	File system or I/O error
ParseError	SQL parsing failed

Database Metrics

Storage Limits

Control database size to prevent runaway disk usage:

use featherdb::{Config, Database};

// Set maximum database size
let config = Config::new("myapp.db")
    .with_max_database_size_mb(100); // 100MB limit

let db = Database::open_with_config(config)?;

// Monitor storage usage
let quota = db.storage_quota();
println!("Database: {} / {} bytes", quota.used_bytes, quota.limit_bytes.unwrap_or(0));
println!("Usage: {:.1}%", quota.usage_percent().unwrap_or(0.0));

if let Some(remaining) = quota.remaining() {
    println!("Remaining: {} bytes", remaining);
}

// Check current size
let size = db.size();
let max = db.max_size(); // None if unlimited

// Handle limit errors
match db.execute("INSERT INTO large_table VALUES (...)") {
    Ok(_) => println!("Insert succeeded"),
    Err(Error::StorageLimitExceeded { current_bytes, limit_bytes, .. }) => {
        eprintln!("Storage limit exceeded: {} / {} bytes", current_bytes, limit_bytes);
        // Clean up old data or increase limit
    }
    Err(e) => eprintln!("Error: {}", e),
}

Observability

Monitor database health and performance with comprehensive metrics:

let metrics = db.metrics();

// Quick health check
println!("Overall Health Score: {:.2}%", metrics.health_score() * 100.0);
println!("Avg Query Time: {} us", metrics.avg_query_time_us());
println!("GC Efficiency: {:.2}%", metrics.gc_efficiency() * 100.0);

// Storage metrics
println!("\nStorage:");
println!("  Buffer Pool Hit Ratio: {:.2}%", metrics.buffer_pool.hit_ratio() * 100.0);
println!("  Cache Hits: {}", metrics.buffer_pool.cache_hits);
println!("  Cache Misses: {}", metrics.buffer_pool.cache_misses);
println!("  Compression Ratio: {:.2}x", metrics.compression.compression_ratio());
println!("  Space Savings: {:.1}%", metrics.compression.space_savings_percent());

// WAL metrics
println!("\nWAL:");
println!("  Total Records: {}", metrics.wal.total_records);
println!("  Group Commits: {}", metrics.wal.group_commits);
println!("  Avg Batch Size: {:.1}", metrics.wal.average_batch_size());

// Query metrics
println!("\nQuery:");
println!("  Queries Executed: {}", metrics.query.queries_executed);
println!("  Avg Parse Time: {:.0} us", metrics.query.avg_parse_time_us());
println!("  Avg Plan Time: {:.0} us", metrics.query.avg_plan_time_us());
println!("  Avg Exec Time: {:.0} us", metrics.query.avg_exec_time_us());
println!("  Selectivity: {:.2}%", metrics.query.selectivity_ratio() * 100.0);

// Plan cache metrics
println!("\nPlan Cache:");
println!("  Size: {}/{}", metrics.plan_cache.size, metrics.plan_cache.capacity);
println!("  Hit Ratio: {:.2}%", metrics.plan_cache.hit_ratio() * 100.0);

// Transaction metrics
println!("\nTransaction:");
println!("  Active: {}", metrics.transaction.active_count);
println!("  Committed: {}", metrics.transaction.total_commits);
println!("  Rolled Back: {}", metrics.transaction.total_rollbacks);
println!("  Commit Rate: {:.2}%", metrics.transaction.commit_rate() * 100.0);
println!("  Avg Duration: {} us", metrics.transaction.avg_duration_us);

// GC metrics
println!("\nGarbage Collection:");
println!("  GC Runs: {}", metrics.gc.gc_runs);
println!("  Versions Created: {}", metrics.gc.versions_created);
println!("  Versions Cleaned: {}", metrics.gc.versions_cleaned);

// Pretty-print all metrics
println!("{}", metrics);

DatabaseMetrics API

The DatabaseMetrics struct provides:

Storage Subsystem:

• buffer_pool: BufferPoolStatsSnapshot - Cache hits, misses, evictions
• wal: WalStatsSnapshot - WAL records, group commits, fsync count
• compression: CompressionStatsSnapshot - Compression ratio, space savings

Query Subsystem:

• query: QueryMetricsSnapshot - Parse/plan/exec times, rows scanned/returned
• plan_cache: PlanCacheSnapshot - Plan cache hits, misses, size

Transaction Subsystem:

• transaction: TransactionMetricsSnapshot - Active count, commits, rollbacks
• gc: GcMetricsSnapshot - GC runs, versions created/cleaned

Helper Methods:

• health_score() -> f64 - Overall health (0-1, weighted by buffer hits, plan cache, txn success)
• storage_efficiency() -> f64 - Compression ratio (0-1, lower is better)
• avg_query_time_us() -> u64 - Average total query time in microseconds
• total_transactions() -> u64 - Total transactions (commits + rollbacks)
• gc_efficiency() -> f64 - Versions cleaned / versions created

Metric Snapshots

All metrics are snapshots taken at a point in time, so they're consistent:

let metrics = db.metrics();

// These values are from the same snapshot
let health = metrics.health_score();
let efficiency = metrics.storage_efficiency();

// Query metrics have helper methods
let query = metrics.query;
println!("Avg total time: {:.0} us", query.avg_total_time_us());
println!("Avg rows scanned: {:.0}", query.avg_rows_scanned());
println!("Avg rows returned: {:.0}", query.avg_rows_returned());
println!("Selectivity: {:.2}%", query.selectivity_ratio() * 100.0);

Display Implementation

The DatabaseMetrics struct implements Display for pretty-printing:

let metrics = db.metrics();
println!("{}", metrics);

Output:

=== FeatherDB Metrics ===

Storage:
  Buffer Pool Hit Ratio: 90.91%
  Cache Hits: 100
  Cache Misses: 10
  Pages Evicted: 5
  Compression Ratio: 0.50x
  Space Savings: 50.0%

WAL:
  Total Records: 1000
  Group Commits: 50
  Fsync Count: 50
  Avg Batch Size: 10.0

Query:
  Queries Executed: 200
  Avg Parse Time: 50 us
  Avg Plan Time: 100 us
  Avg Exec Time: 250 us
  Rows Scanned: 5000
  Rows Returned: 1000

Plan Cache:
  Size: 50/100
  Hit Ratio: 90.00%
  Hits: 90
  Misses: 10

Transaction:
  Active: 2
  Committed: 450
  Rolled Back: 50
  Long Running: 1
  Avg Duration: 1000 us

Garbage Collection:
  GC Runs: 10
  Versions Created: 1000
  Versions Cleaned: 800
  Versions Pending: 200
  GC Time: 100000 us
  GC Efficiency: 80.00%

Overall Health Score: 90.36%

Re-exports

This crate re-exports types from all subsystem crates:

// Core types
pub use featherdb_core::{
    Config, Error, Result, Value, ColumnType,
    PageId, TransactionId, Lsn,
    ToRow, FromRow, ToValue, FromValue,
    TableSchema, ColumnDef, EvictionPolicyType,
};

// Catalog types
pub use featherdb_catalog::{
    Catalog, Column, ColumnConstraint, Index, IndexType, Migration, Table,
};

// Query types
pub use featherdb_query::{
    Parser, Planner, Optimizer, Executor, LogicalPlan,
    Row, Expr, BinaryOp, UnaryOp, AggregateFunction,
    ExecutionContext, PreparedStatement, PlanCache, CacheStats, QueryResult,
};

// MVCC types
pub use featherdb_mvcc::{
    Snapshot, TransactionManager, TransactionMode, TransactionStatus,
};

// Derive macros
pub use featherdb_derive::Table;

Integration with Other Crates

Using Lower-Level APIs

For advanced use cases, you can access the underlying components:

// Access the catalog directly
let catalog = db.catalog();
let table = catalog.get_table("users")?;
println!("Columns: {:?}", table.columns);

// Access the optimizer
let optimizer = db.optimizer();

Custom Query Execution

use featherdb::{Parser, Planner, Optimizer, Executor};

// Parse SQL
let stmt = Parser::parse_one("SELECT * FROM users")?;

// Plan query
let planner = Planner::new(db.catalog());
let plan = planner.plan(&stmt)?;

// Optimize
let optimized = db.optimizer().optimize(plan);

// Execute within a transaction for proper isolation
let txn = db.begin_read_only()?;
// ... execution with proper context

Testing

# Run all tests (using Make)
make test-crate CRATE=featherdb

# Or with cargo directly
cargo test -p featherdb

# Run specific test
cargo test -p featherdb transaction

# Run with output
cargo test -p featherdb -- --nocapture

# Run coverage (from project root)
make coverage  # or: cargo llvm-cov --workspace

Crate Structure

featherdb/
├── src/
│   ├── lib.rs          # Re-exports and module declarations
│   ├── database.rs     # Database handle and main API
│   ├── transaction.rs  # Transaction with savepoint support
│   └── query_builder.rs # Type-safe query construction
└── tests/
    ├── derive_test.rs  # Derive macro integration tests
    └── query_builder_test.rs