Introducing Shiny.SqliteDocumentDb — Schema-Free JSON Documents in SQLite
I’ve been building .NET apps long enough to know that SQLite is the workhorse of local storage. It’s everywhere — mobile apps, desktop apps, embedded systems, even server-side caches. But every time I reach for sqlite-net or raw ADO.NET, I end up in the same loop: design tables, write migrations, manage foreign keys, rehydrate object graphs from JOINs.
For a lot of use cases — settings stores, offline caches, app state, anything with nested data — that ceremony is overkill. What I actually want is to throw an object in and get it back out. So I built Shiny.SqliteDocumentDb.
What Is It?
Section titled “What Is It?”It’s a lightweight document store that sits on top of SQLite. You give it a .NET object, it serializes it to JSON and stores it. You query it with a fluent LINQ query builder, and it translates those expressions to json_extract SQL under the hood. No schema, no migrations, no table design.
var store = new SqliteDocumentStore(new DocumentStoreOptions{ ConnectionString = "Data Source=mydata.db"});
// Store a document — Id is auto-generated and written back to the objectvar user = new User { Name = "Alice", Age = 25 };await store.Set(user);// user.Id is now populated
// Fluent query buildervar results = await store.Query<User>() .Where(u => u.Name == "Alice") .OrderBy(u => u.Age) .ToList();Every document type must have a public Id property of type Guid, int, long, or string. The Id is stored in both the SQLite column and the JSON blob, so query results always include it.
All JsonTypeInfo<T> parameters are optional — configure a JsonSerializerContext once and type info is auto-resolved on every call. No per-call ctx.User needed. Full AOT, fully trimmable, zero reflection.
Why Not Just Use sqlite-net?
Section titled “Why Not Just Use sqlite-net?”sqlite-net is great for flat, single-table CRUD. But the moment your data has structure — an order with line items, a user with addresses, a config with nested sections — things get painful. You need multiple tables, foreign keys, multiple inserts per save, and multiple queries plus manual rehydration per read.
The document store approach collapses all of that into a single operation. One write, one read, one document.
The benchmarks tell the story:
Nested insert (Order + Address + OrderLines + Tags, 100 records):
| Method | Mean |
|---|---|
| DocumentStore | 5.69 ms |
| sqlite-net (3 tables) | 176.48 ms |
Nested get by ID:
| Method | Mean |
|---|---|
| DocumentStore | 5.04 us |
| sqlite-net (3 queries) | 48.26 us |
That’s 31x faster inserts and 10x faster reads for nested data. The document store wins because it does one write and one read instead of multiple table operations.
For flat data, sqlite-net can be faster on indexed column queries (it queries columns directly vs. json_extract). Use the right tool for the shape of your data.
What About EF Core on MAUI?
Section titled “What About EF Core on MAUI?”The other question I get is “why not just use EF Core?” On a server, EF Core is a reasonable choice. On .NET MAUI — iOS, Android, Mac Catalyst — it becomes a liability.
AOT is not optional on Apple platforms. iOS, iPadOS, tvOS, and Mac Catalyst all prohibit JIT compilation at the OS level. EF Core relies heavily on runtime reflection and dynamic code generation for change tracking, query compilation, and model building. Its public API is decorated with [RequiresDynamicCode] and [RequiresUnreferencedCode] throughout. That’s a non-starter for fully native AOT deployments on Apple platforms.
Android doesn’t prohibit JIT, but AOT (PublishAot or AndroidEnableProfiledAot) delivers measurably faster startup and lower memory usage — both of which directly affect user experience on mobile.
Migrations solve a problem mobile apps don’t have. On a server, you run migrations against a shared database with a known lifecycle. On a mobile device, the database is created on first launch or ships inside the app bundle. EF Core’s migration pipeline (Add-Migration, Update-Database, __EFMigrationsHistory) adds complexity with no real benefit. A schema-free document store eliminates migrations entirely.
The dependency graph is heavy. EF Core pulls in Microsoft.EntityFrameworkCore, its SQLite provider, design-time packages, and their transitive dependencies. That increases app bundle size — a real concern when app stores enforce download limits and users expect fast installs.
Mobile data is document-shaped. User preferences, cached API responses, offline data queues, local state — this data naturally has nested structure. Forcing it into normalized tables with foreign keys and JOINs adds accidental complexity.
| Concern | EF Core | Shiny.SqliteDocumentDb |
|---|---|---|
| AOT / trimming | Reflection-heavy; no AOT support | Optional JsonTypeInfo<T> on every API; auto-resolves from context |
| Migrations | Required for every schema change | Not needed — schema-free JSON |
| Nested objects | Normalized tables, foreign keys, JOINs | Single document, single write, single read |
| App bundle size | Large dependency tree | Single dependency on Microsoft.Data.Sqlite |
| Startup time | DbContext model building, migration checks | Open connection and go |
The .NET trimmer makes this worse. Libraries that depend on reflection break under trimming because the trimmer can’t statically determine which types and members are accessed at runtime. This forces you to either disable trimming (larger binaries) or maintain complex trimmer XML configuration. This library avoids both problems — source-generated JSON serialization means the trimmer can see every type, and there’s no Expression.Compile(), no Reflection.Emit, no dynamic delegates anywhere.
The Fluent Query Builder
Section titled “The Fluent Query Builder”This is the heart of the API. store.Query<T>() returns a fluent builder where you chain .Where(), .OrderBy(), .Paginate(), .Select() — then terminate with .ToList(), .ToAsyncEnumerable(), .Count(), .Any(), .ExecuteDelete(), .ExecuteUpdate(), or any aggregate method.
// Filter + sort + paginatevar page = await store.Query<User>() .Where(u => u.Age > 18) .OrderBy(u => u.Name) .Paginate(0, 20) .ToList();
// Count matching documentsvar count = await store.Query<Order>() .Where(o => o.Status == "Pending") .Count();
// Delete by predicate — returns count deletedint deleted = await store.Query<User>() .Where(u => u.Age < 18) .ExecuteDelete();
// Bulk update a property — returns count updatedint updated = await store.Query<User>() .Where(u => u.Age < 18) .ExecuteUpdate(u => u.Age, 18);
// Scalar aggregatesvar maxAge = await store.Query<User>().Max(u => u.Age);var avgAge = await store.Query<User>() .Where(u => u.IsActive) .Average(u => u.Age);The expression visitor translates C# LINQ expressions into SQLite json_extract SQL, resolving property names from JsonTypeInfo metadata so [JsonPropertyName] and camelCase policies work correctly.
// Nested propertiesvar portland = await store.Query<Order>() .Where(o => o.ShippingAddress.City == "Portland") .ToList();// → json_extract(Data, '$.shippingAddress.city') = @p0
// Collection queries with Any()var hasWidgets = await store.Query<Order>() .Where(o => o.Lines.Any(l => l.ProductName == "Widget")) .ToList();// → EXISTS (SELECT 1 FROM json_each(Data, '$.lines') WHERE ...)
// Collection Count()var bigOrders = await store.Query<Order>() .Where(o => o.Lines.Count() > 5) .ToList();// → json_array_length(Data, '$.lines') > 5
// String methodsvar matches = await store.Query<User>() .Where(u => u.Name.Contains("li")) .ToList();// → LIKE '%' || @p0 || '%'Equality, comparisons, logical operators (&&, ||, !), null checks, DateTime/DateTimeOffset, captured variables — they all work. The full expression reference is in the README.
SQL-Level Projections
Section titled “SQL-Level Projections”Need just a few fields from a large document? Chain .Select() to extract only the selected properties at the database level using json_object — no full deserialization.
var summaries = await store.Query<Order>() .Where(o => o.Status == "Shipped") .OrderBy(o => o.CustomerName) .Paginate(0, 50) .Select(o => new OrderSummary { Customer = o.CustomerName, City = o.ShippingAddress.City, LineCount = o.Lines.Count() }) .ToList();That Lines.Count() becomes json_array_length(Data, '$.lines') in SQL. You can also use Any(), Any(predicate), Count(predicate), Sum(), Max(), Min(), and Average() inside selectors.
Aggregate Projections
Section titled “Aggregate Projections”For GROUP BY queries, use the Sql marker class inside .Select():
var stats = await store.Query<Order>() .Where(o => o.Status != "Cancelled") .Select(o => new OrderStats { Status = o.Status, // GROUP BY column OrderCount = Sql.Count(), // COUNT(*) TotalRevenue = Sql.Sum(o.TotalAmount), }) .ToList();Non-aggregate columns are automatically grouped. Sql.Count(), Sql.Max(), Sql.Min(), Sql.Sum(), Sql.Avg() — all available.
Surgical Field Updates
Section titled “Surgical Field Updates”Don’t always need to replace an entire document. SetProperty updates a single field in-place via json_set(), and RemoveProperty strips a field via json_remove() — both without deserializing the document.
// Update a single fieldawait store.SetProperty<User>("user-1", u => u.Age, 31);
// Nested paths work tooawait store.SetProperty<Order>("order-1", o => o.ShippingAddress.City, "Portland");
// Strip a field entirelyawait store.RemoveProperty<User>("user-1", u => u.Email);For multi-field patches, Upsert does RFC 7396 JSON Merge Patch — deep-merging only the provided fields while preserving everything else:
await store.Upsert(new User { Id = "user-1", Name = "Alice", Age = 31 });// Email and other fields are preservedStreaming with IAsyncEnumerable
Section titled “Streaming with IAsyncEnumerable”Use .ToAsyncEnumerable() instead of .ToList() to stream results one-at-a-time without buffering the entire set into memory.
await foreach (var order in store.Query<Order>() .Where(o => o.Status == "Pending") .OrderBy(o => o.CustomerName) .ToAsyncEnumerable()){ await ProcessOrder(order);}The benchmarks show streaming eliminates Gen1 GC collections entirely at 1,000+ documents while maintaining within ~2% of buffered throughput. If you’re processing results incrementally, streaming is free performance.
Expression-Based JSON Indexes
Section titled “Expression-Based JSON Indexes”The default query performance is solid, but for hot paths you can create indexes on json_extract expressions:
await store.CreateIndexAsync<User>(u => u.Name, ctx.User);// CREATE INDEX IF NOT EXISTS idx_json_User_name// ON documents (json_extract(Data, '$.name'))// WHERE TypeName = 'User';Impact on a 1,000-record flat query:
| Method | Mean |
|---|---|
| Without index | 270 us |
| With index | 8.52 us |
~32x faster. The index lets SQLite use a B-tree lookup instead of scanning every row with json_extract. Works with nested properties too:
await store.CreateIndexAsync<Order>(o => o.ShippingAddress.City, ctx.Order);AOT and Trimming — First Class
Section titled “AOT and Trimming — First Class”All JsonTypeInfo<T> parameters are optional with = null defaults. Configure a JsonSerializerContext once at setup and every method auto-resolves type info — no per-call parameters needed.
[JsonSerializable(typeof(User))][JsonSerializable(typeof(Order))]public partial class AppJsonContext : JsonSerializerContext;
var ctx = new AppJsonContext(new JsonSerializerOptions{ PropertyNamingPolicy = JsonNamingPolicy.CamelCase});
var store = new SqliteDocumentStore(new DocumentStoreOptions{ ConnectionString = "Data Source=mydata.db", JsonSerializerOptions = ctx.Options, UseReflectionFallback = false // recommended for AOT});
// Now every call is AOT-safe without passing JsonTypeInfo explicitlyvar user = new User { Name = "Alice", Age = 25 };await store.Set(user); // user.Id is auto-generatedvar users = await store.Query<User>().Where(u => u.Age > 18).ToList();Pass ctx.Options to DocumentStoreOptions.JsonSerializerOptions so the expression visitor and serializer share the same naming configuration. That’s the one thing people forget — and then their LINQ queries silently return zero results because property names don’t match.
Set UseReflectionFallback = false for AOT deployments. Instead of opaque runtime failures, you get a clear InvalidOperationException telling you exactly which type is missing from your JsonSerializerContext.
Transactions
Section titled “Transactions”Atomic multi-document operations with automatic commit/rollback:
await store.RunInTransaction(async tx =>{ await tx.Set(order); // order.Id must be set await tx.Set(user); // user.Id must be set // Exception → automatic rollback});The tx parameter is a full IDocumentStore, so you can use any operation inside the transaction — queries, counts, removes, everything.
DI Registration
Section titled “DI Registration”One line:
services.AddSqliteDocumentStore("Data Source=mydata.db");Registers IDocumentStore as a singleton. For full configuration:
services.AddSqliteDocumentStore(opts =>{ opts.ConnectionString = "Data Source=mydata.db"; opts.TypeNameResolution = TypeNameResolution.FullName; opts.JsonSerializerOptions = ctx.Options; opts.UseReflectionFallback = false;});When to Use It
Section titled “When to Use It”Good fit:
- Offline caches and app state
- Settings and configuration stores
- Data with nested objects and child collections
- Rapid prototyping without schema design
- Any scenario where you want to store and query object graphs without table design
Not the best fit:
- Bulk operations on millions of rows where raw SQL shines
- Simple flat-table CRUD where sqlite-net is already working well
Get Started
Section titled “Get Started”dotnet add package Shiny.SqliteDocumentDbFull documentation at shinylib.net/sqlite-docdb and the GitHub repository has the complete README with benchmarks, expression reference tables, and examples.
