# 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
```rust
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.
```rust
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.
```rust
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.
```rust
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.:
```rust
// 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:
```rust
// 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.
```rust
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
```rust
.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.
```rust
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
```rust
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:
```rust
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:
```rust
db.execute("INSERT INTO users (id, name) VALUES (1, 'Alice')")?;
// Automatically committed
```
### Explicit Transactions
Multiple statements in one transaction:
```rust
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:
```rust
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:
```rust
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:
```rust
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:
```rust
{
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:
```rust
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
| `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:
```rust
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:
```rust
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:
```rust
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:
```rust
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:
```rust
// 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:
```rust
// 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
```rust
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
```bash
# 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
```