WebVella.Database 1.2.4

Checkout our other projects:
WebVella ERP
Data collaboration - Tefter.bg
Document template generation

What is WebVella.Database?

A lightweight, high-performance Postgres data access library built on Dapper. It simplifies data object mapping, migrations and complex database workflows by providing first-class support for nested transactions and effortless advisory lock management.

How to get it

You can either clone this repository or get the Nuget package

Please help by giving a star

GitHub stars guide developers toward great tools. If you find this project valuable, please give it a star – it helps the community and takes just a second!⭐

Features

• Dapper-based CRUD operations - Simple Insert, Update, Delete, Get, and Query methods
• Fluent query builder - Type-safe, expression-based queries with WHERE, ORDER BY, paging, COUNT, and EXISTS — no SQL strings needed
• SQL-free parent-child queries - QueryMultipleList<T>() and QueryWithJoin<T>() auto-generate SQL from entity metadata
• Nested transaction support - Create transaction scopes that properly handle nesting
• PostgreSQL advisory locks - Easy-to-use advisory lock scopes for distributed locking
• Row Level Security (RLS) - Built-in support for PostgreSQL RLS with automatic session context
• Entity caching - Optional in-memory caching with automatic invalidation (RLS-aware)
• JSON column support - Automatic serialization/deserialization of JSON columns
• Attribute-based mapping - Use attributes like [Table], [Key], [JsonColumn], and more
• Database migrations - Version-controlled schema migrations with rollback support

Setup

Basic Registration

using WebVella.Database;

var builder = WebApplication.CreateBuilder(args);

// Add WebVella.Database services
builder.Services.AddWebVellaDatabase("Host=localhost;Database=mydb;Username=user;Password=pass");

With Entity Caching

builder.Services.AddWebVellaDatabase(
    "Host=localhost;Database=mydb;Username=user;Password=pass",
    enableCaching: true);

With Factory Pattern

builder.Services.AddWebVellaDatabase(
    sp => sp.GetRequiredService<IConfiguration>().GetConnectionString("DefaultConnection")!);

With Row Level Security (RLS)

For multi-tenant applications, enable RLS to automatically set PostgreSQL session variables:

// Implement IRlsContextProvider to provide tenant/user context
public class HttpRlsContextProvider : IRlsContextProvider
{
    private readonly IHttpContextAccessor _httpContextAccessor;

    public HttpRlsContextProvider(IHttpContextAccessor httpContextAccessor)
    {
        _httpContextAccessor = httpContextAccessor;
    }

    public Guid? TenantId => GetClaimAsGuid("tenant_id");
    public Guid? UserId => GetClaimAsGuid("sub");
    public IReadOnlyDictionary<string, string> CustomClaims => new Dictionary<string, string>
    {
        ["role"] = GetClaim("role") ?? "user"
    };

    private Guid? GetClaimAsGuid(string type) =>
        Guid.TryParse(_httpContextAccessor.HttpContext?.User?.FindFirst(type)?.Value, out var g) ? g : null;
    private string? GetClaim(string type) =>
        _httpContextAccessor.HttpContext?.User?.FindFirst(type)?.Value;
}

// Register with RLS support
builder.Services.AddWebVellaDatabaseWithRls<HttpRlsContextProvider>(connectionString);

// Or with caching and custom options
builder.Services.AddWebVellaDatabaseWithRls<HttpRlsContextProvider>(
    connectionString,
    enableCaching: true,
    rlsOptions: new RlsOptions { Prefix = "app" });

Each connection automatically sets session variables that PostgreSQL RLS policies can use:

-- Create RLS policy using the session variable
CREATE POLICY tenant_isolation ON orders
    USING (tenant_id = current_setting('app.tenant_id', true)::uuid);

Usage Examples

Define an Entity

using WebVella.Database;

[Table("users")]
[Cacheable(DurationSeconds = 600)]
public class User
{
    [Key]
    public Guid Id { get; set; }

    public string Name { get; set; } = string.Empty;

    public string Email { get; set; } = string.Empty;

    [JsonColumn]
    public UserSettings? Settings { get; set; }

    [External]
    public List<Order>? Orders { get; set; }
}

public class UserSettings
{
    public string Theme { get; set; } = "light";
    public bool NotificationsEnabled { get; set; } = true;
}

Basic CRUD Operations

public class UserService
{
    private readonly IDbService _db;

    public UserService(IDbService db)
    {
        _db = db;
    }

    // Insert - returns the entity with generated Id populated
    public async Task<User> CreateUserAsync(User user)
    {
        return await _db.InsertAsync(user);
    }

    // Get by ID
    public async Task<User?> GetUserAsync(Guid id)
    {
        return await _db.GetAsync<User>(id);
    }

    // Get by composite key using anonymous object
    public async Task<UserRole?> GetUserRoleAsync(Guid userId, Guid roleId)
    {
        return await _db.GetAsync<UserRole>(new { UserId = userId, RoleId = roleId });
    }

    // Get all
    public async Task<IEnumerable<User>> GetAllUsersAsync()
    {
        return await _db.GetListAsync<User>();
    }

    // Update
    public async Task<bool> UpdateUserAsync(User user)
    {
        return await _db.UpdateAsync(user);
    }

    // Update specific properties only
    public async Task<bool> UpdateUserEmailAsync(User user)
    {
        return await _db.UpdateAsync(user, ["Email"]);
    }

    // Delete
    public async Task<bool> DeleteUserAsync(Guid id)
    {
        return await _db.DeleteAsync<User>(id);
    }

    // Delete by composite key using anonymous object
    public async Task<bool> DeleteUserRoleAsync(Guid userId, Guid roleId)
    {
        return await _db.DeleteAsync<UserRole>(new { UserId = userId, RoleId = roleId });
    }
}

Fluent Query Builder

Query<T>() provides an expression-based builder that generates parameterised SQL without writing SQL strings. Chain Where, OrderBy, Limit, Offset, or WithPaging, then call a terminal method.

// Filter, sort, and limit
var pendingOrders = await _db.Query<Order>()
    .Where(o => o.Status == OrderStatus.Pending && o.TotalAmount > 50m)
    .OrderByDescending(o => o.CreatedAt)
    .Limit(20)
    .ToListAsync();

// Multiple Where calls are combined with AND
var results = await _db.Query<User>()
    .Where(u => u.IsActive)
    .Where(u => u.Email != null)
    .ToListAsync();

// String matching — LIKE (case-sensitive) and ILIKE (case-insensitive)
var byPrefix   = await _db.Query<User>().Where(u => u.Name.StartsWith("John")).ToListAsync();
var caseSearch = await _db.Query<User>().Where(u => u.Name.ILikeContains("admin")).ToListAsync();

// Collection membership → id = ANY(@p)
var selected = await _db.Query<Order>()
    .Where(o => orderIds.Contains(o.Id))
    .ToListAsync();

// Page-number pagination
var page = await _db.Query<Order>()
    .Where(o => o.IsActive)
    .OrderBy(o => o.CreatedAt)
    .WithPaging(page: 2, pageSize: 25)
    .ToListAsync();

// Aggregate and existence checks
long count  = await _db.Query<Order>().Where(o => o.IsActive).CountAsync();
bool exists = await _db.Query<Order>().Where(o => o.Id == id).ExistsAsync();

// First matching row or null
var user = await _db.Query<User>()
    .Where(u => u.Email == email)
    .FirstOrDefaultAsync();

Supported WHERE patterns:

• Equality / comparison: e.Price > 10, e.Name != null, e.Status == Status.Active
• Logical operators: &&, ||, !
• Boolean shorthand: e.IsActive, !e.IsActive
• Null checks: e.Description == null → IS NULL
• String: .Contains(), .StartsWith(), .EndsWith() → LIKE
• Case-insensitive: .ILikeContains(), .ILikeStartsWith(), .ILikeEndsWith() → ILIKE
• Case-folding: e.Name.ToLower() == "x" → LOWER(name) = @p
• Collection: list.Contains(e.Id) → id = ANY(@p)
• Enum values are automatically mapped to their underlying int

SQL-Free Parent-Child Queries

QueryMultipleList<T>() and QueryWithJoin<T>() auto-generate SQL from entity metadata — no SQL strings needed. Entity [Table], [Key], and [ResultSet(ForeignKey)] attributes provide everything needed.

// QueryMultipleList — auto-generates multiple SELECTs
// Children are automatically filtered to match parent conditions
var orders = await _db.QueryMultipleList<Order>()
    .Where(o => o.Status == OrderStatus.Active)
    .OrderByDescending(o => o.CreatedAt)
    .Limit(50)
    .ToListAsync();
// Each order has its Lines and Notes collections populated

// QueryWithJoin — auto-generates a single JOIN query
// ChildSelector, ParentKey, ChildKey, SplitOn all auto-derived
var orders = await _db.QueryWithJoin<Order, OrderLine>()
    .Where(o => o.CustomerId == customerId)
    .OrderBy(o => o.CreatedAt)
    .ToListAsync();

// Two children — also fully auto-derived
var orders = await _db
    .QueryWithJoin<Order, OrderLine, OrderNote>()
    .Where(o => o.TotalAmount > 100m)
    .WithPaging(page: 2, pageSize: 25)
    .ToListAsync();

All builders also support raw SQL via .Sql() for full control when needed. See the complete documentation for details.

Custom SQL and Commands

