gensym.org

When working with a software development team, you typically want each developer to have their own database schema. That way, each developer is free to modify their schema as part of their development without getting in the way of other developers. When doing this, however, it's crucial to have the ability to trivially do the following:

1. Rebuild the database from scratch
2. Distribute changes to the schema

(1) is important because it's possible during development to put your database into a bad state that's difficult to back out of. If you can easily rebuild the database, you don't need to waste time figuring out how to back out changes that you've decided against.

(2) is important to keep the developers in synch. If developers need to rebuild their database to incorporate every schema change, they will do so less often than if they can run a script that brings their database up to date with the lasted schema in version control. And more importantly, by doing (2) as part of your development process, you have a way to test the changes that will ultimately be applied to your production database.

I don't if these are obvious, but the majority of projects I've seen do not have these processes in place. While I would place these processes among those that the best teams are following (such as automated deployments, continuous integration, test-driven development, etc.) they are not talked about as much among programmers.

The main thing that drew me to Ruby on Rails was that it incorporates so many good development practices - not only making them possible to use, but encouraging you to use them. Databases are no exception. Rails's method of defining database schemas in terms of migrations is the best way to accomplish practice (2) that I've seen. It also makes rebuilding the schema from scratch very easy, going a long way to accomplish practice (1).

However, rebuilding the database from migrations gives you an empty database. For your test database, this is what you probably want (using fixtures to populate the tables). However, you probably do not want an empty development database. Among other things, user interface problems (both bugs and usability issues) that are obvious when lots of data is on the screen may remain hidden when the only data present is what the developer has created in the process of trying out their own changes.

If you have a demo environment, used either by your sales force or as part of communicating with your customers, it's helpful for your development database to be populated with the same data as your demo database. Since your demo environment, by definition, is used to show off features of your product, it can also give you good data to work with during development. And by working with the data that will be used to demo your product, you may be able to avoid some nasty surprises during demo.

One way to copy data from your demo environment to your development enviroment is to just copy the database, DDL and all. However, this approach does not play well with migrations. Fortunately, if you're working with Rails, you have Ruby, Rails, and Rake to help you out. I use the following code to accomplish this task. Just copy it into a Rake file in your tasks directory and create a directory in the top level of your project called "gold" and another within that called "data". Then, running "rake gold:export" will pull all the data from within your database into a bunch of YAML files, one for each table. These files are structured like test fixtures. You can import the data using "rake gold:import". This approach has the advantage that your development/demo data can easily be stored in source control.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

  namespace :gold do

    task :export do
      require RAILS_ROOT + '/config/environment'
      conn = ActiveRecord::Base.connection
      tables = conn.tables.reject {|i| i == 'schema_info'}
      tables.each do |table|
        filename = RAILS_ROOT + '/gold/data/' + table.pluralize + '.yml'
        open(filename, 'w') do |f|
          rows = conn.select_all("SELECT * FROM #{table}")
          rows.each do |row|
            f.puts("gold_#{row["id"]}:")
            row.keys.each do |key|
              f.puts "  #{key}: #{row[key]}"
            end
          end
        end
      end

    end

    task :import do
      require RAILS_ROOT + '/config/environment'
      require 'test_help'
      Dir.glob(RAILS_ROOT + '/gold/data/*.yml').each do |file|
        Fixtures.create_fixtures(
          RAILS_ROOT + '/gold/data', 
          File.basename(file, '.yml'))
      end
    end

  end