Diagrams to Migrations using OmniGraffle

Once you go through the pain of setting up SQL Fairy (CPAN, yikes!), you can be designing your database visually with OmniGraffle in collaboration with other non-Railsy folks, and then generate your Ruby migration and stick it directly into your Rails project.

I'm in the process of embarking on a new Rails project with a moderate sized database. This project is based on an existing system, but with considerable redesign. I wanted to find a way to document the design so that other non-Railsy people can provide feedback on the design as it evolves, and do it in such a way that it would be natural to keep the documentation in sync with the Rails project.

Most DB tools are designed around an old-school method for database configuration management - the larger tools directly manage the database schema by pumping SQL through an ODBC connection. The smaller tools are focused on just visualizing an existing database. Neither of these options is well suited to developing in Rails. I'd really like to use the diagram as part of the creative process of developing the database rather than a post-hoc documentation step, and I don't want to short-circuit the Rails development process of managing the database through migrations.

After some searching I found graffle2sql.com - a site that takes database diagrams created with OmniGraffle and generates the SQL for the tables in the diagram. This is close, but what I really need is the Ruby for a migration, not SQL. I've been down this road enough to know that if the document requires a lot of tweaking to get it into the development process, it will quickly become stale and useless. A little more searching turned up SQL Fairy. This is a parser-based project that provides a wide variety of tools for manipulating SQL. This allowed me to set up a pipeline from an OmniGraffle DB diagram to a Ruby migration file using the following steps:

1. Make a DB diagram in OmniGraffle using the Graffle2SQL template
2. Convert it to SQL at graffle2sql.com
3. Use SQL Fairy to generate a YAML intermediate DB description
4. Use the template tool to rearrange the schema data into the general structure of a Ruby migration
5. Use string substitution to convert SQL type information into Ruby, yielding a Ruby migration file.

The last step is the only imperfect part of the process. The type information from SQL Fairy is somewhat limited, so complex types like decimal(x,y) don't translate properly. I'm not a Perl wizard (SQL Fairy is written in Perl) so there may be a better way to setup the template file to cover these cases.

In order to set this up, you need to install SQL Fairy and the template module. The template file I used is:

class InitialSchema < ActiveRecord::Migration
  # This file was auto-generated from SQL
  def self.up
[% FOREACH table IN schema.get_tables %]
    create_table "[% table.name %]" do |t|
  [% FOREACH field IN table.get_fields -%]
    t.column :[% field.name %],  :[% field.data_type %]  [% field.size %], :null => [% field.is_nullable %], :default => [% field.default_value %]
  [% END -%]
  end
[% END -%]
  end

  def self.down
[% FOREACH table IN schema.get_tables -%]
    drop_table "[% table.name %]"
[% END -%]
  end
end

and the script that drives the whole conversion process (OK, it's not Ruby, so shoot me) is:

#!/bin/csh
# syntax:  convert.csh file-root
# where the file to input is file-root.sql
# output will be in file-root.rb - the intermediate file file-root.yaml is removed

echo 'Generating YAML file . . .'
sqlt -f MySQL -t YAML $1.sql > $1.yaml
echo 'generating Ruby file . . .'
sqlt -f YAML -t TTSchema --template migration.tt $1.yaml > $1.txt
echo 'cleaning data type information . . .'
cat $1.txt | sed 's/:int  11/:integer/' | \
             sed 's/:varchar \([0-9]*\)/:string, :limit => \1/' | \
             sed 's/:char \([0-9]*\)/:string, :limit => \1/' | \
             sed 's/:tinytext  [0-9]*/:string/' | \
             sed 's/:timestamp  0/:timestamp/' | \
             sed 's/:date  0/:date/' | \
             sed 's/:float  ARRAY(0x1b06ac4)/:float/' | \
             sed 's/, :null => 1//' | \
             sed 's/, :default => $//' | \
             sed 's/, :null => 0/, :null => false/' | \
             sed 's/,  :string, :limit =>  255/,  :string/' | \
             sed -e '/, :id => false do |t|/N' -e 's/, :id => false do |t|\n      t.column :id,  :integer, :null => false/ do |t|/' > $1.rb
rm $1.yaml $1.txt
echo 'done.'

Once you go through the pain of setting up SQL Fairy (CPAN, yikes!), you can be designing your database visually in collaboration with other non-Railsy folks, and then generate your Ruby migration and stick it directly into your Rails project. This approach doesn't detect differences, so it's best suited for cases when you're working out the base schema by migrating back to VERSION=0, making changes to the migration, then migrating forward to VERSION=1 again. OmniGraffle is free for diagrams with 20 objects or less, so you can play around with the process to find out whether it works for you.