Evaluated Applications

HoloDb docs

HoloDb docs

The practical guide: put data in, get answers out. Every program on this page was compiled and run against the published package, and the output shown is what it printed.

Install and first query

Add the package from NuGet with dotnet add package EvaluatedApplications.HoloDb. It targets .NET 8 and later, and ships the compiled library only. HoloDbService is the wrapped, async-friendly entry point, and Execute runs SQL synchronously. ExecuteAsync offloads the work to the thread pool so a request thread is not blocked.

C#
using HoloDb;

var db = new HoloDbService(new HoloDbOptions());           // in memory
db.Execute("CREATE TABLE users (id INT PRIMARY KEY, age INT, name TEXT)");
db.Execute("INSERT INTO users VALUES (1, 30, 'Ada'), (2, 24, 'Linus'), (3, 15, 'Kid')");

var result = db.Execute("SELECT name FROM users WHERE age >= 18 ORDER BY age DESC");
foreach (var row in result.Rows)
    Console.WriteLine(row["name"]);

var count = await db.ExecuteAsync("SELECT COUNT(*) FROM users");
Console.WriteLine(count.Rows[0]["count(*)"]);
Output
Ada
Linus
3

Tables and types

There are three storage types, each with the usual SQL aliases.

INT
A .NET long. Aliases: INTEGER.
REAL
A .NET double. Aliases: FLOAT, DOUBLE, DECIMAL, NUMERIC. DECIMAL and NUMERIC map to a 64-bit float, so they are not exact decimals.
TEXT
A .NET string. Aliases: VARCHAR, STRING.

One column may be PRIMARY KEY, and it is enforced unique. For money, store integer minor units, cents as an INT: it is exact, and it lets the constant-time integer aggregates serve your revenue totals.

SQL
CREATE TABLE sales (
  id     INT PRIMARY KEY,
  region TEXT,
  amount REAL
)

Inserting, updating and bulk loading

Strings use single quotes, and a quote is escaped by doubling it. Rows go in singly or in batches, with or without a column list.

Every Execute also takes positional ? placeholders. The arguments are quoted and escaped for you, before the statement is parsed. A null argument, a wrong argument count or an unsupported type throws.

SQL
INSERT INTO sales VALUES (1, 'EU', 42.50)
SQL
INSERT INTO sales (id, region, amount) VALUES (2, 'US', 17.00), (3, 'EU', 61.10)
SQL
UPDATE sales SET amount = 0 WHERE region = 'US'
SQL
DELETE FROM sales WHERE amount = 0

Parameterized queries

Use ? placeholders instead of building SQL text yourself.

C#
using HoloDb;

var db = new HoloDbService(new HoloDbOptions());
db.Execute("CREATE TABLE people (id INT PRIMARY KEY, name TEXT, age INT)");
db.Execute("INSERT INTO people VALUES (?, ?, ?)", 1, "O'Neil", 41);   // quoted and escaped for you
db.Execute("INSERT INTO people VALUES (?, ?, ?)", 2, "Ada", 36);

var r = db.Execute("SELECT name FROM people WHERE age > ? ORDER BY name", 30);
foreach (var row in r.Rows) Console.WriteLine(row["name"]);
Output
Ada
O'Neil

Bulk loading

For large loads, skip the SQL parser and hand the engine one typed array per column (long[], double[] or string[]). It is atomic and validates primary-key uniqueness before committing. Load in chunks for very large data sets, as the Analyst tool does with a large paste.

C#
using HoloDb;

var db = new HoloDbService(new HoloDbOptions());
db.Execute("CREATE TABLE sales (id INT PRIMARY KEY, region TEXT, amount REAL)");

// Typed column arrays skip the SQL parser: the fast path for large loads.
var ids = new long[] { 1, 2, 3 };
var regions = new string[] { "EU", "US", "EU" };
var amounts = new double[] { 42.50, 17.00, 61.10 };
db.BulkLoad("sales", new[] { "id", "region", "amount" }, new Array[] { ids, regions, amounts }, count: 3);

