EmMysql2ConnectionPool generates a pool of MySQL2 connection with the given parameters. If you execute queries on the pool, these queries get cued and executed using the next free connection.

Configure as usual:

conf = {
    :host => "localhost",
    :database => 'my_db',
    :reconnect => true,
    :username => "root",
    :size => 5,
    # :password => ''
}

my_query = "SELECT * FROM track WHERE in_progress != 0 LIMIT 10"

Mysql2::Client.default_query_options.merge! :symbolize_keys => true, :cast_booleans => true

MySQL = EmMysql2ConnectionPool.new conf

The only additional option parameters are :size (which determines the connection pool size) and :on_error (which takes a proc to define the error behaviour on a connection basis).

The just issue your queries on the connection pool:

EM.run do
  MySQL.query my_query do |results|
    p results.to_a
    EM.stop
  end
end

Go ahead, try the example.

As a lot of eventmachine libraries, EmMysql2ConnectionPool not only supports the direct use of a callback, but returns an Eventmachine::Deferrable. Further callbacks and errbacks can be defined on this deferrable.

The equivalent of the above, including an errback, looks like this:

EM.run do
  my_query = MySQL.query my_query
  my_query.errback{ |error| puts "An error occured: #{error}" }
  my_query.callback do |results|
    p results.to_a
    EM.stop
  end
end

If the query succeeds, the result is yielded to the given block or the given callback(s).

As a second argument, the number of affected rows is yielded. If you don’t need it, your callback doesn’t even need to define a second argument.

Again the same example using the number of affected rows:

EM.run do
  my_query = MySQL.query my_query
  my_query.errback{ |error| puts "An error occured: #{error}" }
  my_query.callback do |results, affected_rows|
    puts "Affected rows: #{affected_rows}"
    p results.to_a
    EM.stop
  end
end

The rationale behind this design decision is as follows: To get hold of the number of affected rows, you need to have the connection on which your last query is issued. As the connection “hides” behind the pool and you even wouldn’t know whether your actual query is really the last query on this connection, the only way to pass the number of affected rows to the user is the callback.

Sometimes you need the connection within the query. For example if you want to SQL-escape a string. But when building the query with EmMysql2ConnectionPool you don’t have a connection at hand as your query just gets cued. Therefore you can wrap your query into a block whose only parameter will be the actual connection executing the query:

MySQL.query proc{ |conn|
  escaped_name = conn.escape some_string
  "SELECT * FROM my_table WHERE name is '#{escaped_name}'"
}