RoomSharp v0.4.3: Production-grade migrations, safe-by-default concurrency, and new companion packages

RoomSharp 0.4.3 in one sentence

New to RoomSharp? Check out What is RoomSharp? for an overview.

RoomSharp v0.4.3 is a "core systems" release: migrations become stricter and more durable, concurrency becomes safe by default (with real opt-in parallelism), mapper caching moves to runtime, and the ecosystem expands with RoomSharp.Cli and RoomSharp.QueryExtensions and RoomSharp.Reactive.

If you're building apps where correctness, predictability, and performance matter (desktop, services, mobile-ish SQLite workloads), this is the release that makes the default path safer and the power-user path more explicit.

TL;DR

• Migrations: Strict chains, journaled execution, transaction-safe
• Concurrency: Serialized by default, Parallel opt-in with sessions
• CLI: room migrate:up, room schema:export, and more
• QueryExtensions: Dapper-like ad-hoc SQL with fluent builders
• Reactive: Observe() + GetValueAsync() + OnDataSourceChanged()

---

Highlights

• Migration engine overhaul

  • Transaction-bound migration execution (via MigrationContext)
  • Durable migration journaling (__room_migrations)
  • Single-row state tracking (__room_state)
  • Dirty-state protection to block unsafe re-execution after partial failure
  • Strict "no gaps / no branching" migration chains

• Concurrency overhaul

  • Serialized mode is the default for safe multi-threaded usage
  • Parallel mode enables true parallel DB I/O through multiple connections (session-per-operation)

• Mapper cache moved to runtime

  • Cache is owned by RoomDatabase, reducing generated code size while preserving hot-path performance characteristics

• New packages

  • RoomSharp.Cli – scaffolding, migrations, schema export/validation, inspection, safe diagnostics
  • RoomSharp.QueryExtensions – Dapper-like ad-hoc SQL + streaming + low-level row access
  • RoomSharp.Reactive – System.Reactive (Rx.NET) integration for RoomSharp queries

• Reactive queries

  • Table-ID based change tracking with transaction awareness
  • Coalescing dispatcher + bounded per-subscriber delivery
  • Built-in operators: Where(), DistinctUntilChanged(), Debounce(), Throttle()
  • NEW: GetValueAsync() – await first query result without Task.Delay() workarounds
  • NEW: OnDataSourceChanged() – zero-allocation lightweight change notifications

---

1) Migrations: strict, durable, and transaction-safe

Migrations are where systems typically fail in production—not because the SQL is hard, but because tooling is permissive, state tracking is weak, or execution leaks outside transactions.

RoomSharp 0.4.3 addresses this by introducing a new runtime migration engine with a few strong rules.

New metadata tables

RoomSharp now tracks migration state using:

• __room_state — single-row "current state"
• __room_migrations — append-only journal of applied migrations (including checksums, timing, and success/failure details)

This makes drift detection and recovery workflows much more deterministic.

New migration API: IRoomMigration + MigrationContext

The preferred style is now:

• Implement IRoomMigration
• Use MigrationContext to create commands that are guaranteed to be bound to the current transaction

Example: a manual migration that adds a column, with an explicit checksum (helper provided by RoomSharp):

using RoomSharp.Migrations;

public sealed class UserMigration_1_2 : IRoomMigration
{
    public int StartVersion => 1;
    public int EndVersion => 2;

    public string Id => "UserMigration_1_2:1->2";
    public string Name => nameof(UserMigration_1_2);

    public string Checksum =>
        MigrationChecksum.Sha256("ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'active'");

    public async Task UpAsync(MigrationContext ctx)
    {
        await using var cmd = ctx.CreateCommand(
            "ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'active'");

        await cmd.ExecuteNonQueryAsync(ctx.CancellationToken);
    }
}

Strict pathing: no gaps, no branching

RoomSharp now enforces a single migration chain:

• A migration can only apply when StartVersion == currentVersion
• No skipping versions
• No divergent chains

This is stricter than many tools, but it prevents ambiguous migration plans and "works on my machine" database histories.

Dirty-state protection

If a previous migration attempt left the database in an unsafe state (tracked via a dirty flag), RoomSharp will fail fast instead of retrying and potentially compounding damage.

Production guidance: restore from backup or manually repair schema before clearing dirty-state.

Cross-process provider locks (implementation detail)

RoomSharp acquires a best-effort cross-process lock before applying migrations to avoid concurrent migration runners.

Provider	Mechanism	Notes
PostgreSQL	Advisory lock	Scoped to the DB session; released on disconnect
SQL Server	Application lock	Uses a named lock; honors timeout
MySQL	Named lock	Requires lock name uniqueness per database/app id
SQLite	Early write lock acquisition + busy timeout	Prevents concurrent writers during migration

This is an implementation detail and may change between versions.

---

2) RoomSharp.Cli: runtime migrations and tooling from the command line

RoomSharp.Cli is more than scaffolding. The important architectural choice is:

The CLI loads your compiled assembly and executes the same migration pipeline the runtime uses.

That means plans and verification are consistent with what happens when your app boots.

Install

dotnet tool install -g RoomSharp.Cli
room --help

Project Configuration (room.config.json)

Optional but recommended. Place at the project root to avoid passing flags with every command.

room make:config --provider sqlite --connection "Data Source=app.db" --ns MyApp.Data

This generates:

{
  "provider": "sqlite|pgsql|mysql|sqlserver",
  "connectionString": "Data Source=room.db",
  "buildConfiguration": "Debug",
  "namespaceRoot": "MyApp.Data",
  "paths": {
    "entities": "Data/Models",
    "daos": "Data/Daos",
    "database": "Data/Database",
    "migrations": "Data/Migrations",
    "autoMigrations": "Data/Migrations/Auto",
    "schema": "Data/Schemas",
    "seeders": "Data/Database/Seeders"
  }
}

Connection string references – avoid hardcoding secrets:

Prefix	Source	Example
$env:	Environment variable	$env:DATABASE_URL
$appsettings:	appsettings.json path	$appsettings:ConnectionStrings:Default

// From environment variable
{ "connectionString": "$env:DATABASE_URL" }

// From appsettings.json → ConnectionStrings → Default
{ "connectionString": "$appsettings:ConnectionStrings:Default" }

Migration workflow

Plan what would run (strict chain enforced):

room migrate:plan -b --to=3

Apply migrations:

room migrate:up -b --to=3

Verify applied migrations against recorded checksums (great for CI):

room migrate:verify -b

Dangerous recovery-only command (edits version state only; does not revert schema):

room migrate:set-version --to=2 --force

Safety defaults (why it matters)

• CLI can auto-build before running runtime commands (-b), and can force Release builds (-r)
• Scaffolding supports --dry-run and requires --force to overwrite
• query defaults to safe behavior and blocks writes unless explicitly allowed

Example query usage:

room query "SELECT * FROM users" --format=table
room query "DELETE FROM users" --allow-writes   # explicit opt-in

Schema export and validation

Schema tooling is an underrated superpower for teams:

room schema:export --out ./schema_export
room schema:export --sql --out ./schema_sql
room schema:validate --provider=sqlserver --connection="..." --schema=./schema_export

Use these steps to support:

• DBA review
• CI schema verification
• Reproducible documentation for migrations

---

3) Concurrency: safe by default, parallel when you opt in

ADO.NET makes it very easy to accidentally do unsafe things:

• Reusing a DbConnection concurrently
• Executing multiple commands at once on the same connection
• Mixing transactions with parallel flows

RoomSharp 0.4.3 makes the safe path the default.

Serialized mode (default)

• All DAO calls on a RoomDatabase instance are serialized through an internal gate
• You can safely do Task.WhenAll(...) with the same DB instance without corrupting connection state

var db = RoomDatabase.Builder<AppDatabaseImpl>()
    .UseSqlite("app.db")
    .Build();

// Safe: both calls are serialized automatically
await Task.WhenAll(
    db.UserDao.GetAllAsync(),
    db.TodoDao.GetAllAsync()
);

Parallel mode (opt-in, true parallel DB I/O)

Parallel mode does not mean "parallel commands on one connection".
It means: multiple independent sessions (and therefore connections).

Enable via the [Database] attribute:

[Database(Version = 1, ConcurrencyMode = ConcurrencyMode.Parallel)]
public abstract class AppDatabase : RoomDatabase { }

