Separate Read and Write Database

Rails
1

Hello,

I need to access an mirrored database with one read and one write
cluster. Is there a way to tell a model to use two connections? So that
all updates etc go to the write database and all read from the read one?

This is a fixed set up I can not use MySQL 5.x stuff and additionally i
get a token on write, which can be checked on read (and wait for
replication) if new data is needed.

I saw an open ticket on http://dev.rubyonrails.org/ticket/2041 but it
seems to be dead.

For me it would be enough to have the possibility to change the
connection of a model on runtime (e.g. block)

Model.use_read do

do some stuff

x= Model.find()
x.test
end

or

token = Model.use_write do
x = Model.new
x.save
end

Any idea how this can be achieved?

Is it possible to use
ActiveRecord::Base.establish_connection(:adapter => “mysql” ,
:host => “localhost” , :database => “railsdb” )
more dynamically?

Maybe creating a inherited model would do it?:

class Order < ActiveRecord::Base
end

ActiveRecord::Base.establish_connection(:adapter => “mysql” ,
:host => “localhost” , :database => “railsdb_read” )

class ReadOrder < Order
end

ActiveRecord::Base.establish_connection(:adapter => “mysql” ,
:host => “localhost” , :database => “railsdb_write” )

class WriteOrder < Order
end

Any other clues?

2

I just ran into a similar requirement. Here’s how I solved it:

#First extend ActiveRecord::Base to allow us to override connections
module ActiveRecord
class Base

# Sets the active connection to conn and runs the given block.

Active
# connection is reset to it’s previous value once the block
finishes.
def self.using_connection(conn)
old_connection = use_connection(conn)
ret = yield if block_given?
use_connection(old_connection)
ret
end

# Sets the active connection to conn, returns the previously active

connection
def self.use_connection(conn)
old_active_connection = active_connections[active_connection_name]
active_connections[active_connection_name] = conn
old_active_connection
end

end
end

class ModelBaseRead < ActiveRecord::Base
self.abstract_class = true
establish_connection “whatever_read_#{RAILS_ENV}” if defined?
RAILS_ENV

@@read_connection = self.connection
def self.read_connection
@@read_connection
end

def self.use_read_connection
use_connection(read_connection())
end

def self.using_read_connection(&blk)
using_connection(read_connection, &blk)
end
end

class ModelBase < ModelBaseRead
self.abstract_class = true
establish_connection “whatever_#{RAILS_ENV}” if defined? RAILS_ENV

@@write_connection = self.connection
def self.write_connection
@@write_connection
end

def self.use_write_connection
use_connection(write_connection())
end

def self.using_write_connection(&blk)
using_connection(write_connection, &blk)
end
end

Then define some model as

class SomeModel < ModelBase

some stuff

end

Then you can do nifty things like this:

ModelBase.using_read_connection() do
s = SomeModel.find(:all)
end

or if you like…

ModelBase.use_read_connection()
s = SomeModel.find(:all)
ModelBase.use_write_connection()

Note that it defaults to the write connection, but you could change that
if you wanted to.

You’ll obviously need to tweak the db config strings as you need.

I know there is a better solution if you make a lot of changes to
connection_specification.rb in activerecord, but I’m a chicken and took
the path of least resistance.

My rails-foo is not great. I would be very interested to what I true
rails-ninja would do to solve this.

cheers,

benv