mvdmio.Database.PgSQL 0.26.0

mvdmio.Database.PgSQL

PostgreSQL access for .NET applications.

The package combines Dapper and Npgsql with a higher-level API for common PostgreSQL workflows.

Installation

dotnet add package mvdmio.Database.PgSQL

Targets net8.0, net9.0, and net10.0.

What You Can Do With It

• Run SQL queries and commands through db.Dapper
• Execute work inside transactions
• Bulk insert or upsert rows
• Check schema and table existence
• Export the current database schema
• Limit schema export to selected PostgreSQL schemas when using the companion CLI configuration
• Preserve identity columns and stored generated columns during schema export
• Run migrations from application code
• Generate repositories from annotated table models
• Auto-embed Schemas/**/*.sql files for direct and transitive project references

Quick Start

using mvdmio.Database.PgSQL;

await using var db = new DatabaseConnection(
   "Host=localhost;Database=mydb;Username=postgres;Password=secret"
);

var users = await db.Dapper.QueryAsync<User>(
   "SELECT * FROM users WHERE active = :active",
   new Dictionary<string, object?> { ["active"] = true }
);

await db.Dapper.ExecuteAsync(
   "INSERT INTO users (name, email) VALUES (:name, :email)",
   new Dictionary<string, object?>
   {
      ["name"] = "Alice",
      ["email"] = "alice@example.com"
   }
);

Common Usage

Transactions

await db.InTransactionAsync(async () =>
{
   await db.Dapper.ExecuteAsync(
      "INSERT INTO orders (customer_id, total) VALUES (:customerId, :total)",
      new Dictionary<string, object?>
      {
         ["customerId"] = 42,
         ["total"] = 99.95m
      }
   );
});

Bulk Operations

var mapping = new Dictionary<string, Func<Product, DbValue>>
{
   ["sku"] = x => x.Sku,
   ["name"] = x => x.Name,
   ["price"] = x => x.Price
};

await db.Bulk.CopyAsync("products", products, mapping);

For streaming COPY sessions, prefer await using so failed writes still dispose the importer and release the connection:

await using var session = await db.Bulk.BeginCopyAsync<Product>("products", mapping);

foreach (var product in products)
   await session.WriteAsync(product);

await session.CompleteAsync();

Migrations In Code

using mvdmio.Database.PgSQL.Migrations;

var migrator = new DatabaseMigrator(db, typeof(Program).Assembly);
await migrator.MigrateDatabaseToLatestAsync();

Embedded Schema Files

When a project references mvdmio.Database.PgSQL directly, or references another project that does, any Schemas/**/*.sql files in that project are automatically included as embedded resources.

This works through the package's build and buildTransitive MSBuild props files.

When DatabaseMigrator is constructed with multiple assemblies and the target database is empty, every assembly that contains an embedded schema.sql (or environment-specific schema.{env}.sql) has its schema applied, in the order the assemblies are passed to the constructor. Assemblies without a matching schema resource are silently skipped. All schemas run in a single transaction, and the migrations table is pre-created so that schema files using CREATE TABLE IF NOT EXISTS "mvdmio"."migrations" do not conflict. The baseline entry in mvdmio.migrations is recorded using the highest -- Migration version: <id> (<name>) header found across the applied schemas. If the database already contains migrations, no schema files are applied. When MigrateDatabaseToAsync(targetIdentifier) is used and any discovered schema's header version exceeds the target, the entire schema-first bootstrap is skipped (applying a subset would leave gaps that later migrations cannot fill).

Generated Repositories

using mvdmio.Database.PgSQL.Attributes;

[Table("public.users")]
public partial class UserTable
{
   [PrimaryKey]
   [Generated]
   public long UserId { get; set; }

   [Unique]
   public string UserName { get; set; } = string.Empty;
}

From that model, the package generates repository types and CRUD command/data types you can use in application code.

CLI Tool

If you want a command-line workflow for migrations and schema files, install the companion tool:

dotnet tool install --global mvdmio.Database.PgSQL.Tool

See ../mvdmio.Database.PgSQL.Tool/README.md.

License

MIT. See ../../LICENSE.