RAIC.Extensions.Configuration.EntityFrameworkCore.PostgreSQL 0.1.1

RAIC.Extensions.Configuration.EntityFrameworkCore.PostgreSQL

This library enhances RAIC.Extensions.Configuration.EntityFrameworkCore with support for reload on update via PostgreSQL's LISTEN/NOTIFY capabilities as exposed via npgsql.

Goals

1. No polling!
2. Updates happen in background via a hosted service implementation.
3. Only update settings which change rather than updating them all

Requirements

• .NET 8

Gotchas

• Setting values cannot be null (as signified by the RequiredAttribute on ISetting.Value)
• Setting keys must not contain the = character (similar to CommandLineConfigurationProvider & EnvironmentVariablesConfigurationProvider)
• Small window of opportunity for updates to be missed during reconnection process after any network dropouts or other connectivity flakiness
• Consider adding Keepalive parameter to your connection string if its not already present

Known Issues

• Not tested under load

Configuration Options

There are four properties which can be configured (cf. the PostgreSQLNotificationConfigurationBackgroundServiceOptions POCO)

1. ChannelName - the name of the notification channel to listen to for updates
2. DebounceInterval - the time interval to wait after a notification before signaling the IEntityFrameworkCoreDbSetConfigurationProvider to reload.
3. MaxReconnectionAttempts - Maximum number of times to retry connecting after a connection dropout (eg. PostgreSQL restart, network connectivity issue)
4. InitialReconnectionDelay - How long to wait after connection dropout before attempting to reconnection.

Setup

For PostgreSQLNotificationConfigurationReloader to work it requires triggers on the Settings table to be established which send notifications to the channel corresponding to that specified by the ChannelName option (defaults to settings_channel). SQL similar to below is one (but not only) way this can be acheived:

CREATE OR REPLACE FUNCTION notify_setting_change()
RETURNS trigger AS $$
BEGIN
    PERFORM pg_notify('settings_channel', concat(NEW.key,'=',NEW.value));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION notify_setting_remove()
RETURNS trigger AS $$
BEGIN
    PERFORM pg_notify('settings_channel', OLD.key);
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER setting_insert_trigger
AFTER INSERT ON settings
FOR EACH ROW EXECUTE FUNCTION notify_setting_change();

CREATE TRIGGER setting_update_trigger
AFTER UPDATE ON settings
FOR EACH ROW WHEN (NEW.value <> OLD.value) EXECUTE FUNCTION notify_setting_change();

CREATE TRIGGER setting_key_update_trigger
AFTER UPDATE ON settings
FOR EACH ROW WHEN (NEW.key <> OLD.key) EXECUTE FUNCTION notify_setting_remove();

CREATE TRIGGER setting_delete_trigger
AFTER DELETE ON settings
FOR EACH ROW EXECUTE FUNCTION notify_setting_remove();

Recommend adding your SQL to the migration which adds the Settings table/view (or a new migration if that table/view already exists).

Usage Example

using RAIC.Extensions.Configuration.EntityFrameworkCore;
using RAIC.Extensions.Configuration.EntityFrameworkCore.Extensions;
using RAIC.Extensions.Configuration.EntityFrameworkCore.PostgreSQL.Extensions;

public record Setting : ISetting
{
    [Key]
    public required string Key { get; set; }

    [Required]
    public required string Value { get; set; }
}

public class MyDbContext(DbContextOptions<MyDbContext> options) : DbContext(options), ISettingsDbContext<DbSet<Setting>>, ISettingsDbContextFactory<MyDbContext>
{
    public DbSet<Setting> Settings { get; set; }

    public static MyDbContext Create(DbContextOptions<MyDbContext> options) => new(options);
}


var builder = Host.CreateApplicationBuilder(args); // or  WebApplication.CreateBuilder(args); 

// build an initial configuration
builder.Configuration.AddJsonFile("appsettings.json")
                     ...
                     .AddUserSecrets<Program>(); // or wherever your connection string lives

builder.Configuration.AddDbContext<MyDbContext>(dbContextOptions => dbContextOptions.UseNpgsql(builder.Configuration.GetConnectionString("Default")));

...
// Add the PostgreSQLNotificationConfigurationReloader background service and supporting services to obtain setting reloading functionalty
builder.Services.AddPostgreSQLNotificationConfigurationReloadService<MyDbContext>(); // uses default settings, other overrides exist - see code docs

await builder.Build().RunAsync(); // use config as normal

Read more about Configuration and Options on the Microsoft Docs site.