Working with the Collection API

Guide March 31, 2026 · 7 min read

Not every application needs SQL. Sometimes you just want to store and retrieve C# objects by key, with optional indexing for fast lookups. That's what the CSharpDB Collection API is built for.

Collections give you a document-oriented interface on top of the same B+tree storage engine that powers CSharpDB's SQL tables — so you get crash recovery, MVCC isolation, and WAL durability without writing a single SQL statement.

Defining Your Model

Collections work with any serializable C# type. Records work particularly well:

public record Customer(
    string Name,
    string Email,
    Address Address,
    string[] Tags
);

public record Address(
    string Street,
    string City,
    string Country
);

CRUD Operations

Every collection is a key-value store where keys are strings and values are your typed objects:

using CSharpDB.Engine;

await using var db = await Database.OpenAsync("shop.db");
var customers = await db.GetCollectionAsync<Customer>("customers");

// Create
var alice = new Customer(
    "Alice", "alice@example.com",
    new Address("123 Main St", "Portland", "US"),
    ["premium", "early-adopter"]
);
await customers.PutAsync("alice", alice);

// Read
var customer = await customers.GetAsync("alice");
Console.WriteLine(customer?.Name); // "Alice"

// Update — just put again with the same key
var updated = alice with { Email = "alice@newdomain.com" };
await customers.PutAsync("alice", updated);

// Delete
await customers.DeleteAsync("alice");

Indexing Properties

Without indexes, the only way to find documents is by key or by scanning. Indexes let you query by any property:

// Index a top-level property
await customers.EnsureIndexAsync(c => c.Email);

// Index a nested property
await customers.EnsureIndexAsync(c => c.Address.City);

// Query by indexed property
await foreach (var match in customers.FindByIndexAsync(
    c => c.Address.City, "Portland"))
{
    Console.WriteLine($"{match.Key}: {match.Value.Name}");
}

Indexes are persisted in the database file. EnsureIndexAsync is idempotent — calling it multiple times has no effect if the index already exists.

Scanning and Filtering

For ad-hoc queries or when you don't have an index, use ScanAsync with LINQ:

// Scan all entries
await foreach (var entry in customers.ScanAsync())
    Console.WriteLine($"{entry.Key}: {entry.Value.Name}");

// Count entries
var count = await customers.CountAsync();
Console.WriteLine($"Total customers: {count}");

Mixing SQL and Collections

Collections and SQL tables coexist in the same database file. You can use both APIs in the same application — SQL for relational data and collections for document-oriented data:

await using var db = await Database.OpenAsync("myapp.db");

// SQL for relational data
await db.ExecuteAsync(@"
    CREATE TABLE IF NOT EXISTS Orders (
        Id INTEGER PRIMARY KEY,
        CustomerId TEXT NOT NULL,
        Total REAL
    )");

// Collections for document data
var profiles = await db.GetCollectionAsync<Customer>("profiles");
await profiles.PutAsync("alice", alice);

Best Practices

  • Use meaningful keys. Keys are your primary access path. Natural keys (email, slug, SKU) are often better than GUIDs.
  • Index properties you query by. Without an index, lookups require a full scan.
  • Keep documents reasonably sized. Collections are backed by B+tree pages. Very large documents may span multiple overflow pages.
  • Use records for immutability. The with expression pattern makes updates clean and predictable.
← Getting Started Next: SQL Deep Dive →