For advanced queries or bulk operations that require raw SQL:

// Query with raw SQL and parameters
var activeUsers = await _db.QueryAsync<User>(
    "SELECT * FROM users WHERE is_active = @IsActive",
    new { IsActive = true });

// Execute commands
var rowsAffected = await _db.ExecuteAsync(
    "UPDATE users SET last_login = @Now WHERE id = @Id",
    new { Now = DateTime.UtcNow, Id = userId });

Transaction Scope

// Simple transaction
await using var scope = await _db.CreateTransactionScopeAsync();

await _db.InsertAsync(new User { Name = "John" });
await _db.InsertAsync(new Order { UserId = userId, Amount = 100 });

await scope.CompleteAsync(); // Commit transaction

// Nested transactions are automatically handled
await using var outerScope = await _db.CreateTransactionScopeAsync();
{
    await _db.InsertAsync(user);

    await using var innerScope = await _db.CreateTransactionScopeAsync();
    {
        await _db.InsertAsync(order);
        await innerScope.CompleteAsync();
    }

    await outerScope.CompleteAsync();
}

Transaction with Advisory Lock

// Acquire advisory lock with transaction
await using var scope = await _db.CreateTransactionScopeAsync(lockKey: 12345L);

// Or use a string key (automatically hashed)
await using var scope = await _db.CreateTransactionScopeAsync(lockKey: "user-update-lock");

// Perform operations with exclusive lock
await _db.UpdateAsync(user);

await scope.CompleteAsync();

Advisory Lock Scope (without transaction)

// Acquire advisory lock without transaction
await using var lockScope = await _db.CreateAdvisoryLockScopeAsync(lockKey: 12345L);

// Perform operations with exclusive lock
var user = await _db.GetAsync<User>(userId);
user.Balance += 100;
await _db.UpdateAsync(user);

await lockScope.CompleteAsync();

Database Migrations

WebVella.Database provides a simple yet powerful migration system for managing database schema changes. Migrations automatically bypass RLS to ensure unrestricted schema access.

Migration Setup

// Register migration services
builder.Services.AddWebVellaDatabase(connectionString);
builder.Services.AddWebVellaDatabaseMigrations();

// Or with custom options
builder.Services.AddWebVellaDatabaseMigrations(options =>
{
    options.VersionTableName = "_my_db_version";
});

Creating Migrations

Create a class that inherits from DbMigration and apply the [DbMigration] attribute:

using WebVella.Database.Migrations;

[DbMigration("1.0.0.0")]
public class InitialSchema : DbMigration
{
    public override Task<string> GenerateSqlAsync(IServiceProvider serviceProvider)
    {
        return Task.FromResult("""
            CREATE TABLE users (
                id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
                email VARCHAR(255) NOT NULL UNIQUE,
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
            );
            """);
    }
}

Using Embedded SQL Files

For larger migrations, use embedded SQL resource files:

[DbMigration("1.0.1.0")]
public class AddUserProfile : DbMigration { }
// Requires: AddUserProfile.Script.sql as embedded resource in same namespace

Pre-Migration Logic

Execute custom code before migration SQL runs:

[DbMigration("1.0.2.0")]
public class RebuildStatusIndex : DbMigration
{
    public override async Task PreMigrateAsync(IServiceProvider serviceProvider)
    {
        var db = serviceProvider.GetRequiredService<IDbService>();
        await db.ExecuteAsync("DROP INDEX IF EXISTS idx_orders_status;");
    }

    public override Task<string> GenerateSqlAsync(IServiceProvider serviceProvider)
    {
        return Task.FromResult(
            "CREATE INDEX idx_orders_status_priority ON orders(status, priority);");
    }
}

Post-Migration Logic

Execute custom code after SQL migration:

[DbMigration("1.0.2.0")]
public class SeedData : DbMigration
{
    public override Task<string> GenerateSqlAsync(IServiceProvider serviceProvider)
    {
        return Task.FromResult("CREATE TABLE settings (key TEXT PRIMARY KEY, value TEXT);");
    }

    public override async Task PostMigrateAsync(IServiceProvider serviceProvider)
    {
        var db = serviceProvider.GetRequiredService<IDbService>();
        await db.ExecuteAsync("INSERT INTO settings VALUES ('app_version', '1.0.0')");
    }
}

Running Migrations

// In Program.cs or startup
using var scope = app.Services.CreateScope();
var migrationService = scope.ServiceProvider.GetRequiredService<IDbMigrationService>();
await migrationService.ExecutePendingMigrationsAsync();

Entity Attributes

Multi-Query Support

QueryMultiple with Container Class

Use QueryMultiple<T> with a container class to map multiple result sets:

[MultiQuery]
public class UserDashboard
{
    [ResultSet(0)]
    public User? Profile { get; set; }

