DatabaseEngine 1.0.0

DatabaseEngine

A powerful .NET 8 database manager library that implements the cache-aside pattern using PostgreSQL (via SqlKata) and Redis for caching.

Features

• Hybrid Data Access Pattern: Combine generic repository, query builder, and manual cache control
• Automatic Caching: Entity operations automatically cache and invalidate
• Attribute-Based Configuration: Configure TTL per entity type with [CacheSettings]
• Query Result Caching: Cache complex SqlKata query results
• Redis Resilience: Automatic fallback to database if Redis is unavailable
• Manual Invalidation: Fine-grained control over cache invalidation
• Type-Safe: Full generic support with compile-time type checking

Installation

Add the DatabaseEngine project reference to your application:

dotnet add reference ../DatabaseEngine/DatabaseEngine.csproj

Quick Start

1. Configure Services

In your Program.cs or Startup.cs:

using DatabaseEngine.Extensions;

var builder = WebApplication.CreateBuilder(args);

// Add DatabaseEngine services
builder.Services.AddDatabaseEngine(
    databaseConnectionString: "Host=localhost;Database=mydb;Username=postgres;Password=password",
    redisConnectionString: "localhost:6379",
    configureCache: options =>
    {
        options.DefaultTtlSeconds = 300;  // 5 minutes default TTL
        options.EnableLogging = true;     // Enable cache hit/miss logging
        options.KeyPrefix = "myapp";      // Custom key prefix
    }
);

var app = builder.Build();

2. Define Your Entities

using DatabaseEngine.Attributes;

// Cache for 5 minutes
[CacheSettings(TtlSeconds = 300)]
public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
    public int CategoryId { get; set; }
}

// Cache for 1 hour
[CacheSettings(TtlSeconds = 3600)]
public class Category
{
    public int Id { get; set; }
    public string Name { get; set; }
}

// Disable caching for this entity
[CacheSettings(Enabled = false)]
public class AuditLog
{
    public int Id { get; set; }
    public string Action { get; set; }
    public DateTime Timestamp { get; set; }
}

3. Use the Repository (Simple CRUD)

using DatabaseEngine.Interfaces;

public class ProductService
{
    private readonly ICachedRepository<Product> _productRepo;

    public ProductService(ICachedRepository<Product> productRepo)
    {
        _productRepo = productRepo;
    }

    public async Task<Product?> GetProductAsync(int id)
    {
        // Checks Redis first, falls back to DB
        return await _productRepo.GetByIdAsync(id);
    }

    public async Task<IEnumerable<Product>> GetAllProductsAsync()
    {
        // Cached query result
        return await _productRepo.GetAllAsync();
    }

    public async Task<Product> CreateProductAsync(Product product)
    {
        // Inserts and caches the new entity
        return await _productRepo.InsertAsync(product);
    }

    public async Task UpdateProductAsync(int id, Product product)
    {
        // Updates and invalidates cache
        await _productRepo.UpdateAsync(product, id);
    }

    public async Task DeleteProductAsync(int id)
    {
        // Deletes and invalidates cache
        await _productRepo.DeleteAsync(id);
    }
}

4. Use Query Builder (Complex Queries)

using DatabaseEngine.Extensions;
using SqlKata.Execution;

public class ProductSearchService
{
    private readonly QueryFactory _db;

    public ProductSearchService(QueryFactory db)
    {
        _db = db;
    }

    public async Task<IEnumerable<Product>> SearchProductsAsync(string searchTerm, int categoryId)
    {
        var query = _db.Query("products")
            .Where("category_id", categoryId)
            .WhereContains("name", searchTerm)
            .OrWhereContains("description", searchTerm)
            .OrderByDesc("created_at");

        // Cache results for 10 minutes
        return await query.GetCachedAsync<Product>(TimeSpan.FromMinutes(10));
    }

    public async Task<Product?> GetFeaturedProductAsync()
    {
        var query = _db.Query("products")
            .Where("is_featured", true)
            .OrderByDesc("popularity");

        // Cache single result for 1 hour
        return await query.FirstOrDefaultCachedAsync<Product>(TimeSpan.FromHours(1));
    }

    // Direct query (bypass cache)
    public async Task<IEnumerable<Product>> GetRealtimeInventoryAsync()
    {
        return await _db.Query("products")
            .Where("stock", ">", 0)
            .GetAsync<Product>();  // No caching
    }
}

