RoomSharp.SqlServer 0.4.7

<p align="center"> <img src="RoomSharp.png" alt="RoomSharp Logo" width="180" /> </p>

<h1 align="center">RoomSharp</h1>

<p align="center"> <a href="https://www.nuget.org/packages/RoomSharp"><img src="https://img.shields.io/nuget/v/RoomSharp.svg?style=flat-square&logo=nuget&label=NuGet" alt="NuGet Version" /></a> <a href="https://www.nuget.org/packages/RoomSharp"><img src="https://img.shields.io/nuget/dt/RoomSharp.svg?style=flat-square&logo=nuget&label=Downloads" alt="NuGet Downloads" /></a> <a href="https://github.com/user/RoomSharp/blob/main/LICENSE"><img src="https://img.shields.io/badge/License-MIT-blue.svg?style=flat-square" alt="License" /></a> <img src="https://img.shields.io/badge/.NET-8.0%20%7C%209.0%20%7C%2010.0-purple.svg?style=flat-square&logo=dotnet" alt=".NET Version" /> <img src="https://img.shields.io/badge/Status-Active%20Development-green.svg?style=flat-square" alt="Status" /> </p>

<p align="center"> <strong>Type-safe, source-generated data layer for modern .NET applications.</strong> </p>

---

RoomSharp is a .NET data layer inspired by Android’s Room database. It supports .NET 8/9/10 and combines a Roslyn source generator, attribute-driven models, and lightweight database providers (SQLite, SQL Server, PostgreSQL, MySQL) to produce zero-reflection DAOs, migrations, and schema builders, while still letting you drop down to raw ADO.NET when needed.

Table of Contents

1. Why RoomSharp
2. Supported Providers
3. Installation
4. Core Concepts
   • Entities & Attributes
   • DAOs & Queries
   • Database Class
5. Quick Start
6. Builder Configuration
7. Concurrency & Threading
8. Transactions
9. Type Converters
10. Relations & RelationLoader
11. Paging & LiveData
12. Reactive Queries & Change Tracking
13. Migrations
14. Raw SQL & QueryExtensions
15. Dependency Injection
16. Samples
17. Roadmap
18. License

Why RoomSharp

• Source-generated DAOs – compile-time codegen (no reflection) for queries, inserts, updates, deletes, and projections.
• Multi-provider runtime – common abstractions over SQLite, SQL Server, PostgreSQL, and MySQL, each with tuned dialect helpers.
• Builder-first configuration – tune journal mode, register callbacks, enable multi-instance invalidation, choose concurrency mode, and auto-close idle connections.
• Transaction-aware – explicit RunInTransaction* helpers plus [Transaction] support in generated DAOs (sync/async).
• Rich annotations – [Entity], [Embedded], [Relation], [DatabaseView], [TypeConverter], [Index], etc.
• Migrations baked in – combine handwritten migrations, auto-migration metadata, and fallback destructive mode.
• Utilities included – LiveData, Flow, paging sources, raw query helpers, bulk insert extensions, and more.

Who is this for?

RoomSharp is designed for developers who:

• Want compile-time safety and source-generated data access without the runtime overhead of full ORMs like EF Core.
• Build desktop, mobile, or backend applications where database control, predictability, and performance matter.
• Rely heavily on SQLite or file-based databases (common in mobile and embedded environments) and still need clear, disciplined concurrency guarantees.
• Prefer explicit SQL, migrations, and schema evolution over implicit runtime behavior.

Supported Providers

Provider	Project	NuGet dependency
SQLite	RoomSharp	Microsoft.Data.Sqlite
SQL Server	RoomSharp.SqlServer	Microsoft.Data.SqlClient
PostgreSQL	RoomSharp.PostgreSql	Npgsql
MySQL / MariaDB	RoomSharp.MySql	MySqlConnector

Every provider implements IDatabaseProvider so it can be swapped in via RoomDatabaseBuilder<T>.UseProvider.

Note: Older RoomSharp versions used MySql.Data. The MySQL provider now uses MySqlConnector for performance and licensing considerations.

Installation

RoomSharp is distributed as a set of NuGet packages.
Choose the core package and the provider(s) that match your target database.

Core Package (includes SQLite)

dotnet add package RoomSharp

Database Providers

SQL Server

dotnet add package RoomSharp.SqlServer

PostgreSQL

dotnet add package RoomSharp.PostgreSql

MySQL / MariaDB

dotnet add package RoomSharp.MySql

Query Extensions (Raw SQL helpers)

dotnet add package RoomSharp.QueryExtensions

Command-Line Interface (CLI)

RoomSharp provides a cross-platform dotnet CLI tool for schema, migrations, and queries.

dotnet tool install --global RoomSharp.Cli

Dependency Injection (Optional)

dotnet add package RoomSharp.DependencyInjection

Register your database and DAOs in ASP.NET Core:

// Basic registration
builder.Services.AddRoomSharpDatabase<AppDatabase>(ctx =>
{
    ctx.UseSqlite("app.db");
    // or: ctx.UseSqliteInMemory();
    // or: ctx.UseSqliteWithConnectionString(connStr);
    // or: ctx.UseSqlServer(connStr);  // requires RoomSharp.SqlServer
    // or: ctx.UsePostgres(connStr);   // requires RoomSharp.PostgreSql
    // or: ctx.UseMySql(connStr);      // requires RoomSharp.MySql
    
    ctx.Builder.SetVersion(1).AddMigrations(new InitialMigration());
});

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