var r = db.Execute("SELECT region, COUNT(*), SUM(amount) FROM sales GROUP BY region ORDER BY region");
foreach (var row in r.Rows) Console.WriteLine($"{row["region"]}: {row["count(*)"]} rows, {row["sum(amount)"]}");
Output
EU: 2 rows, 103.6
US: 1 rows, 17

Querying: the dialect

A SELECT supports aggregates (COUNT, SUM, AVG, MIN, MAX), a full boolean WHERE (AND, OR, NOT, parentheses, the comparisons = != < > <= >=, LIKE, IN, IN (SELECT ...) and BETWEEN ... AND ...), GROUP BY with HAVING, ORDER BY with several keys, LIMIT, DISTINCT and INNER JOIN.

Qualify joined columns with the table name, as above. Table aliases are not supported.

SQL
SELECT region, COUNT(*), SUM(amount), AVG(amount)
FROM sales
WHERE amount BETWEEN 10 AND 1000
GROUP BY region
HAVING SUM(amount) > 50
ORDER BY sum(amount) DESC
LIMIT 10
A join across two tables
SELECT orders.id, customers.name, orders.total
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id
WHERE orders.total > 100
ORDER BY orders.total DESC

Reading results

Execute returns a QueryResult. The simplest way to read it is row by row as dictionaries keyed by column name, through Rows. Aggregate columns are named by the function applied, in lower case: count(*), sum(amount), avg(amount), min(amount) and max(amount). Columns lists the result's column names in order, and for a write statement Affected is the number of rows changed.

For hot paths QueryResult keeps results columnar and exposes direct accessors (GetLong, GetDouble, GetText and GetValue by row and column), so you can read values without materialising a dictionary per row.

Three ways to ask one store

Exact lookup by key, similarity by content and constant-time aggregates all read the same rows. The program below shows each, and the usage meter shows the aggregate scanned no rows.

C#
using HoloDb;

var db = Database.Open();   // null path: in memory
db.Execute("CREATE TABLE invoices (id INT PRIMARY KEY, customer TEXT, region TEXT, amount_cents INT)");
db.Execute("INSERT INTO invoices VALUES (1,'Acme','north',1200),(2,'Beta','south',4500),(3,'Cyan','north',1150),(4,'Dune','east',30000),(5,'Echo','north',1300)");

// 1. By key: ordinary SQL, answered from the primary-key index
Console.WriteLine(db.Execute("SELECT customer FROM invoices WHERE id = 3").Rows[0]["customer"]);

// 2. By content: rank rows by resemblance to a partial description; add score to see the ranking
foreach (var r in db.Execute("SELECT id, region, amount_cents FROM invoices NEAREST (region = 'north', amount_cents = 1200) LIMIT 3").Rows)
    Console.WriteLine($"{r["id"]}: {r["region"]} {r["amount_cents"]}");

// 3. By aggregate: read from maintained totals, no scan
foreach (var r in db.Execute("SELECT region, SUM(amount_cents) FROM invoices GROUP BY region ORDER BY region").Rows)
    Console.WriteLine($"{r["region"]}: {r["sum(amount_cents)"]}");
Console.WriteLine($"rows scanned by the aggregate: {Database.LastRowsScanned}");
Output
Cyan
1: north 1200
5: north 1300
3: north 1150
east: 30000
north: 3650
south: 4500
rows scanned by the aggregate: 0

See the three ways as a diagram

Similarity search

Because rows are stored holographically, HoloDb ranks rows by similarity to a partial description, with no separate vector index. Use the NEAREST clause with the fields you know and a LIMIT for how many neighbours you want. Add score to the projection to read the ranking metric.

Similarity is graded: a close number or date, or a near-identical string, ranks ahead of a far or unrelated one. It is an exact scan below 8,000 rows and whenever the query has a WHERE, and above that an approximate sublinear index (locality-sensitive hashing) ranks candidates, so the result can differ from an exact scan. With a WHERE the filter runs first and the survivors are ranked. NEAREST does not combine with GROUP BY, and an UPDATE, DELETE or ALTER TABLE on the table forces the next NEAREST to rebuild its index.

