NuGet: A new database version for CMS

We are releasing an update to CMS that contain database schema changes, since this will be the first time we release schema changes via NuGet I thought I spend some time describing how it works and why we made certain choices. If you are using Commerce and been updating the past weeks you will already be familiar with these commands.

We started some time ago building support for shipping database updates in NuGet packages based on some design principals:

• Never modify the database without consent: You should be able to update packages without being “scared” that schema changes are applied to a shared database or a database without backups that you need to revert.
• Use ordinary SQL files: Make sure environments that have high auditing requirements can run them manually when needed.
• Simple to update for devs: Make it super-easy to update the database schema cross products, assuming both CMS and Commerce starts shipping schema changes more often you don’t want any manual trickery.

The first thing you notice after applied the latest update is the more helpful error message when you start the site for the first time.

To apply the updates open up the ‘Package Manager Console’ and run the command Update-EPiDatabase:

This command will update database schema for the database defined in the connection string EPiServerDB for CMS (and EcfSqlConnection for Commerce), the printout shows which files it finds that are potential updates.

The files are by convention:

• Stored in a folder tools/epiupdates/sql in each NuGet package.
• Files are named after the package version they were introduced in (ie 7.8.0.sql)
• Each file begins with a validation statements that determines if the file should be executed or not (you can run this statement manually if need to very if a file has been applied or not).

For troubleshooting, or if you just want to see more details, you can add -Verbose:$true.

Our recommended approach to deploy changes to production is to run Export-EPiUpdates to export the schema changes to a folder that is independent of Visual Studio, this command basically does the same thing as Update-EPiDatabase but dumps the commands into a bat file to make them portable and easy to run in any environment. If you have a look inside that file you will notice a simple command line tool epideploy.exe that used to execute scripts, same tool that is executed when you update Update-EPiDatabase.

You can also run this command on the development site to test it out, if there are no changes nothing will be modified so it’s safe to run multiple times.

A final note. We have discussed building automatic database schema updates into the products but based on the feedback we got early on it was more important to get the simple and controlled semi-automatic approach right first. Maybe based on your feedback and ideas we will get back to this area and offer more automation, let us know what you think.

Note: All of this is documented in the SDK under “Updating EPiServer via NuGet”, including schema changes to configuration which I did not cover in this post.