A powerful .NET 8 database manager library that implements the cache-aside pattern using PostgreSQL (via SqlKata) and Redis for caching. Supports generic repositories, query builders, and automatic cache invalidation.
$ dotnet add package DatabaseEngineA powerful .NET 8 database manager library that implements the cache-aside pattern using PostgreSQL (via SqlKata) and Redis for caching.
[CacheSettings]Add the DatabaseEngine project reference to your application:
dotnet add reference ../DatabaseEngine/DatabaseEngine.csproj
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();
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; }
}
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);
}
}
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
}
}
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>();
}
}
public class DatabaseOptions
{
public string ConnectionString { get; set; } // PostgreSQL connection string
public int CommandTimeout { get; set; } = 30; // Timeout in seconds
}
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
}
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
}
The library uses a consistent key naming convention:
{prefix}:entity:{EntityType}:{Id}{prefix}:query:{EntityType}:{QueryHash}{prefix}:querylist:{EntityType} (for invalidation tracking){prefix}:custom:{key}Example:
myapp:entity:Product:123
myapp:query:Product:abc123def456...
myapp:querylist:Product
myapp:custom:popular:products
When you write (insert/update/delete) to an entity:
This ensures consistency between cache and database at the cost of invalidating more than strictly necessary.
By default, the library assumes table names are the lowercase plural of the entity type name:
Product → productsCategory → categoriesIf you need custom table names, you can extend the CachedRepository<T> class and override the GetTableName() method.
Id property (case-sensitive)MIT