Sql Persistence

Project Hosting
NuGet Package NServiceBus.Persistence.Sql (4-pre)
Target NServiceBus Version: 7.x
This page targets a pre-release version and is subject to change prior to the final release.

The SQL Persistence uses Json.NET to serialize data and store in a SQL database.

Supported SQL implementations

This persistence will run on the free version of the above engines, i.e. SQL Server Express, MySQL Community Edition, Oracle XE, and PostgreSQL. However it is strongly recommended to use commercial versions for any production system. It is also recommended to ensure that support agreements are in place from the software vendor or another third party support provider. For example:

Supported name lengths

SQL persistence automatically generates names of database objects such as tables, indexes and procedures used internally. Every database engine has its own rules and limitations regarding maximum allowed name length. The default values are:

When targeting Oracle or PostgreSQL, the SQL persistence will throw an exception in case the name length is too long. See the Oracle caveats or PostgreSQL caveats to learn more.

When targeting MySQL or MS SQL Server, the SQL persistence will not validate name length, for two reasons. Firstly, the supported name length values are higher and should be sufficient for typical scenarios. Secondly, it is possible to modify the database setting locally to support longer names. In case of long names for sagas, etc. the database engine may perform automatic name truncation.

Unicode support

SQL persistence itself supports Unicode characters. However data may become corrupted during saving if the database settings are incorrect. If Unicode support is required, follow the guidelines for each database engine; in particular set the correct character set and collation for databases storing persistence data.

Refer to the dedicated MySQL, SQL Server, Oracle or PostgreSQL documentation for details.

Usage

Install the NServiceBus.Persistence.Sql and NServiceBus.Persistence.Sql.MsBuild NuGet packages.

SQL Server

var connection = @"Data Source=.\SqlExpress;Initial Catalog=dbname;Integrated Security=True";
var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
var subscriptions = persistence.SubscriptionSettings();
subscriptions.CacheFor(TimeSpan.FromMinutes(1));
persistence.SqlDialect<SqlDialect.MsSqlServer>();
persistence.ConnectionBuilder(
    connectionBuilder: () =>
    {
        return new SqlConnection(connection);
    });
Microsoft SQL Server does not support DTC transactions in all deployment models (such as database mirroring or Always On configurations) and support differs between versions of SQL Server. See Transactions - Always On availability groups and Database Mirroring for more information.

MySQL

Using the MySql.Data NuGet Package.

var connection = "server=localhost;user=root;database=dbname;port=3306;password=pass;AllowUserVariables=True;AutoEnlist=false";
var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
var subscriptions = persistence.SubscriptionSettings();
subscriptions.CacheFor(TimeSpan.FromMinutes(1));
persistence.SqlDialect<SqlDialect.MySql>();
persistence.ConnectionBuilder(
    connectionBuilder: () =>
    {
        return new MySqlConnection(connection);
    });

The following settings are required for MySQL connection strings.

  • AllowUserVariables=True: since the Persistence uses user variables.
  • AutoEnlist=false: To prevent auto enlistment in a Distributed Transaction which the MySql .NET connector does not currently support.

Oracle

Using the Oracle.ManagedDataAccess NuGet Package.

var connection = "Data Source=localhost;User Id=username;Password=pass;Enlist=false;";
var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
var subscriptions = persistence.SubscriptionSettings();
subscriptions.CacheFor(TimeSpan.FromMinutes(1));
persistence.SqlDialect<SqlDialect.Oracle>();
persistence.ConnectionBuilder(
    connectionBuilder: () =>
    {
        return new OracleConnection(connection);
    });

In Versions 2.2.0 and above it's possible to specify custom schema using the following code:

var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
persistence.SqlVariant(SqlVariant.Oracle);
persistence.Schema("custom_schema");
The ODP.NET managed driver requires the Enlist=false or Enlist=dynamic setting in the Oracle connection string to allow the persister to enlist in a Distributed Transaction at the correct moment.

PostgreSQL

Using the Npgsql NuGet Package.

var connection = "Server=localhost;Port=5432;Database=dbname;User Id=user;Password=pass;";
var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
var subscriptions = persistence.SubscriptionSettings();
subscriptions.CacheFor(TimeSpan.FromMinutes(1));
var dialect = persistence.SqlDialect<SqlDialect.PostgreSql>();
dialect.JsonBParameterModifier(
    modifier: parameter =>
    {
        var npgsqlParameter = (NpgsqlParameter)parameter;
        npgsqlParameter.NpgsqlDbType = NpgsqlDbType.Jsonb;
    });
persistence.ConnectionBuilder(
    connectionBuilder: () =>
    {
        return new NpgsqlConnection(connection);
    });

Passing Jsonb as NpgsqlDbType

Npgsql requires that parameters that pass JSONB data explicitly have NpgsqlParameter.NpgsqlDbType set to Npgsql​Db​Type.Jsonb. Npgsql does not infer this based on the DB column type. It is not possible for the Sql Persistence to control this setting while still avoiding a reference to Npgsql.

As such it is necessary to explicitly set NpgsqlParameter.NpgsqlDbType to NpgsqlDbType.Jsonb:

var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
var dialect = persistence.SqlDialect<SqlDialect.PostgreSql>();
dialect.JsonBParameterModifier(
    modifier: parameter =>
    {
        var npgsqlParameter = (NpgsqlParameter)parameter;
        npgsqlParameter.NpgsqlDbType = NpgsqlDbType.Jsonb;
    });

Json.Net TypeNameHandling

When using Json.Net $type feature via TypeNameHandling, then MetadataPropertyHandling should be set to ReadAhead.

