Embedded API Reference

This document provides a comprehensive reference for the Oxibase Rust API. Oxibase can be used as an embedded database in Rust applications. Although it is designed for use in server environments, it can also be used as an embedded database. This allows Oxibase to be used in a variety of scenarios, including embedded systems and IoT devices.

By making Oxibase Embeddable, make the single-node development process easier and more efficient.

Table of Contents

1. Database API
   1. Opening a Database
   2. Connection String Options
2. Executing Queries
   1. execute()
   2. query()
   3. execute_with_timeout()
   4. query_with_timeout()
   5. query_one()
   6. query_opt()
   7. Named Parameters
3. Prepared Statements
4. Transactions
   1. Basic Transactions
   2. Savepoints
   3. Isolation Levels
5. Working with Rows
   1. Accessing Column Values
   2. Supported Types
6. Error Handling
7. Thread Safety
8. Complete Example
9. Best Practices

Database API

The Database struct is the main entry point for using Oxibase.

Opening a Database

use oxibase::Database;

// Open an in-memory database (unique instance)
let db = Database::open_in_memory()?;

// Open an in-memory database (shared instance)
let db = Database::open("memory://")?;

// Open a persistent database
let db = Database::open("file:///path/to/database")?;

// Open with configuration options
let db = Database::open("file:///path/to/database?sync_mode=full&snapshot_interval=60")?;

Connection String Options

Parameter	Description	Values
sync_mode	WAL sync mode	none, normal, full (or 0, 1, 2)
snapshot_interval	Snapshot interval in seconds	Integer
keep_snapshots	Number of snapshots to keep	Integer
wal_flush_trigger	Operations before WAL flush	Integer
compression	Enable compression	on, off

See all connection string options in the connection string reference

Executing Queries

execute()

Execute DDL or DML statements that don’t return rows.

use oxibase::{Database, params};

let db = Database::open("memory://")?;

// DDL - no parameters
db.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)", ())?;

// DML with positional parameters ($1, $2, ...)
db.execute("INSERT INTO users VALUES ($1, $2)", (1, "Alice"))?;

// DML with params! macro
db.execute("INSERT INTO users VALUES ($1, $2)", params![2, "Bob"])?;

// Returns rows affected
let affected = db.execute("UPDATE users SET name = $1 WHERE id = $2", ("Charlie", 1))?;
println!("Updated {} rows", affected);

query()

Execute a SELECT query and iterate over results.

// Query all rows
for row in db.query("SELECT * FROM users", ())? {
    let row = row?;
    let id: i64 = row.get(0)?;           // By index
    let name: String = row.get("name")?; // By column name
    println!("User {}: {}", id, name);
}

// Query with parameters
for row in db.query("SELECT * FROM users WHERE id > $1", (10,))? {
    let row = row?;
    // Process row...
}

// Collect into Vec
let users: Vec<_> = db.query("SELECT * FROM users", ())?
    .collect::<Result<Vec<_>, _>>()?;

execute_with_timeout()

Execute a write statement with a timeout. The query is cancelled if it exceeds the specified timeout.

// Execute with 5 second timeout (timeout in milliseconds)
db.execute_with_timeout(
    "DELETE FROM large_table WHERE created_at < $1",
    ("2020-01-01",),
    5000  // 5000ms = 5 seconds
)?;

// Use 0 for no timeout
db.execute_with_timeout("UPDATE users SET active = true", (), 0)?;

query_with_timeout()

Execute a query with a timeout. The query is cancelled if it exceeds the specified timeout.

// Query with 10 second timeout
for row in db.query_with_timeout(
    "SELECT * FROM large_table WHERE complex_condition",
    (),
    10000  // 10000ms = 10 seconds
)? {
    let row = row?;
    // Process row...
}

// Useful for preventing long-running queries from blocking
let results = db.query_with_timeout(
    "SELECT * FROM orders WHERE status = $1",
    ("pending",),
    3000  // 3 second timeout
)?;

query_one()

Execute a query that returns a single value.

// Get a single value
let count: i64 = db.query_one("SELECT COUNT(*) FROM users", ())?;

// With parameters
let name: String = db.query_one("SELECT name FROM users WHERE id = $1", (1,))?;

query_opt()

Execute a query that returns an optional single value.

// Returns None if no rows match
let name: Option<String> = db.query_opt(
    "SELECT name FROM users WHERE id = $1",
    (999,)
)?;

match name {
    Some(n) => println!("Found: {}", n),
    None => println!("Not found"),
}

Named Parameters

Use named parameters with :name syntax.

use oxibase::{Database, named_params};

let db = Database::open("memory://")?;
db.execute("CREATE TABLE users (id INTEGER, name TEXT, age INTEGER)", ())?;

// Insert with named parameters
db.execute_named(
    "INSERT INTO users VALUES (:id, :name, :age)",
    named_params!{ id: 1, name: "Alice", age: 30 }
)?;

// Query with named parameters
for row in db.query_named(
    "SELECT * FROM users WHERE age > :min_age",
    named_params!{ min_age: 25 }
)? {
    // Process rows...
}

Prepared Statements

Prepare a statement for repeated execution.

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

// Execute multiple times with different parameters
for id in 1..=10 {
    for row in stmt.query((id,))? {
        let row = row?;
        // Process row...
    }
}

