Possible features for an Agile Database Release System:

• Allows construction of a database at a particular version
• Allows migration from an existing database to a later version
• Allows migration from an existing database to an earlier version
• Human readable format for releases
• Release ‘action’ available on multiple environments (ie various operating systems) allowing development on a different platform than production
• Provides a complete history of changes for each database object
• Provides a current creation script for each database object
• The source code can be branched and merged
• Allows multiple developers to work with/on the same database source code, at the same time
• Supports an ‘automated build’ / ‘continuous integration’ enviroment
• Automatically records database changes by the developer in their development area
• Supports use of parameters to configure variable parts of the release (eg the password part of “CREATE USER warren IDENTIFIED BY xyzzy”)

by

There are a lot of people who want an Agile Database Release Solution. I’ve listed a few links and occasional interesting bits from them, but the common solution seems to be a master script of all database changes.

General Overview

‘Agile Database Techniques’ A book by Scott Ambler – Describes three types of log files: “Database change log : contains the DDL source code that implements all database schema changes”, “Update Log: source code for future changes to the database schema that are to be run after the deprecation period for database changes”, and “Data migration Log”.

Agility and the Database – A detailed discussion of the problem and high level solution.

Applying Agile SCM to Databases – “Databases come with state.” A short paper on the problems.

Version control of database objects – a long and interesting dicussion talking about a big change log solution. An initial set of considerations: “Track the end state of the object or modifications?”, “Support for automatic builds”, and “Support for branching and merging”. One of the comments describes and interesting approach, to comment out the previous DDL in the source file, so it is still there, but only the new migration command will be executed. The concept of being able to generate whatever artifacts you may be interested in is also mentioned (for example, if you want a clean table creation script for a table, or history of changes for one table, go generate it – don’t repeat yourself in the version control system).

SCM for Databases? – A short overview of some issues. One comment points out the problem with ‘difference’ tools is that they only catch certain changes – not all.

Solution Descriptions

Our Database/SQL version control process – all DML and DDL changes are logged, but this one does not sound like it was automated.

The Database Patch Runner – all alterations for a “conceptual change” are written in one patch file and the patch files documented in a separate txt file. The patch file is actually a PL/SQL procedure that will be executed by a “patch runner”. The patch runner ensures that no patch is ever run into one database more than once, I am guessing that the patch runner knows because it keeps a log file, or an install log within the target database. “In response to the single script with history for each table. Until you’d mentioned it I hadn’t noticed that it wasn’t there, so no…I suppose we don’t miss it.”

Bugzilla Approach – “one great big ‘create-database.sh’ script…Whenever someone wants to make a change to a schema, they add it to the bottom of the script, along with a test to see if the change has already been applied.”

Version Control of Database Data – uses DROP table first then and number of scripts to recreate new table and reference data. Does not seem to address what happens to production data.

XP and Databases – “act as if the database is easy to change”. This article describes databases as Gold (production), Silver (migrated Gold ie test) and Bronze (migrated Silver – ie development).

Database scripts and version control short solution comment that I didn’t really follow.

Other Links

“Sorry, we made database changes so there is no going back” Software releases and database changes suggests that for every change script there should be an equal and opposite rollback script. I prefer the idea that in development, you are able to rebuild everything, and in production it should happen so rarely that the effort involved in dealing with a rollback would be (much) less than the effort involved in providing a repeatable solution.

A comment in The Database Patch Runner – Rollbacks mentions the Oracle “FLASHBACK DATABASE” to rollback the entire database – perhaps this is something that could be turned on before a production release?

The Database Patch Runner: Dealing with code contains an interesting comment about retirement of code. How do you stop using a package? “We produce a patch to drop the objects we no longer need and then remove the objects from version control….Obviously you need version control software that deals with this properly (earlier tagged versions should still contain the removed scripts)”.

by

To work in an Agile way, change should be expected and easy to manage. How does this look from a database perspective? Anything that you can “CREATE OR REPLACE” works fine – it can be stored in version control and the latest full version checked out to release to any environment. But what about those things that require migration? Like a table, or data.

A simple example could be an existing table with a ‘person_name’ column that contains first and last name separated by a space. You need to:

1. create two new columns ‘first_name’ and ‘last_name’
2. migrate the data from the ‘person_name’ column
3. then remove the ‘person_name’ column.

A simple question right now would be (assuming I have the existing database and a new database with the changes applied): Is there any tool that could generate a difference script between the two database states to perform those actions? To date I have not found anything that could handle the “simple example”.

To further complicate the problem, not only is there is migration of objects over time, there are dependencies between objects at a point in time (eg foreign keys).

Some initial questions:

• Do you put the original table creation script into source code control and keep it up to date? For what reason (it can never be used except to build a ‘blank’ database)?
• Do you have a database “patch” script associated with a release? How will this work in a development environment with multiple developers adding to the script during development, and also wanting to only run the new changes to the script into their own development areas? How do you rollback a database patch? Or how do you complete a database patch that failed halfway through?
• How to manage continuous integration, where each new change to the database patch should be applied as soon as it is checked in?
• Are you interested in seeing the current state of a table creation script?
• Are you interested in seeing the history of changes to a specific table?
• How to ensure that the objects referenced by a foreign key exist before the foreign key creation?
• How do you branch the database DDL code?

I plan to explore these issues in some further blog posts, ending with a possible solution. Keep reading 🙂

by