var settings = new JsonSerializerSettings
{
    TypeNameHandling = TypeNameHandling.Auto,
    MetadataPropertyHandling = MetadataPropertyHandling.ReadAhead
};
var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
var sagaSettings = persistence.SagaSettings();
sagaSettings.JsonSettings(settings);

The reason for this is Json.Net normally expects the $type metadata to be the first property of each object for best efficiency in deserialization. If the $type does not appear first, then Json.Net assumes it isn't there. When using the PostgreSQL Jsonb the JSON stored does not preserve the order of object keys. This can result in the $type being stored in a non-first position.

NuGet Packages

The SQL Persistence consists of several Nuget Packages.

NServiceBus.Persistence.Sql.MsBuild

This packages installs into the MSBuild pipeline and generates all SQL installation scripts at compile time. It does this by interrogating types (in the target assembly) and attributes (from the NServiceBus.Persistence.Sql NuGet package) to infer what scripts to create. It is required for any project where those SQL installation scripts are required. For Saga scripts it will be any project that contains Saga classes. For Timeouts, Subscriptions and Outbox scripts it will be the endpoint hosting project. This package has a dependency on the NServiceBus.Persistence.Sql NuGet package

NServiceBus.Persistence.Sql

This package contains several parts

  • APIs for manipulating EndPointConfiguration at configuration time.
  • Runtime implementations of Saga, Timeouts, Subscriptions and Outbox persisters.
  • Attribute definitions used to define certain compile time configuration settings. These attributes are then interrogated by the NServiceBus.Persistence.Sql.MsBuild NuGet Package when generating SQL installation scripts
  • Optionally runs SQL installation scripts at endpoint startup for development purposes. See Installer Workflow.

NServiceBus.Persistence.Sql.ScriptBuilder

This package contains all APIs that enable the generation of SQL installation scripts using code, i.e. without using the NServiceBus.Persistence.Sql.MsBuild NuGet package.

NServiceBus.Persistence.Sql.ScriptBuilder is currently not ready for general usage. It has been made public, and deployed to NuGet, primarily to enable the generation of documentation in a repeatable way. For example it is used to generate the SQL scripts in the MS SQL Server Scripts, MySql Scripts, and Oracle Scripts pages. In future releases, the API may evolve in ways that do not follow the standard of Release Policy - Semantic Versioning. Raise an issue in the NServiceBus.Persistence.Sql Repository to discuss this in more detail.

Script creation

SQL installation scripts are created at compile time by the NServiceBus.Persistence.Sql.MsBuild NuGet package.

Scripts will be created in the directory format of [CurrentProjectDebugDir]\NServiceBus.Persistence.Sql\[SqlDialect].

For example for a project named ClassLibrary built in Debug mode the following directories will be created.

  • ClassLibrary\bin\Debug\NServiceBus.Persistence.Sql\MsSqlServer
  • ClassLibrary\bin\Debug\NServiceBus.Persistence.Sql\MySql
  • ClassLibrary\bin\Debug\NServiceBus.Persistence.Sql\Oracle

Scripts will also be included in the list of project output files. This means those files produced will be copied to the output directory of any project that references it.

Scripts creation can configured via [SqlPersistenceSettings] applied to the target assembly.

SQL scripts are not copied to the publish directory with dotnet publish.
Projects using project.json are not supported. The project.json approach was an experiment by Microsoft at a new project system that was not based on MSBuild. Since project.json did not support running MSBuild files shipped inside a NuGet the SQL Persistence script creation does not work. This experiment has since been abandoned. To fix this either migrate back to the old Visual Studio 2015 project format (.csproj and packages.config) or migrate to the new Visual Studio 2017 project format. dotnet-migrate can help migrating to the new .csproj format.

To produce all scripts

[assembly: SqlPersistenceSettings(
    MsSqlServerScripts = true,
    MySqlScripts = true,
    OracleScripts = true,
    PostgreSqlScripts = true)]

To produce only MS SQL Server scripts

[assembly: SqlPersistenceSettings(MsSqlServerScripts = true)]

To produce only MySQL scripts

[assembly: SqlPersistenceSettings(MySqlScripts = true)]

To produce only Oracle scripts

[assembly: SqlPersistenceSettings(OracleScripts = true)]

To produce only PostgresSql scripts

[assembly: SqlPersistenceSettings(PostgreSqlScripts = true)]

Toggle script creation

At compile time the SQL persistence validates that sagas are of the correct base type and match the required conventions. This can be problematic when mixing persistences. For example using the SQL persistence for timeouts but another persister for saga storage. As such it is possible to selectively control what SQL scripts are generated at compile time, and as a side effect avoid the convention validation of sagas.

Disable outbox script creation

[assembly: SqlPersistenceSettings(
    ProduceOutboxScripts = false)]

Disable saga script creation

[assembly: SqlPersistenceSettings(
    ProduceSagaScripts = false)]

Disable subscriptions script creation

[assembly: SqlPersistenceSettings(
    ProduceSubscriptionScripts = false)]

Disable timeout script creation

[assembly: SqlPersistenceSettings(
    ProduceTimeoutScripts = false)]

Promotion

As stated above, scripts are created in the target project output directory. Generally this directory will be excluded from source control. To add created scripts to source control they can be "promoted".

The target directory will be deleted and recreated as part of each build. Be sure to choose a path that is for script promotion only.

Some token replacement using MSBuild variables is supported.

  • $(SolutionDir): The directory of the solution.
  • $(ProjectDir): The directory of the project

All tokens are drive + path and include the trailing backslash \.

[assembly: SqlPersistenceSettings(
    ScriptPromotionPath = "$(SolutionDir)PromotedSqlScripts")]

The path calculation is performed relative to the current project directory. So, for example, a value of PromotedSqlScripts (with no tokens) would evaluate as $(ProjectDir)PromotedSqlScripts.

Samples

Related Articles


Last modified