// Health checks integration
builder.Services.AddHealthChecks()
    .AddRoomSharpDatabase<AppDatabase>();

// Factory for background services
builder.Services.AddRoomSharpDatabaseFactory<AppDatabase>(ctx => ctx.UseSqlite("app.db"));

Features: Lifetime validation (prevents captive dependencies), IRoomDatabaseFactory<T>.CreateAsync(), health checks.

Core Concepts

Entities & Attributes

• [Entity(TableName = "...")] – mark POCOs as tables. Combine with [PrimaryKey], [Unique], [ColumnInfo], [ForeignKey], [Embedded], [Relation], [TypeConverter], [Index], and [Ignore] to control schema.
• [DatabaseView] – model SQL views similarly.
• Attributes live in src/RoomSharp/Attributes.

using RoomSharp.Attributes;

[Entity(TableName = "todo_lists")]
[Index(Value = ["Name"], Unique = true)]
public class TodoList
{
    [PrimaryKey(AutoGenerate = true)]
    public long Id { get; set; }

    public required string Name { get; set; }
    public string? Description { get; set; }

    [ColumnInfo(TypeAffinity = "INTEGER")]
    public DateTime CreatedAt { get; set; }

    [ColumnInfo(Name = "updated_at")]
    public DateTime? UpdatedAt { get; set; }
}

DAOs & Queries

• Interfaces marked with [Dao] are parsed by the generator.
• [Insert], [BulkInsert], [Update], [Delete], [Query], [Transaction], [Upsert] drive method generation.
• Async return types (Task, Task<T>, ValueTask<T>) are fully supported.

using RoomSharp.Attributes;

[Dao]
public interface ITodoDao
{
    [Insert]
    long Insert(Todo todo);

    // betch insert
    [Insert]
    long InsertAll(params Todo[] todos);

    [BulkInsert]
    long InsertBulk(IEnumerable<Todo> todos);

    [Update]
    int Update(Todo todo);

    [Delete]
    int Delete(Todo todo);

    [Query("SELECT * FROM todos WHERE ListId = :listId ORDER BY CreatedAt DESC")]
    Task<List<Todo>> GetTodosByListAsync(long listId);

    [Query("SELECT COUNT(*) FROM todos WHERE IsCompleted = 0")]
    Task<int> GetActiveCountAsync();

    [Transaction]
    async Task<int> ReplaceAllAsync(IEnumerable<Todo> todos)
    {
        await DeleteCompletedAsync();
        foreach (var todo in todos)
        {
            Insert(todo);
        }
        return todos.Count();
    }

    [Query("DELETE FROM todos WHERE IsCompleted = 1")]
    Task<int> DeleteCompletedAsync();
}

Database Class

• Inherit from RoomDatabase and decorate with [Database(Version = ..., Entities = ...)].
• Expose abstract DAO properties; the generator emits YourDatabaseImpl.
• Build instances via RoomDatabase.Builder<YourDatabaseImpl>().

using RoomSharp.Attributes;
using RoomSharp.Core;

[Database(Version = 2, Entities = [typeof(Todo), typeof(TodoList)])]
public abstract class TodoDatabase(IDatabaseProvider provider, ILogger? logger = null)
    : RoomDatabase(provider, logger)
{
    public abstract ITodoDao TodoDao { get; }
    public abstract ITodoListDao TodoListDao { get; }
}

Quick Start

using RoomSharp.Attributes;
using RoomSharp.Core;
using RoomSharp.Extensions;

[Entity(TableName = "users")]
public class User
{
    [PrimaryKey(AutoGenerate = true)]
    public long Id { get; set; }

    [Unique]
    public required string Email { get; set; }
    public string? Name { get; set; }
}

[Dao]
public interface IUserDao
{
    [Insert] long Insert(User user);

    [Query("SELECT * FROM users WHERE Email = :email")]
    Task<User?> FindByEmailAsync(string email);

    [Update] int Update(User user);

    [Transaction]
    async Task<long> UpsertAsync(User user)
    {
        var existing = await FindByEmailAsync(user.Email);
        if (existing is null)
        {
            return Insert(user);
        }

        existing.Name = user.Name;
        Update(existing);
        return existing.Id;
    }
}

[Database(Version = 1, Entities = [typeof(User)])]
public abstract class AppDatabase(IDatabaseProvider provider, ILogger? logger = null)
    : RoomDatabase(provider, logger)
{
    public abstract IUserDao UserDao { get; }
}

var db = RoomDatabase.Builder<AppDatabaseImpl>()
    .UseSqlite("app.db")
    .AddCallback(new LoggingCallback(logger))
    .SetJournalMode(JournalMode.WAL)
    .EnableMultiInstanceInvalidation()
    .SetAutoCloseTimeout(TimeSpan.FromMinutes(5))
    .Build();

var userId = db.UserDao.Insert(new User { Email = "john@acme.dev", Name = "John" });

LoggingCallback is any class deriving from RoomDatabaseCallback that implements OnCreate, OnOpen, and/or OnDestructiveMigration.

