RedBase 3.2.0 — the SQLite provider you asked for: one file, offline, Blazor WASM and mobile

redb

The single most-requested thing since we open-sourced RedBase was "can it run on SQLite?" — and almost always for the same two reasons: Blazor WebAssembly (a typed object store in the browser, no backend round-trip) and mobile / desktop (MAUI, offline-first, a single .db file you ship with the app). Postgres and MSSql are great servers; they are not something you embed in an iOS app or a WASM bundle.

RedBase 3.2.0 ships the SQLite provider. Same LINQ API, same 13-table model, same AddRedb(...) wiring you already use for Postgres and MSSql — now with Data Source=app.db.

First time here? Short context from earlier posts in the series (same platform):

The shortest possible explanation

services.AddRedb(options => options
    .UseSqlite("Data Source=app.db"));

That's it. Everything else — redb.Query<Employee>().Where(e => e.Salary > 80_000).OrderBy(e => e.HireDate).ToListAsync(), trees, grouping, window functions, dictionaries, List<ListItem>, soft-delete — is the same code it is on Postgres or MSSql. The provider is swappable at the DI line; your application doesn't know which database it's on. That's the whole point of the DB-agnostic core, and SQLite is now a first-class citizen of it.

Two tiers, and the one that matters for Blazor / mobile

RedBase has always had a Free tier and a Pro tier. For SQLite the split lands exactly where it should:

SQLite Pro — pure C#, no native code. The query SQL is generated in C# (ProSqlBuilder) and props are materialized in C#. It calls zero database-side functions. That means it runs anywhere Microsoft.Data.Sqlite runs — including Blazor WebAssembly and mobile (MAUI / iOS / Android), where you cannot load a native SQLite extension. This is the tier people were actually asking for: a typed, LINQ-queryable object store living in a single file inside the browser or the app.

Pro is free of charge — you just register at redbase.app to get a license key, and the setup instructions are on the site right after sign-up. The WASM/mobile tier costs nothing; registration is simply how the key is issued.

SQLite Free — a native loadable extension. This is the direct analog of how Free works on Postgres/MSSql: the heavy machinery (get_object_json materializer, the whole query-compiler) lives inside the database as in-DB SQL functions. On SQLite that's a native C extension (redb.dll / .so / .dylib) loaded per connection. It works anywhere native code loads — desktop, server, CI — and it's also what a non-.NET host (Python, the sqlite3 CLI) would call to talk to a RedBase database directly.

So: WASM and mobile → Pro. Desktop/server/Python → either. The differentiation between Free and Pro is materialization and change-tracking, not the query you get back — both tiers generate the same query shape.

What it took to build the Free extension

The interesting part. On Postgres and MSSql, "Free" means a pile of server-side SQL functions (PL/pgSQL, T-SQL) — the v2-pvt query engine, get_object_json, save_object_json, soft-delete, the permission view. SQLite has no stored-procedure language. So for the Free tier we ported that whole engine to a C loadable extension built against sqlite3ext.h:

  • get_object_json — the recursive materializer: base fields, scalars, arrays and dictionaries (relational via _array_index / _array_parent_id), nested Class fields, object references, ListItem (including a ListItem that itself carries an Object). Reads _values and assembles the JSON the C# layer deserializes.
  • The pvt_* query compiler — ~9k lines of PL/pgSQL logic, re-expressed in C as SQL-string generation: pvt_build_query_sql, pvt_build_aggregate_sql, pvt_build_groupby_sql, pvt_build_window_sql, pvt_build_projection_sql, pvt_build_array_groupby_sql. Postgres-isms become SQLite: array_agg ... FILTER→json_group_array ... FILTER, = ANY(arr)→IN (SELECT value FROM json_each(...)), EXTRACT(... FROM x)→CAST(strftime(...) AS INTEGER), ILIKE→LIKE, and so on.
  • Identity — SQLite has no sequences, so the key generator uses a native AUTOINCREMENT table and reserves id blocks from sqlite_sequence; the C extension and the C# key generator advance the same high-water mark, so ids stay globally unique whether allocated from .NET or from a Python host.
  • Soft-delete, permissions — mark_for_deletion / purge_trash as native multi-statement SQL, and a v_user_permissions view (recursive tree inheritance + global fallback) for effective permissions.

Both tiers pass the full example suite (145/145) — the same suite that gates Postgres and MSSql.

Minimum SQLite version is 3.44.0+ (Nov 2023): we lean on FILTER (WHERE …), modern window functions, RETURNING, JSON1 and recursive CTEs so the SQLite SQL stays close to the Postgres SQL instead of being a rewrite.

Getting started

Free (desktop/server — native extension):

services.AddRedb(options => options
    .UseSqlite("Data Source=app.db"));
// point the loader at the native extension (env REDB_SQLITE_EXTENSION,
// or it resolves redb.{dll,so,dylib} from the package's runtimes folder)

Pro (Blazor WASM / mobile / anywhere — pure C#):

services.AddRedbPro(options => options
    .WithLicense(licenseKey)
    .UseSqlite("Data Source=app.db"));

Note one detail that's the same line for both: UseSqlite is tier-agnostic — AddRedb gives you Free, AddRedbPro gives you Pro, and you flip tiers without touching usings. Then everything is the LINQ you already write.

What's honestly not done yet

The voice of this series is "here's what landed and what didn't," so:

  • Cross-platform native binaries for Free. The C extension currently ships built for Windows x64. Linux (.so) and macOS (.dylib, incl. arm64) build from the same CMake project — the CI matrix and per-RID NuGet packaging (runtimes/<rid>/native/) are the next thing on the list. Pro has no native dependency, so it already runs everywhere today, which is the part WASM/mobile users care about.
  • In-memory databases work, but SQLite's :memory: is per-connection — you need Mode=Memory;Cache=Shared plus a kept-open connection so the pooled connections see the same database. (This is a SQLite lifetime quirk, not a RedBase one.)
  • Decimal precision. NUMERIC maps to REAL by default (fast, lossy beyond double); exact-via-TEXT is the planned config knob. SQLite's known weak spot.
  • Free query performance is the server-function model — fine for embedded/local workloads, but for raw throughput on large sets the Pro tier's C# materialization is faster (by design; that's the tier difference).

Why this is the release we wanted to ship

RedBase started as "EF Core alternative for complex object graphs on Postgres/MSSql." With 3.2.0 the same engine — same LINQ, same no-migrations model, same trees and projections — runs in a browser tab and on a phone, from a single file, offline. That's not a port; it's the DB-agnostic core finally reaching the place most people asked for it.

Repo, docs and packages: redbase.app. Questions, "does it do X on SQLite," and bug reports — bring them, this provider is brand new and the feedback loop is open.