A simple and tool-free way for database versioning (I)
Posted by Vlad on May 26, 2009
One common problem in projects that involve a database is managing its versions.
Basically, there are two needs to be fulfilled:
- to be able to see a history of changes for a database object in a similar way to seeing the changes for a code file
- to be able to update / revert the database to a specific version without losing the data.
For the first one, a simple solution is to just script all objects in the database to a folder and add it to the source code repository (one file per object – in MS SQL Server can be done with a few clicks from SQL Server Management Studio). When there are changes, all you need to do is to repeat this process and the files will be updated. You can then review changes to database objects in the same way you review them for code files.
The second one is a little trickier. Idealy you want scripts that can be automatically used to:
- update developer database copies
- update the continuous integration database (database used for the continuous integration tests)
- update the test database
- update the production database.
There are tools that give you “diff” scripts that can be used to update a database, but, apart from the price problem, they are difficult to use for several-times-a-day automatic updates like the ones needed to update the developer copies. A second problem is that, because the developers have full freedom in their database copy, some “garbage” or unwanted changes might be picked up and included in the diff script.
The solution below is free, the updates can be run automatically whenever is necessary on any database (developer, test and even production) and ensures that the changes were really meant to be included in the update. All you need is a little discipline from the developers.
We’ll use a table in the database to store the current version. This script create this table and sets the version to 1.
-- CREATE Table [dbo].[Version]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET XACT_ABORT ON
GO
BEGIN TRAN
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Version]') AND type in (N'U'))
BEGIN
DECLARE @actual int
SELECT @actual = ISNULL(MAX(VersionNumber),0) FROM Version
RAISERROR('Update to version %d cannot be run - actual version is %d', 1, 127, 1, @actual) WITH LOG
END
CREATE TABLE [dbo].[Version](
[VersionNumber] [int] NOT NULL,
CONSTRAINT [PK_Version] PRIMARY KEY CLUSTERED
(
[VersionNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO [dbo].[Version]
([VersionNumber])
VALUES
(1)
GO
COMMIT
The next step is to define a template for an update:
-- VERSION DESCRIPTION HERE!
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET XACT_ABORT ON
GO
BEGIN TRAN
DECLARE @newVersion int
SET @newVersion = -- version number here
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Version]') AND type in (N'U'))
RAISERROR('Update to version %d cannot be run - actual version is 0', 1, 127, @newVersion) WITH LOG
DECLARE @actualVersion int
SELECT @actualVersion = ISNULL(MAX(VersionNumber),0) FROM Version
IF @actualVersion <> @newVersion - 1
RAISERROR('Update to version %d cannot be run - actual version is %d', 1, 127, @newVersion, @actualVersion) WITH LOG
UPDATE [dbo].[Version]
SET [VersionNumber] = @newVersion
RAISERROR('Updating to version %d...', 1, 1, @newVersion) WITH LOG
GO
-- START UPDATE
-- END UPDATE
GO
COMMIT
What this does is that it checks that the actual version in the database is the one preceding the @newVersion we want to apply. If there is no Version table or it has a different version, the script raises an error and aborts the update. If everything is ok, it applies the changes found between — START UPDATE and — END UPDATE and updates the version number.
The developers can now create update scripts using this template and save them in a “Change Scripts” folder using a naming convention like v0001.sql, v0002.sql, etc. The naming is important because we’ll rely on it to automatically run the scripts in order. The folder should be added to the code repository so everyone can easily get the latest changes.
Also, it is important that the developers take into account that the scripts will be run in production – no data loss is allowed! So no Drop and Create Table – use Alter instead!
In order to run the scripts automatically you can use the following batch files or write your own using your preferred shell.
First, a helper script – runOneSqlScript.bat – receives (in order) servername, database, sql file, output log file and a flag wether to show the output to console and calls sqlcmd passing those parameters.
rem @echo off
sqlcmd -S %1 -d %2 -i %3 -o %4 -X
IF "%5" == "N" GOTO :END
type %4
:END
rem @echo on
This second script uses the first to run all the available updates. You can pass parameters if you need to use another server or database name than the defaults.
set DBSERVER=localhost
set DBNAME=MyDB
IF "%1" == "" GOTO :STARTSQL
SET DBSERVER=%1
IF "%2" == "" GOTO :STARTSQL
SET DBNAME=%2
:STARTSQL
for %%f in ("Change Scripts\"v*.sql) do call runOneSqlScript.bat %DBSERVER% %DBNAME% "%%f" "%%f.log"
Using these, the developers can automatically apply updates to their copy of the database. The script can also be run as part of the Continuous Integration build and update the CI database copy before running the tests. And finally, the same scripts can be use to update the test and production databases. Because the developers have to take the time to create the update script, the chance of adding “garbage” changes is greatly reduced.
For the developer and test databases another important issue is the demo data – how to maintain it up to date and not have it turn into garbage. In the next part I’ll propose a solution that builds on the automatic updates presented above to mitigate this problem.