Builder Configuration

RoomDatabaseBuilder<T> exposes fluent helpers (see RoomSharp.Extensions):

Extension	Effect
UseSqlite(string dbPath) / UseProvider(IDatabaseProvider, string)	Select the backend and connection string.
SetVersion(int) / SetEntities(Type[])	Override metadata from [Database] if needed.
AddMigrations(params Migration[])	Register manual migrations.
FallbackToDestructiveMigration()	Drop & recreate schema when downgrading.
AddCallback(RoomDatabaseCallback)	Receive lifecycle events.
SetJournalMode(JournalMode)	Configure SQLite PRAGMA journal_mode.
EnableMultiInstanceInvalidation()	Prepare SQLite for multi-process WAL usage.
SetAutoCloseTimeout(TimeSpan)	Auto-close idle connections (reopened automatically).
SetMaxParallelConnections(int)	Set max connection pool size for Parallel mode (set in [Database] attribute).
UseConnectionFactory(Func<DbConnection>) / UseRoomDatabaseFactory(IRoomDatabaseFactory)	Configure how Parallel mode opens new connections/sessions.

var db = RoomDatabase.Builder<AppDatabaseImpl>()
    .UseSqlite("app.db")
    .SetVersion(2)
    .AddMigrations(new UserMigration_1_2())
    .AddCallback(new LoggingCallback(logger))
    .SetJournalMode(JournalMode.WAL)
    .EnableMultiInstanceInvalidation()
    .SetAutoCloseTimeout(TimeSpan.FromMinutes(2))
    .Build();

Internally the builder packages these into RoomDatabaseOptions so the runtime can track callbacks, journal configuration, multi-instance flags, auto-close timers, and concurrency mode. When SetAutoCloseTimeout is used, RoomSharp closes idle connections in the background and reopens them transparently the next time a DAO method runs.

Concurrency & Threading

RoomSharp supports two concurrency modes, configured per RoomDatabase instance.

Serialized (default, production-safe)

• All DAO calls on a database instance are serialized through an internal gate.
• Safe for multi-threaded apps even when you call DAOs concurrently (e.g. Task.WhenAll(...)).
• Keeps current performance optimizations (single connection + reusable command templates).
• Recommended for SQLite in most apps (single-writer file database).

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

• Each DAO operation uses a separate DbSession / DbConnection (from provider pooling).
• Enables true parallelism for independent DAO calls (e.g. Task.WhenAll(...)).
• Requires configuring a connection factory (or a custom IRoomDatabaseFactory).
• Transactions are single-connection: use BeginUnitOfWork* and do not run operations in parallel inside the same UnitOfWork/session.

Concurrency Rules & Guarantees

RoomSharp enforces strict concurrency rules to prevent unsafe ADO.NET patterns:

• RoomSharp never executes multiple commands concurrently on the same DbConnection.
• ConcurrencyMode.Parallel enables parallelism via multiple independent DbSessions, not by sharing a connection.
• A DbSession / UnitOfWork is not allowed to be used concurrently; attempting parallel execution within the same session will throw.
• Parallel DAO calls inside an active UnitOfWork reuse the ambient session and therefore run serially.
• Serialized mode guarantees mutual exclusion (no concurrent access), but does not guarantee FIFO task ordering.

Configuration

Serialized (default):

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

Parallel (set via [Database] attribute):

// Define database with Parallel mode
[Database(Version = 1, Entities = [typeof(User)], ConcurrencyMode = ConcurrencyMode.Parallel)]
public abstract class AppDatabase(IDatabaseProvider provider, ILogger? logger = null)
    : RoomDatabase(provider, logger)
{
    public abstract IUserDao UserDao { get; }
}

// Build with connection factory for parallel connections
var cs = "Data Source=app.db;Mode=ReadWriteCreate;Cache=Private";

var db = RoomDatabase.Builder<AppDatabaseImpl>()
    .UseSqliteWithConnectionString(cs)
    .UseConnectionFactory(() => new Microsoft.Data.Sqlite.SqliteConnection(cs))
    .SetMaxParallelConnections(32)
    .Build();

UnitOfWork in Parallel mode:

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

await db.UserDao.InsertAsync(new User { Email = "a@b.com" });
await db.UserDao.UpdateAsync(new User { Id = 1, Email = "c@d.com" });

await uow.CommitAsync();

SQLite notes:

• Parallel mode works best for read-heavy workloads; writes are still single-writer.
• Avoid :memory: in Parallel mode unless you intentionally configure a shared in-memory database (e.g. Mode=Memory;Cache=Shared).

Transactions

• Use RunInTransaction(Action), RunInTransaction<T>(Func<T>), RunInTransactionAsync(Func<Task>), or RunInTransactionAsync<T>(Func<Task<T>>) from RoomDatabase.
• Mark DAO methods with [Transaction] to have the generator wrap the emitted body in the appropriate helper (sync/async). The code generator now handles Task, Task<T>, and synchronous methods automatically.

Type Converters

• Implement ITypeConverter<TProvider, TModel> or derive from TypeConverter<TProvider, TModel>.
• Annotate entity properties with [TypeConverter(ConverterType = typeof(...))].
  RoomSharp registers converters discovered on entities and also exposes RoomDatabase.Converters so you can register custom ones at runtime.

