Writing flexible database scripts with NAnt

I haven't been blogging much lately, but I hope to change that starting next week. In the meantime, here's a quick article on writing flexible database scripts with NAnt. This technique has worked very well for me in the past.

The Problem

You want to script the creation of your project's databases, but you don't want to embed any of the following information in your build files or database scripts:

• The database name.
• The database server name, login information, file paths, initial size and file growth values, etc.
• The database collation name.

Or, you want to programmatically determine the following when the database script is executed:

• Whether or not an existing database with the same name should be overwritten.
• Whether or not test data should be inserted.

The Solution

1. Define NAnt targets named after databases servers that set the values of well-known properties appropriately for each server.
2. Define an NAnt target that takes a templated database script, copies it to a temporary path, replaces well-known tokens with the values of well-known properties, and executes the generated script.

The Code

DatabaseServers.include

DatabaseServers.include defines one or more LoadDatabaseServerProfile-databaseServerName targets. The LoadDatabaseServerProfile-databaseServerName targets set the databaseServerUsername, databaseServerPassword, databaseServerDataPath, and databaseServerLogPath properties for each database server.

<?xml version="1.0"?>
<project>

    <target name="LoadDatabaseServerProfile-DOOKIE">
        <property name="databaseServerUsername" value="sa"/>
        <property name="databaseServerPassword" value=""/>
        <property name="databaseServerDataPath" value="C:\Microsoft SQL Server\MSSQL\Data"/>
        <property name="databaseServerLogPath" value="C:\Microsoft SQL Server\MSSQL\Data"/>
    </target>

    <target name="LoadDatabaseServerProfile-NIMROD">
        ...
    </target>

    ...

</project>

CreateDatabase.include

CreateDatabase.include defines the CreateDatabase target that creates a database. The CreateDatabase target:

• Ensures the tempPath, databaseName, databaseTemplatePath, databaseTemplateFilename, and databaseServerName properties have been set.
• Ensures the LoadDatabaseServerProfile-databaseServerName target exists.
• Calls the LoadDatabaseServerProfile-databaseServerName target.
• Ensures the databaseServerUsername, databaseServerPassword, databaseServerDataPath, and databaseServerLogPath properties have been set. These properties are set in the LoadDatabaseServerProfile-databaseServerName target.
• Prints a friendly message to let you know how things are going.
• Sets the databaseCollationName, overwriteExistingDatabase, and insertTestData properties to their default values, if they don't already have values.
• Copies the database template file from databaseTemplatePath/databaseTemplateFilename to tempPath/databaseTemplateFilename, replacing the following tokens while copying:
  • @DATABASE_NAME@ with the databaseName property
  • @DATABASE_TEMPLATE_PATH@ with databaseTemplatePath
  • @DATABASE_TEMPLATE_FILENAME@ with databaseTemplateFilename
  • @DATABASE_SERVER_NAME@ with databaseServerName
  • @DATABASE_SERVER_USERNAME@ with databaseServerUsername
  • @DATABASE_SERVER_PASSWORD@ with databaseServerPassword
  • @DATABASE_SERVER_DATA_PATH@ with databaseServerDataPath
  • @DATABASE_SERVER_LOG_PATH@ with databaseServerLogPath
  • @DATABASE_COLLATION_NAME@ with databaseCollationName
  • @OVERWRITE_EXISTING_DATABASE@ with overwriteExistingDatabase
  • @INSERT_TEST_DATA@ with insertTestData
• Executes osql.exe, passing in the databaseServerName, databaseServerUsername, and databaseServerPassword properties as arguments, to execute tempPath/databaseTemplateFilename.

The really important stuff is highlighted in green below.

