# 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. 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`):

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

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

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

### Applying migrations on startup

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

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

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

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