public class DateTimeTicksConverter : TypeConverter<long, DateTime>
{
    public override DateTime FromProvider(long provider) => new(provider, DateTimeKind.Utc);
    public override long ToProvider(DateTime model) => model.ToUniversalTime().Ticks;
}

[Entity(TableName = "posts")]
public class Post
{
    [PrimaryKey(AutoGenerate = true)] public long Id { get; set; }

    [TypeConverter(ConverterType = typeof(DateTimeTicksConverter))]
    public DateTime PublishedAt { get; set; }
}

Relations & RelationLoader

• [Relation] & [Embedded] let DAOs project graphs (1:1, 1:N, N:N) and flatten them into DTOs.
• RoomSharp.Relations.RelationLoader can populate relation properties after a query finishes; extension methods WithRelationsAsync exist for single entities and lists.

public class UserWithPosts
{
    [Embedded] public User User { get; set; } = default!;

    // expose key used by RelationLoader
    public long Id => User.Id;

    [Relation(Entity = typeof(Post), ParentColumn = "Id", EntityColumn = "UserId")]
    public List<Post> Posts { get; set; } = new();
}

var user = await db.UserDao.GetByIdAsync(id);
var hydrated = await user.WithRelationsAsync(db.GetConnection(), logger);

Paging & LiveData

• RoomSharp.Paging offers PagingSource<T>, Pager<T>, and helper DTOs for incremental loading using callbacks that RoomSharp DAOs can feed.
• RoomSharp.Core.LiveData<T> provides observer-based in-memory streams; it works well together with paging or relation loaders for simple reactive scenarios.

var pagingSource = new PagingSource<Todo>((page, size) =>
{
    // delegate to DAO using LIMIT/OFFSET query
    return db.TodoDao.GetPage(page * size, size);
});

await foreach (var page in new Pager<Todo>(pagingSource).GetPagesAsync())
{
    Render(page);
}

var liveData = new LiveData<int>(() => db.TodoDao.GetActiveCountAsync().GetAwaiter().GetResult());
liveData.Observe(count => Console.WriteLine($"Active todos: {count}"));
await liveData.RefreshAsync();

Reactive Queries & Change Tracking

RoomSharp includes a reactive query system that automatically re-executes queries when underlying data changes. The system is table-id based, transaction-aware, coalesced, and uses bounded per-subscriber delivery.

Enabling Change Tracking

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 todos = db.GetTableIdOrThrow("todos"); // or db.GetTableIdOrThrow<Note>()

await using var query = db.Observe(
    ct => new ValueTask<IReadOnlyList<Todo>>(db.TodoDao.GetAllAsync()),
    todos);

using var sub = query.Subscribe(
    onNext: list => Console.WriteLine(list.Count),
    onError: ex => Console.WriteLine(ex));

Operators

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

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

RoomDatabase.FlushInvalidations() forces a dispatcher tick (useful for deterministic tests).

Once enabled, Insert, Update, and Delete operations automatically notify registered observers. The source generator emits the necessary invalidation calls in the generated DAO implementations.

Observable Queries

The Observe extension method creates a query that automatically refreshes when watched tables change:

using RoomSharp.Reactive;

// Get table ID (computed once at startup)
var todosTableId = db.GetTableIdOrThrow("todos");

// Create an observable query watching the table
await using var query = db.Observe(
    async ct => await db.TodoDao.GetAllAsync(),
    todosTableId);

// Subscribe to receive updates
using var subscription = query.Subscribe(
    onNext: todos => Console.WriteLine($"Count: {todos.Count}"),
    onError: ex => Console.WriteLine($"Error: {ex.Message}")
);

// The subscription receives:
// 1. Initial query result immediately after subscribing
// 2. Updated results whenever Insert/Update/Delete affects the table

Query Factory Overloads

// Using table names (string overload - simpler)
await using var query = db.Observe(
    async ct => await db.TodoDao.GetAllAsync(),
    "todos");

// Watch multiple tables (string overload)
await using var query = db.Observe(
    async ct => await db.GetTodosWithCategories(),
    "todos", "categories");

// Using table IDs (int overload - slightly faster)
var todosId = db.GetTableIdOrThrow("todos");
await using var query = db.Observe(async ct => await db.TodoDao.GetAllAsync(), todosId);

// Static lambda overload (avoids closure allocation)
await using var query = db.Observe<List<Todo>, AppDatabase>(
    static async (ct, db) => await db.TodoDao.GetAllAsync(),
    "todos");

Getting Initial Value

Use GetValueAsync() to await the first query result without subscribing:

await using var query = db.Observe(
    async ct => await db.TodoDao.GetAllAsync(),
    "todos");

// Wait for initial data (replaces Task.Delay workarounds)
var initialTodos = await query.GetValueAsync();
Console.WriteLine($"Initial count: {initialTodos.Count}");

// Then subscribe for updates
using var sub = query.Subscribe(
    onNext: todos => Console.WriteLine($"Updated: {todos.Count}"),
    onError: ex => Console.WriteLine($"Error: {ex.Message}"));

IObservableQuery Interface

