# SQL Persistence



Stores NServiceBus data in various relational database engines, without the need for an intermediate ORM, using only a configured `DbConnection`.

## Persistence at a glance

For a description of each feature, see the [persistence at a glance legend](/persistence/index.md#persistence-at-a-glance).

|Feature                    |   |
|:---                       |---
|Storage Types              |Sagas, Outbox, Subscriptions
|Transactions               |Local database transactions or distributed transactions when available
|Concurrency control        |Pessimistic concurrency
|Scripted deployment        |SQL scripts generated at compile time, can be [promoted outside build directory](controlling-script-generation.md#promotion).
|Installers                 |Installers execute the generated scripts, which can be enabled in development.



## Highlights

* Supports [multiple database engines](#supported-sql-implementations).
* No ORM dependency: can be used with Entity Framework, Dapper, etc.
* Independent tables for each endpoint. No "noisy neighbor" problems.
* [Generates DDL scripts at compile time](controlling-script-generation.md) in the build output directory.
* Generated scripts can be [promoted outside the build directory ](controlling-script-generation.md#promotion) and:
  * Added to source control.
  * Compared using a diff viewer.
  * Inspected by DBAs.
  * Treated as first-class citizens in operations workflows for [installation and deployment](install.md).
* Sagas are:
  * Stored using [Json.NET](https://www.newtonsoft.com/json) to serialize complex data structures, with no need to manage complex table structures.
  * Built to be [version-aware](saga.md#json-net-settings-custom-settings-version-specific-type-specific-deserialization-settings) with support for data evolution.
  * Built to [allow changing the `CorrelationId` over time](saga.md#correlation-ids).


## Supported SQL implementations

* [SQL Server](/persistence/sql/dialect-mssql.md)
* [MySQL](/persistence/sql/dialect-mysql.md) (including AWS Aurora MySQL)
* [Oracle](/persistence/sql/dialect-oracle.md)
* [PostgreSQL](/persistence/sql/dialect-postgresql.md) (including AWS Aurora PostgreSQL)


## NuGet Packages

The SQL Persister requires the [**NServiceBus.Persistence.Sql**](https://www.nuget.org/packages/NServiceBus.Persistence.Sql) package, which contains:

 * APIs for manipulating `EndpointConfiguration`.
 * Runtime implementations of saga, subscriptions, and outbox persisters.
 * Attributes used to define compile-time configuration settings.
 * An MSBuild target that generates the required SQL installation scripts at compile time.
 * Optionally runs SQL installation scripts at endpoint startup for development purposes. See [Installer Workflow](installer-workflow.md).

### Other packages

* [NServiceBus.Persistence.Sql.ScriptBuilder](https://www.nuget.org/packages/NServiceBus.Persistence.Sql.ScriptBuilder) - This package contains the APIs that enable the generation of SQL installation scripts using code outside of a compile context. It is not intended for general usage, and in future releases, the API may evolve in ways that do not follow [semantic versioning](/nservicebus/upgrades/release-policy.md#semantic-versioning).


## Script creation

SQL installation scripts are created as a compile-time output alongside a project's binary outputs. Additionally, these scripts can be promoted to a directory under source control so that differences can be easily tracked and analyzed. To learn more, see [Controlling Script Generation](/persistence/sql/controlling-script-generation.md).

> [!WARNING]
> The scripts are generated by an MSBuild task at compile time. Any project containing either endpoint configuration or sagas must **directly** reference the SQL Persistence package. If endpoint configuration and sagas are contained in seperate projects, **both** projects must directly reference the SQL Persistence package and include the `assembly: SqlPersistenceSettings` attribute.

## Troubleshooting

### Looping & branching statements are not allowed

```
SqlPersistenceTask: Looping & branching statements are not allowed in a ConfigureHowToFindSaga method.
```

The `ConfigureHowToFindSaga` method can only contain statements that invoke `ConfigureMapping<T>`. Check if there is a null check or if the calling of `ConfigureHowToFindSaga` was done via the `?.` [Null-conditional operator](https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/operators/member-access-operators#null-conditional-operators--and-) and remove the ‘?.’ operator when present as the mapper will never be null.