MYSQL development workflow with git versioned scripts

Question

I'm sure that similar workflows are common, but in my searching I have not found any documented examples.

Goals:

1. I want to manage all of a mysql (v5.1+) database definition in git, so that I have all of the benefits of source control.
2. I want to define changes to the database by editing the scripts, and running the scripts, so that I can edit the database definition in my editor of choice (vim), rather than the mysql shell, or a gui app.

Aside: I currently use the mysql shell, but would like to use vim to maintain routines and events which becomes unwieldy in the shell.

Plan:

I will do an initial dump with the '--no-data' option to get my initial scripts, but from then on I want my workflow to be: edit scripts -> commit -> run scripts rather than: perform change to database -> dump ddl -> commit.

Caveats:

If I dump the database with the following:

mysqldump -u [user] -p [password] --no-data --routines > ddl.sql

It includes:

DROP TABLE IF EXISTS `[table]`; CREATE TABLE `[table]` ([table definition])

Obviously that will destroy, and recreate the table if I run the script. What I would like to do is update the table with definition changes if the table exists, otherwise create the table with the definition. I would prefer to define the table in one [table]([table dfinition]) block, that is re-used, than to have to maintain multiple duplicate blocks.

How would I change the initial dump syntax, or the scripts, so that I can use the same scripts to update the database, and re-create the database definition (without data) in another environment?

Answer 1

As you point out, just mysqldumping does not work for this.

Generally, you need to store the changes to the database structure. So instead of storing:

// commit 1
// foo.sql
CREATE TABLE foo ( foo_id INT );

// commit 2
// foo.sql
CREATE TABLE foo ( foo_id INT, foo_val INT );

You should store something more like:

// commit 1
// foo_1.sql
CREATE TABLE foo ( foo_id INT );

// commit 2
// foo_2.sql
ALTER TABLE foo ADD COLUMN ( foo_val INT );

Then, you should have a script that can run

• foo_1.sql
• foo_2.sql

in the right order.

You would be even better off to write scripts that have up() and down() options, so your migrations can be run forwards (build table, add column) and backwards (drop column, drop table).

You just have to remember to run the backwards scripts before you check out a branch that relies on the db change.