How to use multiple databases for one rails 3.1 app in Heroku?

Question

My Rails 3.1 application connects to 2 databases, one is the default, the other is an Amazon RDS MYSQL instance.

The current database.yml contains two production database connections. The models that need to pull from the second database simply use

establish_connection "production_on_amazon"

Unfortunately Heroku overwrites your database.yml, and only seems to inlcude one database connection. Does anyone know how I can add or configure my second?

Running "heroku config" I can see there are 2 DB's listed but cant seem to configure to connect to both. Perhaps somehow set my default to the SHARED_DATABASE_URL db on Heroku and set the alternate to the DATABASE_URL which points to Amazon...

WattsInABox · Accepted Answer · 2013-01-28 14:26:29Z

7

Working off of the previous responses, but incorporating some Rails 3 advantages with the configuration and simplifying the parsing...

# config/application.rb
module MyApp
  class Application < Rails::Application
    ... other configs

    config.secondary_database_url = ENV['SECONDARY_DB_URL']
  end
end

We may want to override this in development / test

# config/environments/development.rb

module MyApp
  class Application < Rails::Application
    ... other configs

    config.secondary_database_url = 'SOME_CONNECTION_STRING'
  end
end

Now to setup the class we'll have our models inherit from...

# lib/active_record/secondary.rb 
module ActiveRecord
  class Secondary < ActiveRecord::Base
    self.abstract_class = true

    # prior to AR 3.2.1
    url = URI.parse( MyApp::Application.config.secondary_database_url )
    establish_connection(
      :adapter  => 'mysql',
      :host     => url.host,
      :username => url.userinfo.split(':')[0],
      :password => url.userinfo.split(':')[1],
      :database => url.path[1..-1],
      :port     => url.port || 3306
    )

    # as of AR 3.2.1
    establish_connection(MyApp::Application.config.secondary_database_url)

  end

  class SecondaryMigration < ActiveRecord::Migration
    def connection
      ActiveRecord::Secondary.connection 
    end
  end

end

edited Jan 28, 2013 at 14:26

answered Apr 17, 2012 at 16:21

WattsInABox

4,6362 gold badges37 silver badges43 bronze badges

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

3 Comments

Ajedi32 Over a year ago

Note: Since 3.2.1, you don't have to parse the database url at all. You can just pass it directly to establish_connection. apidock.com/rails/v3.2.1/ActiveRecord/Base/establish_connection/…

Ajedi32 Over a year ago

Fyi, making ActiveRecord::Secondary inherit from ActiveRecord::Base can cause problems: ActiveRecord::StatementInvalid: Could not find table 'secondaries'

WattsInABox Over a year ago

@Ajedi32 not if you set abstract class to true which I forgot to include

Neil Middleton · Accepted Answer · 2011-10-12 15:42:25Z

2

Heroku will always connect your app to the production DB that they create for you. If you want to make an additional connection you'll need to do this in your code manually, and create a ENV var that the code can use as a connection string.

Anything in the production segment of database.yml is binned by Heroku and replaced.

answered Oct 12, 2011 at 15:42

Neil Middleton

22.3k19 gold badges84 silver badges137 bronze badges

1 Comment

genkilabs Over a year ago

For those wondering how they might do this, the way I settled on was to extend ActiveRecord::Base in a module in the lib folder, and then extend my new class in my models, that way I could shard across several kinds of DB is a somewhat DRY way.

genkilabs · Accepted Answer · 2012-02-16 23:14:15Z

2

Regarding Neil's answer, here is a way to do it. Not an out-of-box solution, but might give you an idea... /lib/active_record_extensions.rb

module ActiveRecordExtensions
  class Shard < ActiveRecord::Base
    #need to switch to the shard database connection from heroku config 
    primary_database_url = ENV['PRIMARY_DATABASE_URL']

    if(!primary_database_url.nil?)
      parsed_connection_string = primary_database_url.split("://")
      adapter = parsed_connection_string[0]
      parsed_connection_string = parsed_connection_string[1].split(":")
      username = parsed_connection_string[0]
      parsed_connection_string = parsed_connection_string[1].split("@")
      password = parsed_connection_string[0]
      parsed_connection_string = parsed_connection_string[1].split("/")  
      host = parsed_connection_string[0]
      database = parsed_connection_string[1]

      establish_connection(
        :adapter  => adapter,
        :host     => host,
        :username => username,
        :password => password,
        :database => database,
        :port     => 3306,
        :pool     => 5,
        :timeout  => 5000
      )
    else
      self.establish_connection "shard_#{Rails.env}"
    end
  end

  class ShardMigration < ActiveRecord::Migration
    def connection
      ActiveRecord::Shard.connection 
    end
  end
end

So your model should just extend ActiveRecord::Shard instead of Base

answered Feb 16, 2012 at 23:14

genkilabs

2,98631 silver badges37 bronze badges

1 Comment

davidgoli Over a year ago

Instead of doing all this manual parsing, you could just use Ruby's URI, eg: parsed_uri = URI(ENV['PRIMARY_DATABASE_URL'])

Collectives™ on Stack Overflow

How to use multiple databases for one rails 3.1 app in Heroku?

3 Answers 3

3 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related