Can I use 2 DB connections in my app?

Hi there!

How can i use 2 database connections in my rails application ( if it’s
possible of course ): one for reading from DB and one for writing to DB?

Thanks,
Dmitry

Sure, try this on a model object (say called Planet):

class Planet < ActiveRecord::Base
establish_connection(:adapter => “mysql”, :host => “mydbserver.com”,
:database => “myDb” … )

end

where ‘…’ are the other paramters to use in the connection. This
will result in the Planet model using the db connection defined above.

5MileRadius wrote:

Sure, try this on a model object (say called Planet):

class Planet < ActiveRecord::Base
establish_connection(:adapter => “mysql”, :host => “mydbserver.com”,
:database => “myDb” … )

end

where ‘…’ are the other paramters to use in the connection. This
will result in the Planet model using the db connection defined above.

But how can I differentiate between a reader and a writer database? It
seems to be rails limitation here…

On 9/21/06, Dmitry H. [email protected] wrote:

where ‘…’ are the other paramters to use in the connection. This
will result in the Planet model using the db connection defined above.

But how can I differentiate between a reader and a writer database? It
seems to be rails limitation here…

Something along the lines of pgpool
http://pgpool.projects.postgresql.org/ could be appropriate for a
master/slave setup.

Isak

these might be of help as well:
http://blogs.pragprog.com/cgi-bin/pragdave.cgi/Tech/Ruby/Connections.rdoc
http://wiki.rubyonrails.com/rails/pages/HowtoUseMultipleDatabases

ed

On 9/21/06, Isak H. [email protected] wrote:

master/slave setup.


Ed Hickey
Developer
Litmus Media
816-533-0409
[email protected]
A Member of Think Partnership, Inc
www.ThinkPartnership.com
Amex ticker symbol: THK

Is there a way to maintain two open connections simultaneously using
ActiveRecord::Base?

for example, in a generic script with ‘dbi’ or something, I could
establish two connections as such:

class Something
def test
dbh1 = DBI.connect(“dbi:Mysql:test:system1”, “user”, “pass”)
dbh2 = DBI.connect(“dbi:Oracle:test:system2”, “user”,
“pass”)
accounts = {
“system1” => dbh1.distinct(“users from users”),
“system2” => dbh2.distinct(“usernames from user_acc”)
}
dbh1.disconnect
dbh2.disconnect
return accounts
end
end

This is bs code but, can two activerecord::base connections be
established simultaneously like this?

On Thu, Sep 21, 2006 at 09:54:19PM +0200, Isak H. wrote:
[…]
} Something along the lines of pgpool
} http://pgpool.projects.postgresql.org/ could be appropriate for a
} master/slave setup.

I like PostgreSQL and would like to use it, but I have to do my due
diligence in looking for similar solutions on top of our current RDBMS
(MySQL) and, potentially, any commercial databases.

The pgpool solution (or, rather, pgpool-II) looks great in that both
read/write partitioning and vertical (row) partitioning is completely
transparent to the application layer. Does anyone know of anything like
that, commercial or otherwise, exist for MySQL or the various commercial
RDBMSs?

} Isak
–Greg

On 9/22/06, x1 [email protected] wrote:

Is there a way to maintain two open connections simultaneously using
ActiveRecord::Base?

There are no issues at all with establishing as many connections as you
wish. This issue lies more with linking them to a model more then making
the
connection.

What is below is an example - stolen blatantly from earlier in the
thread…

class Planet < ActiveRecord::Base
establish_connection(:adapter => “mysql”, :host => “mydbserver.com”,
:database => “myDb” … )
end

To do what you want below - I might be tempted to have a base model for
users and then create at least one subclass that had look like

class DBxUser < User
establish_connection…(to DBx)
end

Then from your controller create both a User and a DBxUser and then make
you
concatenated array by calling into both of them. I’m assuming that at
least
one of the two database connections is the standard connection you use
for
the rest of your connection (i.e. the one listed under
config/database.yml).

John W Higgins
[email protected]

this has been discussed numerous times on the list and is documented
here as
well:
http://wiki.rubyonrails.org/rails/pages/HowtoUseMultipleDatabases
http://wiki.rubyonrails.org/rails/pages/HowtoSwitchBetweenMultipleDevelopmentDatabases
http://wiki.rubyonrails.org/rails/pages/HowtoUseMultipleDatabasesOnePerTable

ed

On 9/22/06, John H. [email protected] wrote:

connection.
users and then create at least one subclass that had look like

John W Higgins
[email protected]


Ed Hickey
Developer
Litmus Media
816-533-0409
[email protected]
A Member of Think Partnership, Inc
www.ThinkPartnership.com
Amex ticker symbol: THK

Anyway, all these methods seem to be a little tricky, like workarounds I
think.
Maybe something like mysql pool could be more ‘correct’ solution

Dmitry H. wrote:

Anyway, all these methods seem to be a little tricky, like workarounds I
think.
Maybe something like mysql pool could be more ‘correct’ solution

I don’t think so … I hope not ! I don’t really like to see a “pool”
solution like the pg-one on my DB (it’s neither PGSql, nor MySQL).

But all the answers given are right. And just before saying Rails lake
something, ask yourself about what you really want to do and how to do
it the right way.

If you want a read/write access restriction in Rails, so you have the
answer : 2 connections and in one abstract connection you undefine all
the write methods.

If you want read/write access on database basis, then choose a database
that could deal with this, or write an Adapter with those
functionnalities.

Rails-like systems are complex because of many layers. When you have a
problem, try to find the good layer to deal with to solve this problem.
It’s not already easy to find it at first :wink:

Tony

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs