Dr Nic

Magic Multi-Connections: A “facility in Rails to talk to more than one database at a time”

At this point in time there’s no facility in Rails to talk to more than one database at a time.

Alex Payne

I possibly have such a facility. Perhaps it will help, and I will get some DHH-love and perhaps a free Twitter account for my troubles. Or perhaps a t-shirt.

As a bonus, the solution even includes decent Ruby-fu syntax. So, if you’re just here for the view:

class PeopleController < ApplicationController
  def index
    @people = conn::Person.find(:all)
  end
end

That code just there solves all our problems. It will invoke Person.find(:all) on a random database connection to (assumably) a clone database. Awesomeness I think. I hope it helps Twitter and all the Twit-sers (or whatever you call a user of Twitter).

This solution comes from the magic_multi_connections gem.

What is going on here?

I think a tutorial is the best way to demonstrate what is happening here. So, let's create a rails app and mix in the magic_multi_connections gem.

First, get the gem. Second, create a rails app:

$ sudo gem install magic_multi_connections
$ rails multi -d sqlite3

Now edit the config/database.yml file to create some more databases:

development:
  adapter: sqlite3
  database: db/development.sqlite3
  timeout: 5000

development_clone1:
  adapter: sqlite3
  database: db/development_clone1.sqlite3
  timeout: 5000

development_clone2:
  adapter: sqlite3
  database: db/development_clone2.sqlite3
  timeout: 5000

But please pretend these are uber-MySQL clusters or whatever.

Think of :development as the read-write connection, and the :development_cloneN connections are for read-only access.

At the bottom of your environment.rb file, add the following:

require 'magic_multi_connections'
connection_names = ActiveRecord::Base.configurations.keys.select do |name|
  name =~ /^#{ENV['RAILS_ENV']}_clone/
end
@@connection_pool = connection_names.map do |connection_name|
  Object.class_eval <<-EOS
    module #{connection_name.camelize}
      establish_connection :#{connection_name}
    end
  EOS
  connection_name.camelize.constantize
end

Let's test what this gives us in the console:

$ ruby script/console
>> @@connection_pool
=> [DevelopmentClone1, DevelopmentClone2]
>> DevelopmentClone1.class
=> Module
>> DevelopmentClone1.connection_spec
=> :development_clone1

Our new modules will act as connections. One module per connection. The code above gives them names to match the connection names, but its really irrelevant what they are called, thanks to the mysterious conn method.

So, go create some models and some data. I'll use Person as the class here.

To setup the schemas in our clone databases, we'll use rake db:migrate. To do this:

$ cp config/environments/development.rb config/environments/development_clone1.rb
$ cp config/environments/development.rb config/environments/development_clone2.rb
$ rake db:migrate RAILS_ENV=development
$ rake db:migrate RAILS_ENV=development_clone1
$ rake db:migrate RAILS_ENV=development_clone2

To differentiate the databases in our example, assume there are two Person records in the :development database, and none in the two clones. Of course, in real-life, they are clones. You'd have a replicate mechanism in there somewhere.

Now, we can access our normal Rails modules through our connection modules. Magically of course.

>> ActiveRecord::Base.active_connections.keys
=> []
>> Person.count
=> 2
>> ActiveRecord::Base.active_connections.keys
=> ["ActiveRecord::Base"]
>> DevelopmentClone1::Person.count
=> 0
>> ActiveRecord::Base.active_connections.keys
=> ["ActiveRecord::Base", "DevelopmentClone1::Person"]

Wowzers. Person and DevelopmentClone1::Person classes?

But note - Person.count => 2 and DevelopmentClone1::Person.count => 0 - they are accessing different databases. The same class definition Person is being used for multiple database connections. We never defined more Person classes. Just the standard default one in app/models/person.rb.

The active_connections result shows that DevelopmentClone1::Person has its own connection. Yet you never had to manually call DevelopmentClone1::Person.establish_connection :development_clone1 - it was called automatically when the class is created.

Of course, DevelopmentClone2::Person is automatically connected to :development_clone2, and so on.

Behind the scenes

Let's look at our generated classes:

$ ruby script/console
>> DevelopmentClone1::Person
=> DevelopmentClone1::Person
>> Person
=> Person
>> DevelopmentClone1::Person.superclass
=> Person

