Imagine this scenario…

Jenn (Developer): “I’m experiencing a lot of problems testing on v1.3.1.b1 of the database using build 418”
Mike (DBA): “When using build 418, you should use v1.3.1.b2, but I also need to make a few changes to it first”
Jenn: “I just spent three hours for no reason”
Mike: “You should have asked me first”

Back in the early days of my career, I worked on quite a few projects that had a relatively efficient process for developing software. However, when it came to the database, we seemed to throw out all good judgment and retreat to the days without configuration management by using a “throw it over the wall” approach to managing the changes in the database. There was an understanding that the DBAs were doing “their thing” and we (the developers) were doing “our thing”. In those days, as developers, it was seldom that we could recreate the database on our machine using Data Definition Language (DDL) and Data Manipulation Language (DML) scripts because they either A) Did not create the DDL/DML scripts or B) Did not check them into the SCM repository. Instead, we would be at the whim of the DBA if we needed any kind of change to the database. The scenario above is based on my own experiences (the names have been changed to protect the no so innocent).

Fortunately, some of us have awoken to what we could have been doing all along: integrating our DDL and DML into our build process to ensure we are working off the same schema and test data as our fellow developers and DBAs.

To effectively integrate your database definition and manipulation into the build process, you must establish the following: DDL scripts, DML scripts, an SCM tool, a local database, and build script(s). Each is described in more detail below.

Database Build

DDL
DDL is just a fancy acronym for SQL that creates your database schema. Typically, you will see code like this in a DDL script:
CREATE TABLE beer(name VARCHAR(50), brewer VARCHAR(50), date_received DATE);
DML
DML is another one of them highfalutin acronyms for SQL that means inserting, updating, and removing (etc.) data in your database. You’ll find this type of code in your DML:
INSERT INTO beer(name, brewer, date_received) VALUES ('Sam Adams Lager','Boston Beer Company','2005-12-09');
Just remember, we’re talking about test data here; you’re not going to be inserting millions of records into your local database. This is for another time and place.

SCM Tool
You need to manage your DDL and DML just like you would your source code, so you’ll need a source code management tool such as Subversion, ClearCase, Perforce or whatever you’re using at your shop. You will check in the DDL and DML scripts that will recreate your database schema and insert all of the test data.

Local Database
You need a local version of your database running so that your can run the DDL and DML scripts. If you are running this database on your developer workstation, you will probably want a lightweight version of your database so that you don’t degrade your machine’s performance. If you’re using Oracle, look into Oracle Express or McKoi as lightweight options. If you’re using SQL Server, then SQL Server Express is a viable option. If you’re using MySQL, you’re in luck, as you can use MySQL locally.

Build Scripts
Now that you’ve got your DDL/DML scripts, a local database, and you are managing your database scripts in your SCM tool, you’re ready for the final step: making your database definition/manipulation as a part of your build process. In makes no difference whether you are using Ant, NAnt, make or other build tool just as long as the script is run as part of a standard process of developing and building your software. An example in Ant and MySQL is listed below.

<target name="db:create-database" description="Create DB and Insert data">
  <echo>Creating database...</echo>
  <sql
    driver="com.mysql.jdbc.Driver"
    url="jdbc:mysql://localhost:3306/brewery"
    userid="sam"
    password="@d@ms"
    classpathref="db.lib.path"
    delimiter=";">
    <fileset file="${db.dir}/create-database.sql"/>
    <fileset file="${db.dir}/insert-data.sql"/>
  </sql>
</target>

By making it a part of your build script, you can run it often as part of a continuous integration process. You can add more advanced features such as incorporating database migration into the process to ensure that changes can easily be applied to an existing database. On the projects I have used this process, it encourages a more collaborative effort between the developers and DBAs and allows the developers to make simple or more complex changes to the database (depending on their experience) that may be used by others on the team. Just as you do with your source code builds, when the build breaks, it must become a priority to fix the broken build. If you’re not integrating your database scripts into your build process, I’d encourage you to try it out and see how it works for you.