# NHibernate Persistence


Uses the [NHibernate ORM](https://nhibernate.info/) for persistence.


## 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                    |   |
|:---                       |---
|Supported storage types    |Sagas, Outbox, Subscriptions
|Transactions               |Local database transactions or distributed transactions when available
|Concurrency control        |Optimistic concurrency for correctness + pessimistic concurrency for performance
|Scripted deployment        |Not supported
|Installers                 |Table structure is created by installers.


## Supported database engines

 * [Microsoft SQL Server](https://www.microsoft.com/en-au/sql-server/) ([Version 2012](https://learn.microsoft.com/en-us/sql/release-notes/sql-server-2012-release-notes) and above).
 * [Oracle Database](https://www.oracle.com/database/index.html) ([Version 11g Release 2](https://docs.oracle.com/cd/E11882_01/readmes.112/e41331/chapter11204.htm) and above).

> [!NOTE]
> SQL Server Always Encrypted feature is currently not supported by the persister when using Microsoft SQL Server

> [!NOTE]
> When connecting to an Oracle Database, only the ODP.NET-managed driver is supported. The driver is available via the [Oracle.ManagedDataAccess NuGet Package](https://www.nuget.org/packages/Oracle.ManagedDataAccess).

> [!WARNING]
> Although this persistence will run on the free version of the above engines, i.e. [SQL Server Express](https://www.microsoft.com/en-au/sql-server/sql-server-editions-express) and [Oracle XE](https://www.oracle.com/technetwork/database/database-technologies/express-edition/overview/index.html), 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 either [Microsoft Premier Support](https://www.microsoft.com/en-us/microsoftservices/support.aspx), [Oracle Support](https://www.oracle.com/support/index.html), or another third party support provider.

## Usage

The next stage is to tell NServiceBus how to use NHibernate for persistence

<!-- snippet: ConfiguringNHibernate -->

```cs
// Use NHibernate for all persistence concerns
endpointConfiguration.UsePersistence<NHibernatePersistence>();

// or select specific concerns
endpointConfiguration.UsePersistence<NHibernatePersistence, StorageType.Sagas>();
endpointConfiguration.UsePersistence<NHibernatePersistence, StorageType.Subscriptions>();
endpointConfiguration.UsePersistence<NHibernatePersistence, StorageType.Outbox>();
```

<!-- endsnippet -->

## Connection strings

Passing a connection string in the `app.config` file, as described in the [using configuration convention](/persistence/nhibernate/index.md#customizing-the-configuration-using-configuration-convention) section is possible.

### With code

NHibernate persistence requires specifying a connection string.

The connection string might be passed using code configuration:

<!-- snippet: ConnectionStringAPI -->

```cs
var persistence = endpointConfiguration.UsePersistence<NHibernatePersistence>();
persistence.ConnectionString(@"Data Source=.\SqlExpress;Database=nservicebus");
```

<!-- endsnippet -->

## Customizing the configuration

To customize the NHibernate `Configuration` object used to bootstrap the persistence mechanism, either provide a ready-made object via code or use convention-based XML configuration. The code-based approach overrides the configuration-based one when both are used.

### Passing configuration in code

To specify configuration on a per-concern basis:

<!-- snippet: SpecificNHibernateConfiguration -->

```cs
var nhConfiguration = new Configuration
{
    Properties =
    {
        ["dialect"] = "NHibernate.Dialect.MsSql2008Dialect"
    }
};

var persistence = endpointConfiguration.UsePersistence<NHibernatePersistence>();
persistence.UseSubscriptionStorageConfiguration(nhConfiguration);
```

<!-- endsnippet -->

> [!NOTE]
> Combine both approaches to define a common configuration and override it for one specific concern.

To use a given NHibernate `Configuration` object for all the persistence concerns:

<!-- snippet: CommonNHibernateConfiguration -->

```cs
var nhConfiguration = new Configuration
{
    Properties =
    {
        ["dialect"] = "NHibernate.Dialect.MsSql2008Dialect",
        ["connection.provider"] = "NHibernate.Connection.DriverConnectionProvider",
        ["connection.driver_class"] = "NHibernate.Driver.Sql2008ClientDriver"
    }
};

var persistence = endpointConfiguration.UsePersistence<NHibernatePersistence>();
persistence.UseConfiguration(nhConfiguration);
```

<!-- endsnippet -->

> [!WARNING]
> When using the per-concern API to enable the NHibernate persistence, the `UseConfiguration` method still applies to the common configuration, not the specific concern being enabled. The following code will set up NHibernate persistence only for `Subscriptions` concern but will override the default configuration **for all the concerns**.

<!-- snippet: CustomCommonNhibernateConfigurationWarning -->

```cs
var nhConfiguration = new Configuration
{
    Properties =
    {
        ["dialect"] = "NHibernate.Dialect.MsSql2008Dialect"
    }
};

var persistence = endpointConfiguration.UsePersistence<NHibernatePersistence, StorageType.Subscriptions>();
persistence.UseConfiguration(nhConfiguration);
```

<!-- endsnippet -->

### Using configuration convention

NServiceBus picks up the connection setting from the `app.config` from `connectionStrings` and `appSettings` sections. The convention used for `appSettings` does not support defining settings specific for a single persistence concern. If this level of granularity is required, use a code-based approach.

> [!NOTE]
> When using SQL 2012 or later, change the dialect to `MsSql2012Dialect`. Additional dialects are available in the NHibernate.Dialect namespace, [NHibernate documentation.](https://nhibernate.info/doc/)

<!-- snippet: NHibernateAppConfig -->

```xml
<configuration>
  <connectionStrings>
    <add name="NServiceBus/Persistence"
         connectionString="Data Source=.\SqlExpress;Initial Catalog=nservicebus"/>
    <!--Following connection string will be used only for accessing Saga data-->
    <add name="NServiceBus/Persistence/NHibernate/Saga"
         connectionString="Data Source=.\SqlExpress;Initial Catalog=nservicebus_saga"/>
  </connectionStrings>

  <!-- specify the other needed NHibernate settings like below in appSettings:-->
  <appSettings>
    <!-- dialect is defaulted to MsSql2008Dialect, if needed change accordingly -->
    <add key="NServiceBus/Persistence/NHibernate/dialect"
         value="NHibernate.Dialect.MsSql2008Dialect" />
    <!-- other optional settings examples -->
    <add key="NServiceBus/Persistence/NHibernate/connection.provider"
         value="NHibernate.Connection.DriverConnectionProvider" />
    <add key="NServiceBus/Persistence/NHibernate/connection.driver_class"
         value="NHibernate.Driver.Sql2008ClientDriver" />
  </appSettings>
</configuration>
```

<!-- endsnippet -->

## Change database schema

The database schema can be changed by defining the `default_schema` NHibernate property. See the previous *Customizing the configuration* section.

## Subscription caching

The subscriptions can be cached when using NHibernate. This can improve the performance of publishing events as it is not required to request matching subscriptions from storage.

> [!NOTE]
> Publishing is performed on stale data. This is only advised in high-volume environments where latency can be an issue.

<!-- snippet: NHibernateSubscriptionCaching -->

```cs
var persistence = endpointConfiguration.UsePersistence<NHibernatePersistence, StorageType.Subscriptions>();
persistence.EnableCachingForSubscriptionStorage(TimeSpan.FromMinutes(1));
```

<!-- endsnippet -->

## Controlling schema

In some cases, it may be necessary to take full control over creating the SQL structure used by the NHibernate persister. In these cases, the automatic creation of SQL structures on installation can be disabled as follows:

**For all persistence schema updates:**

<!-- snippet: DisableSchemaUpdate -->

```cs
var persistence = endpointConfiguration.UsePersistence<NHibernatePersistence>();
persistence.DisableSchemaUpdate();
```

<!-- endsnippet -->

**For subscription schema update:**

<!-- snippet: DisableSubscriptionSchemaUpdate -->

```cs
var persistence = endpointConfiguration.UsePersistence<NHibernatePersistence>();
persistence.DisableSubscriptionStorageSchemaUpdate();
```

<!-- endsnippet -->



## Generating scripts for deployment

There are some options for generating scripts for deployment without using the [installers](/nservicebus/operations/installers.md) in a production environment.

### Run installers in non-production environments

Run an install in a lower environment and then export the SQL structure. This structure can then be migrated to production.

### Use `ScriptGenerator<T>` API

The NHibernate persistence provides a `ScriptGenerator<T>` API that can be used to generate the required SQL scripts for supported database engines.

#### Outbox script

To generate the SQL script for the Outbox, use the following code:

<!-- snippet: GenerateOutboxScript -->

```cs
var outboxScript = ScriptGenerator<MsSql2012Dialect>.GenerateOutboxScript();
```

<!-- endsnippet -->

#### Saga storage script

To generate the SQL script for a saga, use the following code:

<!-- snippet: GenerateSagaScript -->

```cs
var sagaScript = ScriptGenerator<MsSql2012Dialect>.GenerateSagaScript<ExampleSaga>();
```

<!-- endsnippet -->

#### Other scripts

From version 11.1, it is possible to generate SQL scripts for any type of C# object by using the `GenerateScript` method.

<!-- snippet: GenerateScript -->

```cs
var entityScript = ScriptGenerator<MsSql2012Dialect>.GenerateScript(typeof(ExampleEntity));
```

<!-- endsnippet -->