/DB

ASP.NET Core

Integrate Socigy.OpenSource.DB into an ASP.NET Core application using the generated DI extension methods.

updated 3 May 20263 min readv0.1.82

Prerequisites

  • socigy.json configured in your DB class library with generateDbConnectionFactory: true and generateWebAppExtensions: true
  • At least one successful dotnet build -c DB_Migration run (generates the DI extension methods)
  • Your API project references the DB class library

Registering the database

Call the generated AddAuthDb() extension in Program.cs. Three overloads are available depending on your host builder type:

// WebApplicationBuilder (most common for ASP.NET Core)
builder.AddAuthDb();

// IServiceCollection (generic host, test fixtures, etc.)
builder.Services.AddAuthDb();

// HostApplicationBuilder (generic host)
builder.AddAuthDb();

Replace AuthDb with whatever databaseName you set in socigy.json.

What gets registered

Service Lifetime Key
IDbConnectionFactory Singleton "AuthDb"
IMigrationManager Singleton "AuthDb"

Both services are registered as keyed services. If you have multiple databases, call AddAuthDb and AddUserDb (and so on) independently — each registers under its own key.

appsettings.json

The generated factory reads the connection string from ConnectionStrings.{databaseName}:

{
  "ConnectionStrings": {
    "AuthDb": {
      "Default": "Host=localhost;Port=5432;Username=app;Password=secret;Pooling=true;Minimum Pool Size=2;Maximum Pool Size=20"
    }
  }
}
NOTE
Do not include Database= — the factory appends it automatically using databaseName from socigy.json.

Add appsettings.Development.json for local overrides and keep production secrets out of source control.

Applying migrations at startup

Call the generated EnsureLatestAuthDbMigration() extension after building the app:

var app = builder.Build();

await app.EnsureLatestAuthDbMigration();  // runs pending migrations before serving traffic

app.MapControllers();
app.Run();

This is safe to call on every startup — it only applies migrations that have not yet been recorded in _scg_migrations.

Full Program.cs example

using MyApp.DB;  // generated extension methods live in the DB project's namespace

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddControllers();

// Register the DB — reads ConnectionStrings.AuthDb from appsettings.json
builder.AddAuthDb();

var app = builder.Build();

// Apply any pending schema migrations
await app.EnsureLatestAuthDbMigration();

app.MapControllers();
app.Run();

Injecting IDbConnectionFactory

Inject IDbConnectionFactory into services, controllers, or minimal API handlers using the [FromKeyedServices] attribute:

public class UserService(
    [FromKeyedServices("AuthDb")] IDbConnectionFactory db)
{
    public async Task<User?> FindByUsernameAsync(string username)
    {
        await using var conn = db.Create();  // gets the "Default" connection
        await conn.OpenAsync();

        await foreach (var user in User.Query(x => x.Username == username)
            .WithConnection(conn).ExecuteAsync())
        {
            return user;
        }
        return null;
    }
}

Multiple connection keys

If your appsettings.json defines multiple sub-keys (e.g. Default and ReadOnly), pass the sub-key name to Create():

// Read-heavy query — use the read replica
await using var conn = db.Create("ReadOnly");
await conn.OpenAsync();

await foreach (var report in Report.Query()
    .WithConnection(conn).ExecuteAsync())
{ ... }

Multiple databases

Run the generator for each DB class library with its own databaseName. Register each in Program.cs:

builder.AddAuthDb();        // registers IDbConnectionFactory keyed "AuthDb"
builder.AddUserDb();        // registers IDbConnectionFactory keyed "UserDb"
builder.AddAnalyticsDb();

Inject the one you need by key:

public class ReportService(
    [FromKeyedServices("AnalyticsDb")] IDbConnectionFactory analyticsDb,
    [FromKeyedServices("UserDb")]      IDbConnectionFactory userDb)
{ ... }

Minimal API example

app.MapGet("/users/{username}", async (
    string username,
    [FromKeyedServices("AuthDb")] IDbConnectionFactory db) =>
{
    await using var conn = db.Create();
    await conn.OpenAsync();

    await foreach (var user in User.Query(x => x.Username == username)
        .WithConnection(conn).ExecuteAsync())
    {
        return Results.Ok(user);
    }

    return Results.NotFound();
});
TIP
Each db.Create() call constructs a new connection from Npgsql's connection pool. This is efficient for most workloads. For extreme throughput scenarios, create an NpgsqlDataSource manually and register it as a singleton alongside the factory (see Manual connections).