Developers must have good feedback to ensure productivity. Most shops have a CI build process that allows the developer to quickly know if a build failed, and some shops have an automatic deploy of the CI build to provide website feedback. However, most shops don’t have an automated update for databases in the CI environment. Without this database automation, the website CI is incomplete or requires a database administrator (DBA) to constantly update the database as developers hand over new changes. In this article, I write about an approach to automating the update of databases in a CI environment that we currently use at Onlife Health Inc., a Brentwood, TN based subsidiary of Blue Cross Blue Shield of Tennessee who specializes in personal health coaching.
Database Under Version Control
With any database update process—particularly an automated one—strong version control of the database objects is needed. To achieve this, you need to script out the individual databases into their separate parts like tables, views, stored procedures, functions, etc. There are a number of tools that can do this. The tool that we use at Onlife Health for generating these scripts is Red Gate SQL Compare, as it has the ability to script out an entire database and a command-prompt version of its SQL Compare tool. This makes it an excellent tool for performing comparisons and database syncs in an automated environment.
Once you have scripted out the database, the individual parts will be version controlled in the subversion repository as a complete database set. In order to effect any change to a database’s set of objects, the developer or DBA will modify some part of the set of database’s objects using a traditional edit-and-commit development process.
Database Object Structure in Subversion (or Any Source-code Provider)
The typical structure for databases that have been script out is shown below.
The \Post-deploy and \Pre-deploy folders are for custom scripts that alter or add data to the database. Pre-deploy scripts are those changes that need to happen before the schema is changed. This might be to format data before its type is changed or to create a temporary table to store data before a table is changed or dropped. The Post-deploy folder is for all other data changes. Ninety-nine percent of all data changes will consist of scripts in the post-deploy folder.
A table to track version updates is used to monitor the version number of database objects. This version number information will be stored in a _dbversion table that will contain the build version identifier as well as an AppliedDate field to give some historical indication of when the database was updated and to what levels. This _dbversion table becomes one of the tables of the database and an example of this table is provided below.
CREATE TABLE [dbo].[_DBVersion](
[BuildVersion] [varchar](50) NOT NULL,
[AppliedDate] [datetime] NOT NULL,
[Application] [varchar](50) NOT NULL,
[DBName] [varchar](50) NOT NULL
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[_DBVersion] ADD CONSTRAINT [DF__DBVersion_AppliedDate] DEFAULT (getdate()) FOR [AppliedDate]