@tenderlove @matthewd @rafaelfranca would ❤️ your review here too.

Doesn't this break the current inheritance behaviour?

Doesn't this break the current inheritance behaviour?

It should not. the specification_id method fallbacks to the parent if not set. Which brings the same inheritance behaviour as before. Also, we had tests for that, and they still work.

@matthewd @kaspth thanks for bearing with me on this. I renamed the key to specification_name.

@arthurnn sorry for commenting on a closed issue, but looking through this thread + the changes in the code, I'm not sure I understand how I can make use of these changes.

Basically, my scenario is that I would like to be able to have multiple databases configured at runtime (e.g. new user signs up -> make the Rails app "aware" of a new database -> make a new Pool for that database), and I can switch on each request which database to use.

To my understanding, this PR would make the above use-case possible, including using a multi-threaded server like Puma - it seems though that it eludes me how I can actually define the "specifications" for each DB. Could you please give me some pointers in the right direction?

To summarize:

1. Having connection pools to multiple DBs - is it thread safe? Can I safely switch between DB specs using connection_specification_name per request in a multi-threaded server?
2. How do I specify the .. connection specifications? Is it possible to do so while the Rails app is running or does it need to restart? :)

Thanks a lot!

Hey @rzv01 I am glad you asked. I am planning to write a few blog posts on how people can use this, but I didn't have time yet. I did a talk that touch into this changes too, here is the link: https://youtu.be/-41V0qETxmE?list=PLo3sQWbYZbskTlzH7Ert-FucR7QmIuwE5

Having connection pools to multiple DBs - is it thread safe? Can I safely switch between DB specs using connection_specification_name per request in a multi-threaded server?

The idea is to not use the same connection pool for different database. Each database should have its own connection pool.

How do I specify the .. connection specifications? Is it possible to do so while the Rails app is running or does it need to restart? :)

You can create the connection pools passing a specification name, and in your model you would extend the method connection_specification_name and return the right name.

Hey @arthurnn - thanks a lot for your response. I've watched the video you linked - very informative, thanks! and it got me closer to understanding how multiple DBs should work.

Now, in your talk you mentioned that the future direction for Rails 5.1 and onward would be to have all the DB configs in the database.yml file and to initialize all connections at app startup.

However, this means the config is now static. Would it be possible to make the app "aware" of a new DB config during runtime?

Not sure how this would work to be honest, maybe write to the database.yml file (say, an external process which knows when a new DB is needed) and send a signal to the Rails server process? which would in turn reload the database.yml, which contains the new DB config as well.

The use case I'm thinking of is:

1. one DB per customer
2. one/few Rails app instances (each one serving all/multiple clients)
3. Rails app connects to all/multiple DBs that is assigned to it

here comes the problem:
4. customer signs up, a DB is created for him/her.

How would the Rails app know about the new DB? :)

Ideally, without having to restart the app - I guess it wouldn't be the end of the world, especially with a phased restart of a multi-threaded server, but still, not that elegant, hence me asking if a new DB config can be "injected" in the app dynamically.

Btw, sorry if I missed something and the answer to my question above is obvious!

Thanks a lot, once again! 🍻

I'm also interested in use case where there is one DB per user/customer.

the database connection details don't technically need to be in database.yml, you should be able to create connection pools yourself; the code could be something like this (untested):

def self.connection_specification_name
  if !connection_handler.retrieve_connection_pool(Thread.current['customer_db_name'])
    spec = database_spec.merge(name: Thread.current['customer_db_name'])
    connection_handler.establish_connection(spec)
  end

  Thread.current['customer_db_name']
end

only looked briefly though, and i might be missing something basic there :). i assume, as we're creating a pool per database we'll end up with pool_size * customers database connections. (traditional pg schema based switching or mysql use statement switching uses one pool)

edit: would probably be better do do the connection pool initialisation out of band though (per request?) as this method will be called a ton 😅

