Postgres Outbox

Reference · Applies to Brighter V10

The PostgreSQL Outbox provides a message store for the Transactional Outbox pattern using a PostgreSQL database. This ensures that messages are saved within the same transaction as your business logic and published to a message broker later.

Provisioning the PostgreSQL Outbox Table

You have two equally valid options for creating and maintaining the Outbox table:

Option A — Let Brighter provision and migrate it for you (recommended for greenfield apps).

Brighter ships a library that creates the table on first start and evolves its schema across Brighter releases automatically. See Database Provisioning for the overview and Configuring Box Provisioning for the call-site shape.

Option B — Manage the DDL yourself (recommended where you have schema-change governance).

Use PostgreSqlOutboxBuilder.GetDDL() to obtain the same DDL Brighter ships, then drive it through your own change-management tooling — FluentMigrator, Flyway, Liquibase, an enterprise change-window pipeline, or hand-rolled scripts. The rest of this page describes this option.

Neither option is deprecated. Choose based on fit: small teams and greenfield apps benefit from startup-time provisioning; teams with DBA approval workflows or change windows often prefer to drive the same DDL through their own tooling.

PostgreSQL Outbox NuGet Packages

To use the PostgreSQL Outbox, you need to install the following packages from NuGet. If you are using Entity Framework Core, you will also need the EF Core integration package.

Install-Package Paramore.Brighter.PostgreSql
Install-Package Paramore.Brighter.Outbox.PostgreSql

For Entity Framework Core support:

Install-Package Paramore.Brighter.PostgreSql.EntityFrameworkCore

PostgreSQL Outbox Database Table Schema

The PostgreSQL Outbox requires a specific table in your database to store messages before they are dispatched. You can generate the necessary SQL Data Definition Language (DDL) script to create this table using the PostgreSqlOutboxBuilder helper class.

Note: When you choose Option B, you are responsible for creating the table and applying schema changes when upgrading to new versions of Brighter. Option A handles both for you — see Database Provisioning. Either way, application-level concerns like additional indexes for query performance remain your responsibility.

Generating the DDL

The PostgreSqlOutboxBuilder.GetDDL() method creates the SQL script for you. You can execute this script against your database to create the outbox table.

using Paramore.Brighter.Outbox.PostgreSql;

// The table name can be whatever you choose.
string tableName = "Outbox"; 

// The DDL for a table that stores the message body as TEXT
string ddl = PostgreSqlOutboxBuilder.GetDDL(tableName);

// The DDL for a table that stores the message body as BYTEA
// Useful if your message body is binary
string binaryDdl = PostgreSqlOutboxBuilder.GetDDL(tableName, binaryMessagePayload: true);

The parameter is named binaryMessagePayload here. The MSSQL, MySQL and SQLite builders spell the same argument hasBinaryMessagePayload, so copying a call from one of those pages gives you CS1739.

Example SQL Script

Running PostgreSqlOutboxBuilder.GetDDL("Outbox") against Brighter V10 (10.7.0) generates the following SQL script:

CREATE TABLE IF NOT EXISTS "outbox"
(
    Id bigserial PRIMARY KEY,
    MessageId character varying(255) UNIQUE NOT NULL,
    Topic character varying(255) NULL,
    MessageType character varying(32) NULL,
    Timestamp timestamptz NULL,
    CorrelationId character varying(255) NULL,
    ReplyTo character varying(255) NULL,
    ContentType character varying(128) NULL,
    PartitionKey character varying(128) NULL,  
    WorkflowId character varying(255) NULL,
    JobId character varying(255) NULL,
    Dispatched timestamptz NULL,
    HeaderBag text NULL,
    Body text NULL,
    Source character varying (255) NULL,
    Type character varying (255) NULL,
    DataSchema character varying (255) NULL,
    Subject character varying (255) NULL,
    TraceParent character varying (255) NULL,
    TraceState character varying (255) NULL,
    Baggage text NULL,
    DataRef character varying (255) NULL,
    SpecVersion character varying (255) NULL
);

