The Ruby Sequel ORM on the Hibernate Connection Pool

We will demonstrate how to unify access to your database by leveraging the same connection in Java and JRuby.

Written by Daniel Neighman and Matt Wilson.

Heads up, we’ve moved! If you’d like to continue keeping up with the latest technical content from Square please visit us at our new home https://developer.squareup.com/blog

Square’s Java services use either Hibernate or Jooq as their ORM. As with the theme of the series, we have some pretty cool infrastructure which instruments and enforces proper use of database connections. In this post we will show how to put the Ruby ORM, Sequel, on top of a Hibernate-managed connection.

Sequel Connection Pooling

Sequel on Hibernate is actually the keystone accomplishment that started it all. It was built during a pivotal hack week where Xavier Shay showed integration was possible!

The Sequel gem, by Jeremy Evans, has a very clean architecture around connection pooling which made it the target for the first integration. For those not familiar with Sequel, its design philosophy is based on a dataset rather than a single record. This makes it simple to exploit the power of SQL sets — that’s what RDBMS are good at! Sequel::Model is just a special case of a dataset, one that has been collapsed to a single record. It has many plugins to extend its behavior, including Active::Model.

Hibernate and Sequel both manage their own connection pools. The first task was to write a connection pool proxy which made Sequel take a back seat and use hibernate’s Connection pool. The important part of the API is:
ruby
initialize(db, options={}) # setup the connection pool for a db
hold(name, &block) # yield a connection on demand

Perfect! Initialize the pool and yield a connection on demand. There are a few other methods in the connection pool API but none of them are interesting in this case.

Sequel also already ships with a set of JDBC adapters that can take a raw connection, so we’re 90% of the way there. Now we just need to wire it up. You can find a sneak peek the Ruby code we’re about to go through here.

class MinecartMysqlAdapter < Sequel::JDBC::Database
  include ::Sequel::JDBC::MySQL::DatabaseMethods
  def initialize(opts)
    opts[:pool_class] = Sequel::MinecartConnectionPool
    super
    Sequel::JDBC::DATABASE_SETUP[:mysql].call self
    self.dataset_class = Sequel::MinecartMysqlDataset
  end
end

This simple implementation sets Sequel’s connection pool to our custom one, and changes the dataset class to handle strings a little differently than the default implementation. Now the connection pool just needs to hand over the connections.

module Sequel
  class MinecartConnectionPool
    ##
    # Initialize using the passed Sequel::Database object and options hash.
    #
    # :call-seq:
    #   initialize(Database, Hash)
    def initialize(db, opts={})
      @db = db
      @session_factory = opts.fetch(:session_factory)
      @key = "minecart-connection-pool-#{object_id}-connection".freeze
    end
    ##
    # Yield a connection object to the current block. For sharded connection
    # pools, the Symbol passed is the shard/server to use.
    #
    # :call-seq:
    #   hold(Symbol, &block)
    def hold(name) # :yields: connection
      if connection
        yield connection
      else
        connect do
          yield connection
        end
      end
    end
  end
end

You can see in the hold method, if we don’t currently have a connection then we connect and yield the connection. This is not a traditional pool because Sequel doesn’t manage the connections; it just needs to get the connection from the Hibernate SessionFactory. If it doesn’t have a connection available yet, it calls connect and yields out the connection created by the SessionFactory. Let’s take a look.

def connect(&block)
  # At Square we actually have another layer named
  # the Transacter which manages getting and closing
  # sessions from the session factory
  session = @session_factory.open_session
  begin
    session.doWork(CallInUnitOfWork.new(self, block))
  ensure
    session.close
  end
end

The API for a session requires that you provide class to manage the hibernate JDBC work. The connection is provided by Hibernate only for the execution of the work unit. We need a simple wrapper to hand the connection over to Sequel’s pool.

class CallInUnitOfWork
  include org.hibernate.jdbc.Work # Include the Java Interface as a module
  def initialize(pool, block)
    @pool  = pool
    @block = block
  end
  def execute(connection)
    @pool.connection = connection
    @block.call
  ensure
    @pool.connection = nil
  end
end

And Done! This just sets the connection on the pool which is a thread local variable for the length of the block, then ensures it is removed from Sequels view, allowing Hibernate to manage its full lifecycle.

Try It!

We’re going to download a tar which has a pom file, Gemfile, migration and a model. We leave the implementation of the pizza service to you! Make sure you’re using JRuby.

#BUILD
curl -O https://dl.dropboxusercontent.com/u/526413/work/sequel-on-hibernate.tar.gz
tar xzf sequel-on-hibernate.tar.gz
cd sequel-on-hibernate
bundle
mvn -am package
# MYSQL SETUP
echo "create database pizza;" | mysql -u root
bundle exec rake db:migrate

Now we are going to require the built jar, require the connection pool, and make a connection to the database.

# JRuby shell
require "target/hibernate-1.0-shaded.jar"
require 'sequel'
require 'minecart-sequel'
session_factory = org.hibernate.cfg.Configuration.new.configure.buildSessionFactory
properties = {name: "basic", adapter: Sequel::MinecartMysqlAdapter, session_factory: session_factory}
Sequel.connect(properties) do |db|
  Dir.glob(File.join(Dir.pwd, "models", "*.rb")).each do |file|
    require file
  end
end
Order.create(person_id: 1)
Order.all

Awesome right?

Even if you are unfamiliar with Java, this is a perfect place to start. There are all sorts of cool things you can do like: manage database read-write properties, add slow query monitoring, configure and connect your database automatically, and even throw errors when an external HTTP connection is made inside a transaction! If you are just using JRuby for the garbage collection improvement, this is a great place to start testing out deeper integration.

Square’s infrastructure provides us with these and many more benefits. At Square it’s possible for anyone with a vision to radically improve the state of the world for everyone! It’s just so awesome.

This post is part of a series, which highlights discoveries and insights found while integrating Ruby with our robust Java stack.

Daniel Neighman - Profile

Matt Wilson