SQL Persistence - PostgreSQL dialect

Supported database versions

SQL persistence supports:

• PostgreSQL 10 and above
• AWS Aurora PostgreSQL

Usage

Use the Npgsql NuGet Package.

var connection = "Server=localhost;Port=5432;Database=dbname;User Id=user;Password=pass;";
var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
var subscriptions = persistence.SubscriptionSettings();
subscriptions.CacheFor(TimeSpan.FromMinutes(1));
var dialect = persistence.SqlDialect<SqlDialect.PostgreSql>();
dialect.JsonBParameterModifier(
    modifier: parameter =>
    {
        var npgsqlParameter = (NpgsqlParameter)parameter;
        npgsqlParameter.NpgsqlDbType = NpgsqlDbType.Jsonb;
    });

var dataSource = NpgsqlDataSource.Create(connection);

persistence.ConnectionBuilder(
    connectionBuilder: () =>
    {
        return dataSource.CreateConnection();
    });

Token Authentication

To connect using token credentials, a User ID must be supplied in the connection string with the password supplied from the access token. Given that the token is only short-lived, a data source builder must be utilized to handle password refreshes. The following example uses Microsoft Entra ID.

var connection = "Server=test.postgres.database.azure.com;Database=postgres;Port=5432;User Id=<entra user id>;Ssl Mode=Require;";
var dataSourceBuilder = new NpgsqlDataSourceBuilder(connection);
if (string.IsNullOrEmpty(dataSourceBuilder.ConnectionStringBuilder.Password))
{
    dataSourceBuilder.UsePeriodicPasswordProvider(async (_, ct) =>
    {
        var credentials = new DefaultAzureCredential();
        var token = await credentials.GetTokenAsync(new TokenRequestContext(new string[] { "https://ossrdbms-aad.database.windows.net/.default" }), ct);
        return token.Token;
    }, TimeSpan.FromHours(24), TimeSpan.FromSeconds(10));
}

var builder = dataSourceBuilder.Build();
persistence.ConnectionBuilder(connectionBuilder: () => builder.CreateConnection());

Passing Jsonb as NpgsqlDbType

When handling parameters that pass JSONB data, it is necessary to set NpgsqlParameter.NpgsqlDbType to NpgsqlDbType.Jsonb:

var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
var dialect = persistence.SqlDialect<SqlDialect.PostgreSql>();
dialect.JsonBParameterModifier(
    modifier: parameter =>
    {
        var npgsqlParameter = (NpgsqlParameter)parameter;
        npgsqlParameter.NpgsqlDbType = NpgsqlDbType.Jsonb;
    });

Newtonsoft.Json TypeNameHandling

When using Newtonsoft.Json as the serializer and the $type feature via TypeNameHandling, the MetadataPropertyHandling should be set to ReadAhead.

var settings = new JsonSerializerSettings
{
    TypeNameHandling = TypeNameHandling.Auto,
    MetadataPropertyHandling = MetadataPropertyHandling.ReadAhead
};
var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
var sagaSettings = persistence.SagaSettings();
sagaSettings.JsonSettings(settings);

This is because Newtonsoft.Json normally expects the $type metadata to be the first property of each object for best efficiency in deserialization. If the $type does not appear first, then it assumes the property isn't there. When using Jsonb data the stored object does not preserve the order of its keys, which can result in storing the $type property in any other position, causing for Newtonsoft.Json to assume that the object doesn't have the property.

Spatial data

In order to handle spatial data, it is necessary to add the Npgsql.NetTopologySuite NuGet package. This guide on npgsql explains how to set it up to work with PostgreSQL.

Unicode support

SQL persistence itself supports Unicode characters, however data may become corrupted during saving if the database settings are incorrect. If Unicode support is required, follow the guidelines for each database engine. In particular set the correct character set and collation for databases storing persistence data.

Schema support

The PostgreSQL dialect supports multiple schemas. By default, when a schema is not specified, it uses public schema when referring to database objects.

var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
var dialect = persistence.SqlDialect<SqlDialect.PostgreSql>();
dialect.Schema("MySchema");

Case sensitivity

Unless explicitly specified, PostgreSQL uses lowercase for all identifier names (e.g. column or table names, etc.). To enforce case sensitivity, it is necessary to quote all names.

SQL persistence internally honors the UpperCamelCase (also called PascalCase) convention. SQL persistence uses quoted identifier names in stored procedures, queries, etc.

Supported name lengths

SQL persistence automatically generates names of database objects such as tables, indexes and procedures used internally. Every database engine has its own rules and limitations regarding maximum allowed name length.

The SQL Persistence provides autonomy between endpoints by using separate tables for every endpoint based on the endpoint name. By default PostgreSQL limits object names to 63 characters.

By default, SQL persistence uses an endpoint's name as a table prefix, the maximum length of the table prefix is 20 characters. The table prefix can be customized.

Using pg_temp. schema in installation scripts

The table creation for the SQL Persistence requires some dynamic SQL script execution. To achieve this in PostgreSql it is necessary to create temporary functions. These functions are created in the PostgreSql temporary-table schema, commonly referred to as pg_temp.

As per Client Connection Defaults pg_temp is:

The current session's temporary-table schema, pg_temp_nnn, is always searched if it exists. It can be explicitly listed in the path by using the alias pg_temp