# Design: sqlite-provider
## 1. Overview
`sqlite-provider` is a Rust crate that provides a high-level, backend-agnostic abstraction layer over the SQLite3 C API. It is designed for high-performance infrastructure projects, emphasizing zero-cost abstractions, safety, and extensibility.
The core of the crate is the `Sqlite3Api` trait, a Service Provider Interface (SPI) that defines the low-level contract for a SQLite backend. Different backends (like `libsqlite3`, `SQLCipher`, or `libsql`) can implement this trait. On top of this SPI, the crate provides a safe, idiomatic Rust API with types like `Connection`, `Statement`, and `Row`, which handle resource management, lifetimes, and error handling automatically.
### Guiding Principles
- **Performance:** Hot paths, such as reading data from a result set, should have minimal overhead. This is achieved by avoiding memory allocations and copies wherever possible, using borrowed views (`ValueRef`) into the database's own memory buffers.
- **Safety:** The C API's unsafety is contained within the SPI implementation. The public-facing API is safe and leverages Rust's lifetime system to prevent common errors like using a statement after its connection has been closed.
- **Simplicity:** The API should be easy to use for common tasks. Complexity is managed by separating core functionality from optional features, which are exposed via extension traits.
- **Extensibility:** The design allows for backend-specific features (e.g., encryption) and advanced SQLite functionality (e.g., virtual tables, custom functions) without cluttering the core API.
## 2. High-Level Architecture
The crate is structured into a low-level unsafe SPI and a high-level safe public API.
```text
+-------------------------------------------------+
| Connection, Statement, Row, Value, UDF, VTab |
| |
+----------------------+--------------------------+
|
| (Wraps)
|
+----------------------v--------------------------+
| trait Sqlite3Api |
| |
+----------------------+--------------------------+
|
| (Implemented by)
|
+----------------------v--------------------------+
| [libsqlite3] [SQLCipher] [libsql] ... |
| |
+-------------------------------------------------+
```
### Module Structure
- `provider`: Defines the core `Sqlite3Api` trait and its associated types.
- `error`: Defines `Error`, `ErrorCode`, and `Result<T>`.
- `value`: Defines the safe `Value` (owned) and `ValueRef` (borrowed) types.
- `connection`, `statement`, `row`: Contain the safe wrappers `Connection`, `Statement`, and `Row`.
- `function`: Provides helpers for creating user-defined functions (UDFs).
- `vtab`: Contains traits and helpers for implementing virtual tables.
### Object Lifetime Graph
The safe API uses lifetimes to enforce correct usage patterns.
- `Connection<'p, P: Sqlite3Api>` holds a reference to the provider `&'p P` and the raw database handle.
- `Statement<'c, 'p, P>` holds a reference to its `&'c Connection<'p, P>`, ensuring it cannot outlive the connection.
- `Row<'s, 'c, 'p, P>` is a temporary type that borrows a `&'s Statement<'c, 'p, P>` for the current row snapshot; `Statement::step` requires `&mut self`, so a row borrow cannot overlap with stepping again.
- `ValueRef<'r>` is a temporary view into a column's data, borrowed from a `&'r Row`. Its lifetime is tied to the row, preventing use after the next `step()` call.
```rust
// Simplified sketch of safe wrappers
pub struct Connection<'p, P: Sqlite3Api> {
api: &'p P,
db: NonNull<P::Db>,
}
pub struct Statement<'c, 'p, P: Sqlite3Api> {
conn: &'c Connection<'p, P>,
stmt: NonNull<P::Stmt>,
}
// An owned SQLite value, used for UDF return values or when data must be stored.
pub enum Value {
Null,
Integer(i64),
Float(f64),
Text(String),
Blob(Vec<u8>),
}
// A borrowed view of a SQLite value, used for maximum performance when reading columns or UDF arguments.
pub enum ValueRef<'a> {
Null,
Integer(i64),
Float(f64),
Text(&'a str),
Blob(&'a [u8]),
}
```
## 3. Provider SPI (`Sqlite3Api` Trait)
This `unsafe trait` is the core of the abstraction layer. Implementers are responsible for binding to a specific SQLite C library and upholding the contracts of the C API.
### Core Trait
```rust
use core::ffi::{c_char, c_void};
use core::ptr::NonNull;
// ... other supporting types like ApiVersion, FeatureSet, OpenFlags ...
/// # Safety
/// Implementations must uphold the SQLite C ABI contracts for the
/// specific backend they wrap. This includes but is not limited to:
/// - Pointer validity: All `NonNull` pointers returned must be valid until released.
/// - Threading modes: The implementation must respect the threading guarantees of the underlying library.
/// - Lifetime rules: Resources must be managed according to SQLite's documentation.
pub unsafe trait Sqlite3Api: Send + Sync + 'static {
// Opaque handle types for the backend's internal structs.
type Db;
type Stmt;
type Value;
type Context;
type VTab;
type VTabCursor;
// --- Versioning and Capability Discovery ---
fn api_version(&self) -> ApiVersion;
fn feature_set(&self) -> FeatureSet;
fn backend_name(&self) -> &'static str;
fn backend_version(&self) -> Option<ApiVersion>;
// --- SQLite Allocator + Threading Introspection ---
/// Allocate `size` bytes using the backend/SQLite-compatible allocator.
unsafe fn malloc(&self, size: usize) -> *mut c_void;
/// Release memory returned by `malloc`.
unsafe fn free(&self, ptr: *mut c_void);
/// Return SQLite's threadsafety mode (`sqlite3_threadsafe` contract).
fn threadsafe(&self) -> i32;
// --- Connection Management ---
unsafe fn open(&self, filename: &str, options: OpenOptions<'_>) -> Result<NonNull<Self::Db>>;
unsafe fn close(&self, db: NonNull<Self::Db>) -> Result<()>;
// --- Error Handling ---
unsafe fn errcode(&self, db: NonNull<Self::Db>) -> i32;
unsafe fn errmsg(&self, db: NonNull<Self::Db>) -> *const c_char;
unsafe fn extended_errcode(&self, db: NonNull<Self::Db>) -> Option<i32>;
// --- Statement Management ---
unsafe fn prepare_v2(&self, db: NonNull<Self::Db>, sql: &str) -> Result<NonNull<Self::Stmt>>;
unsafe fn prepare_v3(&self, db: NonNull<Self::Db>, sql: &str, flags: u32) -> Result<NonNull<Self::Stmt>>;
unsafe fn step(&self, stmt: NonNull<Self::Stmt>) -> Result<StepResult>;
unsafe fn reset(&self, stmt: NonNull<Self::Stmt>) -> Result<()>;
unsafe fn finalize(&self, stmt: NonNull<Self::Stmt>) -> Result<()>;
// --- Parameter Binding ---
unsafe fn bind_null(&self, stmt: NonNull<Self::Stmt>, idx: i32) -> Result<()>;
unsafe fn bind_int64(&self, stmt: NonNull<Self::Stmt>, idx: i32, v: i64) -> Result<()>;
unsafe fn bind_double(&self, stmt: NonNull<Self::Stmt>, idx: i32, v: f64) -> Result<()>;
/// # Safety
/// Implementations MUST copy the provided string data (e.g., using `SQLITE_TRANSIENT`),
/// as the `v` slice is not guaranteed to outlive the call.
unsafe fn bind_text(&self, stmt: NonNull<Self::Stmt>, idx: i32, v: &str) -> Result<()>;
/// # Safety
/// Byte-oriented text bind path used by ABI callers that may pass non-UTF8 TEXT bytes.
/// Implementations should preserve raw bytes as SQLite TEXT and copy when required.
unsafe fn bind_text_bytes(&self, stmt: NonNull<Self::Stmt>, idx: i32, v: &[u8]) -> Result<()>;
/// # Safety
/// Implementations MUST copy the provided blob data (e.g., using `SQLITE_TRANSIENT`),
/// as the `v` slice is not guaranteed to outlive the call.
unsafe fn bind_blob(&self, stmt: NonNull<Self::Stmt>, idx: i32, v: &[u8]) -> Result<()>;
// --- Column Data Access ---
unsafe fn column_count(&self, stmt: NonNull<Self::Stmt>) -> i32;
unsafe fn column_type(&self, stmt: NonNull<Self::Stmt>, col: i32) -> ValueType;
unsafe fn column_int64(&self, stmt: NonNull<Self::Stmt>, col: i32) -> i64;
unsafe fn column_double(&self, stmt: NonNull<Self::Stmt>, col: i32) -> f64;
/// # Safety
/// Returns a raw pointer/length view over SQLite-owned bytes for this row snapshot.
/// Validity is bounded by statement lifecycle transitions (`step`/`reset`/`finalize`)
/// and backend-specific text/blob buffer stability rules.
unsafe fn column_text(&self, stmt: NonNull<Self::Stmt>, col: i32) -> RawBytes;
/// # Safety
/// Returns a raw pointer/length view over SQLite-owned bytes for this row snapshot.
unsafe fn column_blob(&self, stmt: NonNull<Self::Stmt>, col: i32) -> RawBytes;
// --- UDF Argument Access ---
unsafe fn value_type(&self, v: NonNull<Self::Value>) -> ValueType;
unsafe fn value_int64(&self, v: NonNull<Self::Value>) -> i64;
// ... other value accessors for double, text, blob ...
// --- UDF Result Setting ---
unsafe fn result_null(&self, ctx: NonNull<Self::Context>);
unsafe fn result_int64(&self, ctx: NonNull<Self::Context>, v: i64);
// ... other result setters ...
// Note: For text/blob results, providers must ensure the data is valid for the
// duration SQLite requires, typically by passing SQLITE_TRANSIENT to tell SQLite to copy it.
unsafe fn result_text(&self, ctx: NonNull<Self::Context>, v: &str);
/// Byte-oriented text result path used by ABI callers that may pass non-UTF8 TEXT bytes.
unsafe fn result_text_bytes(&self, ctx: NonNull<Self::Context>, v: &[u8]);
unsafe fn result_blob(&self, ctx: NonNull<Self::Context>, v: &[u8]);
unsafe fn result_error(&self, ctx: NonNull<Self::Context>, msg: &str);
// ... other functions for UDFs, virtual tables, etc. ...
}
```
### Extension Traits
To keep the core `Sqlite3Api` trait lean, optional functionality is exposed through extension traits. A provider can choose which of these to implement. The safe API wrappers will conditionally expose methods based on the provider's capabilities.
```rust
/// Optional extension for SQLCipher-style database keying.
pub unsafe trait Sqlite3Keying: Sqlite3Api {
unsafe fn key(&self, db: NonNull<Self::Db>, key: &[u8]) -> Result<()>;
unsafe fn rekey(&self, db: NonNull<Self::Db>, key: &[u8]) -> Result<()>;
}
/// Optional extension for the online backup API.
pub unsafe trait Sqlite3Backup: Sqlite3Api {
type Backup;
// ... backup_init, backup_step, backup_finish ...
}
/// Optional extension for incremental blob I/O.
pub unsafe trait Sqlite3BlobIo: Sqlite3Api {
type Blob;
// ... blob_open, blob_read, blob_write, blob_close ...
}
// ... other traits for Hooks, WAL, Metadata, Serialize etc.
```
## 4. Safety and Performance
### Zero-Cost Abstractions
- **Static Dispatch:** The `Sqlite3Api` trait is designed to be used as a generic parameter `P: Sqlite3Api`. This allows the Rust compiler to monomorphize the code for a specific backend, eliminating dynamic dispatch overhead in performance-critical paths.
- **Borrowed Views:** The `ValueRef` enum provides a way to access data from a result row without allocating a `String` or `Vec<u8>`. It is a temporary, borrowed view directly into the buffer provided by SQLite, which is a significant performance optimization.
For text decoding, invalid UTF-8 bytes are surfaced as `ValueRef::Blob` rather than forcing unchecked UTF-8 conversion, preserving safety while still avoiding copies on the hot path.
### Lifetime and Resource Management
- **RAII:** Safe wrappers like `Connection` and `Statement` use `Drop` to automatically release the underlying SQLite resources (e.g., calling `sqlite3_close` or `sqlite3_finalize`). This prevents resource leaks.
- **Lifetime Enforcement:** The compiler enforces that a `Statement` cannot outlive its `Connection`, and that data borrowed from a `Row` cannot outlive the row itself. This prevents use-after-free errors.
### Panic Safety
- All `extern "C"` callbacks that are passed into the SQLite library (e.g., for UDFs, authorizers) must be wrapped with `std::panic::catch_unwind`. If a panic occurs within the Rust code, the trampoline will catch it, report an error to SQLite (e.g., using `sqlite3_result_error`), and prevent the panic from unwinding across the FFI boundary, which would be undefined behavior.
## 5. User-Defined Functions (UDFs)
The crate will provide a safe and convenient API for registering custom SQL functions written in Rust.
```rust
use sqlite_provider::{Connection, Context, Result, Value, ValueRef};
// A simple scalar function that reverses a string.
fn reverse_string(ctx: &Context<'_>, args: &[ValueRef<'_>]) -> Result<Value> {
match args.get(0) {
Some(ValueRef::Text(s)) => {
let reversed = s.chars().rev().collect::<String>();
Ok(Value::Text(reversed))
}
_ => {
// Report an error to SQLite. The safe API wrapper for `Context`
// will provide a method to do this ergonomically.
ctx.result_error("Expected a single text argument.");
// The returned Ok value is ignored when an error is set.
Ok(Value::Null)
}
}
}
fn register_my_functions(conn: &Connection<'_, impl Sqlite3Api>) -> Result<()> {
conn.create_scalar_function(
"reverse",
1, // Number of arguments
reverse_string,
)
}
```
Behind the scenes, `create_scalar_function` generates an `extern "C"` trampoline function. This trampoline is responsible for:
1. Safely casting the `void* user_data` back into a `Box` containing the Rust closure.
2. Wrapping the raw `sqlite3_context*` and `sqlite3_value**` pointers into safe `Context` and `&[ValueRef]` slices.
3. Calling the user's Rust closure inside `catch_unwind`.
4. Translating the returned `Result<Value>` into the appropriate `sqlite3_result_*` C API calls.
## 6. Virtual Tables
A similar abstraction is provided for creating virtual tables in Rust. Users implement provider-generic `VirtualTable<P>` and `VTabCursor<P>` traits. The library provides the `sqlite3_module` structure and C trampolines needed to register with SQLite.
```rust
pub trait VirtualTable<P: Sqlite3Api>: Sized + Send {
type Cursor: VTabCursor<P>;
type Error: Into<Error>;
/// Called to create or connect to the virtual table.
/// The `String` in the return value is the SQL `CREATE TABLE` statement for the table's schema.
fn connect(args: &[&str]) -> core::result::Result<(Self, String), Self::Error>;
/// Called to disconnect or destroy the virtual table.
fn disconnect(self) -> core::result::Result<(), Self::Error>;
/// Plan the best index to use for a query.
fn best_index(&self, info: &mut BestIndexInfo) -> core::result::Result<(), Self::Error>;
/// Open a new cursor to scan the table.
fn open(&self) -> core::result::Result<Self::Cursor, Self::Error>;
}
pub trait VTabCursor<P: Sqlite3Api>: Sized + Send {
type Error: Into<Error>;
/// Start a scan or search.
fn filter(
&mut self,
idx_num: i32,
idx_str: Option<&str>,
args: &[ValueRef<'_>],
) -> core::result::Result<(), Self::Error>;
/// Advance to the next row.
fn next(&mut self) -> core::result::Result<(), Self::Error>;
/// Check if the cursor is at the end of the data.
fn eof(&self) -> bool;
/// Get the value for a specific column in the current row.
fn column(&self, ctx: &Context<'_, P>, col: i32) -> core::result::Result<(), Self::Error>;
/// Get the rowid for the current row.
fn rowid(&self) -> core::result::Result<i64, Self::Error>;
}
```
This design provides a robust, performant, and safe foundation for interacting with SQLite from Rust, while remaining flexible enough to accommodate different backends and advanced use cases.