Using Your Fixture Data in Development

So you've spent hours putting together some sensible data for your tests in a bunch of tediously crafted YAML files, and you're staring at a blank development database. If only there were an easy way to slip that data from the test database back into development . . .

Your strategies for managing data will probably evolve as your project progresses, but I find that in the early stages when I'm trying to be "agile", I'm making frequent changes to the database and I don't really care about full-cycle management. In this stage, writing migrations for each change in DB is a drag, because I don't want to carry the kind of configuration management overhead during this rapid prototyping phase. Managing the data with migrations is even worse.

That's why I find it so convenient to prototype the DB grahically using OmniGraffle and just keep rolling back to migration zero, then recreating the database from the updated initial migration. You're thinking "Gee, doesn't that mean you're blowing your tables and data away every time you make a change"? And indeed, that's what brings us to this point.

Even in rapid-prototyping mode, I still have tests and fixture data. When I make a change to the DB, then I get the fixture data repaired, get the tests running, and then I want to "play" with the system in development. The data I want is in the test DB (because my tests loaded it from the fixtures). It's possible to trick a dump from the MySQL Administrator or command line to load in another database, but that's realy not convenient. To make this fast and easy, I created a script to copy the data from each table in the test DB to the development DB. It doesn't delete the table (which could change the options it was created with), it just moves the data.

#!/bin/csh
echo "Get tables . . ."
mysql -u person -ppass -h localhost -D my_development >table_list.tmp << EOF
show tables from my_test 
EOF
foreach tableName ( `cat table_list.tmp | sed -e '1d' | sed -e '/schema_info/d' `)
  echo "clean and load $tableName  . . ."
  mysql -u person -ppass -h localhost -D my_development << EOF
  delete from $tableName ;
EOF
  mysqldump -u person -ppass -c -n -t --skip-opt prototype_test $tableName | mysql -u person -ppass -h localhost -D my_development
end
rm table_list.tmp
echo "Done."

If you want to move the data from just one table, that's pretty easy too:

#!/bin/csh
# clean and reload the specified table in development from table in test DB
# USAGE:  reload_table table-name
echo "clean table $1  . . ."
mysql -u person -ppass -h localhost -D my_development << EOF
delete from $1 ;
EOF
echo load $1
mysqldump -u person -ppass -c -n -t --skip-opt my_test $1 | mysql -u person -ppass -h localhost -D my_development

These two scripts should allow you to quickly change your DB, blow it away and re-run your initial migration, get your fixture data into the test DB as your work through the tests, and then slide the data back into development. Once you get beyond the early stages of development, you'll want to increase change control and encapsulate your changes in migrations, and this technique may become less useful. But while things are flying fast and loose in the early stages, it really helps to keep things loose, and test driven to have the test data be the data in the system. If you get tired of writing YAML, once your prototype is running, you can use your system to help create test data and dump it to fixtures using the ar_fixtures plugin.