There are many reasons why you may want to connect to multiple databases, a quick context to ours. We recently took over a project that hadn’t launched yet and decided it would be best to refactor parts of the codebase and database structure – Gemma wrote a post about why this is important.
We did this for a few reasons, to simplify the structure, but mainly to make it easier to build new features and cut down development time. To make refactoring the database faster and easier, we agreed to not worry about the data that was currently on there – the tables were truncated. Months of substantial development and a lot more database structural changes later, the client asks us to transfer some data from old database to the new. Like most problems, there are few ways to come up with a solution.

Unfortunately, we have a deadline approaching, so we have to go with the fastest solution. The quickest we could think of is to write a script to directly transfer the data from the old backed up database to our new database – thankfully the database structures are very similar. Our rake task will connect to the old database get the data we want, store it in a variable and then connect to the new database and import the data.

Database setup

Before I touched code, I dusted off an old backup of the database before the refactored work. Created a new Postgres database with the data; for this example, we will call this “old_app_development”, and we will call out current one “app_development.”

When the rake task boots up, it is connected to the database specified in the config file. So I needed a way to create a new database connection, we can archive this using the establish connection method on ActiveRecord::ConnectionHandling class.

The rake task with the connection snippet looks like this

desc "Import old data in to new database"
task import_data: :environment  do |t, args|

  ActiveRecord::Base.establish_connection(
    adapter:  "postgresql",
    host:     "localhost",
    database: "old_app_development"
  )

  puts ActiveRecord::Base.connection.current_database
end

ActiveRecord::Base.connection.current_database to confirm which connection is currently connected.

Upon running the rake task bin/rake import_data, “old_app_development” shows up successfully.

Config file

Now we need to connect back to our “app_development” in order for the data to be transferred. However, I’m sure you can see a problem emerging. I’ll have to copy the contents of the database.yml. In an efforts to not repeat myself and duplicate the connection settings I can use the database config file to specify the connection.

ActiveRecord::Base.establish_connection(:development)

That looks a lot cleaner and reads much better, for that reason I’ll add the connection settings of “old_app_development” to the config and revise the rake task.

default: &default
  adapter: postgresql
  encoding: unicode

development:
  <<: *default
  database: app_development

old_development:
  <<: *default
  database: old_app_development

test:
  <<: *default
  database: app_test

production:
  <<: *default
  url: <%= ENV['DATABASE_URL'] %>

We can then revise the rake task using the new method to connect to both databases.

desc "Import old data in to new database"
task import_data: :environment  do |t, args|

  ActiveRecord::Base.establish_connection(:old_app_development")

  puts ActiveRecord::Base.connection.current_database

  ActiveRecord::Base.establish_connection(:app_development")

  puts ActiveRecord::Base.connection.current_database
end

 

Tip

A really quick tip – if you like me have removed some models and using active record is no longer an option, I suggest using straight up SQL and maybe have something like this…

old_development = ActiveRecord::Base.establish_connection(:old_development).connection

old_records = old_development.execute("SELECT * from records").to_a

You should then be able to loop through the ‘old_records’ as this is retained in memory and import them in the new database.

Happy Coding!

 

 

Photo by Jacob Miller on Unsplash