Databases in Production

using Microsoft.AspNetCore.Diagnostics.HealthChecks;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Diagnostics.HealthChecks;
using Npgsql;

var builder = WebApplication.CreateBuilder(args);


var connectionString = builder.Configuration.GetConnectionString("Shop")
    ?? throw new InvalidOperationException("ConnectionStrings:Shop is not configured.");
// One data source for EF Core and the health check: one pool, one connection budget.
var dataSource = NpgsqlDataSource.Create(connectionString);
builder.Services.AddSingleton(dataSource);
builder.Services.AddDbContext<ShopDb>(o => o.UseNpgsql(dataSource));

builder.Services.AddHealthChecks()
    .AddCheck("self", () => HealthCheckResult.Healthy(), tags: ["live"])
    // Resolves the registered NpgsqlDataSource, so the check shares the app's pool.
    .AddNpgSql(name: "postgres", tags: ["ready"], timeout: TimeSpan.FromSeconds(2))
    .AddDbContextCheck<ShopDb>("shopdb-context", tags: ["ready"]);

var app = builder.Build();

app.MapHealthChecks("/health/live", new HealthCheckOptions { Predicate = c => c.Tags.Contains("live") });
// The default writer returns only "Healthy" or "Unhealthy", so failure details never reach the body.
app.MapHealthChecks("/health/ready", new HealthCheckOptions { Predicate = c => c.Tags.Contains("ready") });

app.Run();

public sealed class ShopDb(DbContextOptions<ShopDb> options) : DbContext(options);

using Dapper;
using Npgsql;
using Polly;
using Polly.Registry;
using Polly.Retry;
using Polly.Timeout;

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddSingleton(NpgsqlDataSource.Create(builder.Configuration.GetConnectionString("Shop")
    ?? throw new InvalidOperationException("ConnectionStrings:Shop is not configured.")));

// The first strategy added is the outermost: 5 s for the whole call, 2 s per attempt.
builder.Services.AddResiliencePipeline("db-read", pipeline => pipeline
    .AddTimeout(TimeSpan.FromSeconds(5))
    .AddRetry(new RetryStrategyOptions
    {
        // Covers dropped connections during failover, deadlock (40P01), serialization failure (40001),
        // and an attempt cut off by the inner timeout.
        ShouldHandle = new PredicateBuilder()
            .Handle<NpgsqlException>(e => e.IsTransient)
            .Handle<TimeoutRejectedException>(),
        MaxRetryAttempts = 3,
        Delay = TimeSpan.FromMilliseconds(200),
        BackoffType = DelayBackoffType.Exponential,
        UseJitter = true,
    })
    .AddTimeout(TimeSpan.FromSeconds(2)));

var app = builder.Build();

app.MapGet("/products/{sku}/stock", async (string sku, NpgsqlDataSource db,
    ResiliencePipelineProvider<string> pipelines, CancellationToken ct) =>
{
    var pipeline = pipelines.GetPipeline("db-read");
    var onHand = await pipeline.ExecuteAsync(async token =>
    {
        // A new connection per attempt: a broken one is never reused for the retry.
        await using var connection = await db.OpenConnectionAsync(token);
        return await connection.ExecuteScalarAsync<int?>(new CommandDefinition(
            "SELECT on_hand FROM inventory WHERE sku = @sku", new { sku }, cancellationToken: token));
    }, ct);
    return onHand is null ? Results.NotFound() : Results.Ok(new { sku, onHand });
});

app.Run();

using Dapper;
using Microsoft.Extensions.DependencyInjection;
using Npgsql;

var builder = WebApplication.CreateBuilder(args);

// e.g. Host=pg-1,pg-2,pg-3;Database=shop;Username=orders_api;Password=<from-secret-store>;Load Balance Hosts=true
var cluster = new NpgsqlDataSourceBuilder(builder.Configuration.GetConnectionString("ShopCluster")
    ?? throw new InvalidOperationException("ConnectionStrings:ShopCluster is not configured.")).BuildMultiHost();
builder.Services.AddSingleton(cluster);
builder.Services.AddKeyedSingleton<NpgsqlDataSource>("primary", cluster.WithTargetSession(TargetSessionAttributes.Primary));
builder.Services.AddKeyedSingleton<NpgsqlDataSource>("replica", cluster.WithTargetSession(TargetSessionAttributes.PreferStandby));

var app = builder.Build();

// Reports tolerate seconds of lag: replica.
app.MapGet("/reports/top-products", async ([FromKeyedServices("replica")] NpgsqlDataSource db, CancellationToken ct) =>
{
    await using var connection = await db.OpenConnectionAsync(ct);
    return Results.Ok(await connection.QueryAsync<(string Sku, long Units)>(new CommandDefinition(
        "SELECT sku, sum(quantity) FROM order_lines GROUP BY sku ORDER BY 2 DESC LIMIT 10", cancellationToken: ct)));
});

// A client reading its own order right after POST /orders must see it: primary.
app.MapGet("/orders/{id:long}/status", async (long id, [FromKeyedServices("primary")] NpgsqlDataSource db, CancellationToken ct) =>
{
    await using var connection = await db.OpenConnectionAsync(ct);
    var status = await connection.ExecuteScalarAsync<string?>(new CommandDefinition(
        "SELECT status FROM orders WHERE id = @id", new { id }, cancellationToken: ct));
    return status is null ? Results.NotFound() : Results.Ok(new { id, status });
});

app.Run();