Transactions

Basic Transactions

// Begin transaction
db.execute("BEGIN", ())?;

// Execute statements
db.execute("INSERT INTO users VALUES ($1, $2)", (1, "Alice"))?;
db.execute("UPDATE accounts SET balance = balance - 100 WHERE id = $1", (1,))?;

// Commit or rollback
db.execute("COMMIT", ())?;
// Or: db.execute("ROLLBACK", ())?;

Savepoints

db.execute("BEGIN", ())?;

db.execute("INSERT INTO users VALUES ($1, $2)", (1, "Alice"))?;
db.execute("SAVEPOINT sp1", ())?;

db.execute("INSERT INTO users VALUES ($1, $2)", (2, "Bob"))?;
// Oops, undo the second insert
db.execute("ROLLBACK TO SAVEPOINT sp1", ())?;

db.execute("COMMIT", ())?;
// Only Alice is inserted

Isolation Levels

// Snapshot isolation (prevents lost updates)
db.execute("BEGIN TRANSACTION ISOLATION LEVEL SNAPSHOT", ())?;
// ... your queries ...
db.execute("COMMIT", ())?;

// Read committed (default, higher concurrency)
db.execute("BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED", ())?;
// ... your queries ...
db.execute("COMMIT", ())?;

Working with Rows

Accessing Column Values

for row in db.query("SELECT id, name, active FROM users", ())? {
    let row = row?;

    // By index (0-based)
    let id: i64 = row.get(0)?;

    // By column name
    let name: String = row.get("name")?;

    // Optional values (for nullable columns)
    let active: Option<bool> = row.get("active")?;
}

Supported Types

SQL Type	Rust Type
INTEGER	i64, i32, i16, i8
FLOAT	f64, f32
TEXT	String, &str
BOOLEAN	bool
TIMESTAMP	String (ISO format)
JSON	String
NULL	Option

Error Handling

use oxibase::{Database, Error};

let db = Database::open("memory://")?;

match db.execute("INSERT INTO users VALUES (1, 'Alice')", ()) {
    Ok(affected) => println!("Inserted {} rows", affected),
    Err(e) => {
        let msg = e.to_string();
        if msg.contains("UNIQUE constraint") {
            println!("Duplicate key error");
        } else if msg.contains("write-write conflict") {
            println!("Transaction conflict - should retry");
        } else {
            return Err(e.into());
        }
    }
}

Thread Safety

The Database struct is thread-safe and can be shared across threads:

use std::sync::Arc;
use std::thread;

let db = Arc::new(Database::open("memory://")?);
db.execute("CREATE TABLE counter (id INTEGER PRIMARY KEY, value INTEGER)", ())?;
db.execute("INSERT INTO counter VALUES (1, 0)", ())?;

let handles: Vec<_> = (0..4).map(|_| {
    let db = Arc::clone(&db);
    thread::spawn(move || {
        for _ in 0..100 {
            db.execute("UPDATE counter SET value = value + 1 WHERE id = 1", ()).unwrap();
        }
    })
}).collect();

for h in handles {
    h.join().unwrap();
}

let count: i64 = db.query_one("SELECT value FROM counter WHERE id = 1", ())?;
println!("Final count: {}", count);

Complete Example

use oxibase::{Database, params};

fn main() -> Result<(), Box<dyn std::error::Error>> {
    // Open persistent database
    let db = Database::open("file:///tmp/myapp.db")?;

    // Create schema
    db.execute("
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTO_INCREMENT,
            name TEXT NOT NULL,
            email TEXT,
            created_at TIMESTAMP
        )
    ", ())?;

    // Create index
    db.execute("CREATE INDEX IF NOT EXISTS idx_email ON users(email)", ())?;

    // Insert data in a transaction
    db.execute("BEGIN", ())?;
    db.execute(
        "INSERT INTO users (name, email, created_at) VALUES ($1, $2, NOW())",
        ("Alice", "alice@example.com")
    )?;
    db.execute(
        "INSERT INTO users (name, email, created_at) VALUES ($1, $2, NOW())",
        ("Bob", "bob@example.com")
    )?;
    db.execute("COMMIT", ())?;

    // Query with aggregation
    let count: i64 = db.query_one("SELECT COUNT(*) FROM users", ())?;
    println!("Total users: {}", count);

    // Query with window function
    for row in db.query("
        SELECT name, email,
               ROW_NUMBER() OVER (ORDER BY created_at) as row_num
        FROM users
    ", ())? {
        let row = row?;
        let name: String = row.get("name")?;
        let row_num: i64 = row.get("row_num")?;
        println!("{}: {}", row_num, name);
    }

    Ok(())
}

Best Practices

1. Use transactions for related operations: Wrap multiple statements in BEGIN/COMMIT
2. Handle conflicts in SNAPSHOT isolation: Be prepared to retry on write-write conflicts
3. Use parameterized queries: Prevents SQL injection
4. Create indexes for frequently filtered columns: Improves query performance
5. Use query_one for single values: Simpler than iterating
6. Close database properly: The database is automatically closed when dropped
7. Run ANALYZE after bulk inserts: Updates optimizer statistics
8. Use timeouts for untrusted queries: Use execute_with_timeout and query_with_timeout to prevent long-running queries from blocking your application