Use multiple databases with Rails 6.0

Starting with Rails 6.0, multiple databases are available as standard functionality Using multiple databases has the advantages of making it easier to scale when the scale of the project grows and increasing the number of connections.

In this article, I will try using two databases in a Rails application and how to use a replica of the database.

The created source code is published on GitHub https://github.com/youichiro/rails-multiple-db-sandbox

What i did

--Use of multiple databases --Create common database and school database --Create a model for each database --Use primary / replica --Prepare a replica of the common database and a replica of the school database --The GET request confirms that the replica is called --Confirmed that JOIN between tables in different databases is not possible

Multiple databases

Multiple databases is a mechanism for reading and writing data by connecting to multiple databases from one application. When there are two DBs, database A and database B, Rails can ** switch the database to connect to depending on the model to call **

primary / replica database

It is a mechanism to prepare a read-only replica for one database and switch between primary and replica according to the request. When the number of accesses to the database increases, the access load can be distributed by separating the database for writing and the DB for reading. Rails automatically switches ** POST, PUT, PATCH, DELETE requests to primary **, and ** GET, HEAD requests to replica ** if there is no recent write.

Database settings

The config / database.yml when creating a database like this looks like this:

--common database --common database replica --school database --replica of school database

config/database.yml

default: &default
  adapter: mysql2
  encoding: utf8mb4
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  username: root
  password:
  host: localhost
  port: 3306

development:
  common:
    <<: *default
    database: rails_app_common_development
    migrations_paths: db/common_migrate
  common_replica:
    <<: *default
    database: rails_app_common_development
    replica: true
  school:
    <<: *default
    database: rails_app_school_development
    migrations_paths: db/school_migrate
  school_replica:
    <<: *default
    database: rails_app_school_development
    replica: true

For the primary database, migrations_paths specifies the storage location of the migration file. Specify replica: true for replica

Create a database with this setting

$ bin/rails db:create

Create an abstract class for your model

Switch the database to connect depending on the model to call Create an abstract class that is the basis of the model that connects to the common database, and describe the settings for connecting to the database.

app/models/common_base.rb

class CommonBase < ApplicationRecord
  self.abstract_class = true

  connects_to database: { writing: :common, reading: :common_replica }
end

Create a CommonBase class that inherits ApplicationRecord, and specify the DB at the time of writing and the DB at the time of reading with connections_to.

Create an abstract class of the model that connects to the school database as well

app/models/school_base.rb

class SchoolBase < ApplicationRecord
  self.abstract_class = true

  connects_to database: { writing: :school, reading: :school_replica }
end

Make sure to inherit either CommonBase or SchoolBase when creating a new model This allows you to switch the database connection destination depending on the model.

Creating a model

This is the procedure for creating a ʻUser model` in the common database. First, create a model file and a migration file with the generate model command.

$ bin/rails g model user name:string school:references --database common

Running via Spring preloader in process 54763
      invoke  active_record
      create    db/common_migrate/20201030135726_create_users.rb
      create    app/models/user.rb
      invoke    test_unit
      create      test/models/user_test.rb
      create      test/fixtures/users.yml

Migration file created in db / common_migrate directory By specifying the database to connect to --database, it will be created in migrations_paths set in database.yml. If you want to create a model in the school database, specify --database school

Then do the migration

#When applying all migration files
$ bin/rails db:migrate

#When applying only the migration file of the common database
$ bin/rails db:migrate:common

Finally change the model file At the time of generation, it inherits ApplicationRecord, but since the User model wants to use the common database, change it to inherit CommonBase.

- class User < ApplicationRecord
+ class User < CommonBase
  end

Now you can read and write from the common database for the User model.

Check if primary / replica is switched by request

By preparing a replica, POST, PUT, DELETE, PATCH requests will be written to primary, and GET, HEAD requests will be read from replica. To check this, use arproxy to display the database connection status in the query log.

arproxy settings

--Add gem arproxy to Gemfile and bundle install --Write the following in config / initializers / arproxy.rb

config/initializers/arproxy.rb

if Rails.env.development? || Rails.env.test?
 require 'multiple_database_connection_logger'
 Arproxy.configure do |config|
   config.adapter = 'mysql2'
   config.use MultipleDatabaseConnectionLogger
 end
 Arproxy.enable!
end

--Write the following in lib / multiple_database_connection_logger.rb

lib/multiple_database_connection_logger.rb

class MultipleDatabaseConnectionLogger < Arproxy::Base
 def execute(sql, name = nil)
  role = ActiveRecord::Base.current_role
  name = "#{name} [#{role}]"
  super(sql, name)
 end
end

Check the database connection status at the time of request

If you send a request from curl and look at the log, you can see if it was written or reading. Try with the pre-created users_controller

index

$ curl localhost:3000/users

show

$ curl localhost:3000/users/1

create

$ curl -X POST -H 'Content-Type: application/json' -d '{"name": "saito", "school_id": 1}' localhost:3000/users

update

$ curl -X PUT -H 'Content-Type: application/json' -d '{"name": "saito(updated)"}' localhost:3000/users/5

destroy

$ curl -X DELETE http://localhost:3000/users/5

In the case of index, show action, it is reading, in the case of create, update, destroy action, it is writing, and you can see that primary / replica is switched.

Check the behavior of JOIN

You can join between tables in the same database

If you JOIN the students table to the grade table, you can JOIN because it is the same database

Grade.joins(:students).where(name: 'grade1')

SQL issued

SELECT `grades`.*
FROM `grades`
INNER JOIN `students` ON `students`.`grade_id` = `grades`.`id`
WHERE `grades`.`name` = 'grade1

Cannot JOIN between tables in different databases

If you try to JOIN the students table to the users table, you cannot JOIN because it is a different database

User.joins(:students).where(name: 'ogawa')

Error that occurs

ActiveRecord::StatementInvalid (Mysql2::Error: Table 'rails_app_common_development.students' doesn't exist)

finally

Looking forward to the sharding features that will be supported from Rails 6.1

reference

-Rails Guide | Using Multiple Databases with Active Record -I tried using multiple database of Rails 6.0