Fisher on SQLite, Part 1: The document store

· 17 min read · fisher sqlite dotnet critter-stack document-database marten
Fisher on SQLite, Part 1: The document store

Series: Fisher on SQLite

  1. 1 Fisher on SQLite, Part 1: The document store
  2. 2 Fisher on SQLite, Part 2: The event store
  3. 3 Fisher on SQLite, Part 3: Backing it up to S3
Next →

Fisher is Marten and Polecat on SQLite: the same session API, LINQ provider, projection model and event store, give or take the divergences this post names as it hits them. What is different is not the dialect. There is no database server. The engine runs inside your process and the store is a file.

SQLite reads as the small option, the thing you settle for. For a desktop app, a CLI, an on-premises install, an edge box, a single-node service or an integration test suite, that has it backwards: the database being a separate machine is the cost, not the benefit.

This is Part 1 of three, on the document side. Part 2 is the event store. Part 3 is how you get that file into S3 without losing committed writes.

The running example is a till. A small chain of coffee shops, each counter with a terminal that has to keep selling when the broadband drops, and head office wanting the day’s numbers when it comes back. Nobody is putting a database server in a shop. Every output block below comes from a sample project run against Fisher 1.0.4 on .NET 10.

Getting started

dotnet add package Fisher

That is the whole installation. Fisher uses Microsoft.Data.Sqlite, which ships the engine itself, so there is nothing to run alongside your application.

builder.Services.AddFisher(opts =>
{
    opts.Connection("Data Source=/var/lib/till/till.db");

    opts.Schema.For<StockMovement>()
        .Index(x => x.SkuCode)
        .Index(x => x.Kind);
})
.ApplyAllDatabaseChangesOnStartup();

AddFisher registers IDocumentStore as a singleton and IDocumentSession / IQuerySession as scoped, so handlers inject a session directly. Every method on Schema.For<T>() returns the mapping expression, so a type’s configuration chains.

You do not need a host at all:

await using var store = DocumentStore.For("Data Source=till.db");

Any Microsoft.Data.Sqlite connection string works. Data Source=:memory: gives a private in-memory database, Data Source=app;Mode=Memory;Cache=Shared a shared one. An in-memory database lives only while something holds a connection open, which is one reason application code should never construct its own SqliteConnection: Fisher’s data source is what keeps it alive and what applies the PRAGMA settings.

Documents and identity

A document is a plain class. Fisher finds the identity by convention, a public member named Id or id, or by [Identity]:

public class StockMovement
{
    public Guid Id { get; set; }
    public required string SkuCode { get; set; }
    public required string TerminalId { get; set; }
    public DateTimeOffset At { get; set; }
    public MovementKind Kind { get; set; }        // Sale, Refund, Delivery, Shrinkage
    public int Quantity { get; set; }
    public decimal Value { get; set; }
    public string ReceiptNo { get; set; } = "";
    public int Corrections { get; set; }
    public List<string> Notes { get; set; } = [];
}

public class Sku
{
    public string Id { get; set; } = "";          // you assign it: "ESP-250"
    public required string Name { get; set; }
    public required string Category { get; set; }
    public decimal UnitPrice { get; set; }
}

public class ShiftReport
{
    public int Id { get; set; }                   // assigned from a Hi-Lo sequence
    public required string Terminal { get; set; }
    public DateTimeOffset Opened { get; set; }
}

Four id types are supported directly, plus strong-typed wrappers around any of them. Storing one of each shows who assigns what:

[id] Guid assigned on Store: 01a0495e-52c3-7d80-aa52-c0f6fd68b0f7
[id] version 7? 7 (the version nibble)
[id] string id kept as given: GFT-BOX
[id] int from hi-lo: 1
[id] fi_hilo row: ShiftReport hi=0

The Guid is version 7, so ids sort roughly in creation order and inserts land near the right of the b-tree instead of scattering. Numeric ids come from a Hi-Lo sequence in fi_hilo, and advancing the high value is one atomic statement: insert … on conflict … do update set hi_value = hi_value + 1 returning hi_value. Marten needs a stored function for that, Polecat a guarded read-then-update with a retry loop.

A natural key like a SKU code is worth taking as a string id rather than carrying a surrogate Guid beside it. The id is assigned inside session.Store(document), before any commit, so you can use it immediately.

What lands on disk

Each document type gets a table prefixed fi_doc_:

CREATE TABLE IF NOT EXISTS fi_doc_stockmovement (
    id               TEXT    PRIMARY KEY,
    data             TEXT    NOT NULL,
    dotnet_type      TEXT    ,
    last_modified    TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
);

CREATE INDEX IF NOT EXISTS idx_fi_doc_stockmovement_sku_code
    ON fi_doc_stockmovement (json_extract(data, '$.skuCode'));

Note the naming: the table is the type name lowercased, while columns and index suffixes are snake case.

data is TEXT holding exactly what System.Text.Json wrote: no whitespace normalisation, no key reordering, no encoding decision. jsonb cannot promise that and neither can nvarchar, which is what makes Fisher’s raw JSON reads a byte copy rather than a re-serialization.

Other columns appear when a feature asks for them: tenant_id under conjoined tenancy, guid_version or revision for concurrency, is_deleted and deleted_at for soft deletes, one generated column per duplicated field.

Sessions and the unit of work

A session is a connection plus a unit of work. Three factories, differing in what the session remembers:

await using var session = store.LightweightSession();     // no tracking, the default
await using var session = store.IdentitySession();        // identity map
await using var session = store.DirtyTrackedSession();    // identity map + change detection
await using var query   = store.QuerySession();           // reads

Under dirty tracking, a document you loaded and mutated is written at commit with no Store call:

await using var session = store.DirtyTrackedSession();

var movement = await session.Query<StockMovement>().Where(x => x.SkuCode == "ESP-250").FirstAsync();
movement.Kind = MovementKind.Refund;

await session.SaveChangesAsync();   // no Store call
[tracking] dirty tracked write without Store: Refund

The identity map covers Query<T>() and not only LoadAsync, so a document read twice comes back as the same instance. It also refuses a second instance under an id it already maps, which is the whole point of it: two instances of one document, both stored, is a lost update wearing a last-write-wins disguise.

[tracking] identity map returns same instance: True
[tracking] second instance under the map: InvalidOperationException

One divergence if you are porting code: SessionOptions.Tracking defaults to None, where Marten’s OpenSession(SessionOptions) defaults to its identity map. Marten’s default predates its own LightweightSession(), and copying it here would hand every existing caller a map they did not ask for, throw included.

Querying

The LINQ provider translates to json_extract expressions, and the surface is the one you know: Where, ordering, projections, Any/All, grouping, aggregates, both paging styles, joins.

var bySku = await session.Query<StockMovement>()
    .GroupBy(x => x.SkuCode)
    .Select(g => new { Sku = g.Key, Movements = g.Count() })
    .ToListAsync();
[linq] grouped: CHM-06=60, ESP-250=61, FLT-500=60, MUG-STD=60, OAT-1L=60

Joins are the tier where SQLite is the easiest of the three dialects rather than the hardest. A join between two document tables is plain SQL over a json_extract locator: no OPENJSON, no lateral join, and an expression index usable on either side.

var refunds = await session.Query<StockMovement>()
    .Where(x => x.Kind == MovementKind.Refund)
    .Join(session.Query<Sku>(),
        movement => movement.SkuCode,
        sku => sku.Id,
        (movement, sku) => new { sku.Name, movement.TerminalId, movement.Value })
    .Take(3)
    .ToListAsync();
[linq] joined rows: till-01 Espresso beans 250g 17.00 | till-01 Espresso beans 250g 8.50 | till-02 Stoneware mug 24.00

Query syntax works too, and for a multi-table join it reads better. One thing to know before you reach for a left join: everything about the inner side belongs in the ON clause, while a post-join Where lands in the WHERE. On an inner join the two are equivalent, on a left join they are visibly not.

