mahemoff · January 6, 2023 15:56 · May 10, 2017 · May 10, 2017 · May 10, 2017 · May 10, 2017
diff --git a/README.md b/README.md
@@ -7,7 +7,7 @@ The simple way to switch databases is:
     ActiveRecord::Base.establish_connection :user_shard1
     puts User.find(1) # this is executed on the database keyed on "user_shard1" in database.yml
     ActiveRecord::Base.establish_connection :development
-    
+
 This should work in a single-thread environment, so it's safe to use in a single-threaded web server like Unicorn or WEBrick. However, in a multi-thread environment like Puma or Sidekiq, it will cause major bugs because ActiveRecord's state - ie which database it's using - is global for the whole process. Threads will constantly be switching this global state and then reading/writing to/from the wrong database.
 
 ### THE RIGHT WAY TO SWITCH DATABASES

diff --git a/multidb.rb b/multidb.rb
@@ -3,7 +3,7 @@
   # Get the hash (i.e. parsed) representation of database.yml
   databases = Rails.configuration.database_configuration
 
-  # Get a fancier AR-specific representation of database.yml
+  # Get a fancier AR-specific version of this hash, which is actually a wrapper of the hash
   resolver = ActiveRecord::ConnectionAdapters::ConnectionSpecification::Resolver.new(databases)
 
   # Get one specific database from our list of databases in database.yml. pick any database identifier (:development, :user_shard1, etc)
@@ -14,8 +14,17 @@
 
   # Use the pool
   # This is thread-safe, ie unlike ActiveRecord's establish_connection, it won't leak to other threads
