How do you check your database in and out of svn (or git)

Question

Currently I go into phpMyAdmin, export my database as a text file and then save it with the application files before I commit things to svn (or git). Then of course, I've got to import it to production.

Is there a better way?

Answer 1

Depends on the language you use, RoR has it built in. Currently for a project I'm doing in ASP.net MVC I have 2 files in the project in a folder: database. One file contains the structure of the database and one file some dummy variables for testing. I must say it is a cumbersome way of sharing your database since when you update something you have to let the others know they have to rerun the (updated) sql structure script.

The structure script deletes tables if the exist and readds them + adds new tables.

Could not find a better way like db::migrate of Ruby on Rails.

Answer 2

Generally, I would create a script that is able to generate the database (i.e., all the tables, users, views, indexes, etc) and another script that populates the DB with data. Then, use DBDeploy (similar to RoRs migrations) to handle all DB modifications. Then I would create build targets for all these script in Ant, NAnt, Buildr, etc. This way everything is versioned and in text files so it works with any SCM.

Answer 3

If you're looking for migrations similar to db:migrate in Rails, but you're not in rails, there are other options. There's migrate4j which is similar to db:migrate, but written in/for Java. There's also liquibase, which is very flexible and (AFAIK) language independent, but does make you write everything in XML (which is kind of the opposite of "the Rails way").

Answer 4

If you don't have something like rails migrate, are in java environment or anything else, check out liquibase. It's pretty cool if you need that much flexibility. We just track .sql files which setup the entire database.

Answer 5

If you look at Apache ODE, they have a h2.rake task for Buildr that builds a database for testing automatically.