Configure with a connection factory:

var cs = "Data Source=app.db;Mode=ReadWriteCreate;Cache=Private";

var db = RoomDatabase.Builder<AppDatabase>()
    .UseSqliteConnection(cs)
    .UseConnectionFactory(() => new SqliteConnection(cs))
    .SetMaxParallelConnections(32)
    .Build();

Unit of Work: explicit single-connection transactional scope

Transactions remain single-connection by nature, so RoomSharp provides a Unit-of-Work abstraction:

await using var uow = await db.BeginUnitOfWorkAsync();

await db.UserDao.InsertAsync(new User { Email = "[e-mail]" });
await db.UserDao.UpdateAsync(new User { Id = 1, Email = "[e-mail]" });

await uow.CommitAsync();

Note: BeginUnitOfWork requires ConcurrencyMode = Parallel.

Practical guidance:

• Use Parallel for read-heavy workloads, especially when you can benefit from concurrent queries
• Keep transactional flows within a single UnitOfWork/session

---

4) Mapper cache moved to runtime (smaller generated code)

In previous designs, mapper caches are often emitted into generated DAO code (or kept as per-DAO statics).
In 0.4.3, the mapper cache is owned by RoomDatabase at runtime:

• Smaller generated output
• Shared caching infrastructure
• Preserves the high-performance "hit path" design

This is an internal optimization. The behavior is transparent to application code.

---

5) RoomSharp.QueryExtensions: ad-hoc SQL without giving up RoomSharp integration

RoomSharp removed QueryExecutor and [RawQuery]. The replacement is a dedicated package:
RoomSharp.QueryExtensions.

It's explicitly positioned as:

• Dapper-like control
• Integrated with RoomSharp's concurrency model
• Supports streaming and low-level row access
• Not a full ORM, and not meant to replace DAOs for core domain queries

Install

dotnet add package RoomSharp.QueryExtensions

Quick examples

// Fluent query (type-safe)
var users = await db.From<User>()
    .Where(u => u.IsActive)
    .OrderBy(u => u.Name)
    .GetAsync();

// With eager loading
var orders = await db.From<Order>()
    .Include(o => o.Customer)
    .GetAsync();

// Raw SQL
var count = await db.ExecuteScalarAsync<long>(
    "SELECT COUNT(*) FROM users WHERE IsActive = @active",
    new { active = true });

// Streaming large results
await foreach (var item in db.QueryStreamAsync<Item>("SELECT * FROM items"))
{
    Process(item);
}

Fluent query builder

using RoomSharp.QueryExtensions.FluentQuery;

// String-based
var todos = await db.Table<Todo>("todos")
    .Where("IsDone", false)
    .Where("CategoryId", ">", 5)
    .OrderBy("CreatedAt")
    .Take(20)
    .GetAsync();

// Expression-based (type-safe)
var activeUsers = await db.From<User>()
    .Where(u => u.IsActive)
    .WhereNotNull(u => u.Email)
    .WhereBetween(u => u.Age, 18, 65)
    .OrderBy(u => u.Name)
    .GetAsync();

Joins with eager loading

// Join with Include and filtered by related entity
var order = await db.From<Order>()
    .Select("orders.id", "orders.customer_id", "orders.total")
    .Join("customers", "orders.customer_id", "customers.id")
    .WhereEndsWith("customers.name", "Smith")
    .Include(o => o.Customer)
    .SingleAsync();

// Multiple JOINs
var results = await db.Table<OrderDto>("orders")
    .Join("customers", "orders.customer_id", "customers.id")
    .LeftJoin("products", "orders.product_id", "products.id")
    .Select("orders.id", "customers.name", "products.title")
    .GetAsync();

⚠️ When using Join(), always use table prefixes in Select() to avoid ambiguous column errors.

Low-level zero-allocation row access: RowReader

For hot paths, aggregations, exports, or high-frequency reads:

using RoomSharp.QueryExtensions.Extensions;
using RoomSharp.QueryExtensions.LowLevel;

