Build scripts for SQL

Using continuous integration build servers for standard development projects (e.g. C#, Java, python, etc…) is very common. Applying the same methodology for sql database scripts is less so.

Introduction

This article describes how to setup just such an environment in Perforce and Bamboo — although note that the process would work with any source control system and build environment — and creates one complete script file. Further multiple files can be created for different environments — e.g. user testing, system integrating testing, and so on.

Before looking at the build process itself, why would you want to do this?

1. Many organizations require developers to hand over the deployment of database changes in the form of scripts.
2. Making changes in the database itself can lead to problems with disconnected code — you keep C# code in a source code repository so why not your SQL?
3. Scripts can differ between Production and Test environments, and these differences can be applied automatically.
4. To aid deployment, scripts need to be created such that they can be deployed repeatably — without having to reload the environment from a backup each time.

Perforce file structure

The diagram below outlines the folders required. Placing SQL objects in the respective folder ensures they are built correctly and in the right order. Note that the folders 001 and 100 may contain adhoc SQL required at the start of the script or at the end, respectively.

Each folder should contain the respective object as a .sql file. Further, each file should contain CREATE and DROP commands bounded by IF NOT EXISTS checks. This will ensure that a script can be run multiple times without adversely impacting the database. At the end of the file be sure to include any GRANT SELECT/EXECUTE permissions.

The Build script

With the above structure in place, the build script file will loop through each folder and concatenate each file into one single file. The process uses NANT.

<!-- Update the <RELEASE NUMBER> with the release
     e.g. Database-1.9.8
  -->
<project name="Database-5.3.0" default="build">
<!-- ================================= 
      This NANT build file creates a single .SQL file
      based on all the .sql objects in the appropriate folders.
This file should be placed in \build in the current release
================================= -->
<property name="deploy.release.output.dir" value="\\\\SERVER\@builds\${project::get-name()}"/>
  <property name="build.db.output.dir" value ="${deploy.release.output.dir}"/>
<!--
	*********************************************************
	Database build targets - used for building database files
	*********************************************************
-->
  <target name ="build">
 		<delete dir="${build.db.output.dir}">
    </delete>
    <call target="build.database.default"/>
  </target>
<target name ="build.database.default">
    <echo message ="Building database to ${build.db.output.dir}"/>
<if unless="${directory::exists('${build.db.output.dir}')}" test="true">
      <mkdir dir="${build.db.output.dir}"/>
    </if>
<!-- Step 1. Concatenate all files in all input directories into one large SQL file-->
    <concat destfile="${build.db.output.dir}\install_prod.sql" failonerror="true">
      <fileset>
        <!-- Include all *.sql files in the source directory hierarchy -->
        <include name="..\**\*.sql"/>
        <!-- Exclude UAT specific file -->
        <exclude name="..\**\999_uat*.sql"/>
      </fileset>
    </concat>
<!-- Step 2. Concatenate all files for UAT in all input directories into one large SQL file-->
    <concat destfile="${build.db.output.dir}\install_uat.sql" failonerror="true">
      <fileset>
        <!-- Include all *.sql files in the source directory hierarchy -->
        <include name="..\**\*.sql"/>
        <include name="..\**\999*.sql"/>
      </fileset>
    </concat>
<!-- Step 3. Make the output files read-only -->
    <attrib readonly="true">
      <fileset>
        <!-- Include all *.sql files in the source directory hierarchy -->
        <include name="${build.db.output.dir}\*.sql"/>
      </fileset>
    </attrib>
<!-- other steps should be inserted here
        e.g. UAT specific
    -->
</target>
</project>
Notes

1. Two files are created in \\SERVER\@builds — install_prod.sql and install_uat.sql
2. Files that do not have a .sql extension are ignored.
3. Other UAT specific steps could, for example:
4. change the USE <DATABASE> if deploying the same files to a database with a different name.
5. Apply specific UAT permissions.

Conclusion

The build process for sql scripts is not very complicated to setup, but saves a huge amount of time when it comes to deployments.  In an integrated Bamboo environment, the script files are created on demand as and when SQL objects are checked in.  And if comments are updated in Perforce at the time of check in, then Bamboo is also a very useful tool for monitoring who is changing what with respect to the database.