<?xml version="1.0"?>
<project>
    <target name="CreateDatabase">
        <fail unless="${property::exists( 'tempPath' )}" message="You must specify a value for the 'tempPath' property."/>
        <fail unless="${property::exists( 'databaseName' )}" message="You must specify a value for the 'databaseName' property."/>
        <fail unless="${property::exists( 'databaseTemplatePath' )}" message="You must specify a value for the 'databaseTemplatePath' property."/>
        <fail unless="${property::exists( 'databaseTemplateFilename' )}" message="You must specify a value for the 'databaseTemplateFilename' property."/>
        <fail unless="${property::exists( 'databaseServerName' )}" message="You must specify a value for the 'databaseServerName' property."/>
        <fail unless="${target::exists( 'LoadDatabaseServerProfile-' + databaseServerName )}" message="There is no database server profile defined for '${databaseServerName}'."/>
        <call target="LoadDatabaseServerProfile-${databaseServerName}"/>
        <fail unless="${property::exists( 'databaseServerUsername' )}" message="The database server profile for '${databaseServerName}' does not specify a value for the 'databaseServerUsername' property."/>
        <fail unless="${property::exists( 'databaseServerPassword' )}" message="The database server profile for '${databaseServerName}' does not specify a value for the 'databaseServerPassword' property."/>
        <fail unless="${property::exists( 'databaseServerDataPath' )}" message="The database server profile for '${databaseServerName}' does not specify a value for the 'databaseServerDataPath' property."/>
        <fail unless="${property::exists( 'databaseServerLogPath' )}" message="The database server profile for '${databaseServerName}' does not specify a value for the 'databaseServerLogPath' property."/>
        <echo message="Creating database '${databaseName}' on server '${databaseServerName}':"/>
        <echo/>
        <property name="databaseCollationName" value="SQL_Latin1_General_CP1_CI_AS" unless="${property::exists( 'databaseCollationName' )}"/>
        <property name="overwriteExistingDatabase" value="0" unless="${property::exists( 'overwriteExistingDatabase' )}"/>
        <property name="insertTestData" value="0" unless="${property::exists( 'insertTestData' )}"/>
        <copy file="${databaseTemplatePath}/${databaseTemplateFilename}" tofile="${tempPath}/${databaseTemplateFilename}" overwrite="true">
            <filterchain>
                <replacetokens>
                    <token key="DATABASE_NAME" value="${databaseName}"/>
                    <token key="DATABASE_TEMPLATE_PATH" value="${databaseTemplatePath}"/>
                    <token key="DATABASE_TEMPLATE_FILENAME" value="${databaseTemplateFilename}"/>
                    <token key="DATABASE_SERVER_NAME" value="${databaseServerName}"/>
                    <token key="DATABASE_SERVER_USERNAME" value="${databaseServerUsername}"/>
                    <token key="DATABASE_SERVER_PASSWORD" value="${databaseServerPassword}"/>
                    <token key="DATABASE_SERVER_DATA_PATH" value="${databaseServerDataPath}"/>
                    <token key="DATABASE_SERVER_LOG_PATH" value="${databaseServerLogPath}"/>
                    <token key="DATABASE_COLLATION_NAME" value="${databaseCollationName}"/>
                    <token key="OVERWRITE_EXISTING_DATABASE" value="${overwriteExistingDatabase}"/>
                    <token key="INSERT_TEST_DATA" value="${insertTestData}"/>
                </replacetokens>
            </filterchain>
        </copy>
        <exec program="osql.exe" commandline="-S ${databaseServerName} -U ${databaseServerUsername} -P ${databaseServerPassword} -i ${tempPath}/${databaseTemplateFilename} -n -b"/>
    </target>
</project>

Project.build

Project.build is your project's build file. It must:

• Include CreateDatabase.include and DatabaseServers.include.
• Set the tempPath property. We usually set this property to a path under the build output directory so the generated database script is stored with all of the other build output.
• Set the databaseName and databaseServerName properties. We usually set these properties on the command line.
• Set the databaseTemplatePath and databaseTemplateFilename properties. We usually create a target responsible for each database and set these properties in it.
• Call the CreateDatabase target.

The really important stuff is highlighted in green below.

<?xml version="1.0"?>
<project name="Project" ...>

    <include buildfile="CreateDatabase.include"/>
    <include buildfile="DatabaseServers.include"/>
    ...

    <property name="tempPath" value="Temp"/>
    <!-- The databaseName and databaseServerName properties are set on the command line. -->
    ...

    <target name="CreateProjectDatabase">
        <property name="databaseTemplatePath" value="DatabaseTemplates"/>
        <property name="databaseTemplateFilename" value="ProjectDatabase.sql"/>
        <call target="CreateDatabase"/>
    </target>

    <target name="CreateUserDatabase">
        ...
    </target>

    ...

</project>

ProjectDatabase.sql

ProjectDatabase.sql is your project's database template. It contains SQL and tokens (highlighted in red below) to be replaced by the CreateDatabase target.