await db.ReadRawAsync(
    "SELECT Id, Title, IsDone FROM todos",
    param: null,
    (in RowReader r) =>
    {
        var id = r.Get<long>(0);
        var title = r.Get<string>(1);
        var isDone = r.Get<bool>(2);
        Console.WriteLine($"{id}: {title} ({(isDone ? "done" : "pending")})");
    });

Concurrency integration

QueryExtensions respects RoomSharp's concurrency model:

• In Serialized mode it acquires the internal gate automatically
• In Parallel mode you can use multiple DbSessions concurrently

// Parallel mode - use sessions for concurrency
await using var session1 = await db.OpenSessionAsync();
await using var session2 = await db.OpenSessionAsync();

var task1 = session1.QueryAsync<Todo>("SELECT * FROM todos WHERE CategoryId = 1");
var task2 = session2.QueryAsync<Todo>("SELECT * FROM todos WHERE CategoryId = 2");

await Task.WhenAll(task1.AsTask(), task2.AsTask());

---

6) Dependency injection improvements (RoomSharp.DependencyInjection)

RoomSharp ships a DI companion package that wires databases and DAOs into IServiceCollection using familiar ASP.NET Core patterns.

Install

dotnet add package RoomSharp.DependencyInjection

Basic registration

builder.Services.AddRoomSharpDatabase<AppDatabase>(context =>
{
    var configuration = context.Services.GetRequiredService<IConfiguration>();
    var connString = configuration.GetConnectionString("Default")!;

    context.UseSqlite(connString);

    context.Builder
        .SetVersion(2)
        .AddMigrations(new InitialMigration())
        .SetEntities(typeof(User), typeof(Todo));
});

builder.Services.AddRoomSharpDao<AppDatabase, ITodoDao>(db => db.TodoDao);

Provider shortcuts

context.UseSqlite("app.db");
context.UseSqlServer("Server=.;Database=MyApp;Trusted_Connection=True;");
context.UsePostgres("Host=localhost;Database=myapp;Username=user;Password=pass;");
context.UseMySql("Server=localhost;Database=myapp;User=root;Password=pass;");

Service lifetimes + scoped shorthand

builder.Services.AddRoomSharpDatabase<AppDatabase>(ctx => ctx.UseSqlite("app.db"));

builder.Services.AddRoomSharpDatabase<AppDatabase>(
    ctx => ctx.UseSqlite("app.db"),
    ServiceLifetime.Scoped);

builder.Services.AddRoomSharpScopedDatabase<AppDatabase>(ctx => ctx.UseSqlite("app.db"));

Factory pattern for background services / Blazor Server

builder.Services.AddRoomSharpDatabaseFactory<AppDatabase>(ctx =>
{
    ctx.UseSqlServer(configuration.GetConnectionString("Default")!);
});

public class MyBackgroundService : BackgroundService
{
    private readonly IRoomDatabaseFactory<AppDatabase> _dbFactory;

    public MyBackgroundService(IRoomDatabaseFactory<AppDatabase> dbFactory)
        => _dbFactory = dbFactory;

    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        using var db = _dbFactory.Create();
        await db.TodoDao.GetAllAsync();
    }
}

---

7) Reactive queries & change tracking

RoomSharp includes a reactive query system that re-executes queries when watched tables change. The system is:

• Table-ID based – uses integer IDs for efficient lookup
• Transaction-aware – notifications dispatched only after commit
• Coalesced – reduces notification noise via dispatcher
• Bounded delivery – supports LatestOnly, Buffered, All modes

Enable change tracking + observe a query

using RoomSharp.Extensions;
using RoomSharp.Invalidation;
using RoomSharp.Reactive;

var db = RoomDatabase.Builder<AppDatabaseImpl>()
    .UseSqlite("app.db")
    .EnableChangeTracking(o =>
    {
        o.DispatchInterval = TimeSpan.FromMilliseconds(50);
        o.DeliveryMode = DeliveryMode.LatestOnly;
    })
    .Build();

var todosTableId = db.GetTableIdOrThrow("todos");

var notesQuery = db.Observe(
    static async (ct, database) => await database.NoteDao.GetAllAsync(),
    notesTableId
);


using var sub = query.Subscribe(
    onNext: list => Console.WriteLine($"Todos: {list.Count}"),
    onError: ex => Console.WriteLine($"Error: {ex.Message}"));

