WebVella.Database 1.2.3

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
• 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

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 a list of parent entities with child collections automatically mapped:

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;
}

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;
";

// Each order will have its Lines and Notes collections populated
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 to use a single SQL query with JOINs instead of multiple result sets:

// 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 });

// 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