Synchronizing Database Schemas among Developers

Question

I'm working on a project with couple others. We all have local copies of the project, which is also constantly updated via svn repo.

Because we are in the early stage of the development, we often change the schema of our database. This leads to a lot of problem when we sync our code, because we don't have a great way to synchronize our database schemas.

What are some intuitive and easy way to sync frequently changing database schema?

We are working with CakePHP (not sure if this would help me find good solutions).

EDIT

Found some tools to do this type of work in CakePHP: http://book.cakephp.org/view/734/Schema-management-and-migrations

And here is an additional website: http://bakery.cakephp.org/articles/view/cake-db-migrations-v2-1

Answer 1

Database migrations are an easy way to keep your working databases in sync. Essentially, migrations are scripts that update a database to the latest schema and fill in the new tables with the correct data, so they are kept in a valid state.

There are few features provided by migrations:

• Tools to automate the creation/update of the tables. The tools keep track of the schema version and which scripts need to be run.
• Some migration tools provide ability to run code (c#, ruby, etc.) instead of sql scripts. Code libraries provided by the migration tool is usually better able to abstract the database dependent parts and make your database scripts more database independent.

There are tools available for Ruby (migrations are an important part of Rails), C# and Java. Surely, other languages also.

There are a number of questions here on migrations and I would suggest searching for a migration tool that fits in your tool chain.

Answer 2

CakePHP Database Migrations by Joel Moss is by far the best solution at the moment.

Project description from github:

Database Migrations for CakePHP 1.2 is a shell script supported by the CakePHP console, that lets you manage your database schema without touching one little bit of SQL. It is based on the Ruby on Rails implementation of Migrations, and uses Pear's MDB2 package, so supports all the databases that that supports.

You could think of Migrations as a version control system for your database. It's power lends itself perfectly to developing as part of a team, as each member can keep their own independent copy of their application's database, and use Migrations to make changes to its schema. All other members have to do then, is to run a simple two word shell command, and their database copy is up to date with everyone else's.

The Migrations shell will generate a migration file for each DB change you want to make. This file can include any number of DB changes.

Answer 3

Here's a great example using Git but the same applies to SVN anyhow. http://thewebandthings.synodicsolutions.com/2009/06/13/cakephp-versioning-database-changes-with-git/

Answer 4

i have started a small project that we use to sync database between developers and deploy to production. Its still at an early stage, but its proven to work it just doesn't have a lot of documentation yet.

http://code.google.com/p/php-mysql-version-control/