SQL persistence supports sagas using the core NServiceBus.Saga API or an experimental API unique to SQL persistence that provides a simpler mapping API.
Saga<T>
base class for sagas, use SQL Persistence version 3.2 or above.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.
class MySaga:SqlSaga<MySaga.SagaData>
{
protected override string TableSuffix => "TheCustomTableName";
Columns
ID
The value of IContainSagaData.
. 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.
In this version of SQL Persistence, a SqlSaga base class must be used to specify the correlation property.
Correlation types
Each correlation property type has an equivalent SQL data type.
Microsoft SQL Server
CorrelationPropertyType | Sql Type |
---|---|
String | nvarchar(200) |
DateTime | datetime |
DateTimeOffset | datetimeoffset |
Int | bigint |
Guid | uniqueidentifier |
MySQL
CorrelationPropertyType | Sql Type |
---|---|
String | varchar(200) character set utf8mb4 |
DateTime | datetime |
Int | bigint(20) |
Guid | varchar(38) character set ascii |
Oracle
CorrelationPropertyType | Sql Type |
---|---|
String | NVARCHAR2(200) |
DateTime | TIMESTAMP |
Int | NUMBER(19) |
Guid | VARCHAR2(38) |
The following .NET types are interpreted as CorrelationPropertyType.
:
Json.net settings
SQL persistence uses the Newtonsoft.Json package to serialize saga data and metadata.
The saga data can be queried by taking advantage of the JSON querying capababilities of SQL Server.
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
};
});