PostgreSQLCopyHelper

PostgreSQLCopyHelper is a library for efficient bulk inserts to PostgreSQL databases. It wraps the COPY methods from Npgsql behind a nice Fluent API.

Installing

To install PostgreSQLCopyHelper, run the following command in the Package Manager Console:

PM> Install-Package PostgreSQLCopyHelper

Basic Usage

Imagine we have the following table we want to copy data to:

CREATE TABLE sample.unit_test
(
	col_smallint smallint,
	col_integer integer,
	col_money money,
	col_bigint bigint,
	col_timestamp timestamp,
	col_real real,
	col_double double precision,
	col_bytea bytea,
	col_uuid uuid,
	col_numeric numeric,
	col_inet inet,
	col_macaddr macaddr,
	col_date date,
	col_interval interval
);

The corresponding domain model in our application could look like this:

private class TestEntity
{
	public Int16? SmallInt { get; set; }
	public Int32? Integer { get; set; }
	public Int64? BigInt { get; set; }
	public Decimal? Money { get; set; }
	public DateTime? Timestamp { get; set; }
	public Decimal? Numeric { get; set; }
	public Single? Real { get; set; }
	public Double? DoublePrecision { get; set; }
	public byte[] ByteArray { get; set; }
	public Guid? UUID { get; set; }
	public IPAddress IpAddress { get; set; }
	public PhysicalAddress MacAddress { get; set; }
	public DateTime? Date { get; set; }
	public TimeSpan? TimeSpan { get; set; }
}

The PostgreSQLCopyHelper now defines the mapping between domain model and the database table:

var copyHelper = new PostgreSQLCopyHelper<TestEntity>("sample", "unit_test")
	.MapSmallInt("col_smallint", x => x.SmallInt)
	.MapInteger("col_integer", x => x.Integer)
	.MapMoney("col_money", x => x.Money)
	.MapBigInt("col_bigint", x => x.BigInt)
	.MapTimeStamp("col_timestamp", x => x.Timestamp)
	.MapReal("col_real", x => x.Real)
	.MapDouble("col_double", x => x.DoublePrecision)
	.MapByteArray("col_bytea", x => x.ByteArray)
	.MapUUID("col_uuid", x => x.UUID)
	.MapInetAddress("col_inet", x => x.IpAddress)
	.MapMacAddress("col_macaddr", x => x.MacAddress)
	.MapDate("col_date", x => x.Date)
	.MapInterval("col_interval", x => x.TimeSpan)
	.MapNumeric("col_numeric", x => x.Numeric);

And then we can use it to efficiently store the data:

Synchronously:

private ulong WriteToDatabase(PostgreSQLCopyHelper<TestEntity> copyHelper, IEnumerable<TestEntity> entities)
{
    using (var connection = new NpgsqlConnection("Server=127.0.0.1;Port=5432;Database=sampledb;User Id=philipp;Password=test_pwd;"))
    {
        connection.Open();

        // Returns count of rows written 
        return copyHelper.SaveAll(connection, entities);
    }
}

Or asynchronously:

private async Task<ulong> WriteToDatabaseAsync(PostgreSQLCopyHelper<TestEntity> copyHelper, IEnumerable<TestEntity> entities, CancellationToken cancellationToken = default)
{
    using (var connection = new NpgsqlConnection("Server=127.0.0.1;Port=5432;Database=sampledb;User Id=philipp;Password=test_pwd;"))
    {
        await connection.OpenAsync(cancellationToken);

        // Returns count of rows written 
        return await copyHelper.SaveAllAsync(connection, entities, cancellationToken);
    }
}

Or asynchronously with asynchronous enumerables:

private async Task<ulong> WriteToDatabaseAsync(PostgreSQLCopyHelper<TestEntity> copyHelper, IAsyncEnumerable<TestEntity> entities, CancellationToken cancellationToken = default)
{
    using (var connection = new NpgsqlConnection("Server=127.0.0.1;Port=5432;Database=sampledb;User Id=philipp;Password=test_pwd;"))
    {
        await connection.OpenAsync(cancellationToken);

        // Returns count of rows written 
        return await copyHelper.SaveAllAsync(connection, entities, cancellationToken);
    }
}

