Database Modes

CSharpDB supports three storage modes — file-based, in-memory, and hybrid — each tuned for different workloads. All three share the same SQL engine, Collection API, and transaction model.

File-Based (Default)

The standard mode for production workloads. Uses FileStorageDevice for true async I/O via RandomAccess.ReadAsync / RandomAccess.WriteAsync. All committed data is durable on disk.

// Simple — uses default options
await using var db = await Database.OpenAsync("myapp.db");

// With custom options
var options = new DatabaseOptions
{
    StorageEngineOptions = new StorageEngineOptionsBuilder()
        .WithCacheSize(4096)
        .WithLockTimeout(TimeSpan.FromSeconds(30))
        .WithCheckpointPolicy(new FrameCountCheckpointPolicy(1000))
        .Build()
};
await using var db = await Database.OpenAsync("myapp.db", options);

How It Works

• ● Storage: Single .db file with 4 KB slotted pages, plus a .db.wal file for the Write-Ahead Log
• ● Page Cache: Unbounded DictionaryPageCache by default, or bounded LruPageCache when MaxCachedPages is set
• ● WAL: Full WAL with frame-level checksums. Pages written to WAL before commit, then merged to the main file during checkpoint
• ● Durability: Every committed transaction is immediately durable via FlushToDisk()
• ● Recovery: On open, the WAL is scanned to recover any committed frames not yet checkpointed
• ● Checkpoint: Configurable policies — frame count, time interval, WAL size, or composite. Pages are evicted from cache after checkpoint by default

Configuration Options

In-Memory

The entire database lives in memory with no disk I/O. Uses MemoryStorageDevice — a thread-safe in-memory byte buffer. Fastest option but data is lost when the process exits unless explicitly saved.

// Create a fresh in-memory database
await using var db = await Database.OpenInMemoryAsync();

// Load an existing file into memory
await using var db = await Database.LoadIntoMemoryAsync("myapp.db");

// Save committed state to disk at any time
await db.SaveToFileAsync("snapshot.db");

How It Works

• ● Storage: MemoryStorageDevice — an in-memory byte[] buffer with thread-safe access
• ● Page Cache: DictionaryPageCache (unbounded, no eviction). All pages remain resident
• ● WAL: Full WAL implementation (MemoryWriteAheadLog) using the same frame format, stored in memory. Ensures transaction atomicity
• ○ Durability: Not persistent by default — data lost on process exit
• ● Snapshot Save: SaveToFileAsync(path) writes the complete database image to disk
• ● Load from Disk: LoadIntoMemoryAsync(path) reads a file (and its WAL) into memory

Shared In-Memory (ADO.NET)

Multiple ADO.NET connections can share a single in-memory database instance. Useful for test scenarios where several connections need to see the same data.

// Multiple connections share the same in-memory instance
var connStr = "Data Source=:memory:testdb";

await using var conn1 = new CSharpDbConnection(connStr);
await using var conn2 = new CSharpDbConnection(connStr);
await conn1.OpenAsync();
await conn2.OpenAsync();

// Both connections see the same tables and data

Shared instances are managed by SharedMemoryDatabaseHost and serialized via a semaphore to ensure consistency.

Hybrid (Lazy-Resident)

Combines the durability of file-based storage with the performance of in-memory caching. Uses FileStorageDevice for durable backing, but keeps touched pages resident in cache across checkpoints — unlike the default mode which evicts pages after checkpoint.

// Simple — uses default hybrid options
await using var defaultHybrid = await Database.OpenHybridAsync("myapp.db");

// With custom options — preload hot tables and collections
var hybridOptions = new HybridDatabaseOptions
{
    PersistenceMode = HybridPersistenceMode.IncrementalDurable,
    HotTableNames = ["Users", "Sessions"],
    HotCollectionNames = ["Settings"],
};
await using var tunedHybrid = await Database.OpenHybridAsync(
    "myapp.db",
    new DatabaseOptions(),
    hybridOptions);

How It Works

• ● Storage: Same FileStorageDevice as default mode — backing .db file on disk with a .db.wal
• ● Page Cache: DictionaryPageCache with PreserveOwnedPagesOnCheckpoint = true — pages stay in cache after checkpoint
• ● WAL: Full WAL implementation, same as file-based mode
• ● Durability: Committed state persisted to disk incrementally via checkpoints
• ● Hot Set Preloading: Specify tables and collections to load into cache at open via HotTableNames / HotCollectionNames
• ● Lazy Residency: Cold pages load from disk on first access, then stay resident. Over time the working set migrates into memory

Persistence Modes

Persistence Triggers

Control when committed state is persisted to the backing file. Flags can be combined:

Comparison

Choosing a Mode

Use File-Based when:

• You need crash durability — every committed transaction survives a power failure
• Your dataset is larger than available memory
• You're deploying to production and need standard database guarantees

Use In-Memory when:

• You're running unit or integration tests and want isolation and speed
• The data is ephemeral — caches, scratch computations, temporary state
• You need a shared in-memory database across multiple ADO.NET connections
• You want to load a file into memory for fast read-only processing, then discard

Use Hybrid when:

• You have a clear hot/cold split — frequently accessed data should be in memory, but you need the full dataset on disk
• You want near-in-memory read performance for your working set with durable backing
• You can identify hot tables or collections to preload at startup
• You want to control exactly when persistence happens (on commit, checkpoint, or dispose)