WebVella.Database 1.0.0

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 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
• Nested transaction support - Create transaction scopes that properly handle nesting
• PostgreSQL advisory locks - Easy-to-use advisory lock scopes for distributed locking
• Entity caching - Optional in-memory caching with automatic invalidation
• JSON column support - Automatic serialization/deserialization of JSON columns
• Attribute-based mapping - Use attributes like [Table], [Key], [JsonColumn], and more

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

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
    public async Task<Guid> CreateUserAsync(User user)
    {
        var keys = await _db.InsertAsync(user);
        return keys["Id"];
    }

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

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

Custom Queries

// Query with 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();

Entity Attributes

License