public interface IObservableQuery<out T> : IAsyncDisposable
{
    T? CurrentValue { get; }
    bool IsStale { get; }

    IDisposable Subscribe(Action<T> onNext, Action<Exception> onError);
    ValueTask<T> GetValueAsync(CancellationToken ct = default);
    ValueTask RefreshAsync(CancellationToken ct = default);
    IAsyncEnumerable<T> ToAsyncEnumerable(CancellationToken ct = default);
}

Key members:

Member	Description
WatchedTables	Tables this query monitors for changes
IsStale	Indicates pending refresh after a table change
Subscribe(onNext, onError)	Register callbacks for query results; returns disposable subscription
GetValueAsync(ct)	Manually fetch current value without subscribing
Invalidate()	Force re-execution of the query

API Reference

The following extension methods are available via using RoomSharp.Reactive;:

Observe

Creates an observable query that auto-refreshes when watched tables change.

// Basic overload
IObservableQuery<T> Observe<T>(
    Func<CancellationToken, ValueTask<T>> query,
    params string[] tables)

// Static lambda overload (avoids closure allocation)
IObservableQuery<T> Observe<T, TDatabase>(
    Func<CancellationToken, TDatabase, ValueTask<T>> query,
    params string[] tables) where TDatabase : RoomDatabase

Examples:

// Basic usage
var query = db.Observe(
    async ct => await db.TodoDao.GetAllAsync(),
    "todos");

// Static lambda (zero allocation)
var query = db.Observe(
    static async (ct, db) => await db.TodoDao.GetAllAsync(),
    "todos");

// Multiple tables
var query = db.Observe(
    async ct => await db.GetTodosWithCategoriesAsync(),
    "todos", "categories");

Manual Notifications

For raw SQL or external modifications:

// Single table
db.NotifyTableChanged("todos");

// Multiple tables
db.NotifyTablesChanged("todos", "categories");

DataSourceChanged (Lightweight Event)

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

// Register a lightweight callback for data changes
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
}

public enum DataChangeOperation : byte
{
    Insert = 0,
    Update = 1,
    Delete = 2,
    BulkInsert = 3
}

Performance Characteristics

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

Use Cases

• Cache invalidation: Clear in-memory caches when data changes
• UI notifications: Show toast messages or update badges
• Audit logging: Record all database operations
• Sync triggers: Start background sync when local data changes

Comparison with Reactive Queries

Feature	OnDataSourceChanged	Observe() + Subscribe()
Setup	Simple callback	Requires EnableChangeTracking()
Data access	TableId only	Re-executes query, returns data
Batching	Per-operation	Coalesced notifications
Allocation	Zero (struct)	Minimal (pooled)
Best for	Simple notifications	Data-bound UI updates

Change Tracker Access

Direct access to the change tracker is available for advanced scenarios:

var tracker = db.ChangeTracker;

// Check if a table is tracked
bool isTracked = tracker.IsTracked("todos");

// Get current version (increments on each change)
long version = tracker.GetVersion("todos");

// List all tracked tables
var tables = tracker.TrackedTables;

Query Operators

IObservableQuery<T> supports fluent operators for stream processing:

var query = db.Observe(async ct => await db.TodoDao.GetAllAsync(), "todos")
    .DistinctUntilChanged()                    // Filter consecutive duplicates
    .Debounce(TimeSpan.FromMilliseconds(300))  // Wait for quiet period
    .Where(list => list.Count > 0);            // Filter results

// Other operators:
// .Throttle(TimeSpan) - Limit emission rate
// .DistinctUntilChanged(keySelector) - Compare by key
// .Select(selector) - Transform results

Architecture Notes

• Queries are coalesced automatically to prevent duplicate refreshes
• In Serialized mode, background refreshes are scheduled through BackgroundQueryScheduler
• In Parallel mode, refreshes execute immediately with separate database sessions
• Notifications are deferred until after transaction commit
• Observers are held via weak references to allow garbage collection if not disposed

Rx.NET Integration

For advanced reactive programming with operators like Throttle, DistinctUntilChanged, and ObserveOn, install the companion package:

dotnet add package RoomSharp.Reactive

This provides ToObservable() to convert IObservableQuery<T> to IObservable<T> and additional extensions for TableChangeTracker.

Migrations

RoomSharp provides a flexible migration system with multiple approaches:

• IRoomMigration (recommended): implement the interface and use MigrationContext for transaction-safe migrations
• Fluent: extend Migration and override UpAsync using helpers (CreateTable, AlterTable, CreateIndex, etc.)
• Auto Migrations: use [AutoMigration] with IAutoMigrationSpec for declarative schema changes
• Legacy: extend Migration and override Migrate(DbConnection) (backward compatible, wrapped via adapter)

Key features:

• All migrations run under a real DB transaction
• MigrationManager tracks state in __room_state and logs applied steps in __room_migrations (with checksums)
• FallbackToDestructiveMigration drops all tables when downgrading (dev/test only)

Upgrading from Legacy to IRoomMigration

If you have existing Migration classes, here's how to upgrade:

Aspect	Legacy	Modern
Base	Migration class	IRoomMigration interface
Method	Migrate(DbConnection)	UpAsync(MigrationContext)
Transaction	Manual	Automatic via ctx.Transaction
Async	No	Yes

