Manual connections

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

updated 3 May 20263 min readv0.1.82

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

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:

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

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

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:

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:

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.

← previousASP.NET Core