Skip to content

PostgreSQL Schema Management

Jump to bottom
A. Shafie edited this page May 29, 2026 · 1 revision

PostgreSQL Schema Management

LiteBus PostgreSQL inbox and outbox stores need a small, stable set of tables and indexes. v5 ships schema helpers and canonical .sql files that let you choose how those objects are created, upgraded, and validated without tying LiteBus to a specific migration framework.

Two Different Version Concepts

LiteBus uses two independent version numbers. Mixing them up is a common source of confusion.

Version Stored where What it versions Who changes it
Contract version Each inbox/outbox row (contract_version) JSON payload shape for one message type Your application when you register Contracts.Register<T>(name, version: 2)
Table schema version litebus_schema_versions metadata table Physical columns and indexes for the store table LiteBus when you call EnsureAsync or run published upgrade scripts

Contract version is per message type. Table schema version is per physical store table.

Packages

Package Role
LiteBus.PostgreSql Shared SQL templates, identifier quoting, schema version metadata, advisory locks, logging hook, drift exceptions
LiteBus.Inbox.PostgreSql Inbox store, PostgreSqlInboxSchema helper, inbox SQL files
LiteBus.Outbox.PostgreSql Outbox store, PostgreSqlOutboxSchema helper, outbox SQL files
LiteBus.Inbox.PostgreSql.Extensions.Microsoft.Hosting Opt-in hosted schema creation for inbox
LiteBus.Outbox.PostgreSql.Extensions.Microsoft.Hosting Opt-in hosted schema creation for outbox

LiteBus.PostgreSql depends only on Npgsql.

Canonical SQL Files (recommended for production)

LiteBus ships the DDL as plain .sql files. The runtime loads them as embedded resources inside the DLL (so EnsureAsync and GetCreateScript work from NuGet). The same files are also included as loose files in the NuGet package under sql/ for copy-paste into migration tools.

Copy from the installed NuGet package

After dotnet add package LiteBus.Inbox.PostgreSql, open the package folder in your NuGet cache:

~/.nuget/packages/litebus.inbox.postgresql/{version}/sql/inbox/v1/create.sql
~/.nuget/packages/litebus.inbox.postgresql/{version}/sql/inbox/v1/ensure_indexes.sql

Shared metadata and upgrade scripts ship in LiteBus.PostgreSql:

~/.nuget/packages/litebus.postgresql/{version}/sql/metadata/create.sql
~/.nuget/packages/litebus.postgresql/{version}/sql/shared/add_trace_context_column.sql

On Windows the root is typically %USERPROFILE%\.nuget\packages\.

Copy from the GitHub repository

You can also copy from the LiteBus source tree (same file contents as the NuGet package):

File Purpose
src/LiteBus.PostgreSql/Sql/metadata/create.sql Creates litebus_schema_versions
src/LiteBus.Inbox.PostgreSql/Sql/inbox/v1/create.sql Creates the version 1 inbox table and indexes
src/LiteBus.Inbox.PostgreSql/Sql/inbox/v1/ensure_indexes.sql Re-applies inbox indexes idempotently
src/LiteBus.Outbox.PostgreSql/Sql/outbox/v1/create.sql Creates the version 1 outbox table and indexes
src/LiteBus.Outbox.PostgreSql/Sql/outbox/v1/ensure_indexes.sql Re-applies outbox indexes idempotently
src/LiteBus.PostgreSql/Sql/shared/add_trace_context_column.sql Version 2 upgrade shared by inbox and outbox

Discover files from code as well:

PostgreSqlSchemaSqlPaths.Files              // shared metadata and upgrade SQL
PostgreSqlInboxSchema.SqlFiles              // inbox SQL catalog
PostgreSqlOutboxSchema.SqlFiles             // outbox SQL catalog

// Repository path constants:
PostgreSqlInboxSchemaSqlPaths.V1Create      // src/LiteBus.Inbox.PostgreSql/Sql/inbox/v1/create.sql
PostgreSqlOutboxSchemaSqlPaths.V1Create     // src/LiteBus.Outbox.PostgreSql/Sql/outbox/v1/create.sql

Render scripts in code

When object names differ from LiteBus defaults, call GetCreateScript or GetUpgradeScript to render the embedded SQL templates with your options:

var options = new PostgreSqlInboxStoreOptions
{
    SchemaName = "app",
    TableName = "litebus_inbox_commands"
};

var ddl = PostgreSqlInboxSchema.GetCreateScript(options);
File.WriteAllText("V001__litebus_inbox.sql", ddl);

