Copy between databases in Rails console

Question

I have three databases:

A. mynewapp_psql (Postgres)
B. old_products_psql (Postgres)
C. old_blogposts_mysql (Mysql)

Each is defined in database.yml

I'm using A (mynewapp_psql) as the database for my new app. In this app I want to be able to copy selected material from my two older databases.

My attempt (updated according to response)

old_db = ActiveRecord::Base.establish_connection(:database => 'old_blogposts_mysql'... etc)
posts = old_db.connection.execute("select * from posts'")
posts.each do |p|
  NewPost.create(:name => p.name.downcase) #NewPost should add Post in A. (mynewapp_psql)
end

It should take each product from my old database and create a new equivalent in the new database.

I really prefer doing it through the console and I can't copy the database straight over since I need to filter and alter the data.

score 5 · Accepted Answer · answered May 01 '14 at 13:23

So your case is one of Lost connection. How about Establishing a new connection to your current DB so that your code becomes like this

old_db = ActiveRecord::Base.establish_connection(:database => 'old_blogposts_mysql'... etc)
posts = old_db.connection.execute("select * from posts'")
new_db = ActiveRecord::Base.establish_connection(:database => 'mynewapp_psql'... etc)
posts.each do |p|
  NewPost.create(:name => p.name.downcase) #NewPost should add Post in A. (mynewapp_psql)
end

score 2 · Answer 2 · answered Apr 30 '14 at 21:05

2

The console is not the only way to dynamically access your databases. Think about a custom ruby task for your purposes using ActiveRecord connections for each database.

require 'pg'
require 'active_record'

ActiveRecord::Base.establish_connection(
  adapter:  'postgresql', # or 'mysql2' or 'sqlite3'
  host:     'localhost',
  database: 'canvas_test',
  username: 'joe',
  password: 'shiitake'
)

# EXEC raw SQL
ActiveRecord::Base.connection.execute("SELECT * FROM posts;")

class Post < ActiveRecord::Base
end

# READ all posts
puts Post.count
=> #<Post:0xb96582cc>
=> ...

# CREATE one new post
my_post = Post.new

answered Apr 30 '14 at 21:05

wurde

2,487
2
20
39

Thanks, that brought me a little bit closer. But won't this just connect to the other database (while leave the connection to the original one)? I mean, the last row "my_post = Post.new" will that not just create a new Post in the canvas_test database (rather than in the original mynewapp_psql database I am creating)? – Christoffer May 01 '14 at 05:53
result = ActiveRecord::Base.connection.execute("SELECT * FROM posts;") - I guess this was really the only thing I needed. Then I could use result (e.g. result.first) to get the data. Thanks again! – Christoffer May 01 '14 at 06:35
Sorry for the multiple comments. I realized this does not really solve the problem anyway because if I establish a connection to my mysql-database I can no longer access my original database in the console (and I really want to use the console). – Christoffer May 01 '14 at 06:40
Your statement is false, you can open multiple connections. – wurde May 02 '14 at 15:28
Ah, @the Areba had to make it explicit for ya. Oh well, glad to help. – wurde May 02 '14 at 15:30

Copy between databases in Rails console

2 Answers2