@mikecmpbll is right.
You can call connection_handler.establish_connection yourself and pass a spec. That spec needs to be a hash, which can come from anywhere, a file, db, http, etc.
So in your case, you would establish the connection, and indeed, probably better to do that during initialization, or out-of-band somehow.

@mikecmpbll @arthurnn thanks a ton for your help, you guys are great :)

I think right now i got all the info i need. I'll give it a try sometime in the next few days and see how it goes.

Is there a thread safe way to remove idle connection pools? With some testing it looks like I just can't use ConnectionHandler's remove_connection, because it removes pool from all threads?

@arthurnn Is there a way to avoid using an instance variable to store @connection_specification_name in remove_connection (see 537a342)? I need to dynamically swap the connection per-model, in a multi-threaded environment. Sometimes this means the same model, usually ActiveRecord::Base, will be connected to separate databases in separate threads. I achieve this by overriding the connection_specification_name method suggested in #24844 (comment) (though with a different, threadsafe implementation). However, the remove_connection method accesses the instance variable directly, bypassing the logic in the overridden method, and becoming un-threadsafe since the instance variable is shared across threads.

Cool @njakobsen. Please do contribute a PR to no longer refer to the ivar directly. And a PR with your dynamic connection swapping would be wonderful as well! 😍

I'm definitely just crashing around, but I'll show you my monkeypatch and maybe you can let me know if there's a better way to store information about which connection the model should use on a per-thread basis. See https://github.com/culturecode/stagehand/blob/5c2d1815738e602584633ef48c7aa2b62ba79ce6/lib/stagehand/active_record_extensions.rb#L28-L53. With some feedback, I'll see what sort of PR I can make from all of this.

Do you have a use case that involves calling remove_connection, or are you just trying to cover the full API?

I don't have a use case for removing the connection, but if someone using my patch were to call establish_connection on the model, it would call remove_connection, so I want to make sure that it uses the overridden method to know which connection to remove based on the current thread. Otherwise I think it'll just default to AR:B's "primary" connection since no other classes are directly setting the instance variable in my monkeypatch.

Basically @arthurnn's suggestion of how his PR can be used opened up a nice avenue for my Gem to use a lighter touch to swap connections dynamically by changing the connection spec name instead of calling establish_connection each time, but it's being hamstrung by the ivar in the remove_connection method.

I am also not happy with my solution of storing the connection spec name for each model on Thread.current. So if there are any suggestions of a better place for it, I'd happily incorporate that into a potential PR.

i think establish_connection calls this remove_connection

If you've overwritten the getter/setter, then @connection_specification_name won't be defined anyway, will it?

I think it's pretty common to need to thread-scope the behaviour.. the only quick observation I'd make is that you don't necessarily need to use the setter: you could define connection_specification_name to directly read the top of the ConnectionStack, for example.

@matthewd that's an interesting idea, but I was trying to keep the changes to AR:B isolated from the inner machinations of the rest of the gem. If I could simply make it threadsafe, then any other code (including subclasses) calling establish_connection or remove_connection would continue to function as expected.

My gem seems to work with my monkeypatches, but at @jeremy's urging, my questions are now more about how to apply this threadsafety to a PR for Rails itself.

• Does Rails have any mechanism other than Thread.current for storing information per-thread (I see some places using Concurrent::Map, and Process.pid, so maybe something similar would be useful here)
• Do the threads ever get reaped? Or is that dependent on which webserver we're running?
• Have I missed something important?

@jeremy @matthewd I think I'm going to tackle this with a very light touch. Instead of making a bunch of decisions about how the connection should be tracked independently per-model, per-thread (which could cause issues in the general case where we don't want independent tracking per thread). I'll make a PR which simply uses the connection_specification_name method when removing a connection, instead of the ivar, while at the same time keeping in mind the change from this commit 537a342. This way, an app that needs independent tracking per thread can implement it by overriding the connection_specification_name with whatever logic is required by their specific app.