How to setup session based database connections with Sinatra, Sequel and Rack

Question

I am using Sequel with Sinatra in a Puma / Rack server.

I wish to store my Sequel DB connection object with the session - not globally - so that I can have a separate pool of DB connections for each logged on user. The intent is to have one database server logon for each web server logon.

I cannot work out how to do this since the Sequel database object appears to be global singleton. If for example I attempt to serialize the database object and store in the session I will get an error message: TypeError - can't dump anonymous class: I don't want to have to connect to the database on every route request.

How can I do this? Here is some sample code that hopefully indicates what I am trying to achieve:

require 'sinatra/base'
require 'pp'
require 'sequel'
require 'json'
require 'java'            
require 'sqljdbc4.jar'
require 'yaml'

class MyApp < Sinatra::Base

  set :sessions, true

  use Rack::Session::Cookie, :key => 'rack.session',
      :expire_after => 2592000,
      :secret => 'sydasx'

  get '/' do
    db = session[:db]
    DB = YAML::Load(db)

    response = ''
    DB['select * from SEC_USER'].each do |row|
      response += row.to_s
    end

    response.to_json
  end

  get '/login/:username' do

    username = params['username']

    puts "username: #{username}"

    conn_str = "jdbc:sqlserver://localhost:1434;databaseName=#{username};integratedSecurity=true;"    
    DB = Sequel.connect(conn_str)
    puts "DB: #{DB.pretty_inspect}"
    db = YAML::dump(DB)
    puts "db: #{db}"

    session[:db] = db

    "logged in"
  end

end

Answer 1

You can't serialize the Sequel::Database object. You have a few decent options:

1. Use a rack middleware that creates a Sequel::Database object per request, using the object only for the request. In this case you wouldn't assign the result of Sequel.connect to a constant, you would pass a block and call the next variable inside that block.

2. Create a single Sequel::Database object at top level and store it in the DB constant. The the arbitrary_servers and server_block extensions into the Sequel::Database object. Then use a rack middleware that checks out a connection to the appropriate server for the duration of the block.

3. If you have few customers, it's possible to just use Sequel's sharding support and use just the server_block extension without arbitrary_servers. One advantage of doing that is that connections can be cached so you aren't making a separate database connection per request (which would be the case for both 1. and 2.).

4. Use a global hash as you mentioned, with keys being user names and values being Sequel::Database objects. You need to make sure you have enough memory to store all the objects you want to track if you do this.