Both approaches are valid. Copying the .sql files gives DBAs full control. Rendering with GetCreateScript avoids manual token replacement.

Best Practices for External SQL Resources

LiteBus ships SQL as plain files that are compiled into the assembly and also copied into the NuGet package. Follow these practices when you own migrations or call schema helpers in production.

Single source of truth

Edit the .sql files under each package's Sql/ folder in the LiteBus repository. Do not maintain a forked copy with different DDL unless your DBAs require it. When you need custom object names, keep the canonical scripts and render them with GetCreateScript / GetUpgradeScript rather than hand-editing placeholders.

Embedded vs loose files

Location When it is used Best practice
Embedded resource in the DLL EnsureAsync, GetCreateScript, integration tests Trust this at runtime; it always matches the package version you referenced
Loose sql/ folder in the NuGet package DBA review, Flyway/Liquibase copy-paste Copy verbatim into your migration repo; pin the LiteBus package version in release notes
GitHub src/.../Sql/ tree Documentation and PR review Same content as the NuGet loose files for a given release tag

Runtime code never reads loose files from disk. Deployments that omit the sql/ folder from the published app still work because the embedded copy is always present.

Token replacement

Placeholders use the form {{TokenName}}. Prefer GetCreateScript(options) over manual search-and-replace so schema names, table names, and index names stay correctly quoted for PostgreSQL.

Connection configuration

Approach When to use
UseDataSource(NpgsqlDataSource) Production apps that already build one shared data source for pooling, health checks, and tracing
UseConnectionString(string) Samples, tests, and small services where the module should create and register the data source

When inbox and outbox share one database, build a single NpgsqlDataSource and pass it to both stores with UseDataSource. Calling UseConnectionString on both stores creates two pools against the same server.

// Preferred when both stores use the same database
var dataSource = NpgsqlDataSource.Create(configuration.GetConnectionString("OrdersDb")!);

liteBus.AddPostgreSqlCommandInboxStore(p => p.UseDataSource(dataSource));
liteBus.AddPostgreSqlOutboxStore(p => p.UseDataSource(dataSource));

// Simpler when only one store is registered
liteBus.AddPostgreSqlCommandInboxStore(p =>
    p.UseConnectionString(configuration.GetConnectionString("OrdersDb")!));

Version alignment

Record the LiteBus package version and CurrentSchemaVersion in your deployment runbook. After upgrading LiteBus, apply any new upgrade script before rolling out pods that call ValidateAsync with ValidateSchemaCreationOnStartup = true.

Do not edit embedded resource names casually

If you contribute to LiteBus, keep SQL paths stable. The loader resolves {AssemblyName}.Sql.{path.with.dots}.sql. Renaming folders without updating EmbeddedResource items breaks runtime schema creation.

Placeholder tokens

Token Example rendered value Used in
{{QuotedSchemaName}} "app" Store table create scripts
{{QualifiedTableName}} "app"."litebus_inbox_commands" Store DDL and v2 upgrade
{{QuotedMetadataSchemaName}} "app" Metadata table create
{{QualifiedMetadataTableName}} "app"."litebus_schema_versions" Metadata DDL
{{IdempotencyIndexName}} quoted index name Inbox only
{{LeaseIndexName}} quoted index name Inbox and outbox
{{TopicIndexName}} quoted index name Outbox only

Three Ownership Models

Pick one model per environment. You can use different models in development and production.

1. Migration-owned (recommended for production)

Your team owns schema timing. Copy the canonical .sql files or rendered output from GetCreateScript / GetUpgradeScript.

Leave EnsureSchemaCreationOnStartup = false (the default). Run validation in deploy checks if you want a fail-fast guard:

await PostgreSqlInboxSchema.ValidateAsync(dataSource, options, cancellationToken);

2. Explicit bootstrap

Call EnsureAsync from application startup, a one-shot deploy job, or integration test setup. The call is idempotent.

await PostgreSqlInboxSchema.EnsureAsync(dataSource, options, cancellationToken);
await PostgreSqlOutboxSchema.EnsureAsync(dataSource, options, cancellationToken);

Use this for internal services, prototypes, and test environments where a migration pipeline is overhead.

3. Opt-in host schema creation

Enable automatic schema creation when the generic host starts. Both the store option and the hosting module must be registered.

