# Installation and deployment


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](installer-workflow.md) for more information.

## Script execution during development

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

<!-- snippet: ExecuteAtStartup -->

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

<!-- endsnippet -->

> [!NOTE]
> 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](/nservicebus/operations/installers.md) need to be used for other purposes.

<!-- snippet: DisableInstaller -->

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

<!-- endsnippet -->

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

<!-- snippet: ScriptRunner -->

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

<!-- endsnippet -->

## 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](/nservicebus/endpoints/specify-endpoint-name.md) with all periods (`.`) replaced by underscores (`_`).

A table prefix is used at runtime and install time.

> [!NOTE]
> While the default table prefix can be inferred by code at runtime, it cannot be inferred when [running deployment scripts manually](#manual-installation) 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.

<!-- snippet: TablePrefix -->

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

<!-- endsnippet -->

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

- [SQL Server](/persistence/sql/dialect-mssql.md#schema-support)
- [Oracle](/persistence/sql/dialect-oracle.md#schema-support)
- [PostgreSQL](/persistence/sql/dialect-postgresql.md#schema-support)

> [!NOTE]
> 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](installer-workflow.md).

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

<!-- snippet: ExecuteScriptsSqlServer -->

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

<!-- endsnippet -->

### MySQL

<!-- snippet: ExecuteScriptsMySql -->

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

<!-- endsnippet -->

#### Oracle

<!-- snippet: ExecuteScriptsOracle -->

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

<!-- endsnippet -->

#### PostgreSQL

<!-- snippet: ExecuteScriptsPostgreSQL -->

```cs
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 tablePrefixParameter = command.CreateParameter();
                tablePrefixParameter.ParameterName = "tablePrefix";
                tablePrefixParameter.Value = tablePrefix;
                command.Parameters.Add(tablePrefixParameter);
                var schemaParameter = command.CreateParameter();
                schemaParameter.ParameterName = "schema";
                schemaParameter.Value = "public";
                command.Parameters.Add(schemaParameter);
                command.ExecuteNonQuery();
            }
        }
        transaction.Commit();
    }
}
```

<!-- endsnippet -->