In this post I will cover the various components you’ll need to migrate multiple databases with Active Record without using Rails. I’ll also throw some template files up on github so you can start messing around with this as well.

To minimize confusion, be aware that whenever you see <bla> , you as the programmer need to specify this particular piece of information, based on your environment. It is also worth noting that I’m writing this code for Unix-based environments. If you want to do this in Windows, please enjoy, and let me know how I can update this article to include that work.

Initial Configuration

Start out by specifying your folder structure for the project. I personally like to adhere to the Rails layout and will be doing so for this example.

mkdir db
mkdir db/migrate
mkdir db/migrate/<name of first db>
mkdir db/migrate/<name of second db>
mkdir logs
mkdir config
touch config/database.yml
touch Rakefile
touch Gemfile

Gemfile

This file enables us to dictate our dependencies for a specific project and allows for an easy installation of these dependencies.

source 'https://rubygems.org'
gem 'logger'
gem 'activerecord', '= 4.1.4'

Once you’ve populated your Gemfile, run the following to get your dependencies installed:

bundle install

YAML

This is used by our Rakefile to determine how to connect to our database(s).

YAML can be a pain due to its whitespace sensitivity. If you’re having issues, grab the database.yml file off of github and roll with it.

Here’s some sample code for connecting to a couple of SQLite dbs:

firstdb:
  adapter: sqlite3
  database: <path to our first database>
  timeout: 5000

seconddb:
  adapter: sqlite3
  database: <path to our second database>
  timeout: 5000

Migrations

The migration files dictate the structure for each table in a database. They allow us to specify the name of the table, the name of each column in that table, and any attributes associated with each column. It also allows us to specify indexes if we choose to do so.

Let’s start by creating a migration file to represent a single table in each database:

touch db/migrate/<name of first db>/001_create_first_table.rb
touch db/migrate/<name of second db>/001_create_first_table.rb

It is important to prefix each file name with the format 00x, where x is the next number in a sequence from 1 to however many tables you have.

Open each file and specify your table’s structure. Here’s some example code from a school project:

require 'active_record'

# Migration to create the MapObjects table
class CreateMapobjects < ActiveRecord::Migration

  def change
    create_table :mapobjects do |t|
      t.column :object_name, :string, null: false
      t.column :location, :string, null: false
      t.column :ground_texture, :string, null: false
      t.column :wall_texture, :string, null: false
      t.column :direction, :string, null: false
      t.column :trap_type, :string
      t.belongs_to :map, null: false
    end
    add_index :mapobjects, [:object_name], :name => "index_mapobjects_on_object_name"
    add_index :mapobjects, [:location], :name => "index_mapobjects_on_location"
    add_index :mapobjects, [:ground_texture], :name => "index_mapobjects_on_ground_texture"
    add_index :mapobjects, [:wall_texture], :name => "index_mapobjects_on_wall_texture"
    add_index :mapobjects, [:direction], :name => "index_mapobjects_on_direction"
    add_index :mapobjects, [:trap_type], :name => "index_mapobjects_on_trap_type"
    add_index :mapobjects, [:map_id], :name => "index_mapobjects_on_map_id"
  end
end

I’ve included two separate migration files in github to serve as examples.

Rakefile

This is used for generating the tables for each database, as well as clearing those tables along with any associated data, which is handy for debugging. Also, if you’re writing your application in such a way that data needs to be inserted and deleted repeatedly, this will be incredibly useful for you.

Start out with your dependencies:

#!usr/bin/env ruby
require 'active_record'
require 'yaml'
require 'logger'

This enables us to specify which database we will be interacting with:

namespace :db do
  task :environment do
    # Take in specified database as an argument
    DB: ENV['db']
    MIGRATIONS_DIR: 'db/migrate/' + DB
  end

Utilize our YAML file to connect to the specified database:

task :configuration => :environment do
    @config: YAML.load_file('config/database.yml')[DB]
end

Establish the connection to the database and log any issues we encounter:

task :configure_connection => :configuration do
    ActiveRecord::Base.establish_connection @config
    ActiveRecord::Base.logger: Logger.new(File.open('logs/db.log', 'a'))
end

The last two components are conveniently commented in the Rakefile, and therefore should be fairly self-explanatory:

 desc 'Migrate the database (options: VERSION=x, VERBOSE=false, db=<name of database>).'
 task :migrate => :configure_connection do
   ActiveRecord::Migration.verbose: true
   ActiveRecord::Migrator.migrate MIGRATIONS_DIR, ENV['VERSION'] ? ENV['VERSION'].to_i : nil
 end

  desc 'Rolls the schema back to the previous version (specify steps w/ STEP=n).'
  task :rollback => :configure_connection do
    step: ENV['STEP'] ? ENV['STEP'].to_i : 1
    ActiveRecord::Migrator.rollback MIGRATIONS_DIR, step
  end
end

It’s good to keep in mind that we are only scratching the surface of what we can do in terms of Rake commands. Please check out the sites I’ve included below under Resources to get more information.

Running rake commands on the command line

To clear all tables and data out of both dbs:

rake db:migrate db='<db1 name>' VERSION=0 && rake db:migrate db='<db2 name>' VERSION=0

To repopulate the tables:

rake db:migrate db='<db1 name>' && rake db:migrate db='<db2 name>'

That should do it. Again, the templates for what I’ve discussed here are available here. Good luck!

Resources: