Rails: connect to multiple databases using sql query

Question

How do we connect two databases with a single sql query? I prefer using sql here since to improve the performance.

config/database.yml

example:
  adapter: postgresql
  encoding: unicode
  database: example_dev

example_report:
  adapter: postgresql
  encoding: unicode
  database: example_report_dev

Now in one of my model I would like to write a query to fetch data from both these databases

SELECT example_dev.*, example_report_dev.*
FROM example_dev.myTable AS firstdb
INNER JOIN example_report_dev.myTable AS seconddb
   ON firstdb.id = seconddb.id

Thank you

Can you say what you have tried? a example of what you want to do? — MZaragoza
– MZaragoza, Commented Jan 23, 2017 at 3:56
Two databases on different servers? If it's in the same server, you can reference them in the raw query (using same connection). Is it MySQL, postgres? — luiscrjr
– luiscrjr, Commented Jan 23, 2017 at 4:07
@mrlew Oh thats nice. Do we need to establish connection to example_report and then try out the raw query or can it be done directly ? and What if they are on different servers ? Is it not the same ? — SureshCS
– SureshCS, Commented Jan 23, 2017 at 4:21

luiscrjr · Accepted Answer · 2017-01-23 18:04:35Z

One approach is to use postgres's dblink.

From the docs:

dblink executes a query (usually a SELECT, but it can be any SQL statement that returns rows) in a remote database.

First, you'll need to enable dblink in your database with:

CREATE EXTENSION dblink;

Then, in your application, you can execute a raw query statement with ActiveRecord::Base.connection.execute.

I created this test setup here:

Database db1 contains a table tbl with field1 and field2 columns.
Database db2 contains a table tbl with field1 and field2 columns.

Both have 5 rows.

My database.yml:

development:
  adapter: postgresql
  encoding: unicode
  database: db1

From rails console:

ActiveRecord::Base.connection.execute("
   SELECT * FROM tbl
   UNION ALL 
   SELECT * FROM dblink('dbname=db2','SELECT * FROM tbl') AS tbl2(field1 varchar, field2 int);
").to_a

# [{"field1"=>"one", "field2"=>1}, {"field1"=>"two", "field2"=>2}, {"field1"=>"three", "field2"=>3}, {"field1"=>"four", "field2"=>4}, {"field1"=>"five", "field2"=>5}, {"field1"=>"one", "field2"=>1}, {"field1"=>"two", "field2"=>2}, {"field1"=>"three", "field2"=>3}, {"field1"=>"four", "field2"=>4}, {"field1"=>"five", "field2"=>5}]

It's just a possible approach. You can setup dblink connection string to point to a remote server (for instance: 'dbname=yourdb port=5432 host=yourhost user=youruser password=yourpwd)

Be advised that this is not the rails way. Raw query is not related to a model. I suggest you to choose this only for specific tasks, like running a report.

EDIT

If you want one query for each database, and don't want to link it to your model, you can use ActiveRecord::Base.establish_connection, like this:

conn1 = {
  adapter: 'postgresql',
  encoding: 'utf8',
  database: 'db1'
}

conn2 = {
  adapter: 'postgresql',
  encoding: 'utf8',
  database: 'db1'
  #, more config here - other host, for instance #
}

arr1 = ActiveRecord::Base.establish_connection(conn1).connection.execute("select * from tbl").to_a
# => [{"field1"=>"one", "field2"=>1}, {"field1"=>"two", "field2"=>2}, {"field1"=>"three", "field2"=>3}, {"field1"=>"four", "field2"=>4}, {"field1"=>"five", "field2"=>5}] => [{"field1"=>"one", "field2"=>1}, {"field1"=>"two", "field2"=>2}, {"field1"=>"three", "field2"=>3}, {"field1"=>"four", "field2"=>4}, {"field1"=>"five", "field2"=>5}]

arr2 = ActiveRecord::Base.establish_connection(conn2).connection.execute("select * from tbl").to_a
# => [{"field1"=>"one", "field2"=>1}, {"field1"=>"two", "field2"=>2}, {"field1"=>"three", "field2"=>3}, {"field1"=>"four", "field2"=>4}, {"field1"=>"five", "field2"=>5}] => [{"field1"=>"one", "field2"=>1}, {"field1"=>"two", "field2"=>2}, {"field1"=>"three", "field2"=>3}, {"field1"=>"four", "field2"=>4}, {"field1"=>"five", "field2"=>5}]

You'll get two arrays arr1 and arr2 with both data.

Is it possible with two queries and without dblink ? I mean one query from each databases .

Ho Man · Accepted Answer · 2017-01-23 06:39:18Z

0

You can try the octopus gem out: https://github.com/thiagopradi/octopus.

Octopus is a better way to do Database Sharding in ActiveRecord. Sharding allows multiple databases in the same rails application. While there are several projects that implement Sharding (e.g. DbCharmer, DataFabric, MultiDb), each project has its own limitations. The main goal of octopus project is to provide a better way of doing Database Sharding.

answered Jan 23, 2017 at 6:39

Ho Man

2,35512 silver badges16 bronze badges

Collectives™ on Stack Overflow

Rails: connect to multiple databases using sql query

2 Answers 2

EDIT

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

EDIT

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related