Automatic database schema updates

3 min

Update schemas manually or automatically.

The EPiServer.Net.Cli command manually updates the database schema. Configure the site to automatically apply updates to the SQL schema during site initialization.

On site startup, Commerce Connect compares the assembly and database versions and applies SQL schema updates when the database version is lower than the assembly version and automatic updates are enabled. (The SQL files are embedded resources in the assembly.) To enable automatic schema updates, add the UpdateDatabaseSchema setting to appsettings.json:

{
  "EPiServer": {
    "Cms": {
      "DataAccess": {
        "UpdateDatabaseSchema": "true"
      }
    }
  }
}

Or configure it in code:

public class Startup {
  public void ConfigureServices(IServiceCollection services) {
    services.Configure<DataAccessOptions>(o => {
      o.UpdateDatabaseSchema = true;
    });
  }
}

If you enable automatic schema updates, you can interact with the schema upgrade process by registering an implementation of ISchemaValidator in the inversion-of-control (IoC) container. The interface has two methods:

IsDatabaseUpdateAllowed – The schema calls this method first where an implementation can check if an automatic update should be allowed.

BeforeUpdating – If all validators allow automatic schema update, the schema calls the method before it performs the actual update, so you can perform some action, such as a backup of the database.

📘 Note Validators are also called when automatic schema creation (see Install database schema) is enabled and a new schema is about to deploy.

The following example shows a basic implementation that allows updates but takes a database backup:

public class BackupDatabaseValidator: ISchemaValidator {
  private readonly string _backupFolder;
  public BackupDatabaseValidator(string backupFolder) {
    _backupFolder = backupFolder;
  }

  public bool IsDatabaseUpdateAllowed(ConnectionStringOptions connectionStringSettings) {
    return true;
  }

  public void BeforeUpdating(ConnectionStringOptions connectionStringSettings) {
    var sqlConStrBuilder = new SqlConnectionStringBuilder(connectionStringSettings.ConnectionString);
    var backupFileName = String.Format(CultureInfo.InvariantCulture, "{0}-{1}.bak", sqlConStrBuilder.InitialCatalog, DateTime.Now.ToString("yyyy-MM-dd"));
    var backupFilePath = Path.Combine(_backupFolder, backupFileName);

    using(var connection = new SqlConnection(sqlConStrBuilder.ConnectionString)) {
      var query = String.Format("BACKUP DATABASE {0} TO DISK='{1}'",
        sqlConStrBuilder.InitialCatalog, backupFilePath);

      using(var command = new SqlCommand(query, connection)) {
        connection.Open();
        command.ExecuteNonQuery();
      }
    }
  }
}

The following example allows only automatic updates when running on LocalDB (a typical development environment):

public class LocalDBDatabaseValidator: ISchemaValidator {
  public void BeforeUpdating(ConnectionStringOptions connectionStringSettings) {}

  public bool IsDatabaseUpdateAllowed(ConnectionStringOptions connectionStringSettings) {
    var sqlConStrBuilder = new SqlConnectionStringBuilder(connectionStringSettings.ConnectionString);
    return sqlConStrBuilder.DataSource.StartsWith("(LocalDB)", StringComparison.OrdinalIgnoreCase);
  }
}

Register several validators to combine behaviors. For example, registering both validators above allows updates only on LocalDB and creates a backup before each update.