Note: Existing Legacy migrations continue to work - RoomSharp wraps them in LegacyMigrationAdapter automatically.

Type-safe Auto Migrations

AddColumnAttribute supports type-safe properties:

// Type-safe (recommended)
[AddColumn(TableName = "users", ColumnName = "Age", 
    ColumnType = typeof(int), Nullable = false, DefaultValue = 0)]

// Or raw SQL (for complex cases)
[AddColumn(TableName = "users", ColumnName = "Age", 
    ColumnDefinition = "INTEGER NOT NULL DEFAULT 0")]

// Declarative auto-migration using IAutoMigrationSpec
[Database(Version = 3, Entities = new[] { typeof(User) })]
[AutoMigration(From = 1, To = 2, Spec = typeof(UserTableSpec))]
[AutoMigration(From = 2, To = 3)]
public abstract class UserDatabase : RoomDatabase
{
    protected UserDatabase(IDatabaseProvider provider, ILogger? logger = null) : base(provider, logger) { }
}

[RenameTable(FromTableName = "old_users", ToTableName = "users")]
[DeleteColumn(TableName = "users", ColumnName = "legacy_flag")]
public sealed class UserTableSpec : IAutoMigrationSpec
{
    [ColumnRename(TableName = "users", FromColumnName = "full_name", ToColumnName = "name")]
    public string UsersTable => "users";


    public void OnPostMigrate(MigrationContext ctx)
    {
        // Execute data migrations within the same transaction
        using var cmd = ctx.CreateCommand("UPDATE users SET status='active' WHERE status IS NULL");
        cmd.ExecuteNonQuery();
    }
}

// Manual migration for custom SQL (transaction-safe)
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);
    }
}

// Fluent migration API (guarded for NOT NULL without default on non-empty tables)
public sealed class AddIsActiveToUsers : Migration
{
    public AddIsActiveToUsers() : base(1, 2) { }

    public override Task UpAsync(MigrationContext ctx)
    {
        ctx.AlterTable("Users", table =>
        {
            table.AddColumn<bool>("IsActive", col =>
            {
                col.NotNull();
                col.Default(true);
            });
        });

        return Task.CompletedTask;
    }
}

// Create table + index
public sealed class CreateUsersTable : Migration
{
    public CreateUsersTable() : base(0, 1) { }

    public override Task UpAsync(MigrationContext ctx)
    {
        ctx.CreateTable("Users", table =>
        {
            table.Column<int>("Id", c => c.PrimaryKey(autoIncrement: true));
            table.Column<string>("Email", c =>
            {
                c.NotNull();
                c.Unique();
            });
            table.Column<DateTime>("CreatedAt", c => c.NotNull());
        }, ifNotExists: true);

        ctx.CreateIndex("Users", "IX_Users_Email", true, true, "Email");
        return Task.CompletedTask;
    }
}

// Fluent checksum helper while keeping IRoomMigration
public sealed class FluentChecksumMigration : IRoomMigration
{
    public int StartVersion => 1;
    public int EndVersion => 2;
    public string Id => "TodoApp.Data.Migrations.FluentChecksumMigration:1->2";
    public string Name => nameof(FluentChecksumMigration);

    private static void Build(IMigrationBuilder m)
    {
        // Explicitly allow destructive operations if you need them:
        // m.AllowDestructiveOperations();

        m.CreateTable("Users", table =>
        {
            table.Column<int>("Id", c => c.PrimaryKey(autoIncrement: true));
            table.Column<string>("Email", c =>
            {
                c.NotNull();
                c.Unique();
            });
        }, ifNotExists: true);

        m.CreateIndex("Users", "IX_Users_Email", true, true, "Email");
    }

    public string Checksum => MigrationSignature.From(DatabaseType.SQLite, Build);

    public Task UpAsync(MigrationContext ctx)
    {
        Build(ctx);
        return Task.CompletedTask;
    }
}

Fluent operations:

• Table: CreateTable, RenameTable, DropTable
• Alter: AddColumn, RenameColumn, DropColumn
• Index: CreateIndex, DropIndex Safe mode: destructive operations (DropTable, DropColumn, DropIndex) are blocked by default. Enable explicitly with AllowDestructiveOperations(). Guard: adding a NOT NULL column without a default to a non-empty table throws an error: Migration rejected: adding NOT NULL column 'IsActive' to non-empty table 'Users' without default value.

Fluent Column Chaining

Chain column configuration directly without callbacks:

ctx.CreateTable("users", table =>
{
    table.Column<int>("Id").PrimaryKey(autoIncrement: true);
    table.Column<string>("Email").NotNull().Unique();
    table.Column<DateTime>("DeletedAt").Nullable();
    
    // Or chain multiple columns
    table.Column<string>("Name").NotNull()
         .Column<int>("Age").Default(0)
         .Timestamps();
});

Convenience methods for common patterns:

• table.Id() - auto-increment int PK
• table.Timestamps() - CreatedAt + UpdatedAt
• table.SoftDelete() - DeletedAt column
• table.String("name") - required string

Understanding Checksums

Checksums are SHA256 hashes that uniquely identify a migration. RoomSharp uses them to detect if a migration was modified after being applied.

Why checksums matter:

• Detect tampering: If someone modifies an applied migration, the checksum mismatch will fail the next migration run
• Ensure consistency: All environments run the exact same migration code
• Audit trail: __room_migrations stores checksums for verification

Three approaches to calculate checksums:

1. Manual: Hash the SQL statement directly with MigrationChecksum.Sha256("ALTER TABLE ...")
2. Fluent (recommended): Use MigrationSignature.From(DatabaseType.SQLite, Build) for automatic computation from fluent operations
3. Auto-computed: When extending Migration base class, checksum is computed from the IL bytecode automatically

Important: Never modify an applied migration. If you need changes, create a new migration with a new version.

MigrationContext Extension Methods

MigrationContext provides extension methods for common operations:

public override async Task UpAsync(MigrationContext ctx)
{
    // Execute simple SQL
    await ctx.ExecuteAsync("UPDATE users SET status='active'");
    
    // Execute with parameters
    await ctx.ExecuteAsync("UPDATE users SET status=@status", new { status = "active" });
    
    // Get scalar value
    var count = await ctx.ExecuteScalarAsync<int>("SELECT COUNT(*) FROM users");
    
    // Check if table exists before dropping
    if (await ctx.TableExistsAsync("legacy_users"))
    {
        ctx.AllowDestructiveOperations();
        ctx.DropTable("legacy_users");
    }
    
    // Check if column exists before adding
    if (!await ctx.ColumnExistsAsync("users", "email"))
    {
        ctx.AlterTable("users", t => t.AddColumn<string>("email"));
    }
}

Migrations in Production

• Migrations run under a real DB transaction via MigrationContext and are protected by a provider-specific migration lock.
• RoomSharp fails fast if __room_state.dirty = 1 (previous migration attempt left the database in an unsafe state). Recovery: restore from backup or manually repair schema, then clear dirty.
• Rollbacks are not supported unless you implement your own “down” strategy; use destructive fallback only when appropriate (typically dev/test).
• __room_migrations stores migration Id + Checksum so you can detect drift; use the CLI migrate:verify command in CI/production checks.

Programmatic Seeding

For MAUI/mobile apps where CLI seeding isn't possible, use the programmatic API:

Basic Usage

// Run all discovered seeders
await db.SeedAsync();

// Or with specific options
await db.SeedAsync(new SeederOptions
{
    Environment = "Development",
    AllowProduction = false
});

// Or with explicit seeders
await db.SeedAsync(new SeederOptions
{
    Seeders = new IRoomSeeder[] { new CurrencySeeder(), new CategorySeeder() }
});

Creating Seeders

Use the strongly-typed base class for best DX:

[Seeder(Order = 1)]
public class CurrencySeeder : RoomSeeder<MyAppDatabase>
{
    protected override async ValueTask SeedCoreAsync(SeederContext ctx, MyAppDatabase db)
    {
        // Use extension methods for common patterns
        await ctx.SeedIfEmptyAsync("currencies", db.CurrencyDao.InsertAsync,
            new Currency { Code = "USD", Name = "US Dollar" },
            new Currency { Code = "EUR", Name = "Euro" },
            new Currency { Code = "SAR", Name = "Saudi Riyal" });
    }
}

[Seeder(Order = 2, DependsOn = new[] { typeof(CurrencySeeder) })]
public class AccountSeeder : RoomSeeder<MyAppDatabase>
{
    protected override async ValueTask SeedCoreAsync(SeederContext ctx, MyAppDatabase db)
    {
        // Only seed in development
        if (!ctx.IsDevelopment) return;
        
        await ctx.SeedIfEmptyAsync("accounts", db.AccountDao.InsertAsync,
            new Account { Name = "Cash", Type = AccountType.Asset });
    }
}

SeederContext Helpers

// Check environment
ctx.IsDevelopment  // true for "Development" or "Dev"
ctx.IsProduction   // true for "Production" or "Prod"
ctx.IsEnvironment("Staging")

// Execute raw SQL
await ctx.ExecuteAsync("UPDATE users SET is_active = 1");
await ctx.ExecuteAsync("INSERT INTO logs (msg) VALUES (@msg)", new { msg = "Seeded" });

// Table helpers
await ctx.IsTableEmptyAsync("currencies");
await ctx.TableExistsAsync("legacy_table");
await ctx.GetTableRowCountAsync("users");

// Conditional seeding
await ctx.SeedInDevelopmentAsync(async () => { /* dev-only seeding */ });
await ctx.SeedInEnvironmentsAsync(new[] { "Dev", "Staging" }, async () => { ... });

Raw SQL & QueryExtensions

RoomSharp no longer includes QueryExecutor or the [RawQuery] attribute. For ad-hoc SQL, use RoomSharp.QueryExtensions instead.

using RoomSharp.QueryExtensions.Extensions;

var todos = await db.QueryAsync<Todo>(
    "SELECT Id, Title, IsDone FROM todos WHERE IsDone = @isDone ORDER BY CreatedAt DESC",
    new { isDone = false });

var affected = await db.ExecuteAsync(
    "UPDATE todos SET IsDone = 1 WHERE Id = @id",
    new { id = 5 });

See src/RoomSharp.QueryExtensions/README.md for streaming (QueryStreamAsync), transactions (InTransactionAsync), raw row reader (ReadRawAsync), and multi-mapping (Query<T1,T2,TResult>).