Note the table name. You configured "Outbox" and the DDL emits "outbox": Brighter lowercases the identifier and then quotes it, so that a configured mixed-case name still resolves to the table that older, unquoted DDL created. A select against "Outbox" therefore fails with relation "Outbox" does not exist while select * from outbox succeeds.

PostgreSQL Outbox Configuration

To configure the PostgreSQL Outbox, you need to provide an outbox implementation in the AddProducers configuration when setting up Brighter.

1. Provide Database Configuration

First, define the configuration for your PostgreSQL database connection. We recommend retrieving the connection string from your application's configuration (e.g., appsettings.json) rather than hardcoding it.

using Microsoft.Extensions.DependencyInjection;
using Paramore.Brighter;

// Get connection string from configuration
var connectionString = "Host=localhost;Port=5432;Database=brighter;Username=postgres;Password=password";

// An object to hold your connection string and table name
var dbConfig = new RelationalDatabaseConfiguration(
    connectionString: connectionString,
    outBoxTableName: "Outbox"
);

// Register the configuration with the service collection
services.AddSingleton<IAmARelationalDatabaseConfiguration>(dbConfig);

2. Register the Outbox

Next, in your ConfigureServices method or Program.cs, add the outbox configuration when calling AddBrighter. You need to specify the Outbox, ConnectionProvider, and TransactionProvider.

The TransactionProvider depends on how you manage your database transactions.

• Use PostgreSqlTransactionProvider for ADO.NET-based transaction management.

• Use PostgreSqlEntityFrameworkTransactionProvider<T> if you are using Entity Framework Core, where T is your DbContext.

Paramore.Brighter.PostgreSql.EntityFrameworkCore brings the EF Core of your target framework — EF Core 10 on net10.0 — so reference the matching major version of Npgsql.EntityFrameworkCore.PostgreSQL. On net10.0, its 9.x provider fails at runtime with a MissingMethodException.

Example with Entity Framework Core

For more detailed information on integrating with Entity Framework Core, please see the EF Core Outbox documentation.

Here is a complete example of configuring the PostgreSQL Outbox with EF Core.

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using Paramore.Brighter;
using Paramore.Brighter.Extensions.DependencyInjection;
using Paramore.Brighter.PostgreSql;
using Paramore.Brighter.PostgreSql.EntityFrameworkCore;
using Paramore.Brighter.Outbox.PostgreSql;
using Paramore.Brighter.Outbox.Hosting;

// In your DbContext, you would have your entities
public class MyDbContext : DbContext
{
    // ... DbSets for your entities
    public MyDbContext(DbContextOptions<MyDbContext> options) : base(options) {}
}

// In your Startup class; in Program.cs, the same calls go on builder.Services
public class Startup
{
    public void ConfigureServices(IServiceCollection services)
    {
        var connectionString = "Host=localhost;Port=5432;Database=brighter;Username=postgres;Password=password";
    
        // 1. Add your DbContext
        services.AddDbContext<MyDbContext>(options => 
            options.UseNpgsql(connectionString)
        );

        // 2. Configure the Outbox
        var outboxConfiguration = new RelationalDatabaseConfiguration(connectionString, outBoxTableName: "Outbox");
        services.AddSingleton<IAmARelationalDatabaseConfiguration>(outboxConfiguration);

        // 3. Configure Brighter
        services.AddBrighter(options =>
        {
            // ... other Brighter options
        })
        .AddProducers(producers =>
        {
            producers.Outbox = new PostgreSqlOutbox(outboxConfiguration);
            producers.ConnectionProvider = typeof(PostgreSqlConnectionProvider);
            // Use the EF Core transaction provider with your DbContext
            producers.TransactionProvider = typeof(PostgreSqlEntityFrameworkTransactionProvider<MyDbContext>);
        
            // ... configure your producers (e.g., for RabbitMQ, Kafka)
        })
        .UseOutboxSweeper() // Optionally add the background sweeper service
        .AutoFromAssemblies(); // Scan for handlers and mappers
    }
}

Running more than one instance? Configure a distributed lock so only one Sweeper (and Archiver) runs at a time — see Postgres Distributed Lock.