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

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 ** スクリーンショット 2020-10-31 4.17.26.png

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.

スクリーンショット 2020-10-31 14.17.32.png

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


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

    <<: *default
    database: rails_app_common_development
    migrations_paths: db/common_migrate
    <<: *default
    database: rails_app_common_development
    replica: true
    <<: *default
    database: rails_app_school_development
    migrations_paths: db/school_migrate
    <<: *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.


class CommonBase < ApplicationRecord
  self.abstract_class = true

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

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


class SchoolBase < ApplicationRecord
  self.abstract_class = true

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

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

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


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

--Write the following in 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)

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


$ curl localhost:3000/users

スクリーンショット 2020-10-31 18.40.58.png


$ curl localhost:3000/users/1

スクリーンショット 2020-10-31 18.41.09.png


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

スクリーンショット 2020-10-31 18.47.08.png


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

スクリーンショット 2020-10-31 18.48.18.png


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

スクリーンショット 2020-10-31 18.48.41.png

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)


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


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

Recommended Posts

Use multiple databases with Rails 6.0
Use multiple checkboxes in Rails6!
[Docker] Use whenever with Docker + Rails
[Rails] How to use rails console with docker
[Rails] Use jQuery
Upload multiple images easily with rails rails + carrierwave + cloudinary
[Rails] Procedure for linking databases with Ruby On Rails
Output simple graph with Rails Use gem-chartkick / groupdate
[Ruby on Rails] Upload multiple images with refile
Use ProGuard with Gradle
Rails deploy with Docker
[Rails 6] RuntimeError with $ rails s
Use Puphpeteer with Docker
Use XVim2 with Xcode 12.0.1
Use CentOS with LXD
Handle devise with Rails
[Rails] Learning with Rails tutorial
Use ngrok with Docker
[Rails] Test with RSpec
Use webmock with Rspec
Use images in Rails
[Rails] Development with MySQL
Use WebJars with Gradle
Supports multilingualization with Rails!
Use jlink with gradle
Double polymorphic with Rails
[Rails] How to search by multiple values ​​with LIKE
Use Lambda Layers with Java
Use GDAL with Python with Docker
Why can I use the rails command installed with gem? ??
Use Thymeleaf with Azure Functions
Introduced graph function with rails
[Rails] Express polymorphic with graphql-ruby
[Rails] Upload videos with Rails (ActiveStorage)
Try using view_component with rails
[Vue Rails] "Hello Vue!" Displayed with Vue + Rails
[RSpec] Let's use FactoryBot [Rails]
[Rails] How to use enum
Japaneseize using i18n with Rails
Use Bulk API with RestHighLevelClient
API creation with Rails + GraphQL
Preparation for developing with Rails
Use SDKMAN! With Git Bash
How to use rails join
Run Rails whenever with docker
Use devise on multiple models
[Docker] Rails 5.2 environment construction with docker
[Rails] Specify format with link_to
Login function implementation with rails
[Rails] How to use validation
Use Spring JDBC with Spring Boot
[Rails] Search from multiple columns + conditions with Gem and ransack
[Rails] How to use authenticate_user!
[Rails] How to use "kaminari"
Use Ruby with Google Colab
Use SpatiaLite with Java / JDBC
Use log4j2 with YAML + Gradle
Use your own classes in the lib directory with Rails6
[Rails] How to use Scope
Use PlantUML with Visual Studio Code
[Rails] How to use gem "devise"