db.md

Agoda.Frameworks.DB

ORM (Dapper) + Load Balancing + Caching

Agoda.Frameworks.DB is a framework for executing stored procedures with Dapper, and handles both load balancing and caching inside of the framework.

Example

Create DbResourceManager

public static DbResourceManager CreateDbResourceManager()
{
    return DbResourceManager.Create(new Dictionary<string, string[]>
    {
        // ["DB Name"] = string[]{ "Connection strings" }
        ["mobile_ro"] = new[] {
            "Data Source=SERVER-0001;Database=mobile_ro;Integrated Security=true",
            "Data Source=SERVER-0002;Database=mobile_ro;Integrated Security=true",
            "Data Source=SERVER-0003;Database=mobile_ro;Integrated Security=true"
        },
        ["db_02"] = new[] { "Data Source=SERVER-0001;Database=db_02;Integrated Security=true" },
    });
}

Main Program

class Program
{
    static async Task Main(string[] args)
    {
        var dbRepo = new DbRepository(CreateDbResourceManager());
        
        // CmsRetrievalStoredProc is readonly metadata, always remains unchanged.
        // CmsRetrievalParam is parameter that sends to DB.
        var rows = await dbRepo.QueryAsync(CmsRetrievalStoredProc.Default, new CmsRetrievalParam()
        {
            CmsId = 11045,
            Language = "en-us"
        });
        // Output
        foreach (var result in rows)
        {
            Console.WriteLine(result.cms_item_id);
            Console.WriteLine(result.cms_data);
        }
    }
}

Define stored procedure

// Parameter type. Can be replaced with dynamic or anonymous type.
public sealed class CmsRetrievalParam
{
    public string Language { get; set; }
    public int CmsId { get; set; }
}

public class CmsRetrievalStoredProc : IStoredProc<CmsRetrievalParam, CmsRetrievalStoredProc.CmsRetrievalVariantResult>
{
    // Read-only singleton
    public static CmsRetrievalStoredProc Default { get; } = new CmsRetrievalStoredProc();
    private CmsRetrievalStoredProc() { }

    // Source name for ResourceManager
    public string DbName => "mobile_ro";
    // Stored procedure name
    public string StoredProcedureName => "mobile_consumer_cms_fetch_v2";
    // Timeout in seconds
    public int CommandTimeoutSecs => 5;
    // Retry count
    public int MaxAttemptCount => 5;
    // Lifespan for cache. Set null for disabling cache.
    public TimeSpan? CacheLifetime => TimeSpan.FromHours(2);

    // Parameters for generating key for caching.
    // *ALWAYS* define this method in the following pattern:
    // new SpParameter(nameof(req.{{Property}}), req.{{Property}}),
    public SpParameter[] GetParameters(CmsRetrievalParam req) => new[]
    {
        new SpParameter(nameof(req.Language), req.Language),
        new SpParameter(nameof(req.CmsId), req.CmsId)
    };

    // Result template for stored procedure.
    public sealed class CmsRetrievalVariantResult
    {
        public int cms_item_id { get; set; }
        public string cms_data { get; set; }
    }
}

Caching

Define any types of cache mechanism by implmenting IDbCache. MemoryCache from Microsoft.Extensions.Caching.Memory is recommended for simple in-memory cache.

DbRepository sets the cache when the query is successful. The key for the cache is generated by the method GetParameters of IStoredProc<,>.

Example of MemoryCache implementation

public sealed class DbMemoryCache : IDbCache
{
    // Microsoft.Extensions.Caching.Memory
    private readonly MemoryCache _cache = new MemoryCache(new MemoryCacheOptions());
    public bool TryGetValue(string key, out object value)
    {
        return _cache.TryGetValue(key, out value);
    }

    public void CreateEntry(string key, object value, TimeSpan? expirationRelativeToNow)
    {
        if (expirationRelativeToNow.HasValue)
        {
            _cache.Set(key, value, expirationRelativeToNow.Value);
        }
        else
        {
            _cache.Set(key, value);
        }
    }
}
// Usage: var dbRepo = new DbRepository(CreateDbResourceManager(), new DbMemoryCache());

Load Balancing

DbRepository uses ResourceManager internally. Check Agoda.Frameworks.LoadBalancing for further information.