Automatic schema updates

By default the cmdlet Update-EPiDatabase is used to manually update the database schema, as described here. It is possible to configure the site to automatically apply updates to the SQL schema during site initialization.

When the site starts up it will compare the assembly version and the database version. If the database version is lower than the assembly version and automatic updates are enabled it will apply the SQL schema updates (the SQL files are embedded resources in the assembly).

Enable automatic updates

By default automatic schema updates are disabled. To enable it add attribute updateDatabaseSchema="true" to the episerver.framework element in configuration as shown below:

<episerver.framework updateDatabaseSchema="true">

Extension points

Given that automatic schema updates have been enabled it is possible to interact with the schema upgrade process by registering an implementation of IDatabaseSchemaValidator in the IOC container. The interface have two methods as:

IsDatabaseUpdateAllowed: This is called first where an implementation can check if an automatic update should be allowed.
BeforeUpdating: Given that all validators allow automatic schema update this method is called before the actual update is performed. Here an implementation can perform some action as for example perform an backup of the database.

Example

Below is a basic implementation that always allows updates but takes a database backup:

public class BackupDatabaseValidator : IDatabaseSchemaValidator
    {
        private readonly string _backupFolder;

        public BackupDatabaseValidator(string backupFolder)
        {
            _backupFolder = backupFolder;
        }

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

        public void BeforeUpdating(ConnectionStringSettings 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();
                }
            }
        }
    }

Below is another example that only allows automatic updates when running on LocalDB (typical development environment):

public class LocalDBDatabaseValidator : IDatabaseSchemaValidator
    {
        public void BeforeUpdating(System.Configuration.ConnectionStringSettings connectionStringSettings)
        {}

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

It is possible to have several validators registered. For example if both validators above are registered updates would only be allowed when running on LocalDB and it would take an backup before the update is applied.

Try our conversational search powered by Generative AI!

See also