builder.Services.AddLiteBus(liteBus =>
{
    liteBus.AddPostgreSqlCommandInboxStore(postgres =>
    {
        postgres.UseDataSource(dataSource);
        postgres.EnsureSchemaCreationOnStartup();
    });

    // Register schema hosting BEFORE processor hosting.
    liteBus.AddPostgreSqlCommandInboxSchemaHosting();

    liteBus.AddCommandInboxModule(inbox => { /* contracts */ });
    liteBus.AddCommandInboxProcessorHosting(host => host.PollInterval = TimeSpan.FromSeconds(1));
});

The schema hosted service no-ops when EnsureSchemaCreationOnStartup is false.

Configuration Options

Both inbox and outbox share the same option shape through PostgreSqlSchemaStoreOptions.

Option Default Purpose
SchemaName public PostgreSQL schema for the store table
TableName litebus_inbox_commands / litebus_outbox_messages Store table name
MetadataSchemaName public Schema for the version metadata table
MetadataTableName litebus_schema_versions Version metadata table name
EnsureSchemaCreationOnStartup false Run EnsureAsync when the host starts
ValidateSchemaCreationOnStartup true Run ValidateAsync after schema creation on startup
Logger null Optional IPostgreSqlSchemaLogger for schema operations

Example with custom object names and logging:

postgres.UseOptions(new PostgreSqlInboxStoreOptions
{
    SchemaName = "messaging",
    TableName = "command_inbox",
    MetadataSchemaName = "messaging",
    MetadataTableName = "litebus_schema_versions",
    Logger = new ConsolePostgreSqlSchemaLogger()
});

Use the same option instance for the store registration and schema helpers so table names stay aligned.

Logging

LiteBus.PostgreSql exposes IPostgreSqlSchemaLogger without taking a dependency on Microsoft logging packages. Assign an implementation through PostgreSqlSchemaStoreOptions.Logger.

Schema operations log at these points:

  • Starting and completing EnsureAsync
  • Advisory lock acquired or waiting on another session
  • Creating version 1 objects
  • Applying version upgrades
  • Recording metadata version rows
  • Validation success or PostgreSqlSchemaDriftException details

Example adapter for local development:

public sealed class ConsolePostgreSqlSchemaLogger : IPostgreSqlSchemaLogger
{
    public void Log(PostgreSqlSchemaLogLevel level, string message, Exception? exception = null)
    {
        Console.WriteLine($"[{level}] {message}");
        if (exception is not null)
        {
            Console.WriteLine(exception);
        }
    }
}

Hosting applications can bridge IPostgreSqlSchemaLogger to ILogger<T> in the hosting package without changing LiteBus.PostgreSql dependencies.

Schema Version Metadata

LiteBus records applied table schema versions in litebus_schema_versions:

Column Purpose
component inbox or outbox
schema_name Store table schema
table_name Store table name
version Applied table schema version
applied_at UTC timestamp of the last recorded upgrade

Primary key: (component, schema_name, table_name).

One metadata table serves all LiteBus store tables in the database. Each inbox or outbox table you configure gets its own row.

Current Schema Versions (v5.0)

Component Version Notes
Inbox 2 v1 initial table; v2 adds nullable trace_context jsonb reserved for future tracing metadata
Outbox 2 v1 initial table; v2 adds nullable trace_context jsonb reserved for future tracing metadata

Constants: PostgreSqlInboxSchema.CurrentSchemaVersion and PostgreSqlOutboxSchema.CurrentSchemaVersion.

The store implementations do not read trace_context yet. The column exists so future LiteBus releases can add tracing metadata without another immediate breaking DDL change.

API Reference

Create and upgrade

Method Purpose
GetCreateScript(options) Full rendered DDL for the current schema version, metadata table, and indexes
GetUpgradeScript(from, to, options) Incremental rendered DDL between two table schema versions
EnsureAsync(dataSource, options, ct) Create, upgrade, and record the current version idempotently
CreateIfNotExistsAsync(...) Alias for EnsureAsync kept for readability in tests
ValidateAsync(dataSource, options, ct) Fail fast when the physical table or metadata does not match the library
SqlFiles Catalog of repository SQL file paths and descriptions

Exceptions

PostgreSqlSchemaDriftException is thrown by ValidateAsync and by startup validation. It includes:

  • Component (inbox / outbox)
  • SchemaName, TableName
  • ExpectedVersion, ActualVersion
  • Details (missing columns, missing table, version mismatch)

Treat this as a deployment blocker. Do not catch and ignore it in production startup.

Multi-Instance and Microservice Safety