Dependency Injection

RoomSharp ships with a companion package, RoomSharp.DependencyInjection, that wires databases and DAOs into IServiceCollection using familiar ASP.NET Core patterns. Install it alongside the core package:

dotnet add package RoomSharp.DependencyInjection

Basic Registration

Register your database during startup:

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

Use convenience methods instead of manually creating provider instances:

// SQLite
context.UseSqlite("app.db");

// SQL Server (requires RoomSharp.SqlServer)
context.UseSqlServer("Server=.;Database=MyApp;Trusted_Connection=True;");

// PostgreSQL (requires RoomSharp.PostgreSql)
context.UsePostgres("Host=localhost;Database=myapp;Username=user;Password=pass;");

// MySQL/MariaDB (requires RoomSharp.MySql)
context.UseMySql("Server=localhost;Database=myapp;User=root;Password=pass;");

Service Lifetime

Control the database lifetime with the ServiceLifetime parameter:

// Singleton (default) - single instance shared across the app
builder.Services.AddRoomSharpDatabase<AppDatabase>(ctx => ctx.UseSqlite("app.db"));

// Scoped - new instance per request/scope
builder.Services.AddRoomSharpDatabase<AppDatabase>(
    ctx => ctx.UseSqlite("app.db"), 
    ServiceLifetime.Scoped);

// Shorthand for scoped registration
builder.Services.AddRoomSharpScopedDatabase<AppDatabase>(ctx => ctx.UseSqlite("app.db"));

Database Factory Pattern

For background services, Blazor Server, or manual lifecycle control, use the factory pattern:

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

// Inject and use in a background service
public class MyBackgroundService : BackgroundService
{
    private readonly IRoomDatabaseFactory<AppDatabase> _dbFactory;

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

    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        // Create a new database instance when needed
        using var db = _dbFactory.Create();
        await db.TodoDao.GetAllAsync();
        // db is disposed when leaving the scope
    }
}

Configuration-Based Registration

Bind connection strings directly from IConfiguration:

builder.Services.AddRoomSharpDatabase<AppDatabase>(
    configuration,
    connectionStringName: "Default",
    providerFactory: sp => new SqlServerProvider(),  // Optional custom provider
    configure: builder => builder.SetVersion(2));   // Optional builder tweaks

DAO Registration

DAOs default to scoped lifetime but can be customized:

// Scoped (default) - new DAO instance per scope
builder.Services.AddRoomSharpDao<AppDatabase, ITodoDao>(db => db.TodoDao);

// Singleton - shared DAO instance
builder.Services.AddRoomSharpDao<AppDatabase, ITodoDao>(
    db => db.TodoDao, 
    ServiceLifetime.Singleton);

API Reference

Method	Description
AddRoomSharpDatabase<T>(configure, lifetime?)	Register database with builder callback
AddRoomSharpDatabase<T>(context, lifetime?)	Register with access to IServiceProvider
AddRoomSharpDatabase<T>(config, connName, ...)	Bind from IConfiguration
AddRoomSharpScopedDatabase<T>(configure)	Shorthand for scoped registration
AddRoomSharpDatabaseFactory<T>(configure)	Register factory for manual lifecycle
AddRoomSharpDao<TDb, TDao>(factory, lifetime?)	Register DAO with factory

Context Methods

Method	Description
UseSqlite(path)	Configure SQLite with file path
UseSqliteConnectionString(connStr)	Configure SQLite with full connection string
UseSqlServer(connStr)	Configure SQL Server (requires RoomSharp.SqlServer)
UsePostgres(connStr)	Configure PostgreSQL (requires RoomSharp.PostgreSql)
UseMySql(connStr)	Configure MySQL/MariaDB (requires RoomSharp.MySql)
UseProvider<T>(connStr)	Use custom provider type from DI
UseProvider(provider, connStr)	Use externally constructed provider

Samples

The samples/TodoApp directory demonstrates:

• Entities, DAOs, and databases for todos, users, departments, posts, and relations.
• Complex queries, migrations, [Transaction] methods, relation loaders, converters, and builder options.
• Generated artifacts under samples/TodoApp/Generated/... (checked in for reference but regenerated whenever you build).

Run it with:

dotnet run --project samples/TodoApp

API Stability Notice

RoomSharp is still under active development.

While the core concepts and architecture are stable, public APIs may change as the project evolves, especially before the first stable (1.0) release.

Breaking changes will be documented clearly in release notes and migration guidance will be provided where possible.

Roadmap

Short-term (Stabilization)

• Finalize RoomSharp CLI (migrations, schema, verification)
• Harden concurrency and parallel execution guarantees
• Restore and expand automated test coverage
• Improve diagnostics, error messages, and documentation

No major new features are planned in this phase.

Mid-term (Polish & Adoption)

• Provider-level optimizations based on real usage
• Improve QueryExtensions diagnostics and ergonomics
• Documentation site and deeper guides

Long-term (Demand-driven)

• Optional FTS support (runtime implementation)
• Advanced provider-specific features where justified

RoomSharp deliberately avoids feature creep.
New capabilities are added only when they align with its explicit, predictable design philosophy.

License

MIT