Dealing with databases within a continuous deployment strategy can be challenging. Databases do not subscribe to the same build/deploy concepts that applications do.
- It is recommended that builds produce the complete and deployable application each and every time. Databases are not re-created or re-deployed each time, only changes are applied.
- Rollbacks are easy with applications, simply re-deploy the previous version. Rollbacks are hard with databases and must be taken into account at all times.
- Application file names typically stay the same from build to build. Database update file names (SQL scripts) do not necessarily stay the same at all. Each update may produce a unique set of SQL scripts and their names are typically not relevant to any other version.
- Initial installations of an application should be the same installation process as an update. Initial installations of a database are usually not scripted and are usually handled outside of the normal deployment process (i.e. the DBAs will handle it!!).
And there are no silver bullets or magic dust when it comes to automating database deployments and integrating them into your continuous delivery strategy. Here are some things to think about.
Use an industry solution – This is not a new problem and others have attempted to create solutions to help. Liquibase is one such solution. Solutions like this make the database version-aware. Typically a table is inserted into the database to keep track of each update version, the scripts that got it to that version, and rollback scripts to get it back to the previous version. Using a solution like this really makes continuous deployment to databases more concrete and easy to build into a strategy.
Focused SQL scripts – If you don’t choose to go the route of something like Liquibase, then a more structured and focused effort needs to be put into SQL scripts to insure automation success. Here is a typical situation I run into. A DBA is brought into an UrbanCode Deploy proof-of-concept. They hand over a set of SQL scripts that coincide with an application version (which sometimes is a cause for celebration in itself). However, these SQL scripts are typically run by humans. A human watches the script and its results and proceeds with the next script in the sequence if the first one is successful. First of all, a human is in charge with determining if a script is successful or not. And you cannot rely on the fact that the script errors out to indicate failure. You may need to run a query to get a value from a table and then create something new based on the query results. Or you may need to count the presence of rows in a table to determine how many of something needs to be created. Second, a human determines what script to run next. There may be no indication of script execution order in the script file name and the only one who knows what comes next is the human.
So in order to make database updates automatable(?), you have to put some discipline into your script writing. A script needs to be able to make all the decisions programatically with no human intervention. It should error out if a problem occurs but a successful return status from a script should indicate that all is good. Also, there should be some mechanism setup to be able to programmatically determine the order of scripts to run. Either use a file name pattern or supply a separate file that lists the order of execution. Rollback scripts also need to be provided.
UrbanCode Deploy can handle either approach very well. It has a plug-in to an open source solution similar to Liquibase (the plug-in is the DBUpgrader plug-in). Or you can follow the disciplined approach and UrbanCode Deploy can execute SQL scripts in a prescribed order. Rollback scripts should follow the same pattern and UrbanCode Deploy will have a rollback process for the database component as well.
Including database updates in a continuous deployment strategy is a good thing, but is easier said than done. It requires some forethought and a strategy. Getting the DBAs to the table may be the biggest challenge to overcome 🙂