That is, there is a DevelopmentClone1::Person class, automagically generated for you, which is a subclass of Person, so it has all its behaviour etc.

Dynamic connection pools within Rails controllers

The magic of the conn method will now be revealed:

$ ruby script/console
>> def conn
>>   @@connection_pool[rand(@@connection_pool.size)]
>> end
>> conn::Person.name
=> "DevelopmentClone2::Person"
>> conn::Person.name
=> "DevelopmentClone1::Person"

The conn method randomly returns one of the connection modules. Subsequently, conn::Person returns a Person class that is connected to a random clone database. Booya. Free Twitter swag coming my way.

Place the conn method in the ApplicationController class, and you can get dynamic connection pooling within Rails actions as needed, as in the following example (from the top of the article):

class PeopleController < ApplicationController
  def index
    @people = conn::Person.find(:all)
  end
end

Decent Ruby-fu, I think. Certainly better than manually calling establish_connection on model classes before each call (or in a before_filter call, I guess).

This is just a concept

I know this tutorial above works. But that might be the extent of what I know. Let me know if this has any quirks (especially if you solve them), or if this is a stupid idea for implementing connection pooling with nice Ruby syntax.

Hope it helps.

Related posts:

  1. Instant new Rails applications with the App Scrolls When I start a new project I want to start...
  2. Using CoffeeScript in Rails and even on Heroku I’m pretty excited about CoffeeScript as a clean-syntax replacement for...
  3. First look at rails 3.0.pre This article is out of date in some aspects....
  4. Rails themes can remember things I was getting annoyed at having to remember all the...
  5. Install any HTML theme/template into your Rails app Have you ever even bothered to Google for “rails...

