Ruby - Sequel Model to access multiple databases

Question

I'm trying to use the Ruby Sequel::Model ORM functionality for a web service, in which every user's data is stored in a separate MySQL database. There may be thousands of users and thus databases.

On every web request I want to construct the connection string to connect to the user's data, do the work, and then close the connection.

When using Sequel, I can specify the database to use for a particular block of code:

    Sequel.connect(:adapter=>'mysql', :host=>'localhost', database=>'test1') do |db|
        db.do_something()
    end

This is all very good, I can perform Sequel operations on the particular user's database. However, when using Sequel::Model, when I come to do my db operations it looks like this:

    Supplier.create(:field1 => 'TEST')

I.e. it doesn't take db as a parameter, so just uses some shared database configuration.

I can configure the database Model uses in two ways, either set the global DB variable:

    DB = Sequel.connect(:adapter=>'mysql', :host=>'localhost', database=>'test1')

    class Supplier < Sequel::Model
    end

Or, I can set the database just for Model:

    Sequel::Model.db = Sequel.connect(:adapter=>'mysql', :host=>'localhost', database=>'test1')

    class Supplier < Sequel::Model
    end

In either case, setting a shared variable like this is no good - there may be multiple requests processed concurrently, each of which needs its own database configuration.

Is there any way around this? Is there a way of specifying per-request db configuration using Sequel::Model?

As an aside, I've run into a similar problem with DataMapper, I'm now wondering whether having a single multi-tenanted database is going to be the only option if using Ruby, although I'd prefer to avoid this as it limits scalability.

A solution, or any pertinent discussion would be much appreciated.

Thanks Pete

Jeremy Evans · Accepted Answer · 2014-02-11 17:15:17Z

6

Use Sequel's sharding support for this: http://sequel.jeremyevans.net/rdoc/files/doc/sharding_rdoc.html

answered Feb 11, 2014 at 17:15

Jeremy Evans

12.2k31 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

marcin naglik · Accepted Answer · 2016-03-18 10:21:11Z

2

Actually in your case it's probably better to use arbitrary_servers extension than sharding:

DB.with_server(:host=>'hash_host_b', :database=>'backup') do
  DB.synchronize do
    # All queries here default to the backup database on hash_host_b
  end
end

See: http://sequel.jeremyevans.net/rdoc/files/doc/sharding_rdoc.html#label-arbitrary_servers+Extension

answered Mar 18, 2016 at 10:21

marcin naglik

462 bronze badges

1 Comment

Peter Reay Over a year ago

that looks like what I was after - haven't tried it, but looks like the answer, thanks!

Collectives™ on Stack Overflow

Ruby - Sequel Model to access multiple databases

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related