

What's your method for pushing database changes from development to production? - fatalerrorx3

I just started using (and loving) git, for keeping track of changes that I'm making, being able to work on different features simultaneously with separate branches and merging to master only when I've finished a specific feature or change and then pushing changes easily from a development
environment to the live environment.<p>I'd like to be able to do the same thing for a mysql database, is there the equivalent of git for database schema and data management?  Afterall a database is really just made up files is it not?<p>Any help would be greatly appreciated
======
manuscreationis
If you're using a framework like rails, it has built in functionality to
manage database versioning, but you have to do everything "the rails way", or
else you won't get the full benefit of it. The good thing there is, it'll also
handle reversing out of a version as well (downgrading).

There are third party takes on this system as well, but I've never tried any
of them.

Various places i've worked for have had a very similar (to each other, not to
rails) approach to doing versions.

First and foremost, you need a core set of scripts that you are always
updating to be the "latest", so that you can very easily spin up an empty db +
required static data + any bells and whistles you wanna throw in (like a
default user account, for example - although you don't want to run that script
on production, clearly).

Then you need another set of scripts that you keep in directories named for
their version. Those scripts either reference the scripts inside of your core
script directory (for example, if in release 1.4 you're adding a new stored
proc, you'd define the proc in your core folder, and in 1.4 you'd have a
script that runs all the 1.4 changes, which references the file from the core
location), or comprise the DDL changes you're making that are now reflected in
your always-updated core scripts (for example, if you modify a table, you'd
update the core script to have the latest definition, then add a modification
script under 1.4 that just does the diffs to that table)

The most important piece here is an environment to run things in that will
handle making changes in a transaction, and rolling things back if something
fails (so you can safely do your updates and recover without putting any
database in an incomplete or corrupted state). On Windows, Powershell works
fine for this (although PS isn't the best thing in the world, it gets the job
done). On *nix, i'd imagine you can accomplish the same thing with a bash
script. You absolutely do not want to be running scripts by hand, 1 after
another. You should have a light set of scripts built around whatever file
structure for your sql files you have, that follows a convention based
approach to locating an "upgrade.sql" file, which then invokes the rest of the
sql files needed for that upgrade.

Hopefully this approach makes sense as I've described it. It's by no means
perfect, but as I've said I've seen it in use in several places, and it seems
to work just fine.

