Rails - How to access tables from external Database

Question

I need to get some data from external db(Not the primary one). So I added a connection entry in database.yml.

external_reporting_table:
  adapter: mysql2
  encoding: utf8
  database: reporting_db
  host: localhost
  username: root
  password: password

Also I've created a class to address it, external_reporting_db.rb

class ExternalReportingDB < ActiveRecord::Base
  self.abstract_class = true
  establish_connection :external_reporting_table
end

I've this model I need to get the data from external db, custom_report.rb

class CustomReport < ExternalReportingDB
  def self.shop_data_collection_abstract(batch_selections)
    p "Here I need to get multiple data from external db's tables."
  end
end

What should I do to access a table from external db in custom_report.rb ?

Jimmy Baker · Accepted Answer · 2016-02-18 21:37:01Z

13

When I do this, I do it according to what ActiveRecord expects which is a single model class per table. For example, let's say my external db has a table called customers, then I would define a class called "ExternalCustomers" and set the establish_connection and the table name inside the class. Here's an example:

class ExternalCustomer < ActiveRecord::Base
  establish_connection :external_reporting_table
  table_name "customers"
end

Then you can use it as you would any other AR model:

ExternalCustomer.where(id: 123)

If you don't want to add new models for every table, you can query the external db through the connection. Example:

ExternalReportingDB.connection.execute("select * from whatever...")

edited Feb 18, 2016 at 21:37

answered Feb 18, 2016 at 21:11

Jimmy Baker

3,2551 gold badge27 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Albert Paul Over a year ago

Thanks, I know this method, I'm looking for any other way to handle external DB tables without creating additional models.

Mark Thomas Over a year ago

@AlbertPaul This is the Rails way. Why wouldn't you want to use ActiveRecord?

Collectives™ on Stack Overflow

Rails - How to access tables from external Database

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related