A DSL for "migrating" content between databases with different schemas

Wed, 28 Jul 2010 01:21:59 -0700

There were several occasions when I had to migrate the contents of a database into another database having a different schema. This happened especially when dealing with legacy databases.

I usually write a script which "maps" fields from one database to another when dealing with this kind of situation. Recently, I thought it would be a better idea to create a DLS where one would specify the mappings in a simpler way. So I started working on a gem that does this. Here is how the gem can be used for migrating the contents of a database to another.

First, the super_migration gem needs to be installed:

  gem install super_migration

Create a new ruby file migrate.rb, where you would handle the migration rules and add the following content. The from_database call sets the details for connection to the source database and to_database, the details for the destination database. Since the gem uses ActiveRecord, they are the same as in the database.yml file inside a rails application.

  require 'super_migration' 
  include SM 

  SuperMigration.setup do |config| 
    config.from_database :database => "sm1", 
                       :adapter => "mysql", 
                       :host => "localhost", 
                       :username => "root", 
                       :password => "" 

    config.to_database :database => "sm2", 
                     :adapter => "mysql", 
                     :host => "localhost", 
                     :username => "root", 
                     :password => "" 
  end

Then using a simple DSL we can specify the field mappings as in the following example:

  SuperMigration.migrate do
    table :from => :books, :to => :livres do

      field :from => :author, :to => :autheur

      # apply a transformation to dob field
      field :from => :title,  :to => :titre do |title|
        Date.today.to_s + title
      end
    end
  end

Then the file needs to be ran and the script will take care of the migration:

  ruby migrate.rb

I plan to further enhance this gem so that it supports more options and transformations.

Cristi Prodan's blog

A DSL for "migrating" content between databases with different schemas