Joins are worth more here than on either sibling. The usual argument against joining in a document store is that a second round trip is cheap next to a join. There is no round trip to be cheap; the alternative is two statements and a client-side stitch.

That also explains the one Marten query feature that is missing. There is no Include<T>, and porting code that uses it fails at compile time rather than at runtime:

error CS1061: 'IQueryable<StockMovement>' does not contain a definition for 'Include'

Include earns its place on Marten by folding a related-document load into one command instead of a second network round trip. Here there is no round trip to fold, so the join above, or simply a second query, costs what it costs. A compile error is also the right failure: Fisher’s LINQ provider refuses what it cannot translate instead of quietly pulling the table into memory to satisfy it.

Paging comes in two flavours, offset and keyset:

var page = await session.Query<StockMovement>()
    .OrderByDescending(x => x.At)
    .ToPagedListAsync(pageNumber: 2, pageSize: 25);
[linq] page 2 of 13: 25 rows, total 301, next? True
[linq] CountAsync over a Take(25): 25

That second line is deliberate. page.TotalItemCount discards Take/Skip and counts the whole result set; CountAsync counts what the query asks for. Both are right for their caller. The total is a second statement rather than count(*) over (), because a window function returns no row when the page is past the end, which is exactly when a pager needs the real total.

String matching is where I would slow down. Contains, StartsWith and EndsWith are ordinal and case-sensitive, matching what .NET’s own string.Contains(string) means. With SKU codes that is easy to trip over:

[linq] StartsWith is ordinal: 'ESP'=61, 'esp'=0

Fisher implements them with instr and substr rather than LIKE. SQLite’s LIKE is case-insensitive for ASCII while = is case-sensitive, so a LIKE-based Contains("esp") would match "ESP-250" on data where == "esp-250" does not: two operators in one Where clause disagreeing about what a string is. There is no StringComparison overload, so normalise on write if a cashier can type the code by hand.

When a document is going straight into an HTTP response, skip the object:

string json = await session.Query<StockMovement>()
    .Where(x => x.SkuCode == "OAT-1L")
    .Take(1)
    .ToJsonArrayAsync();
