CI/CD database migration scripts

The Xperience Continuous Integration and Continuous Deployment features serialize the data of most objects from the database into XML files on the file system. However, changes to database indexes, functions, stored procedures, custom views, and similar are not supported by CI/CD. To keep the database consistent, these changes need to be handled manually.

We recommend using migration scripts to synchronize database changes not managed by CI/CD.

Synchronize database changes using migration scripts

A migration script allows you to alter a database by modifying its schema. This alteration can be as simple as adding or removing a column, or a complex refactoring task such as splitting tables or changing column properties in a way that could affect the stored data.

Migration scripts in a CI/CD workflow

In addition to serialized database object data, you can add the following migration script files to the repository folder:

• @migrations – folder for storing all created migrations connected to a particular repository. Since all migration scripts for a repository are stored in this folder, their file names need to be unique.
• Before.txt – holds the file names of all migrations applied before the repository is restored to the database. Insert only the migration file names without the .sql suffix. Each file name must be inserted on a separate line.
• After.txt –holds the file names of all migrations applied after the repository is restored to the database. Insert only the migration file names without the .sql suffix. Each file name must be inserted on a separate line.

The Xperience database contains CI_Migration and CD_Migration tables, which you can use to store the file names of migrations already executed on the database.

When you need to create or modify objects not supported by CI/CD (for example table indexes, functions, or stored procedures) and you want to synchronize or deploy these changes, you need to:

1. Write a migration script that applies the required change to the database. Refer to Example - Database change migration script for an example of the process.
2. Reference the migration script in either the Before.txt or After.txt file.
3. Execute the migration script alongside the CI/CD restore operation on databases you want to keep synchronized.

When to execute a migration?

After creating a migration script, you need to decide whether it is going to be applied before or after the CI/CD files are restored.

The Before.txt file should only contain migrations that manipulate the database before the CI/CD files are restored. These migrations can, for example:

• Drop indexes before their tables are deleted by the restore process.
• Delete foreign key constraints before their tables are deleted by the restore process.

The After.txt file should only contain migrations that manipulate the restored database. These migrations can, for example:

• Create new indexes.
• Create new views.
• Add foreign key constraints.
• Add or modify stored procedures.

Example - Database change migration script

If you need to create a new index on, for example, a column OfficeNamein a custom module class table CompanyOfficesand want this change to be reflected in the repository, you need to: 

1. Create a new migration script (for example, AddOfficeNameIndex.sql):

   AddOfficeNameIndex.sql

   Copy

   
   
    CREATE INDEX IX_CompanyOffices_OfficeName ON CompanyOffices(OfficeName);
   
    

2. Add the migration script to the @migrations folder.

3. Add the migration name to a new line in the After.txtfile (the index should only be created after the database is updated).

   After.txt

   Copy

   
   
    ...
    AddOfficeNameIndex
   
    

4. Commit your changes to your source control repository (CI) or add them to the appropriate deployment repository (CD).

When the repository containing your changes is restored on another database using a script equivalent to the sample RunRestore.ps1 script, the AddOfficeNameIndex.sql migration is applied after the restore operation (creating the IX_CompanyOffices_OfficeName index). The migration is then added to the CI_Migration table, marked as applied, and will not be executed again on the target database.

Roll back database changes

When rolling back changes in Continuous Integration scenarios, it is important to realize that migrations you have created and committed to source control may have already been applied to other databases. Therefore, removing your migrations from the @migrations folder and Before.txt or After.txt is insufficient. You also need to write a new migration script that returns the database schema to its original state.

Example - Roll back changes

The rollback process is demonstrated here by extending the example above. To revert the changes made by the AddOfficeNameIndex.sql migration, you need to:

1. Delete the migration scriptAddOfficeNameIndex.sqlfrom the @migrations folder.

2. Remove the migration name AddOfficeNameIndex from the list of migrations in the After.txt file.

3. Create a new migration script (for example, RemoveOfficeNameIndex.sql) that removes the index (if it exists):

   RemoveOfficeNameIndex.sql

   Copy

   
   
    IF EXISTS(SELECT * FROM sys.indexes WHERE name = 'IX_CompanyOffices_OfficeName' AND object_id = OBJECT_ID('CompanyOffices'))
    BEGIN
        DROP INDEX IX_CompanyOffices_OfficeName ON CompanyOffices
    END
   
    

4. Add the migration script to the@migrations folder.

5. Add the migration name to a new line in the Before.txt file (the index should be dropped before its table is deleted during the restore process):

   Before.txt

   Copy

   
   
    ...
    RemoveOfficeNameIndex
   
    

6. Commit your changes to your source control repository.

When the repository containing your changes is restored on another database using a script equivalent to the sample RunRestore.ps1 script, the RemoveOfficeNameIndex.sql migration is applied after the restore operation (removing the IX_CompanyOffices_OfficeName index, if it exists). The migration is then added to the CI_Migration table, marked as applied, and will not be executed again on the target database.

Restore the database to a specific point in time

If you need to revert your database to a specific backup, we recommend restoring the backup to a clean database to avoid possible structural database conflicts which may arise when restoring the database to an older version.

1. Create a clean Xperience database or restore a database backup from before the point in time you wish to restore.
2. Get the required version of your Xperience solution and the repository folder from your source control system.
3. Update the CMSConnectionStringin the project’s configuration file (appsettings.json by default) to point to the new database.
4. Rebuild the solution in Visual Studio.
5. Execute your Continuous Integration restore process (including migration scripts).

After the restore finishes, you can continue working with the restored database backup.