# Sqlite Outbox

The SQLite Outbox provides a message store for the [Transactional Outbox pattern](/paramore-brighter-documentation/outbox-and-inbox/brighteroutboxsupport.md) using a SQLite database. This ensures that messages are saved within the same transaction as your business logic and published to a message broker later.

## **Provisioning the 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](/paramore-brighter-documentation/database-provisioning/boxprovisioning.md) for the overview and [Configuring Box Provisioning](/paramore-brighter-documentation/database-provisioning/boxprovisioningconfiguration.md) for the call-site shape. SQLite serialises migrations via file-level locking — long upgrade chains briefly block readers.

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

Use `SqliteOutboxBuilder.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.

## **NuGet Packages**

To use the SQLite 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.

```powershell
Install-Package Paramore.Brighter.Sqlite
Install-Package Paramore.Brighter.Outbox.Sqlite
```

For Entity Framework Core support:

```powershell
Install-Package Paramore.Brighter.Sqlite.EntityFrameworkCore
```

## **Database Table Schema**

The SQLite 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 `SqliteOutboxBuilder` 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](/paramore-brighter-documentation/database-provisioning/boxprovisioning.md). Either way, application-level concerns like additional indexes for query performance remain your responsibility.

### **Generating the DDL**

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

```csharp
// 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 = SqliteOutboxBuilder.GetDDL(tableName);

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

### **Example SQL Script**

Running `SqliteOutboxBuilder.GetDDL("Outbox")` will generate the following SQL script:

```sql
CREATE TABLE Outbox (
    "MessageId" TEXT NOT NULL,
    "Topic" TEXT NOT NULL,
    "MessageType" TEXT NOT NULL,
    "Timestamp" TEXT NOT NULL,
    "CorrelationId" TEXT NULL,
    "ReplyTo" TEXT NULL,
    "ContentType" TEXT NULL,
    "PartitionKey" TEXT NULL,
    "WorkflowId" TEXT NULL,
    "JobId" TEXT NULL,
    "Dispatched" TEXT NULL,
    "HeaderBag" TEXT NOT NULL,
    "Body" TEXT NOT NULL,
    "Source" TEXT NULL,
    "Type" TEXT NULL,
    "DataSchema" TEXT NULL,
    "Subject" TEXT NULL,
    "TraceParent" TEXT NULL,
    "TraceState" TEXT NULL,
    "Baggage" TEXT NULL,
    "Created" TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%f', 'now')),
    "CreatedID" INTEGER PRIMARY KEY AUTOINCREMENT,
    CONSTRAINT "PK_Outbox" PRIMARY KEY ("MessageId")
);
```

## **Configuration**

To configure the SQLite 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 SQLite database connection. We recommend retrieving the connection string from your application's configuration (e.g., `appsettings.json`) rather than hardcoding it.

```csharp
// Get connection string from configuration
var connectionString = "Data Source=brighter.db";

// 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 `SqliteUnitOfWork` for ADO.NET-based transaction management.
* Use `SqliteEntityFrameworkConnectionProvider<T>` if you are using Entity Framework Core, where `T` is your `DbContext`.

### **Example with Entity Framework Core**

For more detailed information on integrating with Entity Framework Core, please see the [EF Core Outbox documentation](https://github.com/BrighterCommand/Docs/blob/master/contents/EFCoreOutbox.md).

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

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

// In ConfigureServices or Program.cs
public void ConfigureServices(IServiceCollection services)
{
    var connectionString = "Data Source=brighter.db";
    
    // 1. Add your DbContext
    services.AddDbContext<MyDbContext>(options => 
        options.UseSqlite(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 SqliteOutbox(outboxConfiguration);
        producers.ConnectionProvider = typeof(SqliteConnectionProvider);
        // Use the EF Core transaction provider with your DbContext
        producers.TransactionProvider = typeof(SqliteEntityFrameworkTransactionProvider<MyDbContext>);
        
        // ... configure your producers (e.g., for RabbitMQ, Kafka)
    })
    .UseOutboxSweeper() // Optionally add the background sweeper service
    .AutoFromAssemblies(); // Scan for handlers and mappers
}
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://brightercommand.gitbook.io/paramore-brighter-documentation/outbox-and-inbox/sqliteoutbox.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.