67 Responses to “Magic Multi-Connections: A “facility in Rails to talk to more than one database at a time””

  1. Xilo32 says:

    Nice article. I think I may have to explore and expand upon this great idea.

    By the way, I do hope you get your swag!

  2. James Adam says:

    Why not push the random connection selection mechanism down into the adapter, replacing the actual ActiveRecord::Base.connection method, so this becomes entirely transparent?

  3. Dr Nic says:

    @xilo – me too, I love free stuff.

    @james – that would be great if you didn’t care which requests went to which connection. I think some apps might want write requests to go to the master DB, and read requests (select) to be distributed to the slaves.

    Though perhaps we could use your idea and pass a :connection option in the crud methods; or perhaps :connection_group.

  4. Rob Sanheim says:

    Not only would some apps want to write to the master and read from salves, but this would also be great for when you need to scale to ginormous sizes and have to data across federated tables – ie. user’s 1 – 100k are on slave1, users 101k-200k on slave 2, etc.

    Obviously not a common case, but even having the standard master-slave setup isn’t very common either.

    Nice work Nic.

  5. mathie says:

    Great idea, and your timing rocks. I was just about to look into implementing exactly this!

    Pushing it down into the connection adapter to make it transparent to the application would utterly rock, though. I guess you could have all the writes going to the master and reads from the slaves by adding an extra key in config/database.yml along the lines of read_only which defaults to false. If it’s true, then that particular db isn’t considered part of the write connection pool. Then all the write-related SQL statements have to be flagged in some way and pushed towards the write connection pool, whereas read connections can come from anywhere…

  6. Dr Nic says:

    @mathie – I think something like is a good idea, in terms of configuration.

    production:
      adapter: mysql
      ...
    
    production_clone1:
      adapter: mysql
      read_only: true
    

    Or, embed the read-only connections within the main connection specification:

    production:
      adapter: mysql
      ...
      read_only:
        clone1:
          ...
        clone2:
          ...
    

    I think I like the latter one, plus I think it might be easier to implement :D

  7. Matthijs Langenberg says:

    This is so great! Timing is indeed perfect, I just needed something like this. Connecting to a different database based on certain parameters is now possible!

  8. JGeiger says:

    I like the idea, and it’s something I had thought about. I love that you created the concept. The problem that I was thinking about was the whole master_write slaves_read would be rather database specific, but that’s no reason to stop going.

    I also thought about somehow automatically routing all select type calls (find, count, sum, etc) to read from the pool, and all the write calls go to the master. This might eventually be a scaling issue as well, but then you could get into a multi-master DB where db1 is all even IDs and db2 is odd… but that’s beyond me right now.

    Either way, thanks for getting the ball rolling on this…

  9. [...] Dr. Nic has posted an article detailing it. It requires the magic_multi_connections gem and a few modifications to your environment.rb file. You can then specify clones to connect to and distribute load on the database backend. [...]

  10. [...] He also points to an excellent proof-of-concept how-to for an in-Rails load balancing solution. [...]

  11. Dr Nic says:

    Val Aleksenko is about to release a plugin to support the read-only connections for read-only operations, and a write connection for the master database.

    http://revolutiononrails.blogspot.com/2007/04/actsaswithreadonly-to-support-read-only.html

  12. Dr Nic makes Rails talk easily with multiple read/write databases…

    Remember that point about Rails lacking an easy-to-use way of dealing with multiple read/write databases? Strike that. Nic Williams has……

  13. [...] Update: David Heinemeier Hanson, aka “DHH”, the primary developer of Ruby on Rails, has posted that Nic Williams has created a 75-line ROR plug-in that solves the multiple database problem.  He somewhat ungraciously blames Twitter for not taking a look and figuring this out themselves, however, technically at least I have to agree with him.  I couldn’t imagine anything inherent in any language or platform that would prohibit access to more than one database.  So even though such a simple and obvious thing should have been included in Rails, and it’s telling that until now apparently they’ve felt no need for it, it does seem to be true that Twitter gave up a little too easily.  So that’s the other lesson here — don’t assume too quickly that a platform is fundamentally flawed, either, at least to the extent that in an effort to Do Something you try to inject a whole new platform into the mix.  That’s expensive and problematic, too. digg_url=’http://bobondevelopment.com/2007/04/13/ruby-on-rails-hits-a-wall-twitter-stutters/’; digg_skin = ‘button’; digg_bgcolor = ‘#FFFFFF’; digg_title = ‘Ruby on Rails Hits a Wall; Twitter Stutters’; digg_bodytext = ”; digg_topic = ”; Powered by Gregarious (34) [...]

  14. [...] World watch out – Rails now connects to multiple databases quickly and easily. In response to an article mentioning a lack of multiple database support in Rails, Dr Nic worked his magic and released magic_multi_connections. Rails apps can now connect to multiple databases without having to refactor existing models. This sweetness was done in less then 75 lines of code. Read all about it and give Dr Nic some good diggage. Read DHH’s blog to see what he thinks. [...]

  15. [...] This is way cleaner than anything I had. I bow down to the Ruby-Fu of Nic Williams. [...]

  16. [...] So, we’ve moved into rough territory, but someone goes and does something nice (Woo hoo!). Dr Nic writes a plugin to use multiple databases with Rails. Way cool. Really way cool. Awesome job Nic. This is the part of open source that I love, when people help each other just because it’s an interesting problem and they’re a nice person. Hugs and kittens all around. [...]

  17. AkitaOnRails says:

    Congratulations for Dr. Nic for yet another great solution! I just posted about this at my brazilian rails blog. I assumed some things though:
    - this is not a drop in sollution that you just plug and everything works
    - I assume that you don’t have to change any write operations such as Person.create
    - I do assume that you have to change every read operation prepending them with the conn proxy method as in conn::Person.find

    Are these assumptions correct?

    Anyway, thanks again for this great plugin.

  18. ismael says:

    Awsome! So simple. So beatiful.

  19. Dr Nic says:

    @fabio (akita) – The tutorial I wrote works – there is nothing missing. You’ll want to ensure the slaves are synchronised with the master very quickly of course.

    Write operations would not use the conn:: prefix, correct. But, I have a sneaky suspicion that the default ActiveRecord::Base.connection might be defaulted to one of the slaves if you call the conn method before a Person.xxx CRUD method.

    Yes, you’d need to change all your read operations. And so having said that…

    Val Aleksenko just released acts_as_readonlyable plugin which offers an even cleaner solution (notably your code doesn’t have the conn:: all through it), so that’s definitely worth checking out too.

    The MMC was built for a different purpose from the master-slave solution I wrote up here – to allow you to create an admin site that connects to many databases. But when Twitter announced they needed a solution, I thought the MMC might help. The acts_as_readonlyable solution has been extracted from a production app – I think its probably a more guaranteed solution in this case!! :)

  20. [...] We’re in uncharted waters in terms of this sort of throughput, although others have suggestions and maybe even solutions. [...]

  21. [...] Regarding optimizing database access, nothing personal, but I don’t like Dr. Nic’s way. Revolution’s is better. (Although I don’t understand why the existing MySQL solutions weren’t the first resort.) The cost of the app servers is Twitter’s current swinging bridge, though. [...]

  22. Sandro says:

    Fantastic, smart and elegant solution ! Congrats !

  23. Scaling Rails : twittering about scale…

    The internets are all atwitter about comments made by Twitter developer Alex Paynt, which seemed to partially blame Ruby / Rails for scaling problems twitter has been having. The common wisdom in the Rails community at this time is that……

  24. [...] Dr Nic » Magic Multi-Connections: A “facility in Rails to talk to more than one database at a time” Connect to multiple databases with ease. Not sure if this is better than running a MySQL cluster, must investigate. (tags: code database mysql scalability rails rubyonrails) [...]

  25. Eric says:

    I think a great addition would be to build some smarts into this plugin so that lagging slaves are not read from, or at least so that lagging slaves are waited on until they are caught up. The problem is (and has always been) you insert a record, and immediately try and re-read that record faster than the slave gets the replicated information. I know wikipedia deals with this with SHOW MASTER STATUS and SHOW SLAVE STATUS, finding the position of the slave and master and delaying with SELECT MASTER_POS_WAIT(). This is really MySQL dependent but is necessary in a high-load environment.

  26. Kris says:

    Do you need to write an application with the gem in mind or can it be added at a later date to an existing application when you need to scale to multiple read databases?

  27. Dr Nic says:

    @kris – add it later. Plus, there might be newer, improved solutions by then too.

  28. [...] A “facility in Rails to talk to more than one database at a time” – Dr.Nic has writen a great plugin to enable our Rails applications to talk to more than one database. Moreover he has written a nice tutor how things work. Good work! [...]

  29. Scaling Twitter: Blain Cook’s awesome presentation…

    Blain’s presentation on how they are scaling Twitter (with ruby) to cope with the massive levels of traffic they are experiencing was awesome! Blain was the developer at the center of last week’s big tempest in a teapot. He made……

  30. Andrew says:

    Remember Yaml is magic too:

    slave: &slave
    adapter: sqlite3
    timeout: 5000
    read_only: true

    production:
    database: db/production.sqlite3
    adapter: sqlite3
    timeout: 5000

    production_clone1:
    database: db/production_clone1.sqlite3

  31. Andrew says:

    wordpress completely ate my post:
    http://pastie.caboo.se/57055

  32. Rails: Scaling to Multiple Databases…

    Via loudthinking.com , DHH points to the Magic Multi-Connections plugin by Nic Williams . This plugin…

  33. [...] Magic Multi-Connections: A “facility in Rails to talk to more than one database at a time” (tags: Rails database) [...]

  34. Fredrik says:

    Great Work!
    The only issue I have is with namespaced models. It does not seem to work or am I wrong here?

    I can successfully run DevelopmentClone1::User.find(:first), but
    DevelopmentClone1::Products::Item.find(:first) fails.
    I have removed the code for now, so I don’t have the exact error, but it seemed to happen where you gsub with the :: .

    Fredrik

  35. Dr Nic says:

    @fredrik, ooh nice find. I don’t think I had namespaced models in mind at the time.

    For laughs, try: Products::DevelopmentClone1::Item.find(:first) and see if that works. If it does, it probably doesn’t help, but it’s amusing nonetheless :)

    I’ll have a think about how to support namespaced models properly.

  36. Fredrik says:

    I will set it up and try that tomorrow. Hey, if that’s how I need to do it, that’s just fine with me. If it works it works. Before you published this I had nothing :) .

    Fredrik

  37. Fredrik says:

    That was a no go unfortunately.
    Would it be too much to ask for a brief explanation on what’s required to get this working. I’ll take a look and do what I can, but I can’t claim that I understand everything that is going on in your gem.

    Fredrik

  38. [...] Dr Nic » Magic Multi-Connections: A “facility in Rails to talk to more than one database at a time” Definitely need to investigate this. [...]

  39. [...] Dr Nic » Magic Multi-Connections: A “facility in Rails to talk to more than one database at a time” Apr 2007 (tags: rails scaling) [...]

  40. Alex Peuchert says:

    Hi Dr. Nic! really cool ruby magic!… but, I see 3 problems arise here, if one uses this for load balancing:

    First, you have to change your complete model code to use the plugin – DRY comes to my mind ;-)

    Secondly, one has to hardcode the load distribution into the application. Every model is stuck to a specific database. So, there is no reorganization possible afterwards.

    And lastly, there is a big issue with database health checking. For every database connection you add, you have to implement some kind of availability checking. And a single database failure can break the complete application.

    Keep up the magic! -Alex

  41. Dr Nic says:

    @alex – I think #2+#3 would be implemented by your #conn method implementation. In the example above it was a pure random selection of original connections. You could do health checking there, etc.

    But more importantly, this code is just an idea/proof of concept to show how you could modify an ActiveRecord (or any class) via a Module.

  42. [...] Dr Nic » Magic Multi-Connections: A “facility in Rails to talk to more than one database at a time” [...]

  43. Hi DrNic! me again… I had some thoughts about this multiple-database-thing and came up with an idea about it. Why not change the database connection inside the controller action? Maybe you think this could be interesting and want to read more about it at:

    http://scriptserver.blogspot.com/2007/06/uploaded-dynamicdatabasechanger-plugin.html

    -alex

  44. [...] Gespannt verfolgte damals die Blog-Gemeinde die Anfeindungen zwischen signal37.com und Twitter; und jeder wußte es nun besser. Jeder fragte sich, warum die Twitter-Entwickler so blauäugig auf Ruby on Rails gesetzt hatten, warum man bestimmte Probleme nicht so, sondern so gelöst hätte und überhaupt (möglicherweise war das hier der entscheidende Hinweis?) [...]

  45. [...] Remember that point about Rails lacking an easy-to-use way of dealing with multiple read/write databases? Strike that. Nic Williams has released Magic Multi-Connections. It makes it dead easy to use a cluster of databases to scale read and write speeds higher than a single connection would ever allow. [...]

  46. Kyle says:

    Has anyone used this in a real production environment. I used a similar gem http://rubyfurnace.com/gems/mysql_replication_adapter (Mysql_replication_adapter), but found it to make our write database hang for a long time for queries. I’ll blame it on myself for not knowing the inner workings of the gem prior to rolling it out on the test clusters, but I’m wondering if there are any other options to accomplish this.

    Perhaps using a load balanced pool of connections to the slave with 3rd party hardware / software could be the most fool proof solution here. Where it intercepts which are read and writes. Although I like having control over which connection a query gets sent to, and this really can help you solve performance problems right in the butt.

    I’d love to hear about any experience though in a real world high load production environment (1mil + connections a day) and rails internals doing the slave db-load balancing for you.

    Also the more connections you have open for a server deamon such as mysqld the slower it will run.

  47. eduludi says:

    Is there a way to use this technique to connect multiple database users/roles to the same database? what about performance? Could I simulate a multi-thread connection?

  48. GW says:

    I too am looking for a solution to allow multiple users per database. In order to apply principle of least privilege to database actions, I prefer to set up a few database users, each with specific duties relative to read/write/delete and table access rights.

    I’ll have a look at the plugin code and see if I can decipher a way to use it / extend it, but I think this is also one of those “obvious” lackings in the Rails connection interface.

  49. [...] rough territory, but someone goes and does something nice (Woo hoo!). Dr Nic writes a plugin to use multiple databases with Rails. Way cool. Really way cool. Awesome job Nic. This is the part of open source that I love, when [...]

  50. Multiple Database Connections in Ruby on Rails…

    I’ve been doing a lot of work in Ruby on Rails lately and absolutely love it. In the past few days I’ve needed to port an existing database over to a new schema for comparisons and benchmarking. It took a……