Dumping database to YAML fixtures

I finally got fed up with the bugs in Rails 1.0 handling of mysql connections and have decided to move to postgres. I have talked about the move and even migrated my DDL to the database agnostic schema language. The one thing I had not yet thought about was how to move my data.

After doing a little bit of asking and searching I decided to just dump my data out the databse to fixtures and then reload these fixtures. This is surprisingly simple using the rake task.

desc Dump a database to yaml fixtures. 
task :dump\_fixtures => :environment do
path = ENV\[FIXTURE\_DIR\] || \#{RAILS\_ROOT}/data”

select\_values(show tables).each do |table\_name|
i = 0
File.open(\#{path}/\#{table\_name}.yml”, ‘wb’) do |file|
file.write ActiveRecord::Base.connection.
select\_all(SELECT \* FROM \#{table\_name}”).inject({}) { |hash, record|
hash\[\#{table\_name}\_\#{i += 1}”\] = record

desc Reset Database data to that in fixtures that were dumped
task :load\_dumped\_fixtures => :environment do
require active\_record/fixtures
path = ENV\[FIXTURE\_DIR\] || \#{RAILS\_ROOT}/data”
Dir.glob(\#{path}/\*.{yml}”).each do |fixture\_file|
Fixtures.create\_fixtures(path, File.basename(fixture\_file, .\*))

This is Mysql specific due to the use of select\_values(‘show tables’) but apparently sqlite usues select\_values(‘.table’) and postgres uses the following.

SELECT c.relname
FROM pg\_class c
LEFT JOIN pg\_roles r ON r.oid = c.relowner
LEFT JOIN pg\_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN (r,‘’)
AND n.nspname IN (myappschema, public)
AND pg\_table\_is\_visible(c.oid)

This worked like a charm except when my data contained embedded ERB directives because when rails loads the fixtures it attempts to evaluate the fixture as an ERB script. In this scenario I just needed to nip into the read\_fixture\_files method in $RUBY\_HOME\\activerecord-1.13.2\\lib\\active\_record\\fixtures.rb and comment out the erb rendering while I imported my data.

Update: The code for this can be found is available in dump_fixtures.rake