The SQL persistence package generates scripts to create necessary database assets. It is 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>();
Automatically executing generated scripts is recommended only in development environments.
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 need 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,
cancellationToken: CancellationToken.None);
Table prefix
The table prefix is the string that is prefixed to every table name, e.g. Saga, Outbox, Subscription and Timeout tables.
The default table prefix is Endpoint Name with all periods (.) replaced by underscores (_).
A table prefix is used at runtime and install time.
While the default table prefix can be inferred by code at runtime, it cannot be inferred when running deployment scripts manually and as such must be passed in as a parameter.
When using the default approach to installation (execute at startup), the value configured in the 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 the selected SQL dialect for details.
The same value must be passed to the installation scripts as a parameter.
Manual installation
When performing a custom script execution the table prefix is required. See also Installer Workflow.
Note that scriptDirectory can be either the root directory for all scripts or 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();
}
}