[json] raw json read: [{"id":"01a0495e-c30c-7c63-ac8b-ae483748dcd7","skuCode":"OAT-1L","terminalId":"till-02","at":"2026-08-28...

On a server-based store that saves a deserialize-then-reserialize pass, a fraction of the cost. Here the round trip is the cost, so the endpoint goes from “parse JSON, build an object, serialize an object” to “copy bytes”. Fisher.AspNetCore wraps this as streaming IResult types (StreamOne, StreamMany, StreamPaged, StreamPagedByCursor) with ETag handling.

Indexing, and the generated column

Two ways to make a predicate fast, and the difference matters:

opts.Schema.For<StockMovement>()
    .Duplicate(x => x.SkuCode)     // a generated column, and an index on it
    .Index(x => x.At)              // an expression index, no column at all
    .UniqueIndex(x => x.ReceiptNo);
[idxchain] idx_fi_doc_stockmovement_receipt_no unique=1
[idxchain] idx_fi_doc_stockmovement_at unique=0
[idxchain] idx_fi_doc_stockmovement_sku_code unique=0

Index creates a SQLite indexed expression over the member’s json_extract locator, so the member is indexed where it lives and nothing is materialised. Marten needs a computed index for this, Polecat a JSON_VALUE index.

Duplicate lifts the member into a real column, and here is the divergence from both siblings: the column is VIRTUAL and generated, not written.

[dup] before: CREATE TABLE fi_doc_stockmovement ( id TEXT PRIMARY KEY, data TEXT NOT NULL, ... )
[dup] rows matching through the new column, with no backfill: 250
[dup] after:  CREATE TABLE fi_doc_stockmovement ( ..., sku_code TEXT GENERATED ALWAYS AS (json_extract(data, '$.skuCode')) VIRTUAL)
[dup] raw sql against the generated column: 250

That run seeded 500 movements into a table with no duplicated field, then reopened the same file with Duplicate configured. The column appeared by migration and every existing row was correct immediately. Nothing writes it, so there is nothing to backfill and nothing that can drift from data. It costs index space rather than row space, and the write path is untouched. On Marten or Polecat the same change is a column, a backfill, and an extra binder on every upsert.

Foreign keys between document tables are real and enforced, cascade included:

opts.Schema.For<StockMovement>().ForeignKey<Sku>(x => x.SkuCode);
[fk] movement against a known SKU: stored
[fk] movement against an unknown SKU: SqliteException: SQLite Error 19: 'FOREIGN KEY constraint failed'

The SKU and the movement referencing it went in on the same SaveChangesAsync and the constraint held, because the unit of work ordered the parent write ahead of the child. Enforcement is per-connection in SQLite and off by default in the library, but on for every connection Fisher opens.

Patching

Change part of a document without loading it:

session.Patch<StockMovement>(id)
    .Set(x => x.Kind, MovementKind.Shrinkage)
    .Increment(x => x.Corrections)
    .Append(x => x.Notes, "reclassified by the duty manager");

await session.SaveChangesAsync();
[patch] Shrinkage, corrections=1, notes=[reclassified by the duty manager]

This is the strongest case for SQLite in the library. json1 is built into the engine, where Marten needs a PL/pgSQL patch function installed server-side, and the operations compose: each is a json1 function inside a single update … set data = …, so a chain nests into one statement. You can patch by criteria as well as by id, which is how an overnight job reprices a whole category without loading it.

The duplicated field decision pays off again: a patch has nothing to refresh, because the generated column recomputes from data on read. Both siblings update their duplicated columns inside the patch SQL.

One caveat the docs are blunt about: json_set re-renders the document, so a patched row is no longer byte-identical to what the serializer would have written, and a new key lands at the end. A patch avoids the deserialize / mutate / serialize round trip. It does not avoid the row rewrite.

Optimistic concurrency

Two styles, and they are alternatives rather than a stack. A Guid version guards that the stored version equals the one you loaded; a numeric revision guards that yours is strictly greater. Declaring both is refused at configuration time.

opts.Schema.For<Sku>().UseOptimisticConcurrency();

Or implement IVersioned, which turns it on by itself:

public class Sku : IVersioned
{
    public string Id { get; set; } = "";
    public Guid Version { get; set; }
    // …
}

Two sessions load the same SKU, both reprice it, and the loser finds out at commit:

[concurrency] stale price update rejected with ConcurrencyException

The upsert carries a where on the version and ends RETURNING id. A guard that does not match returns no row and leaves the document untouched, which the operation reads as a concurrency failure.

Multi-tenancy

Conjoined tenancy adds a tenant_id column, per type or by policy:

opts.Policies.AllDocumentsAreMultiTenanted();

await using var session = store.LightweightSession("riverside");

Because there is one writer per file, a single SaveChangesAsync can span tenants:

session.Store(new StockMovement { SkuCode = "ESP-250", TerminalId = "till-01", Quantity = 1 });
session.ForTenant("market-street").Store(new StockMovement
{
    SkuCode = "OAT-1L", TerminalId = "till-04", Quantity = 6
});

await session.SaveChangesAsync();   // one transaction, two shops
[tenancy] riverside sees: ESP-250
[tenancy] market-street sees: OAT-1L

A database-per-tenant store would need a distributed transaction to match that. The other style, one file per shop or per till, is where Part 3 picks up.

Running it in production

Four decisions worth making deliberately rather than inheriting.

Durability. StoreOptions.PragmaSettings picks a profile, applied per connection. Read back off Fisher’s own connection:

[pragma] Default          journal=wal synchronous=1 busy_timeout=5000ms  foreign_keys=1 cache_size=-64000
[pragma] HighSafety       journal=wal synchronous=2 busy_timeout=10000ms foreign_keys=1 cache_size=-32000
[pragma] HighPerformance  journal=wal synchronous=0 busy_timeout=5000ms  foreign_keys=1 cache_size=-128000

Default is synchronous = NORMAL, which in WAL mode can lose the last transactions on power loss but not corrupt the file. HighSafety is FULL, an fsync per commit, which is what I would want on a machine somebody switches off at the wall at closing time. HighPerformance is OFF, for throwaway data rather than for the store you are backing up. Leave WAL alone: the async daemon needs it.

Schema. ApplyAllDatabaseChangesOnStartup() is right for development and wrong for a deployment you want to be able to review. Export the DDL and apply it as a deliberate step instead:

opts.AutoCreateSchemaObjects = AutoCreate.None;   // JasperFx namespace

await store.Advanced.WriteCreationScriptToFileAsync("schema.sql");
[migrate] script exported: 56 lines, 5 tables, db exists: False
[migrate] AutoCreate.None refuses an unmigrated database: InvalidOperationException
[migrate] after applying the script: 1 document

The export does not touch the database, so it is a build artifact. AutoCreate.None then means a terminal whose schema has drifted fails loudly at first use instead of quietly altering its own tables in a shop. Note that SQLite’s ALTER TABLE is narrow: adding a column or an index is fine, adding a constraint or changing a column type requires recreating the table, and Weasel reports that rather than attempting it.

Deployment. One process per file. Not two instances behind a load balancer sharing a volume, and never a network filesystem, where SQLite’s locking cannot be trusted. If you need more writers, split into more files.

Contention and retries. A blocked write surfaces as SQLITE_BUSY and is retried through a Polly pipeline you can extend with opts.ExtendPolly(...). Post-commit listeners deliberately run outside that pipeline, so an AfterCommitAsync fires once rather than once per retry. And never call SqliteConnection.ClearAllPools(); it disposes every pooled connection in the process, including other stores’. Disposing a Fisher store releases its own.

The constraint everything follows from

One database file permits one writer at a time. Fisher builds on that rather than hiding it.

It costs you a strictly sequential unit of work. Two processes writing one file contend, and a long transaction blocks every other writer for its duration. For a till, where one cashier serves one queue, that is close to free.

It buys contiguous committed sequence numbers, so the async daemon’s high-water mark is max(seq_id) with no gaps to reason about, and it buys the cross-tenant write above.

The way out, when you need concurrent writers, is more files rather than more connections: database-per-tenant, or multiple stores. Because a tenant is a file rather than a provisioning event, it is cheap enough to do on first use. On PostgreSQL that decision is heavyweight; here it is a performance feature.

Write-ahead logging is on by default and load-bearing: WAL is what lets the async daemon read while a session writes. It is also what makes naive backups wrong, which is Part 3’s opening argument.

Three things Fisher deliberately does not do. No message bus, and there will not be one: the projection side-effect seam exists, the default outbox drops every message, and delivery is a bus integration’s job here as on both siblings. No table partitioning, since SQLite has no partition functions. No hot-cold daemon failover, because leader election means several nodes sharing one file; DaemonMode.HotCold is refused rather than quietly downgraded.

And one thing it is not: a drop-in test double for a Marten or Polecat application. Fisher passes all 37 suites and 320 tests of JasperFx.Events.ComplianceTests, the shared cross-store suite Marten and Polecat also enroll in, but that suite pins API portability, not behavioural equivalence. Concurrency under contention, string collation, timestamp precision and staleness semantics all differ, which is precisely what integration tests exist to cover.

Next

Part 2 takes the other half: a sale as an event stream committing in the same transaction as these documents, projections, the async daemon, and the two places where SQLite changes the semantics rather than just the SQL. Then Part 3, where the documentation’s cheerful “backup is cp” turns out to be true only when nothing is writing, and silently wrong the rest of the time.

Fisher is on NuGet, documented at fisher.jasperfx.net, and the source is at github.com/JasperFX/fisher.

Series: Fisher on SQLite

  1. 1 Fisher on SQLite, Part 1: The document store
  2. 2 Fisher on SQLite, Part 2: The event store
  3. 3 Fisher on SQLite, Part 3: Backing it up to S3
Next →
#fisher #sqlite #dotnet #critter-stack #document-database #marten