# Manual connections

Use Socigy.OpenSource.DB without DI — manage NpgsqlConnection and NpgsqlDataSource directly.

## When to use manual connections

The generated DI extensions (`AddAuthDb`) are optional. You can use every query builder, insert, update, delete, join, and set operation purely with raw Npgsql connections — no DI container required. This is useful for:

- Console tools and scripts
- Unit and integration tests
- Non-ASP.NET Core hosts (AWS Lambda, Azure Functions, gRPC services)
- Projects that already have their own connection management

## Opening a connection directly

```csharp
using Npgsql;

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

// Use it with any builder
await foreach (var user in User.Query(x => x.Username == "alice")
    .WithConnection(conn).ExecuteAsync())
{
    Console.WriteLine(user.Id);
}
```

> **NOTE** The builder does not open or close the connection — pass an **already-open** `DbConnection`. Passing a closed connection throws when the command executes.

## Using NpgsqlDataSource (recommended for pooling)

`NpgsqlDataSource` manages the connection pool and is the recommended approach for applications that make many short-lived queries. Create it once and reuse it:

```csharp
// Create once (e.g. as a singleton or static field)
await using var dataSource = NpgsqlDataSource.Create(connectionString);

// Lease a connection from the pool for each operation
await using var conn = await dataSource.OpenConnectionAsync();

var user = new User { Username = "alice" };
await user.Insert()
    .WithConnection(conn)
    .ExcludeAutoFields()
    .ExecuteAsync();
```

`OpenConnectionAsync()` returns a connection already opened — no separate `OpenAsync()` call needed.

## Console application pattern

```csharp
using Npgsql;

var connectionString = Environment.GetEnvironmentVariable("DB_CONN")
    ?? "Host=localhost;Database=dev;Username=postgres;Password=secret";

await using var dataSource = NpgsqlDataSource.Create(connectionString);

// Seed some data
var user = new User { Username = "seed-user" };
await using (var conn = await dataSource.OpenConnectionAsync())
{
    await user.Insert().WithConnection(conn).ExcludeAutoFields().ExecuteAsync();
    Console.WriteLine($"Created user {user.Username}");
}

// Query it back
await using (var conn = await dataSource.OpenConnectionAsync())
{
    await foreach (var u in User.Query().WithConnection(conn).ExecuteAsync())
        Console.WriteLine($"  {u.Id}: {u.Username}");
}
```

## Integration test pattern

```csharp
public class UserTests : IAsyncLifetime
{
    private NpgsqlConnection _conn = null!;

    public async Task InitializeAsync()
    {
        _conn = new NpgsqlConnection(
            "Host=localhost;Database=testdb;Username=postgres;Password=1234");
        await _conn.OpenAsync();

        // Bootstrap schema
        await using var cmd = _conn.CreateCommand();
        cmd.CommandText = CreateTableSql;
        await cmd.ExecuteNonQueryAsync();
    }

    public async Task DisposeAsync()
    {
        await _conn.DisposeAsync();
    }

    [Fact]
    public async Task Insert_and_query_roundtrip()
    {
        var user = new User { Username = "test-alice" };
        await user.Insert().WithConnection(_conn).ExcludeAutoFields().ExecuteAsync();

        await foreach (var found in User.Query(x => x.Username == "test-alice")
            .WithConnection(_conn).ExecuteAsync())
        {
            Assert.Equal("test-alice", found.Username);
            return;
        }
        Assert.Fail("User not found");
    }
}
```

## Connection string format

The standard Npgsql keyword-value format:

```plaintext
Host=localhost;Port=5432;Database=myapp;Username=postgres;Password=secret;
Pooling=true;Minimum Pool Size=1;Maximum Pool Size=20;Connection Idle Lifetime=15;
SSL Mode=Require;Include Error Detail=true
```

All standard Npgsql connection string parameters are supported. `Include Error Detail=true` is useful in development because it includes the PostgreSQL server error detail in exception messages.

## Without connection pooling

If your application opens one long-lived connection (CLIs, scripts), disable pooling to avoid holding idle pool connections:

```csharp
var cs = new NpgsqlConnectionStringBuilder
{
    Host     = "localhost",
    Database = "myapp",
    Username = "postgres",
    Password = "secret",
    Pooling  = false,
};

await using var conn = new NpgsqlConnection(cs.ConnectionString);
await conn.OpenAsync();
```

> **WARNING** Disabling pooling in a server application means every request opens a new TCP connection to PostgreSQL, which is expensive. Only disable pooling for scripts and one-shot tools.