Akka.Persistence.Sql

A Cross-SQL-DB Engine Akka.Persistence plugin with broad database compatibility thanks to Linq2Db.

This is a port of the amazing akka-persistence-jdbc package from Scala, with a few improvements based on C# as well as our choice of data library.

Please read the documentation carefully. Some features may have specific use case and have trade-offs (namely, compatibility modes).

If you're migrating from legacy Akka.Persistence.Sql.Common based plugins, you can read the migration guide documentation, the migration tutorial, and watch the migration tutorial video.

Table Of Content

• Akka.Persistence.Sql
• Getting Started
  • The Easy Way, Using Akka.Hosting
    • Health Checks
  • The Classic Way, Using HOCON
  • Supported Database Providers
    • Tested Database Providers
    • Supported By Linq2Db But Untested In Akka.Persistence
• Sql.Common Compatibility modes
• Migration Guide
  • Migrating Using Compatibility Mode
    • Akka.Hosting Migration
    • HOCON Migration
  • Upgrading to Tag Table (Optional)
  • Enable WriterUuid Anti-Corruption Layer Feature (Recommended)
• Migration Tutorial
• Features/Architecture
  • Currently Implemented
  • Incomplete
• Performance Benchmarks
• Configuration
  • Journal
  • Snapshot Store
• DDL Scripts for Database Schema Management
• Tuning Akka.Persistence.Query for Low-Latency Projections
• Building this solution
  • Conventions
  • Release Notes, Version Numbers, Etc

Getting Started

The Easy Way, Using Akka.Hosting

Assuming a MS SQL Server 2019 setup:

var host = new HostBuilder()
    .ConfigureServices((context, services) => {
        services.AddAkka("my-system-name", (builder, provider) =>
        {
            builder.WithSqlPersistence(
                connectionString: _myConnectionString,
                providerName: ProviderName.SqlServer2019);
        });
    });

You can also provide your own LinqToDb.DataOptions object for a more advanced configuration. Assuming a setup with a custom NpgsqlDataSource:

NpgsqlDataSource dataSource = new NpgsqlDataSourceBuilder(_myConnectionString).Build();

DataOptions dataOptions = new DataOptions()
    .UseDataProvider(DataConnection.GetDataProvider(ProviderName.PostgreSQL, dataSource.ConnectionString) ?? throw new Exception("Could not get data provider"))
    .UseProvider(ProviderName.PostgreSQL)
    .UseConnectionFactory((opt) => dataSource.CreateConnection());
    
var host = new HostBuilder()
    .ConfigureServices((context, services) => {
        services.AddAkka("my-system-name", (builder, provider) =>
        {
            builder.WithSqlPersistence(dataOptions);
        });
    });

If dataOptions are provided, you must supply enough information for linq2db to connect to the database. This includes setting the connection string and provider name again, if necessary for your use case. Please consult the Linq2Db documentation for more details on configuring a valid DataOptions object. Note that MappingSchema and RetryPolicy will always be overridden by Akka.Persistence.Sql.

Health Checks

Starting with Akka.Persistence.Sql v1.5.51 or later, you can add health checks for your persistence plugins. Akka.Persistence.Sql supports two types of health checks that integrate with Microsoft.Extensions.Diagnostics.HealthChecks and can be used with ASP.NET Core health check endpoints.

Standard Health Checks

Standard health checks monitor the persistence plugins themselves and verify that journals and snapshot stores are properly initialized and accessible.

To configure standard health checks, use the journalBuilder and snapshotBuilder parameters with the .WithHealthCheck() method:

var host = new HostBuilder()
    .ConfigureServices((context, services) => {
        services.AddAkka("my-system-name", (builder, provider) =>
        {
            builder.WithSqlPersistence(
                connectionString: _myConnectionString,
                providerName: ProviderName.SqlServer2019,
                journalBuilder: journal => journal.WithHealthCheck(HealthStatus.Degraded),
                snapshotBuilder: snapshot => snapshot.WithHealthCheck(HealthStatus.Degraded));
        });
    });

