# Saga Persister


SQL persistence supports sagas using the core [NServiceBus.Saga](/nservicebus/sagas/index.md) 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](/persistence/sql/install.md#table-prefix) defined at the endpoint level.
 * The suffix of the table name is **either** the saga [Type.Name](https://msdn.microsoft.com/en-us/library/system.type.name.aspx) **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:

<!-- snippet: table-suffix-with-attribute -->

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

<!-- endsnippet -->

> [!NOTE]
> Using [delimited identifiers](https://technet.microsoft.com/en-us/library/ms176027.aspx) in the TableSuffix is currently **not** supported.


### 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

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](/nservicebus/sagas/message-correlation.md) is implemented by promoting the correlation property to the level of a column on the saga table. When saga data is persisted, the correlation property is copied from the instance and duplicated in a column named, by convention, (`Correlation_[PROPERTYNAME]`).

For NServiceBus sagas, an attempt will be made to determine the correlation property at compile time by analyzing the `ConfigureHowToFindSaga` method. There are a few unsupported scenarios 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 or specify the correlation property with a [`[SqlSaga]` attribute](#correlation-ids-specifying-correlation-id-using-an-attribute).


### 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:

<!-- snippet: correlation-with-attribute -->

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

<!-- endsnippet -->


### 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. This takes into account in-flight messages and in-progress sagas that do not contain 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 correlation property, 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:

<!-- snippet: transitional-correlation-with-attribute -->

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

<!-- endsnippet -->



### 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.Int`:

 * [Int16](https://msdn.microsoft.com/en-us/library/system.int16.aspx)
 * [Int32](https://msdn.microsoft.com/en-us/library/system.int32.aspx)
 * [Int64](https://msdn.microsoft.com/en-us/library/system.int64.aspx)
 * [UInt16](https://msdn.microsoft.com/en-us/library/system.uint16.aspx)
 * [UInt32](https://msdn.microsoft.com/en-us/library/system.uint32.aspx)
 * [UInt64](https://msdn.microsoft.com/en-us/library/system.uint64.aspx)


## Json.NET settings

SQL persistence uses the [Newtonsoft.Json](https://www.nuget.org/packages/Newtonsoft.Json/) package to serialize saga data and metadata.

The saga data can be queried manually by taking advantage of the [JSON querying capababilities of SQL Server](https://learn.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server). The persister itself does not use any JSON querying capababilities.

### Custom settings

An instance of [JsonSerializerSettings](https://www.newtonsoft.com/json/help/html/T_Newtonsoft_Json_JsonSerializerSettings.htm) can be used to customize 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).

<!-- snippet: SqlPersistenceCustomSettings -->

```cs
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);
```

<!-- endsnippet -->


#### Version-specific / type-specific deserialization settings

The type and saga assembly version are persisted as part of the saga data. 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.

<!-- snippet: SqlPersistenceJsonSettingsForVersion -->

```cs
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;
    });
```

<!-- endsnippet -->


### Custom reader

Customize the creation of the [JsonReader](https://www.newtonsoft.com/json/help/html/T_Newtonsoft_Json_JsonReader.htm).

<!-- snippet: SqlPersistenceCustomReader -->

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

<!-- endsnippet -->


### Custom writer

Customize the creation of the [JsonWriter](https://www.newtonsoft.com/json/help/html/T_Newtonsoft_Json_JsonWriter.htm).

<!-- snippet: SqlPersistenceCustomWriter -->

```cs
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
        };
    });
```

<!-- endsnippet -->