SQL
SELECT id, title
FROM articles
NEAREST (title = 'holographic database')
LIMIT 5

Transactions

ACID transactions with explicit control: wrap statements in BEGIN and COMMIT, or discard them with ROLLBACK. BEGIN costs the same at any table size, because rows are journaled as they change instead of the table being copied, and ROLLBACK replays the journal. With a WalPath set, a committed transaction is durable.

C#
using HoloDb;

var db = new HoloDbService(new HoloDbOptions());
db.Execute("CREATE TABLE accounts (id INT PRIMARY KEY, balance INT)");
db.Execute("INSERT INTO accounts VALUES (1, 500), (2, 100)");

db.Execute("BEGIN");
try
{
    db.Execute("UPDATE accounts SET balance = 400 WHERE id = 1");
    db.Execute("UPDATE accounts SET balance = 200 WHERE id = 2");
    db.Execute("COMMIT");
}
catch
{
    db.Execute("ROLLBACK");
    throw;
}
Console.WriteLine("after commit: " + string.Join(", ", db.Execute("SELECT balance FROM accounts ORDER BY id").Rows.Select(r => r["balance"])));

db.Execute("BEGIN");
db.Execute("UPDATE accounts SET balance = 0 WHERE id = 1");
db.Execute("ROLLBACK");
Console.WriteLine("after rollback: " + string.Join(", ", db.Execute("SELECT balance FROM accounts ORDER BY id").Rows.Select(r => r["balance"])));
Output
after commit: 400, 200
after rollback: 400, 200

In memory or durable

By default the database lives in memory and is gone when the process ends. To make writes durable, give it a write-ahead log path. Durable state is a compact snapshot of the current data plus a short log of the writes since the last checkpoint. A durable commit flushes to disk before it returns. Recovery is bounded by how much data you hold, not by how long the database has been running: reopening loads a snapshot and replays only the recent tail.

The log is folded into a fresh snapshot automatically once it passes a threshold (CheckpointThresholdBytes, 8 MB by default), and you can force one with Checkpoint, for example before a clean shutdown. In WebAssembly there is no file system, so leave WalPath null and use the in-memory engine. To work with the engine directly, without the async wrapper, call Database.Open with a path, or with null for in memory.

C#
using HoloDb;

var wal = Path.Combine(Path.GetTempPath(), "holodb-docs-" + Guid.NewGuid().ToString("N") + ".wal");
try
{
    var db = new HoloDbService(new HoloDbOptions { WalPath = wal });   // writes are durable
    db.Execute("CREATE TABLE notes (id INT PRIMARY KEY, body TEXT)");
    db.Execute("INSERT INTO notes VALUES (1, 'first'), (2, 'second')");
    db.Checkpoint();                                                    // fold the log into a snapshot
    db.Execute("INSERT INTO notes VALUES (3, 'after the checkpoint')");
    db.Database.Close();

    var reopened = new HoloDbService(new HoloDbOptions { WalPath = wal });   // snapshot plus the short tail
    Console.WriteLine(reopened.Execute("SELECT COUNT(*) FROM notes").Rows[0]["count(*)"]);
    reopened.Database.Close();
}
finally
{
    foreach (var f in Directory.GetFiles(Path.GetTempPath(), Path.GetFileName(wal) + "*")) File.Delete(f);
}
Output
3

Larger than memory: paged tables

The default engine keeps everything in RAM. When a table needs to outgrow memory, create it as a paged table. It lives on an 8 KB buffer-pool page store with a redo-only write-ahead log, so cold pages spill to disk, and the constant-time aggregates stay maintained on disk. Once created, a paged table is driven through the same Execute call as an in-memory table.

The buffer-pool size (bufferFrames, 8,192 frames of 8 KB by default) bounds how much stays resident. The one structure that scales with row count is the primary-key index, which stays in RAM at about 35 bytes per row, measured at 39 and 34 bytes per entry for 1,000,000 and 10,000,000 entries. That index, not the buffer pool, bounds how far a single paged table can grow past memory.