-  pool.with_connection {
-    puts User.find(1)
+  pool.with_connection { |conn|
+
+    # Now we can perform direct SQL commands
+    result = conn.execute('select count(*) from users') # result will be an array of rows
+    puts result.first
+
+    # We can make AR queries using to_sql
+    # See http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/DatabaseStatements.html
+    sql = User.where('created_at > ?', 1.day.ago).limit(5).to_sql # generate SQL string
+    raw_users = conn.select_all sql # get list of hashes, one hash per matching result
+
   }
 
 rescue => ex

diff --git a/README.md b/README.md
@@ -1,4 +1,4 @@
-This is a tech demo showing how to switch between databases in Rails 5, in a thread-safe manner.
+The code here is a tech demo showing how to switch between databases in Rails 5, in a thread-safe manner.
 
 ### BACKGROUND: HOW *NOT* TO CHANGE DATABASES
 

diff --git a/multidb.rb b/multidb.rb
@@ -7,15 +7,15 @@
   resolver = ActiveRecord::ConnectionAdapters::ConnectionSpecification::Resolver.new(databases)
 
   # Get one specific database from our list of databases in database.yml. pick any database identifier (:development, :user_shard1, etc)
-  spec = resolver.spec(:development)
+  spec = resolver.spec(:user_shard1)
 
   # Make a new pool for the database we picked
   pool = ActiveRecord::ConnectionAdapters::ConnectionPool.new(spec)
 
   # Use the pool
   # This is thread-safe, ie unlike ActiveRecord's establish_connection, it won't leak to other threads
   pool.with_connection {
-    puts Post.count
+    puts User.find(1)
   }
 
 rescue => ex

diff --git a/README.md b/README.md
@@ -10,7 +10,7 @@ The simple way to switch databases is:
 
 This should work in a single-thread environment, so it's safe to use in a single-threaded web server like Unicorn or WEBrick. However, in a multi-thread environment like Puma or Sidekiq, it will cause major bugs because ActiveRecord's state - ie which database it's using - is global for the whole process. Threads will constantly be switching this global state and then reading/writing to/from the wrong database.
 
-### THE SOLUTION
+### THE RIGHT WAY TO SWITCH DATABASES
 
 The solution, as in the code example here, is to DIY pool management. Any general ActiveRecord statements will use the default database as normal (ie "development" or "production"). The pools should be established on initialisation and any code that needs to use another database should use pool.with_connection, which is a Rails method on the database pool class that will not affect global state. This is also nice from a clean-code perspective, because we don't need to reset to using the default database afterwards. It's just a block that temporarily uses a different database.
 

diff --git a/README.md b/README.md
@@ -5,7 +5,7 @@ This is a tech demo showing how to switch between databases in Rails 5, in a thr
 The simple way to switch databases is:
 
     ActiveRecord::Base.establish_connection :user_shard1
-    puts User.find(1) # this happens on database keyed on "user_shard1" in database.yml
+    puts User.find(1) # this is executed on the database keyed on "user_shard1" in database.yml
     ActiveRecord::Base.establish_connection :development
 
 This should work in a single-thread environment, so it's safe to use in a single-threaded web server like Unicorn or WEBrick. However, in a multi-thread environment like Puma or Sidekiq, it will cause major bugs because ActiveRecord's state - ie which database it's using - is global for the whole process. Threads will constantly be switching this global state and then reading/writing to/from the wrong database.

diff --git a/README.md b/README.md
@@ -1,6 +1,6 @@
 This is a tech demo showing how to switch between databases in Rails 5, in a thread-safe manner.
 
-# BACKGROUND: HOW *NOT* TO CHANGE DATABASES
+### BACKGROUND: HOW *NOT* TO CHANGE DATABASES
 
 The simple way to switch databases is:
 
@@ -10,11 +10,11 @@ The simple way to switch databases is:
 
 This should work in a single-thread environment, so it's safe to use in a single-threaded web server like Unicorn or WEBrick. However, in a multi-thread environment like Puma or Sidekiq, it will cause major bugs because ActiveRecord's state - ie which database it's using - is global for the whole process. Threads will constantly be switching this global state and then reading/writing to/from the wrong database.
 
-# THE SOLUTION
+### THE SOLUTION
 
 The solution, as in the code example here, is to DIY pool management. Any general ActiveRecord statements will use the default database as normal (ie "development" or "production"). The pools should be established on initialisation and any code that needs to use another database should use pool.with_connection, which is a Rails method on the database pool class that will not affect global state. This is also nice from a clean-code perspective, because we don't need to reset to using the default database afterwards. It's just a block that temporarily uses a different database.
 
-# REFERENCES
+### REFERENCES
 
 * http://www.wordofmike.net/j/shard-query-rails-querying-multiple-databases
 * https://stackoverflow.com/questions/43691840/switching-between-multiple-databases-in-rails-without-breaking-transactions
diff --git a/README.md b/README.md
@@ -0,0 +1,20 @@
+This is a tech demo showing how to switch between databases in Rails 5, in a thread-safe manner.
+
+# BACKGROUND: HOW *NOT* TO CHANGE DATABASES
+
+The simple way to switch databases is:
+
+    ActiveRecord::Base.establish_connection :user_shard1
+    puts User.find(1) # this happens on database keyed on "user_shard1" in database.yml
+    ActiveRecord::Base.establish_connection :development
+
+This should work in a single-thread environment, so it's safe to use in a single-threaded web server like Unicorn or WEBrick. However, in a multi-thread environment like Puma or Sidekiq, it will cause major bugs because ActiveRecord's state - ie which database it's using - is global for the whole process. Threads will constantly be switching this global state and then reading/writing to/from the wrong database.
+
+# THE SOLUTION
+
+The solution, as in the code example here, is to DIY pool management. Any general ActiveRecord statements will use the default database as normal (ie "development" or "production"). The pools should be established on initialisation and any code that needs to use another database should use pool.with_connection, which is a Rails method on the database pool class that will not affect global state. This is also nice from a clean-code perspective, because we don't need to reset to using the default database afterwards. It's just a block that temporarily uses a different database.
+
+# REFERENCES
+
+* http://www.wordofmike.net/j/shard-query-rails-querying-multiple-databases
+* https://stackoverflow.com/questions/43691840/switching-between-multiple-databases-in-rails-without-breaking-transactions
diff --git a/multidb.rb b/multidb.rb
@@ -0,0 +1,25 @@
+begin
+
+  # Get the hash (i.e. parsed) representation of database.yml
+  databases = Rails.configuration.database_configuration
+
+  # Get a fancier AR-specific representation of database.yml
+  resolver = ActiveRecord::ConnectionAdapters::ConnectionSpecification::Resolver.new(databases)
+
+  # Get one specific database from our list of databases in database.yml. pick any database identifier (:development, :user_shard1, etc)
+  spec = resolver.spec(:development)
+
+  # Make a new pool for the database we picked
+  pool = ActiveRecord::ConnectionAdapters::ConnectionPool.new(spec)
+
+  # Use the pool
+  # This is thread-safe, ie unlike ActiveRecord's establish_connection, it won't leak to other threads
+  pool.with_connection {
+    puts Post.count
+  }
+
+rescue => ex
+  puts ex, ex.backtrace
+ensure
+  pool.disconnect!
+end