Tim Scott's Blog

March 20, 2008

Introducing DbVerse – A Tool For Database Change Management

Filed under: C# — Tim Scott @ 8:25 pm

I have just released DbVerse, an open source solution to the problem of database change management in software development.

Let me describe the problem that DbVerse seeks to solve:

Your team has gotten a good start developing a new application. Like most business applications, yours uses a relational database for persistence. You’ve partially defined the database schema. Each developer has a copy of the dev database on their local machine. Things are humming along nicely. Then Sally adds a new feature which requires adding a column to the Orders table. How do you get that change applied to all other developer machines, your CI server and your QA server all in lockstep with the code that depends on that change? Before you’re done dealing with Sally’s change, Jim adds an record to the OrderTypes lookup table. The code will break without it.

So you have struggled through development by emailing change scripts here and there. Or perhaps you periodically replace every instance of the database with a master copy that is kept in the source code repository. You are not too bothered by the fact that once you week or so you chase your tale for an hour because your local database is missing some recent change. You’re also not too worried that 20% of your integration tests no longer pass because they are never run on the CI server. But now you have gone into production. Version 1 of the database is deployed and is piling up production data. Development continues. By what process will you apply new schema changes to production? How will you keep track of which changes go with the new version you’re about to deploy? How will you be certain they have not yet been applied?

DbVerse provides a system to manage this whole process. Developers author database changes in .NET code or SQL scripts. These changes are versioned with rest of the source code and thus immediately available to the entire development team. Developer and CI databases can be automatically rebuilt to match the current code from a command line (and therefore a build script) or via a Windows UI. Likewise QA and production databases can be automatically updated with only new changes via command line or via a Windows UI.

Under the hood, DbVerse uses SQL Management Objects (SMO) for authoring changes in code. Therefore it is targeted at SQL Server based applications. DbVerse uses a project template for easy setup, which creates a C# project. It would be possible to use DbVerse with Visual Basic, but this has not been tested.

Advertisements

5 Comments »

  1. Hi Tim,

    Can you please provide VS2008 project template? Many thanks for the good work.

    Kevin

    Comment by Kevin Dan — April 8, 2008 @ 8:20 pm

  2. Kevin,

    Glad to here you are using DbVerse. Yes, I can do this. I should say that given my schedule I might not get to it right away. It’s an open source project and contributions are very welcome. I have created a discussion group to suggest new features, discuss usage, etc. It’s here:
    http://groups.google.com/group/dbverse-discuss

    Tim

    Comment by Tim Scott — April 8, 2008 @ 10:28 pm

  3. I have created a VS 2008 version of the template. It is now available on the downloads tab, here: http://code.google.com/p/dbverse/downloads/list

    Comment by Tim Scott — April 24, 2008 @ 3:17 pm

  4. Hi Tim,

    I was wondering, does dbVerse help with scripting permissions on db objects? I see lots of support for adding tables and stored procedures, but without appropriate GRANTs these objects won’t be usable after being deployed.

    I’m hoping I just missed this in the code samples. Would I be limited to SQL scripts or SQL query strings to do this?

    Comment by bonder — July 8, 2008 @ 1:17 pm

  5. bonder,

    There are methods to add logins/users with various permission levels to all objects in the database. DbVerse authoring language does not provide methods to lock down certain database objects. By default users with permissions to the database should have access based on those permissions to all database objects.

    If you need to do something DbVerse authoring does not support, you can do anything by script. You can also use the SMO object directly something like this:

    Db.Table(“SomeTable”).SmoTable.[any property or method of an smo table object]
    Db.Table(“SomeTable”).SmoTable.Alter();

    All DbVerse objects expose the SMO objects they wrap, so you can do this at ant level.

    Comment by Tim Scott — July 9, 2008 @ 1:20 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: