How can I switch database inside a Rails console on Heroku?

1
votes

I'm trying to migrate users from one system to another. Each system has its own database and different classes.

My plan is to connect to one database, read some info from one database via SQL commands:

ActiveRecord::Base.connection.execute(sql_command)

do something with the data and then write some results on the new database using normal models.

I plan on doing that inside Sidekiq job but I'm trying to do some testing using a Rails console on Heroku.

This should be pretty straightforward, but this proves ridiculously difficult.

When I launched a Rails console on Heroku. I'm connecting to DATABASE_URL, which is ok, but when I try to connect to the old database and execute a command, like this:

ActiveRecord::Base.establish_connection(adapter: "postgresql", encoding: "unicode", pool: 10, url: "postgres://...info...")
ActiveRecord::Base.connection.execute("select count(*) from users")

I end up with:

PG::ConnectionBad (could not connect to server: No such file or directory)
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

I can, however, connect to this old database by launching my rails console on heroku using DATABASE_URL as the env variable:

$ heroku run 'DATABASE_URL=postgres://...info... rails console' -a  console' -a app

BUT I don't know how to switch back to my new database so I can update things.

How does one switch databases at run time when using rails console on heroku?

2
ruby-on-railsherokuactiverecordsidekiq
Have you tried the answer fo @jvergeldedios? 1. Setting up multiple databases is possible with rails. Follow the active record instructions. 2. Be sure that you have attached the old database to your heroku app. It should have another tagged name as DATABASE_URL. Heroku does that automatically when you attach it. When attached your app is able to establish a connection. – Simon Franzen
@SimonFranzen Thanks, changed from 2 tier to 3 tier and specified the second database link here, but don't know how to run queries against the second database. Also, I'm unsure what you mean by "attached the old database" to the app. Do you mean specify it in database.yml? The second database is used by another app. – Jeremy
You can in heroku attach Resources like DBs from one app to another. Just means, that the database can be accessible in both apps or multiple apps on heroku. You can access the other DB with active record. You can define in your models which db it should use. Or you write raw SQL statements and here you can see how to switch dbs -> guides.rubyonrails.org/… – Simon Franzen

2 Answers

2
votes

Why try to runtime switch the database? Why not have both connected at the same time and specify at the model level which database they read/write from? Rails supports connecting multiple databases and specifying in individual models what database connection to use: https://guides.rubyonrails.org/active_record_multiple_databases.html

2
votes

The problem was that using url: didn't work and all parameters needed to be specified.

config = {"adapter"=>"postgresql", "encoding"=>"unicode", "pool"=>10, "username"=>"u...", "password"=>"p...", "port"=>5432, "database"=>"d...", "host"=>"ec2..."}

If you go for a 3tier database yml, you can use this:

config = ActiveRecord::Base.configurations["production"]["seconddb"]

Then, you can use establish_connection

ActiveRecord::Base.establish_connection(config)
ActiveRecord::Base.connection.execute("select count(*) from users")

Once I started specifying username, password, port, database and host, it all worked like a charm.

To work with both databases at the same time, a good way is to create a class

class OtherDB < ActiveRecord::Base
  establish_connection(ActiveRecord::Base.configurations["production"]["seconddb"])
end

Then you can call things that way

OtherDB.table_name = "table_name"
OtherDB.first

ref (Establish a connection to another database only in a block?)

And to run SQL commands:

OtherDB.connection.execute("select count(*) from users")