Ruby script that uses sequel to populate my new Rails database with data from my old (non-Rails) DB. Used a script instead of a standard Postgres copy to prevent gaps in auto-increment fields.

copy.rb

#!/usr/bin/env ruby

require 'sequel'
require 'pp'

# script to copy data from my old, non-Rails postgres DB to my new rails DB.
#
# The DB has users, which have a number of Lists.
# And Lists, which have a number of Items.
# 
# The script assumes that the Rails DB has been populated with users, but no
# lists or items have been created. The script will match users from the old db
# with users from the new db. Then it will iterate through each list (and each
# item in each list) and copy it over.
#
# I chose to write this over a straight DB import because I don't want the
# auto_increment fields to have gaps in the numbers.


# connection settings for both DBs
DB = Sequel.postgres('test', user: 'test')
RailsDB = Sequel.postgres('rlist-dev', user: 'rlist-dev')

# models/associations for my Rails DB
class User < Sequel::Model(RailsDB)
  one_to_many :lists
end
class List < Sequel::Model(RailsDB)
  one_to_many :items
end
class Item < Sequel::Model(RailsDB)
end

# select all users from original (non-Rails) DB
DB[:users].select(:id, :email).each do |user|

  # This associates my users from the original DB with the users in the Rails
  # DB via the 'email' attribute. Obviously, the emails must match
  ruser = User[email: user[:email]]

  # select each list in old for the given user
  DB[:lists].select(:id, :name, :user_id,
                    :created_at, :updated_at)
    .where(user_id: user[:id])
    .each do |list|

    # make a new List object for the Rails DB (based on the data in old)
    rlist = List.create(name: list[:name], created_at: list[:created_at],
                        updated_at: list[:updated_at])

    # select each item in the old list
    DB[:items].select(:id, :list_id, :created_at,
                      :updated_at, :content)
        .where(list_id: list[:id])
        .each do |item|

      # make a new Item object for RailsDB (based on data from old)
      ritem = Item.create(content: item[:content], created_at: item[:created_at],
                          updated_at: item[:updated_at])
      # add that new Item to the List
      rlist.add_item(ritem)
    end
    # add that new List to the User
    ruser.add_list( rlist )
  end

  # Save the user (and thus the user's Lists, and the List's items)
  ruser.save

end