PostgreSQL Overview

Weasel.Postgresql provides full schema management and migration support for PostgreSQL databases using the Npgsql driver.

Installation

dotnet add package Weasel.Postgresql

Key Components

PostgresqlProvider is the singleton that handles type mappings between .NET types and PostgreSQL column types. It uses public as the default schema.

// Access the singleton
var provider = PostgresqlProvider.Instance;

// Map a .NET type to a PostgreSQL type
string dbType = provider.GetDatabaseType(typeof(string), EnumStorage.AsInteger);
// Returns "varchar"

^{snippet source | anchor}

PostgresqlMigrator controls DDL generation rules including formatting, transactional wrapping, and schema creation. It also validates identifier lengths against PostgreSQL's NAMEDATALEN limit.

var migrator = new PostgresqlMigrator
{
    Formatting = SqlFormatting.Pretty,
    IsTransactional = true
};

^{snippet source | anchor}

PostgresqlDatabase is the abstract base class for building a database with managed schema objects. It requires an NpgsqlDataSource for connection management.

public class AppDatabase : PostgresqlDatabase
{
    public AppDatabase(NpgsqlDataSource dataSource)
        : base(new DefaultMigrationLogger(), AutoCreate.CreateOrUpdate,
               new PostgresqlMigrator(), "app", dataSource)
    {
    }

    public override IFeatureSchema[] BuildFeatureSchemas()
    {
        // Return your feature schemas containing tables, functions, sequences, etc.
        return Array.Empty<IFeatureSchema>();
    }
}

^{snippet source | anchor}

Supported Schema Objects

Object	Class	Description
Tables	Weasel.Postgresql.Tables.Table	Full DDL with columns, indexes, foreign keys, partitioning
Functions	Weasel.Postgresql.Functions.Function	PL/pgSQL functions with delta detection
Sequences	Weasel.Postgresql.Sequence	Auto-incrementing sequences
Views	Weasel.Postgresql.Views.View	Standard and materialized views
Extensions	Weasel.Postgresql.Extension	PostgreSQL extensions (e.g., uuid-ossp, postgis)

NpgsqlDataSource Integration

Weasel uses Npgsql's NpgsqlDataSource for connection management rather than raw connection strings. This aligns with the modern Npgsql connection pooling model.

var dataSourceBuilder = new NpgsqlDataSourceBuilder("Host=localhost;Database=myapp;");
await using var dataSource = dataSourceBuilder.Build();

var database = new AppDatabase(dataSource);
await database.ApplyAllConfiguredChangesToDatabaseAsync();

^{snippet source | anchor}