MYSQL development workflow with git versioned scripts

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?

  • Is there a convenient way to include a mysqldump in my git commits?
  • mysqldump schema only, schema update without drop
  • Git Squash commits from mysqldump
  • Howto sync database with git and git hooks and howto debug if it doesn't work
  • Git Push doesn't work when script runs through Launchd
  • Export and import database on Git push and pull
  • git pre-commit + mysqldump: cannot find path, not existing command
  • How to include MySQL database schema on GitHub?
  • One Solution collect form web for “MYSQL development workflow with git versioned scripts”

    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.

    Git Baby is a git and github fan, let's start git clone.