SQLite Integration

INFO

Wolverine can use the SQLite durability options with any mix of Entity Framework Core as a higher level persistence framework. SQLite is a great choice for smaller applications, development/testing scenarios, or single-node deployments where you want durable messaging without the overhead of a separate database server.

Wolverine supports a SQLite backed message persistence strategy and even a SQLite backed messaging transport option. To get started, add the WolverineFx.Sqlite dependency to your application:

dotnet add package WolverineFx.Sqlite

Message Persistence

To enable SQLite to serve as Wolverine's transactional inbox and outbox, you just need to use the WolverineOptions.PersistMessagesWithSqlite() extension method as shown below in a sample:

var builder = WebApplication.CreateBuilder(args);
var connectionString = builder.Configuration.GetConnectionString("sqlite");

builder.Host.UseWolverine(opts =>
{
    // Setting up SQLite-backed message storage
    // This requires a reference to Wolverine.Sqlite
    opts.PersistMessagesWithSqlite(connectionString!);

    // Other Wolverine configuration
});

// This is rebuilding the persistent storage database schema on startup
// and also clearing any persisted envelope state
builder.Host.UseResourceSetupOnStartup();

var app = builder.Build();

// Other ASP.Net Core configuration...

// Using JasperFx opens up command line utilities for managing
// the message storage
return await app.RunJasperFxCommands(args);

^{snippet source | anchor}

Connection String Examples

Use file-based SQLite databases for Wolverine durability:

// File-based database (recommended)
opts.PersistMessagesWithSqlite("Data Source=wolverine.db");

// File-based database in an application data folder
opts.PersistMessagesWithSqlite("Data Source=./data/wolverine.db");

^{snippet source | anchor}

WARNING

In-memory SQLite connection strings are intentionally not supported for Wolverine durability. Use file-backed SQLite databases instead.

SQLite Messaging Transport

The WolverineFx.Sqlite Nuget also contains a simple messaging transport that was mostly meant to be usable for teams who want asynchronous queueing without introducing more specialized infrastructure. To enable this transport in your code, use this option which also activates SQLite backed message persistence:

var builder = Host.CreateApplicationBuilder();
builder.UseWolverine(opts =>
{
    var connectionString = builder.Configuration.GetConnectionString("sqlite");
    opts.UseSqlitePersistenceAndTransport(connectionString!)

        // Tell Wolverine to build out all necessary queue or scheduled message
        // tables on demand as needed
        .AutoProvision()

        // Optional that may be helpful in testing, but probably bad
        // in production!
        .AutoPurgeOnStartup();

    // Use this extension method to create subscriber rules
    opts.PublishAllMessages().ToSqliteQueue("outbound");

    // Use this to set up queue listeners
    opts.ListenToSqliteQueue("inbound")

        // Optionally specify how many messages to
        // fetch into the listener at any one time
        .MaximumMessagesToReceive(50);
});

using var host = builder.Build();
await host.StartAsync();

^{snippet source | anchor}

The SQLite transport is strictly queue-based at this point. The queues are configured as durable by default, meaning that they are utilizing the transactional inbox and outbox. The SQLite queues can also be buffered:

opts.ListenToSqliteQueue("sender").BufferedInMemory();

^{snippet source | anchor}

Using this option just means that the SQLite queues can be used for both sending or receiving with no integration with the transactional inbox or outbox. This is a little more performant, but less safe as messages could be lost if held in memory when the application shuts down unexpectedly.

Polling

Wolverine has a number of internal polling operations, and any SQLite queues will be polled on a configured interval. The default polling interval is set in the DurabilitySettings class and can be configured at runtime as below:

var builder = Host.CreateApplicationBuilder();
builder.UseWolverine(opts =>
{
    // Health check message queue/dequeue
    opts.Durability.HealthCheckPollingTime = TimeSpan.FromSeconds(10);

    // Node reassignment checks
    opts.Durability.NodeReassignmentPollingTime = TimeSpan.FromSeconds(5);

    // User queue poll frequency
    opts.Durability.ScheduledJobPollingTime = TimeSpan.FromSeconds(5);
});

^{snippet source | anchor}

