RoomSharp.PostgreSql 0.4.2

RoomSharp

RoomSharp is a Room-inspired data layer for modern .NET (net8/net9/net10). It 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 & Build
4. Core Concepts
   • Entities & Attributes
   • DAOs & Queries
   • Database Class
5. Quick Start
6. Builder Configuration
7. Transactions
8. Type Converters
9. Relations & RelationLoader
10. Paging & LiveData
11. Migrations
12. Raw Queries & QueryExecutor
13. Dependency Injection
14. Samples
15. Roadmap
16. 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, plug custom query executors, enable multi-instance invalidation, 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.

Supported Providers

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

Installation & Build

For now, clone the repository and reference the projects directly (NuGet packaging is planned).

dotnet restore
dotnet build
dotnet test          # optional, once tests are added back

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], [Update], [Delete], [Query], [RawQuery], [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);

    [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();

    [RawQuery]
    Task<List<Todo>> RunCustomQueryAsync(ISupportSQLiteQuery query);

    [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):

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

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

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<TFrom, TTo> or derive from TypeConverter<TFrom, TTo>.
• 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<DateTime, long>
{
    public override long Convert(DateTime value) => value.ToUniversalTime().Ticks;
    public override DateTime ConvertBack(long value) => new(value, DateTimeKind.Utc);
}

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

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

// Register global converters during startup if needed
var db = RoomDatabase.Builder<AppDatabaseImpl>().UseSqlite("posts.db").Build();
db.Converters.Register(new GuidToStringConverter());

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();

Migrations

• Extend Migration and override Migrate(DbConnection connection) for handcrafted steps.
• Or apply [AutoMigration], [RenameTable], [DeleteTable], [ColumnRename], etc., with an IAutoMigrationSpec for declarative schema changes.
• FallbackToDestructiveMigration drops all tables and restarts the metadata table when downgrading.
• MigrationManager keeps the __room_metadata table in sync and reports whether destructive migrations occurred so callbacks can react.

// 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(SqliteConnection connection)
    {
        using var cmd = connection.CreateCommand();
        cmd.CommandText = "UPDATE users SET status='active' WHERE status IS NULL";
        cmd.ExecuteNonQuery();
    }
}

// Manual migration for custom SQL
public sealed class UserMigration_1_2() : Migration(1, 2)
{
    public override void Migrate(DbConnection connection)
    {
        using var cmd = connection.CreateCommand();
        cmd.CommandText = "ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'active'";
        cmd.ExecuteNonQuery();
    }
}

Raw Queries & QueryExecutor

• Use [RawQuery] or RoomSharp.RawQuery.QueryBuilder to run custom SQL while still benefiting from generated mapping.
• Implement IQueryExecutor to centralize diagnostics, caching, or tracing and register it through the builder’s SetQueryExecutor.

var query = new QueryBuilder()
    .Select("*")
    .From("todos")
    .Where("CreatedAt >= @arg0", DateTime.UtcNow.AddDays(-7).Ticks)
    .OrderBy("CreatedAt DESC")
    .Build();

var latest = await db.TodoDao.RunCustomQueryAsync(query);

public sealed class ProfilingQueryExecutor(ILogger logger) : IQueryExecutor
{
    public async Task<List<T>> QueryAsync<T>(string sql, object? parameters = null)
    {
        var sw = Stopwatch.StartNew();
        try
        {
            return await Dapper.SqlMapper.QueryAsync<T>(/* connection */, sql, parameters).ConfigureAwait(false);
        }
        finally
        {
            logger.LogInformation("Executed {Sql} in {Elapsed}ms", sql, sw.ElapsedMilliseconds);
        }
    }

    // implement other members (QuerySingleAsync, ExecuteAsync, etc.) similarly
}

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

Register your database during startup:

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

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

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

• Multiple overloads let you bind connection strings from IConfiguration, inject custom providers (UseProvider<TProvider>()), or supply additional builder tweaks.
• Databases are registered as singletons and disposed automatically when the host shuts down; DAOs default to scoped lifetimes.
• See docs/dependency-injection.md for advanced scenarios (provider factories, DAO registration lifetimes, multi-database setups).

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

Roadmap

• Full-text search (FTS) attributes exist but implementation is pending.
• Packaging to NuGet with CI builds.
• Re-enabling the unit test suite once new APIs settle.

License

MIT