5. Manual Cache Control

using DatabaseEngine.Interfaces;

public class BulkProductService
{
    private readonly QueryFactory _db;
    private readonly ICacheInvalidator _cache;

    public BulkProductService(QueryFactory db, ICacheInvalidator cache)
    {
        _db = db;
        _cache = cache;
    }

    // Scenario 1: Bulk update with manual invalidation
    public async Task BulkUpdatePricesAsync(Dictionary<int, decimal> priceUpdates)
    {
        // Direct DB update for performance
        foreach (var (productId, newPrice) in priceUpdates)
        {
            await _db.Query("products")
                .Where("id", productId)
                .UpdateAsync(new { price = newPrice });

            // Invalidate specific entity
            await _cache.InvalidateEntityAsync<Product>(productId);
        }

        // Clear all product query caches
        await _cache.InvalidateAllQueriesAsync<Product>();
    }

    // Scenario 2: External system updated data
    public async Task OnExternalInventoryUpdateAsync(int productId)
    {
        await _cache.InvalidateEntityAsync<Product>(productId);
    }

    // Scenario 3: Cache warming with popular items
    public async Task WarmCacheWithPopularProductsAsync()
    {
        var popularProducts = await _db.Query("products")
            .OrderByDesc("view_count")
            .Limit(100)
            .GetAsync<Product>();

        await _cache.SetCustomAsync("popular:products", popularProducts, TimeSpan.FromHours(1));
    }

    // Scenario 4: Get custom cached data
    public async Task<IEnumerable<Product>?> GetPopularProductsFromCacheAsync()
    {
        return await _cache.GetCustomAsync<IEnumerable<Product>>("popular:products");
    }

    // Scenario 5: Complete cache wipe for entity type
    public async Task ClearAllProductCachesAsync()
    {
        await _cache.InvalidateAllAsync<Product>();
    }
}

Configuration Options

Database Options

public class DatabaseOptions
{
    public string ConnectionString { get; set; }  // PostgreSQL connection string
    public int CommandTimeout { get; set; } = 30; // Timeout in seconds
}

Redis Options

public class RedisOptions
{
    public string ConnectionString { get; set; } = "localhost:6379";
    public int DefaultDatabase { get; set; } = 0;
    public int ConnectTimeout { get; set; } = 5000;        // milliseconds
    public bool AbortOnConnectFail { get; set; } = false;  // false = failover to DB
}

Cache Options

public class CacheOptions
{
    public int DefaultTtlSeconds { get; set; } = 300;     // Default 5 minutes
    public bool Enabled { get; set; } = true;              // Global enable/disable
    public string KeyPrefix { get; set; } = "dbcache";     // Redis key prefix
    public bool EnableLogging { get; set; } = false;       // Log cache hits/misses
}

Redis Key Structure

The library uses a consistent key naming convention:

• Entity by ID: {prefix}:entity:{EntityType}:{Id}
• Query results: {prefix}:query:{EntityType}:{QueryHash}
• Query list: {prefix}:querylist:{EntityType} (for invalidation tracking)
• Custom keys: {prefix}:custom:{key}

Example:

myapp:entity:Product:123
myapp:query:Product:abc123def456...
myapp:querylist:Product
myapp:custom:popular:products

Cache Invalidation Strategy

When you write (insert/update/delete) to an entity:

1. The specific entity cache is invalidated
2. ALL query caches for that entity type are invalidated

This ensures consistency between cache and database at the cost of invalidating more than strictly necessary.

Error Handling

• Redis unavailable: Automatic fallback to direct database access (no exceptions thrown)
• Redis errors: Logged but don't break operations
• Database errors: Propagated to caller as usual

Table Naming Convention

By default, the library assumes table names are the lowercase plural of the entity type name:

• Product → products
• Category → categories

If you need custom table names, you can extend the CachedRepository<T> class and override the GetTableName() method.

Best Practices

1. Use attributes for TTL: Configure cache duration per entity type based on how often data changes
2. Query caching: Use for expensive queries that don't need real-time data
3. Manual invalidation: Use for bulk operations or external data updates
4. Monitoring: Enable logging during development to understand cache behavior
5. Key prefix: Use unique prefixes for different environments (dev, staging, prod)

Limitations

• Assumes entities have an Id property (case-sensitive)
• Table names follow lowercase plural convention
• PostgreSQL only (easily extendable to other databases by changing the compiler)

License

MIT