Rhinox 0.0.41-alpha

Rhinox

A source generator and declarative migration framework for PostgreSQL. Define your schema as SQL files and get a complete, type-safe data access layer at build time — entities, repositories, query builders, and DI registration — without writing a single line of boilerplate. When your schema changes, Rhinox computes the migration diff automatically.

Features

Source Generator — Reads db/schema/*.sql files at build time and generates:

• Entity classes (POCOs with attributes and navigation properties)
• Repositories with CRUD, pagination, index-aware queries, and FK join methods
• Expression-based query builder with cursor pagination
• DI registration extension method

The generator never connects to a live database. Use rhinox introspect to bootstrap db/schema/*.sql from an existing database.

Declarative Migrations — Define your desired schema as SQL files, and Rhinox computes the diff:

• No hand-written migrations — diffs are computed, not authored
• Terraform-like workflow: edit state files, build generates the migration, apply executes it
• Full SQL parser for CREATE TABLE, INDEX, TYPE, and ALTER TABLE
• Topological ordering respects FK dependencies
• Destructive operations clearly marked for review
• Concurrent-safe with PostgreSQL advisory locks

Quick Start

Prerequisites

• .NET 10 SDK
• PostgreSQL 12+
• Docker (for local development)

1. Start a local database

docker compose up -d

2. Source Generator

Install from NuGet:

dotnet add package Rhinox --prerelease
dotnet tool install --global Rhinox.Cli

If you're starting from an existing database, bootstrap your schema files from it:

rhinox introspect --connection "Host=localhost;Port=5435;Database=mydb;Username=postgres;Password=secret" --schema-dir db/schema --schemas public

This writes db/schema/<schema>/<table>.sql (and db/schema/types/<enum>.sql) — commit these. From this point on, edit the SQL files to evolve your schema; the source generator never touches the database again.

Optional MSBuild config (only needed to override defaults):

<PropertyGroup>
  
  <RhinoxNamespace>MyApp.Db</RhinoxNamespace>
</PropertyGroup>

Build your project. The generator parses db/schema/*.sql and emits C# code:

// Generated entity (Rx suffix avoids domain type collisions)
public partial class UserRx
{
    [Column("id"), PrimaryKey]
    public Guid Id { get; set; }

    [Column("email")]
    public string Email { get; set; } = default!;

    [Column("first_name")]
    public string? FirstName { get; set; }

    public TenantRx Tenant { get; set; } = default!;
    public IReadOnlyList<OrderRx> Orders { get; set; } = new List<OrderRx>();
}

// Generated repository (takes DbSession, uses the DI-scoped IDbConnection)
public partial class UserRepository : IUserRepository
{
    public UserRepository(DbSession session) { ... }

    // CRUD
    public Task<UserRx?> GetByIdAsync(object id, CancellationToken ct = default);
    public Task<IEnumerable<UserRx>> GetAllAsync(CancellationToken ct = default);
    public Task<object> InsertAsync(UserRx entity, CancellationToken ct = default);
    public Task<bool> UpsertAsync(UserRx entity, CancellationToken ct = default);
    public Task<bool> InsertIfNotExistsAsync(UserRx entity, CancellationToken ct = default);
    public Task<bool> UpdateAsync(UserRx entity, CancellationToken ct = default);
    public Task<bool> DeleteAsync(object id, CancellationToken ct = default);

    // Cursor-based pagination
    public Task<Page<UserRx>> GetPageAsync(int pageSize = 20, string? afterCursor = null,
        bool includeTotalCount = false, CancellationToken ct = default);

    // Index-aware queries (auto-generated from your indexes)
    public Task<UserRx?> GetByEmailAsync(string email, CancellationToken ct = default);
    public Task<IEnumerable<UserRx>> GetByTenantIdAndIsActiveAsync(Guid tenantId, bool isActive, CancellationToken ct = default);

    // FK join methods
    public Task<UserRx?> GetByIdWithTenantAsync(object id, CancellationToken ct = default);
    public Task<UserRx?> GetByIdWithOrdersAsync(object id, CancellationToken ct = default);

    // Expression query builder
    public UserQueryBuilder Query();
}

3. Three ways to query

Repository methods — for direct lookups and CRUD:

// Inject via DI
app.MapGet("/users/{id}", async (Guid id, UserRepository repo) =>
{
    var user = await repo.GetByIdAsync(id);
    return user is null ? Results.NotFound() : Results.Ok(user);
});

// Paginated listing
app.MapGet("/users", async (UserRepository repo, string? cursor) =>
{
    var page = await repo.GetPageAsync(pageSize: 20, afterCursor: cursor);
    return Results.Ok(page);
});

Query builder — for dynamic filtering with cursor pagination:

app.MapGet("/products", async (ProductRepository repo,
    Guid? tenantId, decimal? minPrice, bool? active, string? search, string? cursor) =>
{
    var query = repo.Query();

    if (tenantId.HasValue)
        query = query.Where(p => p.TenantId == tenantId.Value);
    if (minPrice.HasValue)
        query = query.Where(p => p.BasePrice >= minPrice.Value);
    if (active.HasValue)
        query = query.Where(p => p.IsActive == active.Value);
    if (!string.IsNullOrEmpty(search))
        query = query.Where(p => p.Name.Contains(search));

    var page = await query
        .OrderBy(p => p.CreatedAt, descending: true)
        .ToPageAsync(pageSize: 20, afterCursor: cursor, includeTotalCount: true);

    return Results.Ok(page);
});

Supported expressions:

// Comparison: ==, !=, >, <, >=, <=
.Where(p => p.Price >= 50 && p.Price <= 200)

// String: Contains, StartsWith, EndsWith (case-insensitive via ILIKE)
.Where(p => p.Name.Contains("phone"))

// Null checks
.Where(p => p.BrandId != null)

// Collection (IN / ANY)
var ids = new List<Guid> { id1, id2, id3 };
.Where(p => ids.Contains(p.Id))

// Boolean
.Where(p => p.IsActive)
.Where(p => !p.IsDigital)

// Ordering with multiple columns
.OrderBy(p => p.CreatedAt, descending: true)
.ThenBy(p => p.Name)

Raw SQL via Dapper — for complex queries not covered by the repository:

app.MapGet("/orders/stats", async (DbSession session) =>
{
    var stats = await session.Connection.QueryAsync(
        "SELECT status, COUNT(*) as count, SUM(grand_total) as revenue FROM orders GROUP BY status");
    return Results.Ok(stats);
});

Register everything with DI (requires IDbConnection to be registered as scoped):

// Register the scoped connection
services.AddScoped<IDbConnection>(_ => new NpgsqlConnection(connectionString));

// Register Rhinox (DbSession + all repositories)
services.AddRhinoxDb();

4. Declarative Migrations

Define your schema as SQL files — one file per table:

db/
  schema/
    types/
      order_status.sql          # CREATE TYPE order_status AS ENUM (...)
    public/
      users.sql                 # CREATE TABLE users (...) + indexes
      orders.sql                # CREATE TABLE orders (...)
  migrations/                   # Auto-generated, never hand-edited
    001_initial.sql

Edit a schema file to add a column:

-- db/schema/public/users.sql
CREATE TABLE users (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    email text NOT NULL,
    phone text,                 -- new column
    created_at timestamptz NOT NULL DEFAULT now()
);

Build your project — the migration is generated automatically:

$ dotnet build
Rhinox.Migrate: Generated 002_add_column_users_phone.sql
  - Add column: users.phone

Apply it:

rhinox migrate apply --connection "Host=localhost;Database=mydb;Username=postgres;Password=secret"
# Applying 002_add_column_users_phone.sql... done.
# Applied 1 migration(s).

Check status:

rhinox migrate status --connection "Host=localhost;Database=mydb;..."
# Migration Status:
# ------------------------------------------------------------
#   [applied] 001_initial.sql
#   [applied] 002_add_column_users_phone.sql
# ------------------------------------------------------------
# 2 applied, 0 pending.

Pagination

Rhinox provides cursor-based pagination out of the box — consistent performance regardless of page depth.

// Simple pagination via repository
var page1 = await repo.GetPageAsync(pageSize: 20);
var page2 = await repo.GetPageAsync(pageSize: 20, afterCursor: page1.NextCursor);

// Filtered pagination via query builder
var page = await repo.Query()
    .Where(p => p.IsActive)
    .OrderBy(p => p.CreatedAt, descending: true)
    .ToPageAsync(pageSize: 20, afterCursor: cursor, includeTotalCount: true);

// Page<T> result
page.Items              // IReadOnlyList<T>
page.HasNextPage        // bool
page.HasPreviousPage    // bool
page.NextCursor         // opaque string — pass to afterCursor for next page
page.PreviousCursor     // pass to beforeCursor for previous page
page.TotalCount         // int? — only populated when includeTotalCount: true

Project Structure

src/
  Rhinox.Schema/              # Shared schema model (TableInfo, ColumnInfo, etc.)
  Rhinox.Core/                # Runtime library (attributes, IRepository, QueryBuilder<T>, Page<T>)
  Rhinox.Generator/           # Roslyn source generator (introspects DB, emits C#)
  Rhinox.Migrate.Core/        # Migration engine (SQL parser, diff, generator, replayer)
  Rhinox.Migrate.MSBuild/     # MSBuild target (generates migrations on build)
  Rhinox.Cli/                 # CLI tool (apply, status, plan, generate)
tests/
  Rhinox.Core.Tests/          # QueryBuilder, ExpressionTranslator, CursorHelper tests
  Rhinox.Generator.Tests/     # Emitter unit tests
  Rhinox.Integration.Tests/   # Schema introspection against real Postgres
  Rhinox.EndToEnd.Tests/      # 190 tests against 100-table e-commerce DB
  Rhinox.Migrate.Core.Tests/  # Parser, differ, replayer, planner tests
  Rhinox.Migrate.Integration.Tests/  # Full migration workflow tests
samples/
  SampleApp/                  # ASP.NET Core e-commerce API
db/
  schema/                     # Desired state (100 tables, 10 enum types)
  seed.sql                    # Full seed for Docker Postgres

What Gets Generated

Type mapping:

Migration Diff Operations

The diff engine detects and generates SQL for:

All generated SQL uses quoted identifiers for safety with reserved words. Destructive operations are marked with -- DESTRUCTIVE for easy review.

pg_cron Jobs

Rhinox can declare and manage pg_cron scheduled jobs alongside schema. Jobs live in db/cron/ as a sibling of db/schema/.

File format

Each file is one job. The filename (without .sql) is the job name. The first non-blank line must be a schedule directive; the rest of the file is the command body.

-- rhinox:schedule 17 2 * * *
CALL partman.run_maintenance_proc();

Run rhinox migrate generate as usual — cron files are diffed against the database and appended to the generated migration alongside table changes.

Prerequisites

Rhinox does NOT install the pg_cron extension. Before using cron support:

1. Set shared_preload_libraries = '...,pg_cron' on the PostgreSQL instance (parameter group on RDS)
2. Set cron.database_name = '<your_db>' to the target database
3. Restart PostgreSQL
4. Run CREATE EXTENSION pg_cron; in the target database as a superuser

If any of these steps are missing, rhinox migrate apply fails with a clear error before touching any state.

Ownership

Rhinox only manages jobs it created. A tracking table _rhinox_cron_jobs records which cron.job rows belong to Rhinox. Jobs scheduled manually, by application code, or by other tools are never touched by Rhinox's diff. This makes db/cron/ safe to use on shared databases where cron.job hosts other tenants' work.

Monitoring

Rhinox does not bundle a healthcheck — monitoring policy is deployment-specific. A reference healthcheck is provided at samples/cron/rhinox-cron-healthcheck.sql. Copy it into your own db/cron/ directory and adapt the surfacing mechanism (CloudWatch log filter, external poller, etc.) to your environment. The sample:

• Runs hourly
• Scans cron.job_run_details for failures in the last 2 hours
• Joins against _rhinox_cron_jobs so noise from non-Rhinox jobs is ignored
• Emits RAISE WARNING with a RHINOX_CRON_FAILURE prefix that's easy to grep for

Turning the warning into an actionable alert is your responsibility. Options include: RDS log export → CloudWatch metric filter → SNS, an application-level log scraper, or a dedicated poller that queries cron.job_run_details directly.

CLI Commands

# Install the CLI tool
dotnet tool install --global Rhinox.Cli

# Bootstrap db/schema/*.sql from an existing database (one-time, then commit the files)
rhinox introspect --connection "Host=...;Database=..." --schema-dir db/schema --schemas public

# Generate a migration (dry run, prints to stdout)
rhinox migrate plan --schema-dir db/schema --migrations-dir db/migrations

# Generate and write migration file
rhinox migrate generate --schema-dir db/schema --migrations-dir db/migrations --name "add_user_phone"

# Apply pending migrations (uses advisory lock for concurrent safety)
rhinox migrate apply --connection "Host=...;Database=..." --migrations-dir db/migrations

# Show migration status
rhinox migrate status --connection "Host=...;Database=..." --migrations-dir db/migrations

Testing

# Start the test database
docker compose up -d

# Run all tests (474 tests)
dotnet test

# Run specific test suites
dotnet test tests/Rhinox.Core.Tests              # QueryBuilder, ExpressionTranslator, pagination
dotnet test tests/Rhinox.Generator.Tests          # Emitter unit tests
dotnet test tests/Rhinox.EndToEnd.Tests            # E2E against 100-table DB
dotnet test tests/Rhinox.Migrate.Core.Tests        # Migration engine unit tests
dotnet test tests/Rhinox.Migrate.Integration.Tests # Migration workflow tests

Architecture

The source generator runs inside the Roslyn compiler process at build time. It parses your db/schema/*.sql files, builds an in-memory model, and emits C# source files that become part of your compilation. No database connection is required to build. The generated code uses Dapper for all database operations — all SQL uses quoted identifiers and parameterized queries. Bootstrapping from an existing database is a one-time CLI operation (rhinox introspect) that writes the SQL files; from then on, those files are the source of truth.

The query builder (QueryBuilder<T>) uses C# expression trees translated to SQL at runtime. It resolves property names to column names via [Column] attributes, supports cursor-based pagination, and produces parameterized queries with LIKE wildcard escaping.

The migration framework is file-based and requires no database connection at build time. It parses your SQL schema files to determine the desired state, replays existing migration files to reconstruct the current state, computes the diff, and generates a new migration file. Migrations are applied via the CLI tool, which tracks applied migrations in a _rhinox_migrations table with SHA-256 checksums and uses PostgreSQL advisory locks for concurrent safety.

Sample App

The samples/SampleApp is an ASP.NET Core Minimal API e-commerce backend powered entirely by Rhinox. It demonstrates:

• DI-injected repositories as the primary data access pattern
• Expression query builder with cursor pagination for product catalog
• Raw SQL via Dapper for aggregate statistics
• Swagger UI at http://localhost:5000/swagger

docker compose up -d
dotnet run --project samples/SampleApp
# API running at http://localhost:5000

License

MIT