ApiFeatures.Mqtt.Postgresqls 9.2.0

ApiFeatures.Mqtt.Postgresqls

PostgreSQL persistence layer for the ApiFeatures.Mqtt module, using Entity Framework Core 9 + Npgsql. Implements the four DB-tied Cores service interfaces (IMqttService, IMqttConnectionService, IBrokerService, IBrokerUserService) as an alternative to ApiFeatures.Mqtt.MongoDatabases. References ApiFeatures.Mqtt.Cores and ApiFeatures.Modules.Cores; never references ApiFeatures.Mqtt.Apis or ApiFeatures.Mqtt.MongoDatabases.

Schema

Greenfield, fixed snake_case names (no per-table overrides):

Enums (MqttConnectionStatuses, BrokerProtocols) persist as text (HasConversion<string>()). Timestamps are timestamptz (UTC). No relational FKs are declared — the Mongo layer has no referential link either, and the services manage cascade/orphan cleanup in code (DeleteByBrokerIdAsync); keeping parity avoids a behavioural change a FK would introduce.

Polymorphic MqOptions → jsonb

MqttEntity.Options (abstract MqOptions, discriminated by Type; concretes HiveMqOptions, SelfHostedBrokerOptions) maps to a single jsonb column via an EF ValueConverter<MqOptions, string> backed by an internal System.Text.Json Type-switch converter (Serializers/MqOptionsJsonConverter.cs). This mirrors the Apis-resident converter but is a deliberate local duplicate — not shared via Cores — to keep zero blast radius on the already-merged restructure (Tasks 1-6). The password stays embedded inside the jsonb (same trust boundary as Mongo); password-omission in API responses remains an Apis-layer mapper concern.

Usage

using ApiFeatures.Modules.Cores.Models.Options;
using ApiFeatures.Mqtt.Postgresqls.Extensions;

var postgresOptions = new ModulePostgresDatabaseOptions
{
    Host = "localhost",
    Port = 5432,
    DatabaseName = "mqtt",
    Username = "postgres",
    Password = "postgres",
    UseTls = false,       // true => SSL Mode=Require
    Schema = "mqtt"       // optional; omit/blank => the database's default schema
};

builder.Services.AddMqttsModule(addMqttsFeatureOptions);
builder.Services.WithPostgresDatabase(postgresOptions);

WithPostgresDatabase registers the MqttPostgresDatabaseContext and the four Postgres service implementations, all keyed ServiceKeys.Default. It does not register IBrokerUserPasswordHasher — that implementation stays in ApiFeatures.Mqtt.Apis and is registered by AddMqttsModule; PostgresBrokerUserService resolves it lazily via keyed DI.

Duplicate-key → 409 translation

The Mongo layer relies on the Apis endpoint catching a Mongo-specific write exception. That catch does not fire for Postgres, so this library translates the Npgsql unique-violation itself, at the service layer:

• PostgresBrokerService.CreateAsync/UpdateAsync catch a DbUpdateException wrapping a Npgsql.PostgresException with SqlState == "23505" on ix_brokers_port → BrokerPortConflictException.
• PostgresBrokerUserService.CreateAsync/UpdateAsync do the same on ix_broker_users_broker_id_username → BrokerUserUsernameConflictException.

This asymmetry (Mongo translates at the endpoint, Postgres at the service) is intentional for this task; the pre-check validators (PortExistsAsync/UsernameExistsAsync) remain the friendly first line of defence in both providers, and the 23505 catch is the authoritative backstop for the concurrent-create race.

Migrations tooling

Migrations live inside this library (Migrations/); MigrationsAssembly is this assembly. A design-time factory (DesignTime/MqttPostgresDesignTimeDbContextFactory.cs) lets dotnet ef run without booting a host app — it reads the MQTT_POSTGRES_CONNECTION environment variable, falling back to Host=localhost;Port=5432;Database=mqtt;Username=postgres;Password=postgres. For TLS at design time add SSL Mode=Require to that connection string. The optional MQTT_POSTGRES_SCHEMA variable (for example mqtt) selects the schema: it sets Search Path on the design-time connection and puts __EFMigrationsHistory in that schema; a missing schema is created by dotnet ef database update. Allowed names match ^[a-z_][a-z0-9_]{0,62}$; unset or blank means the default schema.

Run from the repo root (requires dotnet-ef: dotnet tool install --global dotnet-ef):

# Create a new migration (authored inside this library)
dotnet ef migrations add <Name> \
  -p src/modules/mqtt/databases/ApiFeatures.Mqtt.Postgresqls \
  -c MqttPostgresDatabaseContext

# Apply the schema to a running Postgres (manual — operator/dev runs this; no auto-migrate on startup)
dotnet ef database update \
  -p src/modules/mqtt/databases/ApiFeatures.Mqtt.Postgresqls \
  -c MqttPostgresDatabaseContext

# (Re)generate the committed idempotent SQL script
dotnet ef migrations script --idempotent \
  -p src/modules/mqtt/databases/ApiFeatures.Mqtt.Postgresqls \
  -c MqttPostgresDatabaseContext \
  -o src/modules/mqtt/databases/ApiFeatures.Mqtt.Postgresqls/Migrations/Scripts/mqtt-postgres.sql

Alternatively, run the committed idempotent script directly (Migrations/Scripts/mqtt-postgres.sql) against your target database — it produces the same schema as dotnet ef database update. The script is schema-neutral: to install into a custom schema run CREATE SCHEMA IF NOT EXISTS mqtt; SET search_path TO mqtt; in the same session first (the history table is then created in mqtt too). Tables that already exist in another schema are never moved when a schema is introduced; move them yourself or start from an empty schema.

Configuration

Bound to ModulePostgresDatabaseOptions (from ApiFeatures.Modules.Cores):

With Schema set, search_path contains ONLY that schema (public is excluded), which matters for extensions installed in public.

Settings are validated when WithPostgresDatabase is called: a blank host/database name, a port outside 1-65535 or an invalid schema throws InvalidModuleDatabaseOptionsException. If TLS is required but the server refuses it, the first connection fails with Npgsql's SSL error. A schema that was never migrated fails on the first query (42P01), exactly like an unmigrated database today.

Design-time only (used by dotnet ef): MQTT_POSTGRES_CONNECTION (connection string) and MQTT_POSTGRES_SCHEMA (optional schema, example mqtt).

Breaking change: the options are now DataMagic's PostgresqlDatabaseOptions (via ModulePostgresDatabaseOptions); Port is an int, and UseTls/Schema are honored.

* required for a password-authenticated server; optional only for trust-auth local dev.

Dependencies

• ApiFeatures.Mqtt.Cores
• ApiFeatures.Modules.Cores
• BusinessFeatures.Cores
• Microsoft.EntityFrameworkCore (9.0.0)
• Npgsql.EntityFrameworkCore.PostgreSQL (9.0.2)