2

I have two databases (a legacy MySQL one and a new PostgreSQL one), and the schema for the new one was redesigned. As a result, I can't just dump the old database to YAML and load it into the the new one, since columns are named different things and may need to be manipulated. Is there an elegant way to do this?

Joshua Grosso Reinstate CMs
  • 2,086
  • 2
  • 15
  • 32
  • csv file extract is elegant to *me*. Happens in iterations of [ETL](https://en.wikipedia.org/wiki/Extract,_transform,_load) – Drew Nov 13 '15 at 16:43
  • 1
    @Drew ETL is the word I've been trying to find for the last couple of days! Thank you so much for mentioning that! I remembered it was something like that, but didn't remember the exact terminology (Google searches should be a breeze now). I'm going to leave this question open just in case someone has a nice answer, but you just sent me off on the right path. Thanks! – Joshua Grosso Reinstate CMs Nov 13 '15 at 16:44
  • can I get 10 points if I post that ? j/k – Drew Nov 13 '15 at 16:45
  • if I were you, I would manage the tags better, and get `postgresql` tag in there. Because if I could only have 3, it would be mysql postgresql and migration. ruby is irrelevant, unless you want it to happen 1/1000th as fast – Drew Nov 13 '15 at 16:47
  • @Drew Well, I'm wondering if there's a Rails Way to do this.... – Joshua Grosso Reinstate CMs Nov 13 '15 at 16:52
  • consider PHP ruby phpmyadmin any of that, C#, java, doesnt matter, let's call that Solution A. Here is [solution B](http://stackoverflow.com/a/32702768). Your job is to find the postgresql guru's to make a variant of solution B for you – Drew Nov 13 '15 at 16:55

1 Answers1

4

It's actually fairly easy. First you need to define the connection to your MySQL database in your database.yml. Let's call it legacy:

development:
  adapter: postgresql
  .....

test:
  adapter: postgresql
  .....

legacy:
  adapter: mysql2
  encoding: utf8
  database: your_old_mysql_db
  username: root
  password: 
  host: localhost
  port: 3306

You will need the mysql2 gem in your gemfile, alongside the pg gem!

Now just create models for each of the tables you want to connect to:

Here's one called LegacyUser, which will let you get the old users out of your MySQL database:

# app/models/legacy_user.rb 
class LegacyUser < ActiveRecord::Base
  establish_connection :legacy
  self.table_name = "whatever_your_my_sql_user_table_name_is"
end

Now, in a Rake task you can pull data out of the MySQL table and stick it into your Postgres table like so:

# lib/tasks/import.rake
namespace :import do

  desc "Import Users"
  task users: :environment do

    puts ""
    puts "Importing Legacy Users:"

    LegacyUser.find_each do |lu|
      print "#{lu.id} - #{lu.first_name}"
      u = User.new
      u.email = lu.email
      u.first_name = lu.first_name
      u.last_name = lu.last_name
      if u.save
        puts "... saved"
      else
        puts "... bad: #{u.errors.full_messages.join(',')}"
      end
    end
  end
end

Now you can just run:

rake import:users
rlarcombe
  • 2,958
  • 1
  • 17
  • 22