This one is a pretty specific hack. I’ve had the need to build mini reporting apps, using Sinatra, that aggregate data from multiple databases and produce a report. This is all fine since ActiveRecord is awesome, but I’ve always missed AR’s query caching ability.

After some digging I found that query caching is only enabled on AR’s Base connection and all other models must use that awkward Model.cache{} construct — which is annoying to use. I’ve hacked up a solution that works, although it relies on :send and instance_variable_set which isn’t very elegant or solid.

For what it’s worth, here is a “simple” way to get Sinatra to use ActiveRecord’s native query caching for more than 1 database:

require 'sinatra_query_caching_demo'
run Demo

File: sinatra_query_caching_demo.rb
———————————–

require 'rubygems'
require 'sinatra/base'
require 'active_record'
require 'logger'

# override some instance variables and _force_ query caching to be enabled on databases other than AR::Base's connection
module ARQueryCachingHack
def self.included(klass)
# make sure you call this _AFTER_ a connection has been established, otherwise the connection
# will override these. it is kind of a pain, so the best bet is to include this in the initialization
def klass.enable_query_caching
self.connection.instance_variable_set(:@query_cache_enabled, true)
self.connection.instance_variable_set(:@query_cache, {})
end
def klass.disable_query_caching
self.connection.instance_variable_set(:@query_cache_enabled, false)
end
end
end

ActiveRecord::Base.configurations = {
# this is a garbage connection so we can make sure AR::Base is on a different connection. If we are sharing
# the connection it kind of renders this moot since AR::Base is always query cached with the AR::QueryCache
# middleware
'test' => {
'adapter' => 'mysql',
'host' => 'localhost',
'username' => 'root',
'password' => '',
'database' => 'testing' # change this to some other DB besides +mysql+
},
# our separate target database. if we are on an app that uses multiple DB's then this would be the +other+ db, not
# AR::Base
'mysql' => {
'adapter' => 'mysql',
'host' => 'localhost',
'username' => 'root',
'password' => '',
'database' => 'mysql'
}
}

# AR::Base needs a connection before AR::QueryCache can take effect
ActiveRecord::Base.establish_connection 'test'
ActiveRecord::Base.logger = Logger.new($stdout)

# just to keep things separate, tuck all external models in their own module
module MySQL
# this would be a common base for all models that need a different DB, since all AR:Base models will just
# inherit from AR::Base
class Base < ActiveRecord::Base
self.abstract_class = true
establish_connection 'mysql'
end
# some model in a different database
class User < Base
include ARQueryCachingHack
set_table_name :user
end
# enable query caching on each model you want
def self.query_cache_models
[User].each &:enable_query_caching
end
end

class Demo < Sinatra::Base
use ActiveRecord::QueryCache

before do
# enable query caching by default. this probably isn't where you _want_ to put this, an initializer is probably best
MySQL.query_cache_models
ActiveRecord::Base.logger.info '– enabling up per-model query caching –'
end

get '/' do
# with query caching
10.times{ MySQL::User.first }
# => CACHE (0.0ms) SELECT * FROM `user` LIMIT 1

# without query caching
ActiveRecord::Base.logger.info '– disabling up per-model query caching –'
MySQL::User.disable_query_caching
10.times{ MySQL::User.first }
# => MySQL::User Load (0.3ms) SELECT * FROM `user` LIMIT 1

"Hack0rific!"
end
end

Start’er up: $> open http://localhost:9292 && rackup and you should see “Hack0rific!” — now check out the log and see how AR cached and didn’t cache, it should resemble:

[2010-03-24 15:12:46] INFO  WEBrick::HTTPServer#start: pid=15801 port=9292
  SQL (0.2ms)   SET SQL_AUTO_IS_NULL=0
  SQL (0.2ms)   SET SQL_AUTO_IS_NULL=0
-- enabling up per-model query caching --
  MySQL::User Load (0.4ms)   SELECT * FROM `user` LIMIT 1
  CACHE (0.0ms)   SELECT * FROM `user` LIMIT 1
  ...a few more times...
-- disabling up per-model query caching --
  MySQL::User Load (5.3ms)   SELECT * FROM `user` LIMIT 1
  ...a few more times...

The one big caveat is that you need to enable_query_caching after the connection has been made (otherwise AR’s establish_connection will override your hacks) — so it needs to be in an initializer or something similar.

I’ve had great success with this in some apps, although it works a little _too_ well so updates don’t push through when they need to. Take it for what its worth, a silly hack, and let me know how it works out for you .