Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

When working in a team of developers, where everyone is making changes to local tables, and development tables, how do you keep all the changes in sync? A central log file where everyone keeps their sql changes? A wiki page to track alter table statements, individual .sql files that the devs can run to bring their local db's to the latest version? I've used some of these solutions, and I'm tyring to get a good solid solution together that works, so I'd appreciate your ideas.

share|improve this question
add comment

7 Answers

up vote 4 down vote accepted

I use a code-based Database Migration tool and keep the migration code in source control.

By using timestamps as version numbers, any number of devs are mostly free to add migrations as they please and we can run the Migration tool against any copy of our database confidently.

I used to use SQL Scripts under version control, but find the code-based approach much, much easier to work with due to them all being in one logical "spot" and being able to execute all needed scripts with a single command.

share|improve this answer
add comment

I don't do it myself, but some developers maintain a collection of SQL scripts under source control which, when executed, can recreate the database tables for testing purposes, and build up an empty database for production purposes.

The same technique can be used to version the database at the customer site, when fields or tables need to be added or removed, or data transformations need to take place.

share|improve this answer
add comment

Build scripts under version control and continuous integration to verify them

One approach that worked for me was to have each developer work with their own schema which they can do what they like with. Their schema was destructible and populated with test data taken from a version controlled set of scripts that all developers contributed to.

The nightly continuous integration build took the latest version of all the scripts and attempted to build a cohesive test database from them. The application then had a series of integration and functional tests run against it to verify that the current schema was in line with the current release candidate.

Before starting down this road, there was a pretty solid database design in place and a DBA was always keeping an eye on things to prevent developers going mad with denormalisation and other horrors.

Version control helped immensely here because changes to the scripts were immediately obvious. We also made use of a database VERSION table to identify the overall state of the database. This was a simple integer sequence and was not linked to any particular application.

Overall, it worked well and meant that developers ceased to be fearful of changing the persistence tiers because they could always roll back their own schemas without impacting others.

share|improve this answer
add comment

Along with keeping schemata and other SQL scripts under version control, another handy practice is to maintain a 'schema version' table in the actual DB.

create table schema_migrations (
    `appliedAt` timestamp not null default CURRENT_TIMESTAMP,
    `migrationCode` varchar(256) not null,
    `extraNotes` varchar(256),
    primary key (`migrationCode`)
)
share|improve this answer
add comment

The approach that I use is to provide one table for parameters. This table will have one name/value pair for the version that the database is on. This gives me two benefits: I have a way of verifying a database only fix has been applied through the application, and I can use that value for my SQL scripts.

The SQL script will create new tables, alter columns, and whatever work is needed on the database to promote the script from the version before. Ideally I would also have a rollback script, but most of the time I don't.

BTW, this whole approach has been automated as part of Ruby on Rails, complete with the rollback scripts. I like the idea of that, but not all frameworks do that. When I'm not using Ruby on Rails, I use the approach outlined above.

share|improve this answer
add comment

If you are in a MS shop, Visual Studio 2010 has some nice database version control tools, which can also generate change/deployment scripts based on the differences between two databases.

share|improve this answer
add comment

Doctrine has an awesome Migration tool: http://www.doctrine-project.org/documentation/manual/1_2/en/migrations, best part is that they are autogenerated or can be easily coded by hand.

share|improve this answer
    
I didn't even realize they provided that... neat. –  gabe. Dec 30 '10 at 0:18
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.