Fisher as Document DB

Fisher lets you use SQLite as a document database. Documents are stored as JSON text, with each document type getting its own table (prefixed fi_doc_).

Key Concepts

• Documents are plain .NET objects serialized to JSON
• Sessions provide a unit of work for batching changes
• LINQ queries translate to SQLite json_extract expressions
• Automatic schema management creates and migrates tables as needed

Document Tables

Each document type gets a table. The core columns:

Column	Type	Description
id	Varies	Primary key — Guid, string, int or long
data	TEXT	The serialized document, exactly as the serializer wrote it
doc_type	TEXT	Sub-class discriminator, for a hierarchy
dotnet_type	TEXT	Assembly-qualified .NET type name
last_modified	TEXT	ISO-8601 UTC, written by SQLite
tenant_id	TEXT	Present under conjoined tenancy

Further columns appear when a feature asks for them: guid_version or revision (concurrency), is_deleted / deleted_at (soft delete), the opt-in metadata columns, and a generated column per duplicated field.

TIP

A document table is created on demand at first write, as well as by the migration. A document type can be stored without ever being registered, and a snapshot type is registered by projection configuration — either way the first write may be the first time the table is needed.

The exception is an enlisted session, where on-demand creation would deadlock against the caller's own write lock. There a missing table throws by name.

Quick Example

// Define a document
public class User
{
    public Guid Id { get; set; }
    public string FirstName { get; set; } = "";
    public string LastName { get; set; } = "";
    public string Email { get; set; } = "";
}

// Store a document
await using var session = store.LightweightSession();
var user = new User { FirstName = "Jane", LastName = "Doe", Email = "jane@example.com" };
session.Store(user);
await session.SaveChangesAsync();

// Load by id
var loaded = await session.LoadAsync<User>(user.Id);

// Query with LINQ
var users = await session.Query<User>()
    .Where(x => x.LastName == "Doe")
    .ToListAsync();

What is here

Topic
Document Identity	Guid, string, int, long and strong-typed wrappers
Opening Sessions	Tracking modes, enlistment, SessionOptions
Storing Documents	Store, Insert, Update, the unit of work
Deleting Documents	Hard and soft deletes, delete by criteria
Querying	LINQ, joins, grouping, paging, raw SQL, JSON reads
Indexing	Duplicated fields, declared indexes, foreign keys
Hierarchies	A base type and its sub-classes in one table
Concurrency	Guid versions or numeric revisions
Patching	Changing part of a document without loading it
Bulk Insert	Loading a lot of documents at once
Metadata	What Fisher records, and mapping it back onto members
Multi-Tenancy	Conjoined tenancy, and writing across tenants