EnsureAsync is safe when many pods or services start at the same time.

  1. Initial create uses CREATE ... IF NOT EXISTS for schemas, tables, and indexes.
  2. Upgrades use idempotent DDL such as ADD COLUMN IF NOT EXISTS.
  3. Advisory locks ensure one connection applies upgrades at a time per store table. Other instances wait until the table reaches the expected version or the wait timeout elapses.

Lock key format: litebus:{component}:{schema}:{table}.

Waiting instances poll the metadata table and physical column shape. They do not fail merely because another instance holds the lock.

What to run from every pod vs one deploy job

Operation Safe from all pods Recommended pattern
First-time EnsureAsync Yes Opt-in host schema creation or startup call
Version upgrade (EnsureAsync) Yes (with advisory lock) Prefer a deploy hook in large fleets; host schema creation is still safe
Destructive DDL (drop/rename/type change) No Manual migration only; never auto-run from LiteBus
ValidateAsync Yes Deploy check or startup when ValidateSchemaCreationOnStartup = true

Upgrade Path for Existing v1 Tables

If you created tables with early v5 previews using CreateIfNotExistsAsync before metadata existed, EnsureAsync handles the transition:

  1. Detects the existing table.
  2. Infers version 1 from column shape when metadata is missing.
  3. Applies the 1 → 2 upgrade script.
  4. Writes metadata at version 2.

If metadata says version 2 but a column was removed manually, EnsureAsync trusts the physical column shape and re-applies missing upgrades.

Registration Order With Processor Hosting

Schema creation must finish before inbox or outbox processors lease rows. Register modules in this order:

liteBus.AddPostgreSqlCommandInboxStore(/* ... */);
liteBus.AddPostgreSqlCommandInboxSchemaHosting();   // 1. schema creation
liteBus.AddCommandInboxModule(/* ... */);
liteBus.AddCommandInboxProcessorHosting(/* ... */);  // 2. processor

liteBus.AddPostgreSqlOutboxStore(/* ... */);
liteBus.AddPostgreSqlOutboxSchemaHosting();           // 1. schema creation
liteBus.AddOutboxModule(/* ... */);
liteBus.AddOutboxProcessorHosting(/* ... */);         // 2. processor

Schema hosting registers an IHostedService that completes StartAsync before background processor loops begin.

Manual Deploy Job Example

For teams that want automatic DDL without running it from every pod:

var dataSource = NpgsqlDataSource.Create(connectionString);

await PostgreSqlInboxSchema.EnsureAsync(dataSource, inboxOptions);
await PostgreSqlOutboxSchema.EnsureAsync(dataSource, outboxOptions);

await PostgreSqlInboxSchema.ValidateAsync(dataSource, inboxOptions);
await PostgreSqlOutboxSchema.ValidateAsync(dataSource, outboxOptions);

Console.WriteLine("LiteBus PostgreSQL schema is ready.");

Run this once per deployment before rolling out application pods with EnsureSchemaCreationOnStartup = false.

Flyway / Liquibase Workflow

  1. Copy the .sql files from PostgreSqlInboxSchema.SqlFiles / PostgreSqlOutboxSchema.SqlFiles into your migration repository, or generate rendered scripts with GetCreateScript.
  2. On LiteBus upgrades that bump CurrentSchemaVersion, add the new upgrade .sql file or call GetUpgradeScript(previous, current).
  3. Record the LiteBus release and schema version in your internal runbook.
  4. Call ValidateAsync from a smoke test after migration.

Prefer copying the shipped .sql files verbatim. Edit only when DBAs require renames, tablespaces, or ownership clauses.

Custom Stores

If you implement ICommandInboxWriter / IOutboxMessageWriter and your own storage, you own the schema entirely. LiteBus does not require the metadata table or trace_context column for custom stores. Match the envelope fields your implementation reads and writes.

Future LiteBus Releases

When LiteBus bumps CurrentSchemaVersion:

  1. Read the release notes and migration guide entry for the target version.
  2. Add the new .sql upgrade file to your migration pipeline, or apply GetUpgradeScript(oldVersion, newVersion).
  3. Deploy application code that expects the new library version after DDL succeeds.
  4. Prefer additive, backward-compatible DDL (nullable columns, new indexes). LiteBus auto-upgrade only runs safe scripts.

LiteBus will not auto-run destructive changes (drops, renames, narrowing type changes) from application pods.

Related Docs

Introduction

Core Modules

Advanced Concepts

Practical Guides

Ecosystem & Project

Clone this wiki locally
⚡