You can also override the polling interval for a specific queue:

opts.ListenToSqliteQueue("inbound").PollingInterval(2.Seconds());

When not set, the queue falls back to the global DurabilitySettings.ScheduledJobPollingTime.

Control queue

Wolverine has an internal control queue (dbcontrol) used for internal operations. This queue is hardcoded to poll every second and should not be changed to ensure the stability of the application.

Deterministic Queue Ordering

A SQLite queue row's timestamp defaults to datetime('now'), which only has one-second precision, so every message sent to a queue within the same second gets the same timestamp. Scheduled messages that are promoted to the ready queue together also share one timestamp. Ties are the normal case on SQLite, not an edge case.

By default, the listeners read rows in timestamp order and hand each batch to the handlers in that order, and scheduled messages are promoted in execution_time order. Neither requires a schema change. Rows with the same timestamp have no defined order, though. To break those ties deterministically, opt in:

opts.UseSqlitePersistenceAndTransport(connectionString)
    .EnableDeterministicQueueOrdering();

The same option is available from PersistMessagesWithSqlite(connectionString).EnableMessageTransport(x => x.EnableDeterministicQueueOrdering()).

The tie-breaker is SQLite's implicit rowid. The queue tables have a text primary key, so each row already carries a rowid, and a new row always gets a larger rowid than every row still in the table. No column is added. With the option enabled, the listeners order by timestamp, rowid, and promotion inserts due scheduled messages in execution_time, rowid order, so promoted messages keep their order in the ready queue. It applies to both durable and buffered listeners, and to every tenant database.

The schema change is one index on each queue table: idx_{table}_timestamp on the ready queue table and idx_{table}_execution_time on the scheduled table. SQLite stores the rowid in every index entry, so these indexes serve the timestamp, rowid and execution_time, rowid ordering without a sort. They are created in place on existing tables, without copying rows, and existing messages keep their rowids.

Messages that are resent to a queue, for example when a listener defers one, are written again and go to the back of the queue. The order is stable within a queue; it does not define a global order across different queues or different SQLite files.

The option is disabled by default so existing applications do not receive an automatic queue schema change. With AutoCreate.None, apply the Wolverine resource migration before starting with the option enabled. Configure the option consistently on every application using the same SQLite file; once the schema has been upgraded, leaving the option disabled keeps the added indexes. Rolling back to a version that predates this option may drop the indexes again, which loses no messages.

Prefixing the Queue Tables 6.50

By default, the SQLite transport names the tables for a queue called orders wolverine_queue_orders and wolverine_queue_orders_scheduled. That's fine right up until you want two logically separate applications, or two separate sets of queues, to share one SQLite database file and they both happen to use a queue named orders. Since SQLite doesn't have schemas, Wolverine can't separate those tables the way it would with PostgreSQL or SQL Server. Instead, you can opt into a prefix on the queue table names:

opts.UseSqlitePersistenceAndTransport("Data Source=shared.db")

    // The "orders" queue now lives in reporting_wolverine_queue_orders
    // and reporting_wolverine_queue_orders_scheduled
    .PrefixName("reporting");

opts.ListenToSqliteQueue("orders");

^{snippet source | anchor}

The same option is available from PersistMessagesWithSqlite(connectionString).EnableMessageTransport(x => x.PrefixName("reporting")), and from FisherIntegration.PrefixName if you're using Fisher.

A few things to know about this:

• It's completely opt in. If you never call PrefixName(), or you pass SQLite's built-in "main", the queue tables keep the exact names they've always had, so existing applications don't lose track of the messages already sitting in those tables.
• It only applies to the queue tables. To prefix Wolverine's own inbox, outbox and node tables too, use the schema name argument to PersistMessagesWithSqlite() as described in Schema Names.
• Every application on a queue has to agree. A sender and a listener using different prefixes are talking to different tables, and the messages will just sit there.
• Changing the prefix later points at new, empty tables. Wolverine does not copy messages from the old tables to the new ones, so drain the queues first or move the rows yourself.
• SQLite still allows only one writer per file. The prefix separates table names; it doesn't change how SQLite handles concurrent writes.

Resetting in Tests

