Creating database test fixtures and the rails export fixtures plugin

Being able to quickly and easily create test fixtures for your database is important yet it’s not always easy. There are basically 3 approaches I’ve seen used:

1. Use a MySQL or PostgreSQL dump that gets imported before the tests are run. I’ve never seen this approach used with Oracle, most likely because it’s such a PITA to export to text (or at least it used to be)
2. Just create the schema before the tests and then populate the test data in code
3. Use a database test fixture tool such as the XML based DBUnit for Java or Ruby’s YAML based fixtures

I have a strong preference for database test fixture tools because they work across databases (often a requirement at ISV’s) and generally offers tools to easily export fixtures in addition to importing them. However, the ability to export fixtures from your database to a fixture file is sometimes overlooked but it’s vitally important as populating those fixtures gets really time consuming if you need to type it all in. The most common use case I run into is when I pull rows of data from production into my local database and once I’m happy with the sample data want to export it into a fixture file.

At GreatSchools we’re a Java shop and we use DBUnit which does a fantastic job of exporting and importing database test fixtures. Now that I’m using Ruby on Rails on outside projects though I was starting to burn out having to manually write the fixtures.yml files by hand because Rails doesn’t offer an export facility out of the box. In this particular case I was parsing NOAA weather forecasts which I do using script/runner via cron to retrieve over HTTP and populate the database with. They can be fairly lengthy and I needed dozens of records for the tests and the thought of having to write the YAML fixtures file had me scared!

Sure enough after a little web searching I found Chris McGrath and Nathaniel Brown’s export fixtures plugin and I’m a happy camper again!

Here’s how you install it:

Then to use it I populate the tables in question with sample data and then run:

It’s a little shy on giving you feedback but once it completes you’ll find table1.yml and table2.yml in the test/fixtures directory with all of the data from your tables in it. They also have other options to export data from another database such as production or export data selectively based on a query.