/DB

Connections & DI

How to supply database connections manually, and how the generated IDbConnectionFactory and DI extensions work in ASP.NET Core.

updated 3 May 20263 min readv0.1.82

Manual connections (no DI)

Every command builder accepts an open DbConnection directly. This is the simplest approach and works in any host type — console apps, test projects, background services, or ASP.NET Core:

await using var conn = new NpgsqlConnection(
    "Host=localhost;Port=5432;Database=myapp;Username=postgres;Password=secret");
await conn.OpenAsync();

// Pass the open connection to any command builder
await user.Insert()
    .WithConnection(conn)
    .ExcludeAutoFields()
    .ExecuteAsync();

await foreach (var u in User.Query(x => x.Username == "alice")
    .WithConnection(conn).ExecuteAsync())
{
    Console.WriteLine(u.Username);
}
NOTE
The connection returned by any factory or constructor is not pre-opened. Always call await conn.OpenAsync() before passing it to a builder. If the connection is closed when ExecuteAsync() runs, Npgsql will throw.

IDbConnectionFactory

When generateDbConnectionFactory: true is set in socigy.json, the build emits an implementation of IDbConnectionFactory (defined in Socigy.OpenSource.DB.Core):

public interface IDbConnectionFactory
{
    DbConnection Create(string? connectionKey = null);
    Task<bool> EnsureDbExists();
}

Create(connectionKey) constructs a new (closed) DbConnection using the connection string sub-key specified. A null argument resolves to "Default". Any other string resolves to the matching sub-key in appsettings.json.

EnsureDbExists() checks whether the target database exists and creates it if not. Returns true if the database already existed, false if it was just created.

appsettings.json format

Connection strings are read from ConnectionStrings.{databaseName} using a nested object structure. Multiple named connections (for example a primary and a read replica) are supported as sub-keys.

NOTE
Do not include Database= in the connection string. The generated factory automatically appends Database={databaseName} (the value of databaseName in socigy.json) when opening a connection.
{
  "ConnectionStrings": {
    "AuthDb": {
      "Default":  "Host=localhost;Port=5432;Username=postgres;Password=secret",
      "ReadOnly": "Host=replica.example.com;Port=5432;Username=ro_user;Password=secret"
    }
  }
}

factory.Create() or factory.Create(null) uses "Default".
factory.Create("ReadOnly") uses "ReadOnly".

Generated DI extensions

After setting databaseName: "AuthDb" in socigy.json and running dotnet build, the following extension methods are generated and ready to use:

// WebApplicationBuilder (ASP.NET Core minimal API / MVC)
builder.AddAuthDb();

// IServiceCollection (any host)
builder.Services.AddAuthDb();

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

All three register the same services:

  • IDbConnectionFactory — keyed "AuthDb", reads from ConnectionStrings.AuthDb
  • IMigrationManager — keyed "AuthDb", applies pending migrations

Applying migrations on startup

var app = builder.Build();
await app.EnsureLatestAuthDbMigration();
app.Run();

EnsureLatestAuthDbMigration() is also a generated extension on WebApplication. It resolves the keyed IMigrationManager and applies any unapplied migrations in order.

Full Program.cs example

var builder = WebApplication.CreateBuilder(args);

// Register IDbConnectionFactory + IMigrationManager keyed "AuthDb"
builder.AddAuthDb();

builder.Services.AddScoped<UserService>();

var app = builder.Build();

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

app.MapGet("/users/{username}", async (string username, UserService svc) =>
    await svc.FindAsync(username) is { } u ? Results.Ok(u) : Results.NotFound());

app.Run();

Injecting and using the factory

Inject IDbConnectionFactory into any service. Resolve it with the [FromKeyedServices] attribute or via the keyed service APIs if you have multiple databases registered:

public class UserService
{
    private readonly IDbConnectionFactory _db;

    public UserService(IDbConnectionFactory db) => _db = db;

    public async Task<User?> FindAsync(string username)
    {
        await using var conn = _db.Create();   // creates a "Default" connection
        await conn.OpenAsync();

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

        return null;
    }

    public async Task<IReadOnlyList<User>> ListReadOnlyAsync()
    {
        await using var conn = _db.Create("ReadOnly");  // uses the replica
        await conn.OpenAsync();

        var results = new List<User>();
        await foreach (var u in User.Query().WithConnection(conn).ExecuteAsync())
            results.Add(u);

        return results;
    }
}

Thread safety

Command builders are not thread-safe. Create a new builder per operation — the generated static factory methods (User.Query(), user.Insert(), etc.) each return a new builder instance.

A single NpgsqlConnection must not be used concurrently from multiple threads. For concurrent workloads, call factory.Create() separately for each concurrent path and open each connection independently.