Getting Started
Architecture
NServiceBus
Transports
ServiceInsight
ServicePulse
ServiceControl
Monitoring
Samples

Saga Persister

Component: Sql Persistence
Target Version: NServiceBus 9.x

SQL persistence supports sagas using the core NServiceBus.Saga API or an experimental API unique to SQL persistence that provides a simpler mapping API.

Table structure

Table name

The name used for a saga table consists of two parts:

  • The prefix of the table name is the table prefix defined at the endpoint level.
  • The suffix of the table name is either the saga Type.Name or, if defined, the table suffix defined at the saga level.

On an NServiceBus Saga<T>, the table suffix can be overridden by decorating the saga class with an attribute:

[SqlSaga(
    tableSuffix: "TheCustomTableName"
)]
public class MySaga
    : Saga<MySaga.SagaData>
    // Rest of saga declaration omitted

The table suffix can also be defined more easily using a property override when using a SqlSaga<T> base class.

Columns

ID

The value of IContainSagaData.Id. Primary Key.

Metadata

A JSON-serialized dictionary containing all NServiceBus-managed information about the saga.

Data

The JSON-serialized saga data.

PersistenceVersion

The assembly version of the SQL persister.

SagaTypeVersion

The version of the assembly where the saga exists.

Correlation columns

There are between 0 and 2 correlation ID columns named Correlation_[PROPERTYNAME]. The type will correspond to the .NET type of the mapped property on the saga data.

For each correlation ID there will be a corresponding index named Index_Correlation_[PROPERTYNAME].

Correlation IDs

Saga message correlation is implemented by promoting the correlation property to the level of a column on the saga table. When a saga data is persisted the correlation property is copied from the instance and duplicated in a column named by convention (Correlation_[PROPERTYNAME]) on the table.

For NServiceBus sagas, in most cases the correlation property can be inferred at compile time by inspecting the intermediate language (IL) for calls to .ToSaga(sagaData => sagaData.CorrelationPropertyName). There are a few unsupported instances where this is impossible and an exception will be thrown:

  • Use of an external method or delegate
  • Branching or looping logic inside the ConfigureHowToFindSaga method
  • Non-matching correlation properties for multiple message types

In these cases, either redesign the saga to avoid these patterns, specify the correlation property with a [SqlSaga] attribute, or use the SqlSaga<T> base class.

Specifying correlation ID using an attribute

In rare cases where the correlation property cannot be inferred from the ConfigureHowToFindSaga method, it can be specified with the [SqlSaga] attribute:

[SqlSaga(
    correlationProperty: nameof(SagaData.OrderId)
)]
public class SagaWithAmbiguousCorrelationProperty 
    : Saga<SagaWithAmbiguousCorrelationProperty.SagaData>
// Rest of saga declaration omitted

Transitional correlation ID

In cases where business requirements dictate that the correlation property for a saga must change, a transitional correlation property can be used to gradually make that change over time, taking into account that there may be in-flight messages and in-progress sagas that are not updated with the new data.

If an incoming message cannot be mapped to a saga data instance using the correlation property, a saga that has a defined transitional correlation property will also query against the additional column for a match. Once all sagas have been updated to contain the transitional ID, the old correlation property can be retired and the transitional property can become the new standard correlation property.

To define a transitional correlation property on a saga, use the [SqlSaga] attribute:

[SqlSaga(
    correlationProperty: nameof(SagaData.CorrelationProperty),
    transitionalCorrelationProperty: nameof(SagaData.TransitionalCorrelationProperty)
)]
public class OrderSaga
    : Saga<OrderSaga.SagaData>
    // Rest of saga declaration omitted

A transitional correlation property can also be expressed as a class property when using the SqlSaga base class.

Correlation types

Each correlation property type has an equivalent SQL data type.

Microsoft SQL Server

CorrelationPropertyTypeSql Type
Stringnvarchar(200)
DateTimedatetime
DateTimeOffsetdatetimeoffset
Intbigint
Guiduniqueidentifier

MySQL

CorrelationPropertyTypeSql Type
Stringvarchar(200) character set utf8mb4
DateTimedatetime
Intbigint(20)
Guidvarchar(38) character set ascii

Oracle

CorrelationPropertyTypeSql Type
StringNVARCHAR2(200)
DateTimeTIMESTAMP
IntNUMBER(19)
GuidVARCHAR2(38)

The following .NET types are interpreted as CorrelationPropertyType.Int:

Json.net settings

SQL persistence uses the Newtonsoft.Json package to serialize saga data and metadata.

The saga data can be queried by the user by taking advantage of the JSON querying capababilities of SQL Server. The persister itself does not use any JSON querying capababilities.

Custom settings

Customizes the instance of JsonSerializerSettings used for serialization. In this snippet, a custom DateTime converter is included and the DefaultValueHandling setting is changed to Include (by default, the DefaultValueHandling setting is set to Ignore to minimize the amount of data stored in the database).

var settings = new JsonSerializerSettings
{
    DefaultValueHandling = DefaultValueHandling.Include,
    Converters =
    {
        new IsoDateTimeConverter
        {
            DateTimeStyles = DateTimeStyles.RoundtripKind
        }
    }
};
var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
var sagaSettings = persistence.SagaSettings();
sagaSettings.JsonSettings(settings);

Version-specific / type-specific deserialization settings

The type and saga assembly version are persisted. It is possible to explicitly control the deserialization of sagas based on version and/or type. This allows the serialization approach to evolve while avoiding migrations.

var currentSettings = new JsonSerializerSettings
{
    DateFormatHandling = DateFormatHandling.IsoDateFormat
};
var settingForVersion1 = new JsonSerializerSettings
{
    DateFormatHandling = DateFormatHandling.MicrosoftDateFormat
};
var persistence = endpointConfiguration.UsePersistence<SqlPersistence, StorageType.Sagas>();
var sagaSettings = persistence.SagaSettings();
sagaSettings.JsonSettings(currentSettings);
sagaSettings.JsonSettingsForVersion(
    builder: (type, version) =>
    {
        if (version < new Version(2, 0))
        {
            return settingForVersion1;
        }
        // default to what is defined by persistence.JsonSettings()
        return null;
    });

Custom reader

Customize the creation of the JsonReader.

var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
var sagaSettings = persistence.SagaSettings();
sagaSettings.ReaderCreator(
    readerCreator: textReader =>
    {
        return new JsonTextReader(textReader);
    });

Custom writer

Customize the creation of the JsonWriter.

var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
var sagaSettings = persistence.SagaSettings();
sagaSettings.WriterCreator(
    writerCreator: builder =>
    {
        var writer = new StringWriter(builder);
        return new JsonTextWriter(writer)
        {
            Formatting = Formatting.None
        };
    });

Samples

Related Articles