C#
using HoloDb;

var dir = Path.Combine(Path.GetTempPath(), "holodb-paged-" + Guid.NewGuid().ToString("N"));
try
{
    var db = new HoloDbService(new HoloDbOptions());
    // a durable, larger-than-memory table backed by a directory
    db.CreatePaged("CREATE TABLE events (id INT PRIMARY KEY, kind TEXT, value INT)", dir);
    var rows = Enumerable.Range(1, 1000).Select(i => (object[])new object[] { (long)i, i % 2 == 0 ? "even" : "odd", (long)i });
    db.PagedBulkLoad("events", rows);
    foreach (var r in db.Execute("SELECT kind, COUNT(*), SUM(value) FROM events GROUP BY kind ORDER BY kind").Rows)
        Console.WriteLine($"{r["kind"]}: {r["count(*)"]} rows, sum {r["sum(value)"]}");
    db.Database.ClosePaged("events");

    var again = new HoloDbService(new HoloDbOptions());
    again.OpenPaged(dir);                                               // reopen it later
    Console.WriteLine(again.Execute("SELECT COUNT(*) FROM events").Rows[0]["count(*)"]);
    again.Database.ClosePaged("events");
}
finally { if (Directory.Exists(dir)) Directory.Delete(dir, true); }
Output
even: 500 rows, sum 250500
odd: 500 rows, sum 250000
1000

Indexes, and why aggregates are instant

A sorted index speeds range and lookup queries. You rarely need one for counting: COUNT and SUM over integer columns are served from maintained accumulators in constant time, independent of how many rows the table holds, so the engine does not scan to count. An index needs an INT or TEXT column.

CREATE INDEX also builds the optional aggregate plane, and SHOW INDEXES and EXPLAIN report what exists and what answers a query. Those are described with the plane.

SQL
CREATE INDEX ON sales (region)

The plane, index SQL and EXPLAIN

Changing a table

ALTER TABLE supports ADD COLUMN with a mandatory DEFAULT, which backfills the rows that exist. There is no DROP, rename or type change.

SQL
ALTER TABLE sales ADD COLUMN note TEXT DEFAULT ''

Dependency injection and EvalApp

Register HoloDb in a .NET host and inject HoloDbService wherever you need it. HoloDb also runs inside an EvalApp harness: WithHoloDb attaches a service to a pipeline, so heavy queries execute as compiled, resource-gated, self-tuning pipeline steps and any step can reach the service.

C#
using HoloDb;
using Microsoft.Extensions.DependencyInjection;

var services = new ServiceCollection();
services.AddHoloDb(options => { options.WalPath = null; });   // singleton HoloDbService

using var provider = services.BuildServiceProvider();
var db = provider.GetRequiredService<HoloDbService>();
db.Execute("CREATE TABLE sales (id INT PRIMARY KEY, region TEXT, amount INT)");
db.Execute("INSERT INTO sales VALUES (1, 'EU', 10), (2, 'US', 5), (3, 'EU', 7)");
var totals = await db.ExecuteAsync("SELECT region, SUM(amount) FROM sales GROUP BY region ORDER BY region");
foreach (var r in totals.Rows) Console.WriteLine($"{r["region"]}: {r["sum(amount)"]}");
Output
EU: 17
US: 5

The usage meter

After each query, static per-thread readings report what the engine actually processed: LastRowsScanned, LastTableRows, LastRowBytes and LastLogicalBytes. An aggregate served from a maintained total reports zero rows scanned, which is the figure a metered API bills on.

In the browser, and over the network

HoloDb is plain managed .NET with no native dependencies, so it compiles to WebAssembly and runs client-side with no server and nothing leaving the device. The Analyst tool is exactly that: a real HoloDb in your browser tab, profiling and querying whatever you paste in.

The same engine runs as a server, and a typed client talks to it over TLS with the same surface as the in-process engine.

The client and the wire protocol