    [ResultSet(1)]
    public List<Order> RecentOrders { get; set; } = [];

    [ResultSet(2)]
    public List<Notification> Alerts { get; set; } = [];
}

var sql = @"
    SELECT * FROM users WHERE id = @UserId;
    SELECT * FROM orders WHERE user_id = @UserId ORDER BY created_at DESC LIMIT 10;
    SELECT * FROM notifications WHERE user_id = @UserId AND is_read = false;
";

var dashboard = await _db.QueryMultipleAsync<UserDashboard>(sql, new { UserId = userId });

QueryMultipleList with Parent-Child Mapping

Use QueryMultipleList<T> to fetch parent entities with child collections. The recommended approach uses the SQL-free fluent builder:

public class Order
{
    [Key]
    public Guid Id { get; set; }

    public string CustomerName { get; set; } = string.Empty;

    public decimal TotalAmount { get; set; }

    [External]
    [ResultSet(1, ForeignKey = "OrderId")]
    public List<OrderLine> Lines { get; set; } = [];

    [External]
    [ResultSet(2, ForeignKey = "OrderId")]
    public List<OrderNote> Notes { get; set; } = [];
}

public class OrderLine
{
    public Guid Id { get; set; }
    public Guid OrderId { get; set; }  // Foreign key to Order.Id
    public string ProductName { get; set; } = string.Empty;
    public int Quantity { get; set; }
}

public class OrderNote
{
    public Guid Id { get; set; }
    public Guid OrderId { get; set; }  // Foreign key to Order.Id
    public string Text { get; set; } = string.Empty;
}

// SQL-free — auto-generates all SELECTs from metadata
var orders = await _db.QueryMultipleList<Order>()
    .Where(o => o.CustomerName == "Acme Corp")
    .OrderByDescending(o => o.TotalAmount)
    .ToListAsync();

// Raw SQL — for full control
var sql = @"
    SELECT * FROM orders WHERE customer_id = @CustomerId;
    SELECT ol.* FROM order_lines ol 
        JOIN orders o ON ol.order_id = o.id WHERE o.customer_id = @CustomerId;
    SELECT n.* FROM order_notes n 
        JOIN orders o ON n.order_id = o.id WHERE o.customer_id = @CustomerId;
";
var orders = await _db.QueryMultipleListAsync<Order>(sql, new { CustomerId = customerId });

The [ResultSet] attribute supports:

• Index - The result set index (0-based, first result set is parent entities)
• ForeignKey - The property name in child entity that references parent's key
• ParentKey - The parent's key property name (defaults to "Id")

QueryWithJoin for Single Query Mapping

Use QueryWithJoin when you want a single SQL query with JOINs. The recommended approach uses the SQL-free fluent builder:

// SQL-free — single child, everything auto-derived
var orders = await _db.QueryWithJoin<Order, OrderLine>()
    .Where(o => o.CustomerId == customerId)
    .OrderBy(o => o.CreatedAt)
    .ToListAsync();

// SQL-free — two children
var orders = await _db
    .QueryWithJoin<Order, OrderLine, OrderNote>()
    .Where(o => o.CustomerId == customerId)
    .ToListAsync();

Raw SQL mode is also supported for full control:

// Raw SQL — single child collection
var sql = @"
    SELECT o.*, l.*
    FROM orders o
    LEFT JOIN order_lines l ON l.order_id = o.id
    WHERE o.customer_id = @CustomerId
    ORDER BY o.id, l.id
";

var orders = await _db.QueryWithJoinAsync<Order, OrderLine>(
    sql,
    parent => parent.Lines,           // Child collection selector
    parent => parent.Id,              // Parent key selector
    child => child.Id,                // Child primary key selector (for deduplication)
    splitOn: "Id",                    // Column to split results on
    parameters: new { CustomerId = customerId });

// Raw SQL — two child collections (handles Cartesian product deduplication)
var sql = @"
    SELECT o.*, l.*, n.*
    FROM orders o
    LEFT JOIN order_lines l ON l.order_id = o.id
    LEFT JOIN order_notes n ON n.order_id = o.id
    ORDER BY o.id
";

var orders = await _db.QueryWithJoinAsync<Order, OrderLine, OrderNote>(
    sql,
    parent => parent.Lines,
    parent => parent.Notes,
    parent => parent.Id,
    child1 => child1.Id,              // Child1 primary key (for deduplication)
    child2 => child2.Id,              // Child2 primary key (for deduplication)
    splitOn: "Id,Id");

Note: The splitOn parameter tells Dapper where to split the columns between entity types. For multiple children, use comma-separated column names. The child key selectors should return each child's unique identifier (primary key) for proper deduplication when JOINs produce Cartesian products.

Documentation

View complete documentation

License