Simplify SQL Migration Scripts with SQrbL

by Sebastien Auvray on Aug 24, 2009 |

Managing SQL-based script can become a nightmare with time. Rails solved this with ActiveRecord Migration. Sam Livingston-Gray wrote a small standalone Ruby tool to generate hierarchical migration script. Based on the fact that SQL scripts can become very verbose and duplication-prone, Sam started SQrbL which is a mix of SQL and Ruby.

You'll be writing such script:

include Sqrbl

Sqrbl.migration do
    @output_directory='/path/to/generated/sql'

    group "Widgets" do
      step "Create widgets" do
        up do
       
          helpers do
            def widget_import_note
                '"Imported from old_widgets"'
            end
          end
       
          action "Migrate old_widgets" do
            <<-SQL
              #{
                insert_into("new_widgets", {
                  :name     => 'widget_name',
                  :part_num => 'CONCAT("X_", part_number)',
                  :note     => widget_import_note,
                })
              }
              FROM old_widgets
            SQL
          end
        end

        down do
          action "Drop imported organizational contacts" do
            'DELETE FROM new_widgets WHERE note LIKE "Imported from old_widgets"'
          end
        end
      end
    end   
   
    group 'Second Group' do
      step 'Step one' do
        up { write '-- Step one up' }
        down { write '-- Step one down' }
      end
      step 'Step two' do
        up { write '-- Step two up' }
        down { write '-- Step two down'}
      end
    end       
       
end

And SQrbL will produce the following files:

/path/to/generated/sql/up/1_widgets/1_create_widgets.sql
/path/to/generated/sql/down/1_widgets/1_create_widgets.sql
/path/to/generated/sql/up/2_second_group/1_step_one.sql
/path/to/generated/sql/down/2_second_group/1_step_one.sql
/path/to/generated/sql/up/2_second_group/2_step_two.sql
/path/to/generated/sql/down/2_second_group/2_step_two.sql
/path/to/generated/sql/all_up.sql
/path/to/generated/sql/all_down.sql

For example all_up.sql is filled up with the SQL queries:

-- Migrate old_widgets

INSERT INTO new_widgets (
    name,
    part_num,
    note
)
SELECT
    widget_name AS name,
    CONCAT("X_", part_number) AS part_num,
    "Imported from old_widgets" AS note
FROM old_widgets

-- Step one up

-- Step two up

For the moment SQrbL only insert_into to simplify INSERT writing statements.

While the use of SQrbL might not look the best solution for people already using ActiveRecord Migration, it still might satisfy people looking for a quick simple standalone tool. For the moment SQrbL is in its 0.1.3 version and is lacking a proper SQL DSL.