USE [master]
GO

IF EXISTS( SELECT * FROM [master].[dbo].[sysdatabases] sdt WHERE sdt.[name] = '@DATABASE_NAME@' )
BEGIN
	IF ( @OVERWRITE_EXISTING_DATABASE@ = 1 )
	BEGIN
		DROP DATABASE [@DATABASE_NAME@]
	END
	ELSE
	BEGIN
		RAISERROR( 'Database ''@DATABASE_NAME@'' exists on ''@DATABASE_SERVER_NAME@'' and overwriteExistingDatabase is not set.' , 18 , 1 )
	END
END
GO

CREATE DATABASE [@DATABASE_NAME@] ON (NAME = N'@DATABASE_NAME@_Data', FILENAME = N'@DATABASE_SERVER_DATA_PATH@\@DATABASE_NAME@_Data.MDF' , SIZE = 1, FILEGROWTH = 10%) LOG ON (NAME = N'@DATABASE_NAME@_Log', FILENAME = N'@DATABASE_SERVER_LOG_PATH@\@DATABASE_NAME@_Log.LDF' , SIZE = 1, FILEGROWTH = 10%) COLLATE @DATABASE_COLLATION_NAME@
GO

EXEC sp_dboption N'@DATABASE_NAME@', N'autoclose', N'false'
GO

...

USE [@DATABASE_NAME@]
GO

CREATE TABLE [dbo].[Users] (
	[userID] [int] IDENTITY (1, 1) NOT NULL ,
	[name] [nvarchar] (50) COLLATE @DATABASE_COLLATION_NAME@ NOT NULL ,
	[username] [varchar] (50) COLLATE @DATABASE_COLLATION_NAME@ NOT NULL ,
	[password] [varchar] (50) COLLATE @DATABASE_COLLATION_NAME@ NOT NULL
) ON [PRIMARY]
GO

...

IF ( @INSERT_TEST_DATA@ = 1 )
BEGIN

	SET IDENTITY_INSERT [dbo].[Users] ON
	INSERT INTO [dbo].[Users] ( [userID] , [name] , [username] , [password] ) VALUES ( 1 , 'Colin Coller' , 'colin' , 'insomniac' )
	SET IDENTITY_INSERT [dbo].[Users] OFF

    ...

END
GO

...

Using The Code

Now that all of the pieces are in place, you can use them to create your project's databases automatically as part of your build process.

To create the three project databases named Colin_ProjectDatabase1, Colin_ProjectDatabase2, and Colin_ProjectDatabase3 on DOOKIE:

nant.exe CreateProjectDatabase -D:databaseName=Colin_ProjectDatabase1 -D:databaseServerName=DOOKIE
nant.exe CreateProjectDatabase -D:databaseName=Colin_ProjectDatabase2 -D:databaseServerName=DOOKIE
nant.exe CreateProjectDatabase -D:databaseName=Colin_ProjectDatabase3 -D:databaseServerName=DOOKIE

To create a project database named Colin_ProjectDatabase on DOOKIE, overwriting the existing database if it exists, and insert test data:

nant.exe CreateProjectDatabase -D:databaseName=Colin_ProjectDatabase1 -D:databaseServerName=DOOKIE -D:overwriteExistingDatabase=1 -D:insertTestData=1

You can, of course, use these these tasks as part of a larger task.

Comments

• You can name the files, targets, and properties whatever you like.
• You can have more than one DatabaseServers.include file, and you have some flexibility as to which files are included. You might want to have two files (one for development servers, one for preproduction servers) and select which file to include based on a property, for example.
• You can have more than one ProjectDatabase.sql file.
• You can add or remove tokens. You might want to create databases using the default paths (remove the @DATABASE_SERVER_DATA_PATH@ and DATABASE_SERVER_LOG_PATH@ tokens), add a conditional block to insert production data (add a @INSERT_PRODUCTION_DATA@ token), etc.
The tokens I've included are useful for me, but they might not be useful for you.
• The templated database script is pretty simple, so you're not locked into NAnt. Your application could consume the templated database script programmatically, for example.
• The generated database script is stored on disk, so you can inspect it if there are errors, you want to audit what was executed, you want to include it in a formal release plan, etc.

Cheers,

Colin

09:15 PM | Colin