PostgreSQLCopyHelper Custom Type Maps

One can always define a custom map function for any property to any Npgsql type.

For example:

.Map("geo", x => x.geo, NpgsqlDbType.Point)

Mapping Composite Types

Imagine you have a composite type called person_type in a schema of your database:

create type sample.person_type as
(
    first_name text,
    last_name text,
    birth_date date
);

And it is used in a table called CompositeTest:

create table sample.CompositeTest
(
    col_text text,
    col_person sample.person_type                
)

You first need to map the Postgres person_type to a C# class:

private class PersonType
{
    public string FirstName { get; set; }

    public string LastName { get; set; }

    public DateTime BirthDate { get; set; }
}

A hint: Npgsql always converts the property name to a snake case column name, so FirstName is mapped to first_name by convention. You can use the [PgName] attribute to explicitly set the Postgres type name.

Next the table is mapped to the following C# model:

private class SampleEntity
{
    public string TextColumn { get; set; }

    public PersonType CompositeTypeColumn { get; set; }
}

And now we can bulk write SampleEntity instances using PostgreSQLCopyHelper like this:

connection.TypeMapper.MapComposite<PersonType>("sample.person_type");

// ... alternatively you can set it globally at any place in your application using the NpgsqlConnection.GlobalTypeMapper:
//
// NpgsqlConnection.GlobalTypeMapper.MapComposite<PersonType>("sample.person_type");

var subject = new PostgreSQLCopyHelper<SampleEntity>("sample", "CompositeTest")
         .MapText("col_text", x => x.TextColumn)
         .Map("col_person", x => x.CompositeTypeColumn);

var entities = new List<SampleEntity>();

entities.Add(new SampleEntity
{
    TextColumn = "0",
    CompositeTypeColumn = new PersonType { FirstName = "Fake", LastName = "Fakerton", BirthDate = new DateTime(1987, 1, 11) }
});

entities.Add(new SampleEntity
{
    TextColumn = "1",
    CompositeTypeColumn = new PersonType { FirstName = "Philipp", LastName = "Wagner", BirthDate = new DateTime(1912, 1, 11) }
});

subject.SaveAll(connection, entities);

In the listing you see, that we need to tell Npgsql how to map the Postgres type using MapComposite<>. This can be done per Connection like this:

connection.TypeMapper.MapComposite<PersonType>("sample.person_type");

Or you can alternatively set the Mapping globally at any place in your application using the NpgsqlConnection.GlobalTypeMapper:

NpgsqlConnection.GlobalTypeMapper.MapComposite<PersonType>("sample.person_type");

PostgreSQLCopyHelper.NodaTime: NodaTime Support

The PostgreSQLCopyHelper.NodaTime package extends PostgreSQLCopyHelper for NodaTime types.

To install PostgreSQLCopyHelper.NodaTime, run the following command in the Package Manager Console:

PM> Install-Package PostgreSQLCopyHelper

It uses the Npgsql.NodaTime plugin, which needs to be enabled by running:

using Npgsql;

// Place this at the beginning of your program to use NodaTime everywhere (recommended)
NpgsqlConnection.GlobalTypeMapper.UseNodaTime();

// Or to temporarily use NodaTime on a single connection only:
conn.TypeMapper.UseNodaTime();

For more details see the Npgsql documentation for NodaTime.

Case-Sensitive Identifiers

By default the library does not apply quotes to identifiers, such as Table Names and Column Names. If you want PostgreSQL-conform quoting for identifiers, then use the UsePostgresQuoting method like this:

var copyHelper = new PostgreSQLCopyHelper<MixedCaseEntity>("sample", "MixedCaseEntity")
                     .UsePostgresQuoting()
                     .MapInteger("Property_One", x => x.Property_One)
                     .MapText("Property_Two", x => x.Property_Two);

License

PostgreSQLCopyHelper is licensed under the MIT License. See LICENSE for details.

Copyright (c) Philipp Wagner, Steven Yeh and Contributors