Is there a MySQL version control system or something?

March 12, 2008 5:13 PM

What is the best way to work databases like we does with version control (Subversion or CVS) for the source files? I was researching but probably I'm not using the right words or there's some special way to work.
posted by Leech to computers & internet (19 comments total)

Are you talking about using source control for stored procedures, views, and things like that?

I've heard of people saving their sproc code as .SQL files and putting them into source control, but this sounds like it would be such a clusterfuck to keep straight that it wouldn't even be worth it. Best solution, to my mind, is to just use a development DB and then integrate when necessary.
posted by Afroblanco at 5:23 PM on March 12

If you're trying to keep versions of your database structure- the database schema, as its usually called- the typical route would be to save the schema to an SQL file, by using your database's dump feature, and then store that SQL file in a subversion repository. You could do the same thing to store versions of contents of your database too, by dumping both the contents and the schema, but, depending on how much is in there, that could quickly start to use a lot of space. We could probably provide more info if you can give a better description of what you're trying to do.
posted by gsteff at 5:25 PM on March 12

Stored procedure code, DDL, and the like are all code and should be treated as code. The database SHOULD NOT be used as a repository for code-Instead, object definitions should be maintained as .sql(or whatever) files and placed in $VERSIONCONTROL.

Most databases will let you export the entire contents of the database, minus the data, for safekeeping. I'm an Oracle guy, not a MySQL user so I can't give you specifics.
posted by neilkod at 5:27 PM on March 12

What we do is far from ideal but it's the best we've come up with so far:

1. All changes to the schema are scripted and those scripts are checked into subversion.
2. When a release is shipped we tag the repository at that point (including the DB scripts) so that (in theory) we could recreate that release by applying each of the SQL scripts in turn (I mean including previous releases) up to the release level we want.

... what we don't yet do but is planned is to....

nightly write the the structure of the schema out to a file and check it into Subversion so that we have an ongoing record of the entire schema. We will use the PySVN modules which allow you to script Subversion using Python
posted by southof40 at 5:33 PM on March 12 [1 favorite]

Might want to take a look at the "Migrations" that Rails uses, they're quite clever.
We just use the numbered .sql files checked in with the code that requires it, it isn't all that hard to manage.
There are also tools out there that will 'diff' 2 databases and give you the SQL needed to turn one into the other.
posted by gregjones at 5:40 PM on March 12

The rails migration system (the fundamentals of which are relatively easy to port to other systems) is a pretty good example of how to deal with db changes.

Basically, you store your entire db structure as a series of changes, each of which specifies how to do it and undo it, so you can also use it to roll back changes.

It's not perfect, but it's better than anything else I've seen.
posted by Caviar at 5:41 PM on March 12

Great, seems like all are going to the same direction.

Yes, we need a way to control changes made by all team members on the database structure (we already have database backup) and roll back if necessary.
The idea of dump structure to .SQL files and control these files are good. Also the automation with PySVN too.

Thank you, if anyone knows a better way would be helpful.
posted by Leech at 5:46 PM on March 12

I haven't played with MySQL all too much, but in MS Sql Server, there's a concept called "Database triggers", meaning that whenever a sproc change or a schema change is made, a trigger can be executed. I've used this to grab the code for a sproc, for instance, write it to a file, and call the appropriate SVN commands to check it in automatically. The end result: automatic version tracking of all sprocs without user effort.

The last part (automatic) is important, as devs are lazy, and will not remember to go through the process of saving the sproc out to a file, checking it in, etc. This is especially true for folks who write SQL directly in a query tool, rather than in a text file. I've been meaning to dump this in an open source project one day, but...I've been too lazy :)
posted by razdrez at 5:55 PM on March 12 [1 favorite]

Yeah, if you need to migrate your database structure between versions, you're probably going to want to keep an SQL script that migrates each version of the schema to the next version, along with a script that goes in the reverse direction. As long as those scripts work correctly, you won't really need a version control system, at least for these purposes... every time you create a new version of the schema, you'll create a new pair of files, and the old files won't in general be changed again. At least, that's how we did it at my previous job (although after I left they moved towards making the scripts Java programs instead of SQL scripts).
posted by gsteff at 5:59 PM on March 12

My approach is pretty simple: I run mysqldump and svn commit each night via a cron job.
posted by fleeba at 6:46 PM on March 12

Nightly dumps of the entire db. Stores procs and schema changes are scripted out and stored in Subversion.

But I work in the financial sector, so we're a bit paranoid about these kinds of things. You could probably get by with monthly dumps and daily diffs.
posted by Civil_Disobedient at 9:33 PM on March 12

@gregjones: do you remember any of these tools that does a 'diff' to both db an creates the SQL queries to make changes?

@Caviar: hmm looks very complicated, specially for small changes on db structure like ALTER column type, but looks good for big versions migration.

@gsteff: yes, nice idea. Do you know if there's a way to compare both databases and get the SQL script to replicate the changes made before?

@fleeba: I was thinking about that. The easy way :) But not the better.
posted by Leech at 10:09 PM on March 12

With MySQL, there's MySQLDiff (google it), but it's old ... and a link I can't find now to someone who charges money for a class that they wrote to do essentially the same thing, but I can't seem to find the link.

I've also seen a perl script included with OSCommerce called mysqldiff.pl.
posted by SpecialK at 10:34 PM on March 12

razdrez, any chance of seeing that trigger code?
posted by yerfatma at 4:50 AM on March 13

hmm looks very complicated, specially for small changes on db structure like ALTER column type, but looks good for big versions migration.

No - the entire point is that each migration itself gets a small change, or a small set of related changes, and you have a bunch of these numbered files. You have to do every change to the db through migrations, or it doesn't help you.

In practice, it's not very complicated at all. When you need a change to the db, you add a new migration file specifying the change (and most of that file is boilerplate structure), and then you can run "migrate" on each of your dbs (rails uses the ruby make analog "rake" to do this). Each db has a special table, managed by the migration system itself, that stores what version number the db is at, and when you run it, it only runs the new migrations added since that version. If you specify a version number, the db gets rolled up or down to that specific version. There are some other features, but that's the core of it.
posted by Caviar at 7:06 AM on March 13

IMO, the real point of using something like the rails migrations is that it can "know" how to back out an ALTER or something without you explicitly writing SQL to do the back-out.

I've seen a lot of back-out scripts that were never tested and that don't work right.
posted by smackfu at 8:09 AM on March 13

yerfatma, inspired again with the idea (and to get some help to better the code), I'm opening up a sourceforge project for my "DBVC" project. I'll put a link there once it gets approved, if nobody minds me slightly hijacking the thread.

And I realized that when I mentioned "database triggers", I was actually talking about "DDL triggers".
posted by razdrez at 9:14 PM on March 13

Well that makes a heck of a lot more sense to me.
posted by yerfatma at 6:51 AM on March 14

I've seen a lot of back-out scripts that were never tested and that don't work right.

Ha, yeah. There's been a lot about versioning database schemas on the web in the last couple of years and I wonder how much of it is simple piece of mind. I'd hate to find out how well my method doesn't work in a crunch. You'd still need some way to migrate data back into older tables.
posted by yerfatma at 6:52 AM on March 14