Agile development and database changes

Question

For those agile practitioners out there...

How do you manage changes to a database schema during a project? My assumption is that in an agile project the schema of any database involved will change and be refactored just as happens with the codebase.

Is this assumption correct? If so, do you have any particular tools or processes that you use to help keep things running smoothly?

Answer 1

Ideally you make non breaking changes, then when release is done you can fully deprecate old parts of the schema. This is not easy and requires discipline. It is not even always possible.

Answer 2

AgileData.org is an excellent resource -- much more than I cram into a single response -- on Agile Database development. In particular, you might be interested in Agile Data Best Practices. If you use SQL Server, you might also be interested in SQL Compare from Red Gate software. Our DBAs have used it to help me migrate changes from QA to Production for existing applications.

Answer 3

Take a look at Ruby on rails migrations. Doesn't matter if you don't use Rails, as the idea has been copied to other framework already.

Answer 4

In our Agile setup, there's a folder for DB changes, done as .SQL files. So far we've had a DB change in every version, and the file is named after the app version. The install script automagically applies all the change files when updating sites.

We also have a full schema dump of a reference DB, that's used for new installs, created by our DB Admin tool.

I know there are tools that help automate this process, such as Red Gate, but manually creating the SQL change file is very easy.

Answer 5

For each update, I'd be:

• deploying roll forward and rollback scripts,
• deploying a "build the DB from scratch" script,
• deploying a data migration script, and
• enforcing a mechanism whereby the code is locked to the database version, i.e. testing for a value that returns the current version of the DB, if there's a mismatch, the system bails and bleats loudly about the mismatch.

HTH

cheers,

Rob

Answer 6

The database structure is most likely to be a dependency of many parts of your code, and schema changes will have cascading effects. Kind of like making changes to the interface in a class which many classes extend. So be cautious about schema changes.

Agile methodology is no different than other methodologies in that it's to your benefit to design the database up front as much as possible, and you should seek to change it less frequently than the code. Not to say you can never change it, but it is costly to do so.

Migrations are a simple but effective tool for tracking schema changes, as others have noted. The concept being scripts of CREATE and ALTER statements to upgrade from one revision of the schema to the next, accompanied by scripts of ALTER and DROP statements to downgrade the same changes. Ruby on Rails uses a database abstraction layer on top of this to make it easier to switch brands of database, but if you only need to support one brand you could simply use SQL files.

There's a highly-regarded book on this subject (though I haven't gotten around to buying or reading it yet) called "Refactoring Databases: Evolutionary Database Design" by Scott Ambler