Installation and deployment

SQL persistence outputs scripts to create needed database assets. It's recommended to run those scripts as part of the deployment process. See Installer Workflow for more information.

Script execution during development

To streamline development SQL persistence will execute generated scripts at endpoint startup if installers are enabled.

endpointConfiguration.EnableInstallers();
var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();

Note this is only recommended during development.

Script execution in non-development environments

In non-development environments, where the SQL persistence installation scripts have been executed as part of the deployment, it may be necessary to explicitly disable the SQL persistence installers if standard installers needs to be used for other purposes.

endpointConfiguration.EnableInstallers();
var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
persistence.DisableInstaller();

The ScriptRunner class can be used to run the scripts without creating and starting an NServiceBus endpoint

await ScriptRunner.Install(
    sqlDialect: new SqlDialect.MsSqlServer(),
    tablePrefix: "MyEndpoint",
    connectionBuilder: () => new SqlConnection(connectionString), 
    scriptDirectory: @"C:\Scripts",
    shouldInstallOutbox: true,
    shouldInstallSagas: true,
    shouldInstallSubscriptions: true,
    shouldInstallTimeouts: true);

Table Prefix

Table prefix is the string that is prefixed to every table name, i.e. Saga, Outbox, Subscription and Timeout tables.

The default TablePrefix is Endpoint Name with all periods (.) replaced with underscores (_).

A Table Prefix is used at runtime and install time.

While the above default Table Prefix at runtime can be inferred by code, it cannot be inferred when running deployment scripts manually and as such much be passed in as a parameter.

When using the default (execute at startup) approach to installation the value configured in code will be used.

var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
persistence.TablePrefix("ThePrefix");

Database Schema

When using a database that supports schemas, a schema value other than default can be defined in the configuration API. Consult the documentation of selected SQL dialect for details.

The same value will need to be passed to the installation scripts as a parameter.

Manual installation

When performing a custom script execution the TablePrefix is required. See also Installer Workflow.

Note that scriptDirectory can be either the root directory for all scripts for, alternatively, the specific locations for a given storage type i.e. Saga, Outbox, Subscription and Timeout scripts.

SQL Server

using (var connection = new SqlConnection("ConnectionString"))
{
    connection.Open();
    using (var transaction = connection.BeginTransaction())
    {
        foreach (var createScript in Directory.EnumerateFiles(
            path: scriptDirectory,
            searchPattern: "*_Create.sql",
            searchOption: SearchOption.AllDirectories))
        {
            using (var command = connection.CreateCommand())
            {
                command.Transaction = transaction;
                command.CommandText = File.ReadAllText(createScript);
                var tablePrefixParameter = command.CreateParameter();
                tablePrefixParameter.ParameterName = "tablePrefix";
                tablePrefixParameter.Value = tablePrefix;
                command.Parameters.Add(tablePrefixParameter);
                var schemaParameter = command.CreateParameter();
                schemaParameter.ParameterName = "schema";
                schemaParameter.Value = "dbo";
                command.Parameters.Add(schemaParameter);
                command.ExecuteNonQuery();
            }
        }
        transaction.Commit();
    }
}

MySQL

using (var connection = new MySqlConnection("ConnectionString"))
{
    connection.Open();
    using (var transaction = connection.BeginTransaction())
    {
        foreach (var createScript in Directory.EnumerateFiles(
            path: scriptDirectory,
            searchPattern: "*_Create.sql",
            searchOption: SearchOption.AllDirectories))
        {
            using (var command = connection.CreateCommand())
            {
                command.Transaction = transaction;
                command.CommandText = File.ReadAllText(createScript);
                var parameter = command.CreateParameter();
                parameter.ParameterName = "tablePrefix";
                parameter.Value = tablePrefix;
                command.Parameters.Add(parameter);
                command.ExecuteNonQuery();
            }
        }
        transaction.Commit();
    }
}

Oracle

using (var connection = new OracleConnection("ConnectionString"))
{
    connection.Open();
    using (var transaction = connection.BeginTransaction())
    {
        foreach (var createScript in Directory.EnumerateFiles(
            path: scriptDirectory,
            searchPattern: "*_Create.sql",
            searchOption: SearchOption.AllDirectories))
        {
            using (var command = connection.CreateCommand())
            {
                command.Transaction = transaction;
                command.CommandText = File.ReadAllText(createScript);
                var parameter = command.CreateParameter();
                parameter.ParameterName = "tablePrefix";
                parameter.Value = tablePrefix;
                command.Parameters.Add(parameter);
                command.ExecuteNonQuery();
            }
        }
        transaction.Commit();
    }
}

PostgreSQL

using (var connection = new NpgsqlConnection("ConnectionString"))
{
    connection.Open();
    using (var transaction = connection.BeginTransaction())
    {
        foreach (var createScript in Directory.EnumerateFiles(
            path: scriptDirectory,
            searchPattern: "*_Create.sql",
            searchOption: SearchOption.AllDirectories))
        {
            using (var command = connection.CreateCommand())
            {
                command.Transaction = transaction;
                command.CommandText = File.ReadAllText(createScript);
                var parameter = command.CreateParameter();
                parameter.ParameterName = "tablePrefix";
                parameter.Value = tablePrefix;
                command.Parameters.Add(parameter);
                command.ExecuteNonQuery();
            }
        }
        transaction.Commit();
    }
}