RebuildAsync() / ClearAllAsync() on the message store clear envelope storage only — they leave this transport's queue and scheduled-message tables alone. To wipe both in an integration test harness, call IHost.ClearAllWolverineStorageAsync(), which leaves the queue tables built but empty across every tenant database.

Lightweight Saga Usage

See the details on Lightweight Saga Storage for more information.

SQLite saga storage uses a TEXT column (JSON serialized) for saga state and supports optimistic concurrency with version tracking.

SQLite-Specific Considerations

Advisory Locks

SQLite does not have native advisory locks like PostgreSQL. Wolverine uses a table-based locking mechanism (wolverine_locks table) to emulate advisory locks for distributed locking. Locks are acquired by inserting rows and released by deleting them.

Data Types

The SQLite persistence uses the following data type mappings:

Purpose	SQLite Type
Message body	BLOB
Saga state	TEXT (JSON)
Timestamps	TEXT (stored as datetime('now') UTC format)
GUIDs	TEXT
IDs (auto-increment)	INTEGER

Schema Names

SQLite has no user-defined schemas. The only schema names a plain connection knows are main, temp, and whatever has been ATTACHed, so there is nothing for a schema.table qualifier to point at.

Wolverine therefore treats the schema name as a table name prefix. The default is main, which prefixes nothing — the tables are wolverine_incoming_envelopes, wolverine_outgoing_envelopes and so on:

opts.PersistMessagesWithSqlite(connectionString, "reporting");
// => reporting_wolverine_incoming_envelopes, reporting_wolverine_outgoing_envelopes, ...

That gives the setting a meaning SQLite can honour: several logically separate Wolverine table sets living in one database file without colliding. Saga tables take the same prefix. The advisory lock table (wolverine_locks) deliberately does not — lock ids are derived from the schema name, so prefixed table sets share one lock table and still take distinct rows out of it.

WARNING

Before Wolverine 6.28.1 the schema name was passed through as a real schema qualifier, which meant any value other than main produced SQL against a database that was never attached and failed at runtime with SQLite Error 1: 'no such table: <name>.wolverine_incoming_envelopes'. See GH-3943.

The SQLite transport's queue tables are named by the transport rather than by the message store, so this prefix does not apply to them. To prefix the queue tables as well, see Prefixing the Queue Tables.

UseSqlitePersistenceAndTransport() is intentionally connection-string only:

opts.UseSqlitePersistenceAndTransport("Data Source=wolverine.db");

^{snippet source | anchor}

Multi-Tenancy

SQLite multi-tenancy is supported by mapping tenant ids to separate SQLite files (connection strings). You can do this with static configuration:

opts.PersistMessagesWithSqlite("Data Source=main.db")
    .RegisterStaticTenants(tenants =>
    {
        tenants.Register("red", "Data Source=red.db");
        tenants.Register("blue", "Data Source=blue.db");
    })
    .EnableMessageTransport(x => x.AutoProvision());

opts.ListenToSqliteQueue("incoming").UseDurableInbox();

^{snippet source | anchor}

Or with Wolverine-managed master-table tenancy for dynamic tenant onboarding:

opts.PersistMessagesWithSqlite("Data Source=main.db")
    .UseMasterTableTenancy(seed =>
    {
        seed.Register("red", "Data Source=red.db");
        seed.Register("blue", "Data Source=blue.db");
    })
    .EnableMessageTransport(x => x.AutoProvision());

^{snippet source | anchor}

For tenant-specific sends, set DeliveryOptions.TenantId.

await host.SendAsync(new SampleTenantMessage("hello"), new DeliveryOptions { TenantId = "red" });

^{snippet source | anchor}

When transport is enabled, each tenant database gets its own durable queue tables and scheduled polling.

Concurrency

SQLite uses file-level locking, which means only one writer can access the database at a time. For applications with high write throughput, consider using PostgreSQL or SQL Server instead. However, for moderate workloads and single-node deployments, SQLite performs well and eliminates the need for external database infrastructure.

Compatibility

The SQLite persistence is compatible with any platform supported by Microsoft.Data.Sqlite. The implementation uses the Weasel.Sqlite library for schema management.