# 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.

```csharp
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. The command builders open it automatically if it's still closed when `ExecuteAsync()` runs, so an explicit `await conn.OpenAsync()` is optional. They do not close it afterwards: only the command and reader are disposed, and the connection is left open so you can reuse it across several builders. You own its lifetime, so dispose it yourself (for example `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`):

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

`Create(connectionKey)` constructs a new (closed) `DbConnection` using the connection-string sub-key you specify. 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. It 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.

```json
{
  "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:

```csharp
// 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, keyed `"AuthDb"`:

- `IDbConnectionFactory`, reads from `ConnectionStrings.AuthDb`.
- `IMigrationManager`, applies pending migrations.

Both register under the key `"AuthDb"`. Resolve them with `GetRequiredKeyedService<IDbConnectionFactory>("AuthDb")` or inject with `[FromKeyedServices("AuthDb")]`.

### Applying migrations on startup

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

`EnsureLatestAuthDbMigration()` is a generated extension on `WebApplication` and `IHost`. It resolves the keyed `IMigrationManager` and applies any unapplied migrations in order. If you need finer control, resolve the keyed `IMigrationManager` yourself and call `EnsureLatestVersion()`.

## Full Program.cs example

```csharp
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

`IDbConnectionFactory` is registered as a keyed service. Resolve it with `[FromKeyedServices("AuthDb")]` (or the keyed service APIs) so the right database is selected even when several are registered:

```csharp
public class UserService
{
    private readonly IDbConnectionFactory _db;

    public UserService([FromKeyedServices("AuthDb")] 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;
    }
}
```

> **TIP** For business code, prefer the [database context](/database/0.3.2/core-concepts/database-context). It acquires and disposes the connection for you and keeps services fully mockable, instead of passing a `DbConnection` around by hand.

## Rotating credentials (IDbCredentialsProvider)

The generated connection factory can source its connection string from an optional `IDbCredentialsProvider` instead of static configuration. This is useful when a secrets manager rotates your database credentials, such as HashiCorp Vault's Database secrets engine.

```csharp
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=…`) and falls 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](/database/0.3.2/integrations/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()`) each return a fresh 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.
</content>