Connections & DI
How to supply database connections manually, and how the generated IDbConnectionFactory and DI extensions work in ASP.NET Core.
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);
}ExecuteAsync() runs, so an explicit await conn.OpenAsync() is optional. They do not close it afterwards — only the command/reader are disposed, and the connection is left open so you can reuse it across several builders. You own its lifetime: dispose it yourself (e.g. await using var conn = factory.Create()), which closes it and returns it to the pool. (The one exception is the static …Sequence helpers, which restore the connection's original open/closed state.)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.
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 fromConnectionStrings.AuthDbIMigrationManager— 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;
}
}Rotating credentials (IDbCredentialsProvider)
The generated connection factory can source its connection string from an optional IDbCredentialsProvider instead of static configuration — useful when a secrets manager rotates your database credentials (e.g. HashiCorp Vault's Database secrets engine).
public interface IDbCredentialsProvider
{
// Called synchronously by the factory on every Create(); must return a cached value (no I/O).
string? GetConnectionString(string database, string? connectionKey);
// Primes/refreshes the cache (startup + the implementation's own renewal timer).
ValueTask RefreshAsync(string database, string? connectionKey, CancellationToken ct = default);
}When an IDbCredentialsProvider is registered in DI, the factory uses it automatically: it returns the base connection string (the factory still appends ;Database=…), falling back to IConfiguration when the provider returns null. Because Create() is synchronous, the provider must serve a cached string and refresh out-of-band; the factory primes it during startup (EnsureDbExists). When credentials rotate, returning the new string makes Npgsql open a fresh pool while the old one drains.
A ready-made Vault implementation ships in the optional package — see HashiCorp Vault.
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.