Built-in operators

// Debounce: delays refresh until a quiet period
await using var debounced = db.Observe(ct => ..., todosTableId)
    .Debounce(TimeSpan.FromMilliseconds(100))
    .DistinctUntilChanged();

// Throttle: limits refresh rate
await using var throttled = db.Observe(ct => ..., todosTableId)
    .Throttle(TimeSpan.FromMilliseconds(200))
    .Where(list => list.Count > 0);

Rx.NET integration

using RoomSharp.Reactive.Rx;

// Convert to IObservable<T> for use with Rx.NET operators
var observable = query.ToObservable();

observable
    .Buffer(TimeSpan.FromSeconds(1))
    .Subscribe(batch => Console.WriteLine($"Received {batch.Count} updates"));

GetValueAsync: await first query result

Instead of using Subscribe() + Task.Delay(), you can now await the first query result directly:

// Before (workaround)
var subscription = query.Subscribe(onNext: list => { ... });
await Task.Delay(500);  // Unreliable!

// After (recommended)
var initialData = await query.GetValueAsync();
Console.WriteLine($"Initial count: {initialData.Count}");

DataSourceChanged: lightweight change notifications

For simple notification scenarios where you don't need reactive queries, RoomSharp provides a zero-allocation callback:

// Register a lightweight callback
db.OnDataSourceChanged(info =>
{
    Console.WriteLine($"Table {info.TableId}: {info.Operation} ({info.AffectedRows} rows)");
});

// Remove callback when no longer needed
db.OnDataSourceChanged(null);

DataChangeInfo structure

public readonly struct DataChangeInfo
{
    public readonly int TableId;              // Table identifier
    public readonly DataChangeOperation Operation;  // Insert, Update, Delete, BulkInsert
    public readonly int AffectedRows;         // Number of affected rows
}

Performance characteristics

Feature	Description
Zero-allocation	Uses struct instead of class
Zero-cost when unused	Inline null check with early return
Immediate notification	Called synchronously after each operation
No dependencies	Works without EnableChangeTracking()

---

9) Provider update: MySQL now uses MySqlConnector

RoomSharp's MySQL provider switched its internal driver from MySql.Data to MySqlConnector for performance and licensing considerations. This change is transparent at the application level—no code changes required.

---

Breaking / behavioral changes

Change	Migration path
__room_metadata deprecated	New installs use __room_state + __room_migrations. Existing databases are upgraded automatically.
QueryExecutor and [RawQuery] removed	Use RoomSharp.QueryExtensions package

---

Suggested upgrade checklist

1. Preview migrations:

   room migrate:plan -b

1. Apply migrations:

   room migrate:up -b

1. Verify checksums (CI-friendly):

   room migrate:verify -b

1. If you used [RawQuery]: migrate those call sites to RoomSharp.QueryExtensions.

2. Decide concurrency mode:

   • Stick to Serialized (default) for most apps (especially SQLite)
   • Enable Parallel only when you need real concurrency and can provide a connection factory

---

Closing thoughts

RoomSharp 0.4.3 sharpens the contract between your application and the database:

• Migrations are strict and journaled
• Concurrency is safe by default and explicit when parallel
• Tooling matures with a CLI that runs the same migration pipeline
• Ad-hoc SQL is moved into a focused companion package without sacrificing integration
• Reactive queries are transaction-aware and coalesced by design

This release focuses on making the default path safer while providing explicit power-user options when needed. RoomSharp deliberately avoids feature creep—new capabilities are added only when they align with its explicit, predictable design philosophy.

---

Get Started

dotnet add package RoomSharp

NuGet Packages

Package	Description
RoomSharp	Core library with SQLite support
RoomSharp.MySql	MySQL/MariaDB provider
RoomSharp.PostgreSql	PostgreSQL provider
RoomSharp.SqlServer	SQL Server provider
RoomSharp.DependencyInjection	Microsoft.Extensions.DI integration
RoomSharp.Cli	CLI tool for migrations and schema
RoomSharp.QueryExtensions	Fluent query builders
RoomSharp.Reactive	Observable queries and change tracking

Links

• Documentation

---

For questions, feedback, or support, contact us at [e-mail]