C# / .NET: Npgsql

Connects, creates a todo table if it does not exist, inserts a row and counts open rows. The code is the same against the original database and against MachSQL; only the address changes.

The only change

DB_CONNECTION="Host=old-postgres;Port=5432;Database=app;Username=app;Password=..."
DB_CONNECTION="Host=machsql-host;Port=5432;Database=app;Username=app;Password=..."

Nothing else changes: not the code, not the driver, not its version, not the SQL.

Versions

• .NET 10
• Npgsql 10.0.3

Run

export DB_CONNECTION="Host=machsql-host;Port=5432;Database=app;Username=app;Password=..."
dotnet run

Expected output: inserted todo <id>, <n> open.

Source

The program as it is kept and tested in the Mach Database for PostgreSQL repository; highlighted lines read the connection setting.

Program.cs

// Npgsql: ADO.NET over the PostgreSQL protocol.
using Npgsql;

var connectionString = Environment.GetEnvironmentVariable("DB_CONNECTION")
    ?? throw new InvalidOperationException("set DB_CONNECTION");
await using var dataSource = NpgsqlDataSource.Create(connectionString);

await using (var create = dataSource.CreateCommand("CREATE TABLE IF NOT EXISTS todo (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, title text NOT NULL, done boolean NOT NULL DEFAULT false)"))
{
    await create.ExecuteNonQueryAsync();
}

await using var insert = dataSource.CreateCommand("INSERT INTO todo (title) VALUES ($1) RETURNING id");
insert.Parameters.Add(new NpgsqlParameter { Value = "write the examples" });
var id = (long)(await insert.ExecuteScalarAsync())!;

await using var count = dataSource.CreateCommand("SELECT count(*) FROM todo WHERE NOT done");
var open = (long)(await count.ExecuteScalarAsync())!;

Console.WriteLine($"inserted todo {id}, {open} open");

Example.csproj

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net10.0</TargetFramework>
    <Nullable>enable</Nullable>
    <ImplicitUsings>enable</ImplicitUsings>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Npgsql" Version="10.0.3" />
  </ItemGroup>

</Project>