Connection String Reference

This document provides information about Stoolap connection string formats and their usage.

Connection String Basics

Stoolap connection strings follow a URL-like format:

scheme://[path][?options]

Where:

• scheme specifies the storage mode (memory or file)
• path provides location information for persistent storage
• options are optional query parameters for configuration

Storage Modes

Stoolap supports two storage modes:

In-Memory Mode (memory://)

memory://

All data stored in RAM:

• Maximum performance for temporary data
• Full MVCC transaction isolation
• Data is lost when the process terminates
• Ideal for testing and ephemeral workloads

Persistent Mode (file://)

file:///path/to/data

Data persisted to disk:

• WAL (Write-Ahead Logging) for crash recovery
• Immutable cold volumes for fast startup on large tables
• Optional backup snapshots for disaster recovery
• Same MVCC features as memory mode
• Survives process restarts

Examples:

file:///data/mydb
file:///Users/username/stoolap/data
file:///tmp/test_db

Configuration Options

Configuration can be set via query parameters in the connection string:

file:///path/to/data?sync_mode=normal&checkpoint_interval=60

Option	Values	Default	Description
sync / sync_mode	none/off, normal, full (or 0, 1, 2)	normal	WAL synchronization mode. none/off=no fsync (data durable at checkpoint), normal=fsync every 1s, full=fsync every write
checkpoint_interval	Integer (seconds)	60	Time between automatic checkpoint cycles
compact_threshold	Integer (min 2)	4	Sub-target volumes per table before merging
keep_snapshots	Integer	3	Backup snapshots to retain per table
wal_flush_trigger	Integer (bytes)	32768	Size in bytes before WAL flush
wal_buffer_size	Integer (bytes)	65536	WAL write buffer size
wal_max_size	Integer (bytes)	67108864	Maximum WAL file size (64MB)
wal_compression	on/off	on	Enable LZ4 compression for WAL entries
volume_compression	on/off	on	Enable LZ4 compression for cold volume files
compression	on/off	on	Enable LZ4 compression for both WAL and volumes
compression_threshold	Integer (bytes)	64	Minimum size for WAL compression
checkpoint_on_close	on/off	on	Seal all hot rows to volumes on clean shutdown
commit_batch_size	Integer	100	Commits to batch before sync
sync_interval_ms / sync_interval	Integer (ms)	1000	Minimum time between syncs in normal mode
target_volume_rows	Integer	1048576	Target rows per cold volume (min: 65536). Controls compaction split boundary.

Legacy parameter names are accepted for backward compatibility:

• snapshot_interval maps to checkpoint_interval
• snapshot_compression maps to compression (sets both WAL and volume)

Cleanup Options

Option	Values	Default	Description
cleanup	on/off	on	Enable/disable background cleanup thread
cleanup_interval	Integer (seconds)	60	Interval between automatic cleanup runs
deleted_row_retention	Integer (seconds)	300	How long deleted rows are kept before permanent removal
transaction_retention	Integer (seconds)	3600	How long stale transaction metadata is kept

The background cleanup thread periodically removes deleted rows, old version chains, and stale transaction metadata. On WASM where background threads are unavailable, use the VACUUM command for manual cleanup.

Example:

file:///data/mydb?cleanup_interval=30&deleted_row_retention=60
file:///data/mydb?cleanup=off

Sync Mode Details

Mode	Value	Description
none	0	No fsync. Data is durable only after checkpoint writes volumes to disk.
normal	1	Fsync every 1 second (configurable via sync_interval_ms). DDL fsyncs immediately. Comparable to SQLite WAL + synchronous=NORMAL.
full	2	Fsync on every write operation. Maximum durability.

Usage Examples

Rust API

use stoolap::Database;

fn main() -> Result<(), Box<dyn std::error::Error>> {
    // In-memory database
    let db = Database::open("memory://")?;

    // Or create a unique in-memory instance
    let db = Database::open_in_memory()?;

    // Persistent database
    let db = Database::open("file:///data/mydb")?;

    // With configuration options
    let db = Database::open("file:///data/mydb?sync_mode=full&checkpoint_interval=60")?;

    // Execute SQL
    db.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)", ())?;
    db.execute("INSERT INTO users VALUES ($1, $2)", (1, "Alice"))?;

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

    Ok(())
}

Command Line

# In-memory database (default)
stoolap

# Persistent database
stoolap --db "file:///data/mydb"

# With configuration via DSN
stoolap --db "file:///data/mydb?sync_mode=full"

# With configuration via CLI flags
stoolap --db "file:///data/mydb" --sync full --checkpoint-interval 30

# Execute a query directly
stoolap --db "file:///data/mydb" -e "SELECT * FROM users"

# Execute from a SQL file
stoolap --db "file:///data/mydb" -f script.sql

Backup and Restore

# Create a backup snapshot
stoolap --db "file:///data/mydb" --snapshot

# Restore from a specific backup by timestamp (recommended)
stoolap --db "file:///data/mydb" --restore "20260315-100000.000"

# Restore from latest backup
stoolap --db "file:///data/mydb" --restore

# Recovery from corrupted volumes/manifests (cleans up first)
stoolap --db "file:///data/mydb" --reset-volumes --restore

The --restore command opens the database and replaces current data with snapshot data. If volumes or manifests are corrupted, use --reset-volumes --restore which removes bad on-disk state before opening.

PRAGMA Configuration

You can also configure settings after connection using PRAGMA commands:

-- Set configuration values
PRAGMA sync_mode = 2;
PRAGMA checkpoint_interval = 60;
PRAGMA compact_threshold = 4;
PRAGMA keep_snapshots = 5;
PRAGMA wal_flush_trigger = 500;

-- Read current values
PRAGMA sync_mode;
PRAGMA checkpoint_interval;
PRAGMA keep_snapshots;

-- Create a backup snapshot
PRAGMA SNAPSHOT;

-- Restore from a backup snapshot
PRAGMA RESTORE;
PRAGMA RESTORE = '20260315-100000.000';

-- Run checkpoint cycle manually
PRAGMA CHECKPOINT;

-- Manual cleanup (works on all platforms including WASM)
VACUUM;
VACUUM table_name;
PRAGMA vacuum;

See the PRAGMA Commands documentation for details.

Best Practices

1. Development: Use memory:// for fast iteration and testing
2. Production: Use file:// with appropriate sync_mode
3. Critical data: Set sync_mode=full for maximum durability
4. High throughput: Use sync_mode=normal with the default checkpoint cycle
5. Backup: Use PRAGMA SNAPSHOT or --snapshot for backups, keep_snapshots to control retention
6. Recovery: Use --reset-volumes --restore from CLI to recover from corrupted state