Standard health checks are tagged with akka, persistence, and either journal or snapshot-store for filtering and organization purposes.

Connectivity Health Checks (Akka.Persistence.Sql.Hosting v1.5.X+)

Connectivity health checks proactively verify database connectivity regardless of recent operation activity. This helps detect database outages during idle periods when the standard health checks might not catch issues.

Using Akka.Hosting 1.5.55.1 or later (Simplified API):

services.AddAkka("my-system-name", (builder, provider) =>
{
    builder.WithSqlPersistence(
        connectionString: connectionString,
        providerName: ProviderName.SqlServer2022,
        journalBuilder: journal =>
        {
            journal.WithHealthCheck();           // Monitor plugin status
            journal.WithConnectivityCheck();     // Verify database connectivity
        },
        snapshotBuilder: snapshot =>
        {
            snapshot.WithHealthCheck();          // Monitor plugin status
            snapshot.WithConnectivityCheck();    // Verify database connectivity
        });
});

The connectivity checks will automatically:

• Verify connectivity to the underlying SQL database
• Test database responsiveness with a lightweight "SELECT 1" query
• Report Healthy when the database is accessible
• Report Unhealthy (configurable) when the database is unreachable or unresponsive

Connectivity checks are tagged with akka, persistence, sql, and either journal or snapshot-store for filtering and organization purposes.

Exposing Health Checks via ASP.NET Core

For ASP.NET Core applications, you can expose these health checks via an endpoint:

var builder = WebApplication.CreateBuilder(args);

// Add health checks service
builder.Services.AddHealthChecks();

builder.Services.AddAkka("my-system-name", (configBuilder, provider) =>
{
    configBuilder.WithSqlPersistence(
        connectionString: _myConnectionString,
        providerName: ProviderName.SqlServer2019,
        journalBuilder: journal => journal.WithHealthCheck(),
        snapshotBuilder: snapshot => snapshot.WithHealthCheck());
});

var app = builder.Build();

// Map health check endpoint
app.MapHealthChecks("/healthz");

app.Run();

Customizing Health Check Configuration

You can customize the names, tags, and unhealthy status for both standard and connectivity health checks:

services.AddAkka("my-system-name", (builder, provider) =>
{
    builder.WithSqlPersistence(
        connectionString: connectionString,
        providerName: ProviderName.SqlServer2022,
        journalBuilder: journal =>
        {
            // Customize standard health check
            journal.WithHealthCheck(
                unHealthyStatus: HealthStatus.Degraded,
                name: "sql-journal",
                tags: new[] { "backend", "database", "sql" });

            // Customize connectivity health check
            journal.WithConnectivityCheck(
                unHealthyStatus: HealthStatus.Unhealthy,
                name: "sql-journal-connectivity",
                tags: new[] { "backend", "database", "sql", "connectivity" });
        },
        snapshotBuilder: snapshot =>
        {
            // Customize standard health check
            snapshot.WithHealthCheck(
                unHealthyStatus: HealthStatus.Degraded,
                name: "sql-snapshot",
                tags: new[] { "backend", "database", "sql" });

            // Customize connectivity health check
            snapshot.WithConnectivityCheck(
                unHealthyStatus: HealthStatus.Unhealthy,
                name: "sql-snapshot-connectivity",
                tags: new[] { "backend", "database", "sql", "connectivity" });
        });
});

Default tags when not specified:

• Standard health checks: ["akka", "persistence", "journal"] or ["akka", "persistence", "snapshot-store"]
• Connectivity health checks: ["akka", "persistence", "sql", "journal", "connectivity"] or ["akka", "persistence", "sql", "snapshot-store", "connectivity"]

The Classic Way, Using HOCON

These are the minimum HOCON configuration you need to start using Akka.P