このシリーズについて

Rails 6.0 の DatabaseSelector middleware を理解する ①使ってみる （この記事）
- DatabaseSelector を使ってみて、挙動を確認する。
Rails 6.0 の DatabaseSelector middleware を理解する ②読んでみる（次回公開）
- DatabaseSelector のコードリーディングをして、仕組みを理解する。

目的

ActiveRecord::Middleware::DatabaseSelector のコードリーディングを通して、その仕組みを理解して使えるようになる。
DatabaseSelector を知ることで、 Ruby on Rails 6.0 の複数データベース対応について理解する。

要約

HTTP GET / HTTP HEAD では :reading
それ以外は :writing
:writing を使って数秒間は HTTP メソッドにかかわらず :writing を使わせる

サンプルコード

PostgreSQL の非同期レプリケーション
Rails 6.0 の Multiple DBs 機能
DatabaseSelector を使った HTTP Method による接続先自動切り替え

確認環境

Ruby on Rails 6.0
Docker 19.03.2 on WSL2

DatabaseSelector

DatabaseSelector は、Railsガイドの3 コネクションの自動切り替えを有効にするが詳しいです。

今回はサンプルアプリを作り、実際に手元で動かして、挙動を確認してみました。

DatabaseSelector を設定する

ロールと接続先

まずは複数データベース接続設定を書きます。

1 アプリケーションのセットアップ - Active Record で複数のデータベース利用

app/modles/application_record.rb

class ApplicationRecord < ActiveRecord::Base
  self.abstract_class = true
  connects_to database: { writing: :primary, reading: :primary_readonly }
end

Role: :reading :writing Railsにより提供される。変更もできるが、基本このまま。
Specification: :primary :primary_readonly は database.yml に記載した接続先名。 :animal :replica など、わかりやすいようにつけられます。
詳しくは Railsガイド

config/database.yml

default: &default
  adapter: postgresql
  encoding: unicode
  # For details on connection pooling, see Rails configuration guide
  # https://guides.rubyonrails.org/configuring.html#database-pooling
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>

development:
  # 仮にこれを `hoge:` にすると `schema.rb` は `hoge_schema.rb` になる。
  primary:
    <<: *default
    database: MyApp_development
    host: <%= ENV.fetch('DATABASE_HOST_PRIMARY') { 'db' } %>
    username: <%= ENV.fetch('DATABASE_USER') { 'postgres' } %>
    password: <%= ENV.fetch('DATABASE_PASSWORD') { 'password' } %>
  primary_readonly:
    <<: *default
    database: MyApp_development
    host: <%= ENV.fetch('DATABASE_HOST_READONLY') { 'db' } %>
    username: <%= ENV.fetch('DATABASE_USER') { 'postgres' } %>
    password: <%= ENV.fetch('DATABASE_PASSWORD') { 'password' } %>
    replica: true

DatabaseSelector を有効にする

DatabaseSelector ミドルウェアを使うには、次のようにします。
少し冗長に思えますが、これによりカスタマイズの余地が生まれています。（次回解説します）

  config.active_record.database_selector = { delay: 2.seconds }
  config.active_record.database_resolver = ActiveRecord::Middleware::DatabaseSelector::Resolver
  config.active_record.database_resolver_context = ActiveRecord::Middleware::DatabaseSelector::Resolver::Session

DatabaseSelector の挙動を確認する

サンプルアプリを起動して、どこにどんなクエリが飛ぶか見てみます。

HTTP GET / HTTP HEAD では :reading

GET http://localhost:3000/posts/1/edit をみてみます。

このとき、ログから :reading に指定したレプリカを使用したことがわかります。

app_1          | Started GET "/posts/1/edit" for 172.21.0.1 at 2019-10-03 19:17:58 +0000
app_1          | Cannot render console from 172.21.0.1! Allowed networks: 127.0.0.0/127.255.255.255, ::1
pg_primary_1   | 2019-10-03 19:17:58.241 UTC [27] LOG:  statement: SELECT 1
app_1          | Processing by PostsController#edit as HTML
app_1          |   Parameters: {"id"=>"1"}
pg_readonly_1  | 2019-10-03 19:17:58.243 UTC [28] LOG:  statement: SELECT 1
pg_readonly_1  | 2019-10-03 19:17:58.243 UTC [28] LOG:  execute a1: SELECT "posts".* FROM "posts" WHERE "posts"."id" = $1 LIMIT $2
pg_readonly_1  | 2019-10-03 19:17:58.243 UTC [28] DETAIL:  parameters: $1 = '1', $2 = '1'
app_1          |   Post Load (0.4ms)  SELECT "posts".* FROM "posts" WHERE "posts"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]
app_1          |   ↳ app/controllers/posts_controller.rb:67:in `set_post'
app_1          |   Rendering posts/edit.html.erb within layouts/application
app_1          |   Rendered posts/_form.html.erb (Duration: 1.2ms | Allocations: 747)
app_1          |   Rendered posts/edit.html.erb within layouts/application (Duration: 1.5ms | Allocations: 867)
app_1          | Completed 200 OK in 8ms (Views: 5.4ms | ActiveRecord: 0.4ms | Allocations: 7126)

HTTP GET / HTTP HEAD 以外では :writing

では POST http://localhost:3000/posts ではどうでしょうか？
記事を作成してみます。

このときのログは次のように、先ほどの GET とは異なり、:writing に設定したプライマリを使ったことがわかります。

app_1          | Started POST "/posts" for 172.21.0.1 at 2019-10-03 19:21:22 +0000
app_1          | Cannot render console from 172.21.0.1! Allowed networks: 127.0.0.0/127.255.255.255, ::1
pg_primary_1   | 2019-10-03 19:21:22.039 UTC [27] LOG:  statement: SELECT 1
app_1          | Processing by PostsController#create as HTML
app_1          |   Parameters: {"authenticity_token"=>"BHkgkW4WTj5JAdB4DZ6PmS2ZEq9bDHmSzZ3900V4roTY6CDKHUhd3savRJCag0oaXOlQKlVyd8BPsl9VFhTZQg==", "post"=>{"title"=>"Hello Rails 6.0", "body"=>""}, "commit"=>"Create Post"}
pg_primary_1   | 2019-10-03 19:21:22.042 UTC [27] LOG:  statement: BEGIN
app_1          |    (0.3ms)  BEGIN
app_1          |   ↳ app/controllers/posts_controller.rb:30:in `block in create'
pg_primary_1   | 2019-10-03 19:21:22.043 UTC [27] LOG:  execute <unnamed>: INSERT INTO "posts" ("title", "body", "created_at", "updated_at") VALUES ($1, $2, $3, $4) RETURNING "id"
pg_primary_1   | 2019-10-03 19:21:22.043 UTC [27] DETAIL:  parameters: $1 = 'Hello Rails 6.0', $2 = '', $3 = '2019-10-03 19:21:22.041603', $4 = '2019-10-03 19:21:22.041603'
app_1          |   Post Create (0.9ms)  INSERT INTO "posts" ("title", "body", "created_at", "updated_at") VALUES ($1, $2, $3, $4) RETURNING "id"  [["title", "Hello Rails 6.0"], ["body", ""], ["created_at", "2019-10-03 19:21:22.041603"], ["updated_at", "2019-10-03 19:21:22.041603"]]
app_1          |   ↳ app/controllers/posts_controller.rb:30:in `block in create'
pg_primary_1   | 2019-10-03 19:21:22.045 UTC [27] LOG:  statement: COMMIT
app_1          |    (0.5ms)  COMMIT
app_1          |   ↳ app/controllers/posts_controller.rb:30:in `block in create'
app_1          | Redirected to http://localhost:3000/posts/2
app_1          | Completed 302 Found in 6ms (ActiveRecord: 1.7ms | Allocations: 2959)

:writing へのクエリ発行直後は HTTP GET でも :writing

次のログは、記事を更新後、リダイレクト先で更新した記事を表示したときのログです。

ログから、 GET でも :reading ではなく :writing を使用したことがわかります。

非同期レプリケーションの場合、プライマリでの更新直後、レプリカへの変更が反映されていない可能性があり、これはユーザーに対して期待と異なる挙動を示すため、それを防ぐための措置だと思います。

app_1          | Started PATCH "/posts/2" for 172.21.0.1 at 2019-10-03 19:26:39 +0000
app_1          | Cannot render console from 172.21.0.1! Allowed networks: 127.0.0.0/127.255.255.255, ::1
pg_primary_1   | 2019-10-03 19:26:39.776 UTC [62] LOG:  statement: SELECT 1
app_1          | Processing by PostsController#update as HTML
app_1          |   Parameters: {"authenticity_token"=>"BDy9M4hAOSryBYPDpx3vu/bx03w9GatkTFO2GsBH0GneYILF6Y+pmk7UxF/3WVnr3Cosup5/EDRM3zVS10WdZA==", "post"=>{"title"=>"Hello Rails 6.0!", "body"=>""}, "commit"=>"Update Post", "id"=>"2"}
pg_primary_1   | 2019-10-03 19:26:39.779 UTC [62] LOG:  statement: SHOW search_path
pg_primary_1   | 2019-10-03 19:26:39.780 UTC [62] LOG:  execute a1: SELECT "posts".* FROM "posts" WHERE "posts"."id" = $1 LIMIT $2
pg_primary_1   | 2019-10-03 19:26:39.780 UTC [62] DETAIL:  parameters: $1 = '2', $2 = '1'
app_1          |   Post Load (0.8ms)  SELECT "posts".* FROM "posts" WHERE "posts"."id" = $1 LIMIT $2  [["id", 2], ["LIMIT", 1]]
app_1          |   ↳ app/controllers/posts_controller.rb:67:in `set_post'
pg_primary_1   | 2019-10-03 19:26:39.783 UTC [62] LOG:  statement: BEGIN
app_1          |    (0.4ms)  BEGIN
app_1          |   ↳ app/controllers/posts_controller.rb:44:in `block in update'
pg_primary_1   | 2019-10-03 19:26:39.786 UTC [62] LOG:  execute <unnamed>: UPDATE "posts" SET "title" = $1, "updated_at" = $2 WHERE "posts"."id" = $3
pg_primary_1   | 2019-10-03 19:26:39.786 UTC [62] DETAIL:  parameters: $1 = 'Hello Rails 6.0!', $2 = '2019-10-03 19:26:39.783058', $3 = '2'
app_1          |   Post Update (0.7ms)  UPDATE "posts" SET "title" = $1, "updated_at" = $2 WHERE "posts"."id" = $3  [["title", "Hello Rails 6.0!"], ["updated_at", "2019-10-03 19:26:39.783058"], ["id", 2]]
app_1          |   ↳ app/controllers/posts_controller.rb:44:in `block in update'
pg_primary_1   | 2019-10-03 19:26:39.788 UTC [62] LOG:  statement: COMMIT
app_1          |    (0.4ms)  COMMIT
app_1          |   ↳ app/controllers/posts_controller.rb:44:in `block in update'
app_1          | Redirected to http://localhost:3000/posts/2
app_1          | Completed 302 Found in 12ms (ActiveRecord: 2.8ms | Allocations: 3467)
app_1          |
app_1          |
app_1          | Started GET "/posts/2" for 172.21.0.1 at 2019-10-03 19:26:39 +0000
app_1          | Cannot render console from 172.21.0.1! Allowed networks: 127.0.0.0/127.255.255.255, ::1
pg_primary_1   | 2019-10-03 19:26:39.801 UTC [62] LOG:  statement: SELECT 1
app_1          | Processing by PostsController#show as HTML
app_1          |   Parameters: {"id"=>"2"}
pg_primary_1   | 2019-10-03 19:26:39.803 UTC [62] LOG:  execute a1: SELECT "posts".* FROM "posts" WHERE "posts"."id" = $1 LIMIT $2
pg_primary_1   | 2019-10-03 19:26:39.803 UTC [62] DETAIL:  parameters: $1 = '2', $2 = '1'
app_1          |   Post Load (0.5ms)  SELECT "posts".* FROM "posts" WHERE "posts"."id" = $1 LIMIT $2  [["id", 2], ["LIMIT", 1]]
app_1          |   ↳ app/controllers/posts_controller.rb:67:in `set_post'
app_1          |   Rendering posts/show.html.erb within layouts/application
app_1          |   Rendered posts/show.html.erb within layouts/application (Duration: 0.3ms | Allocations: 94)
app_1          | Completed 200 OK in 7ms (Views: 4.8ms | ActiveRecord: 0.5ms | Allocations: 6362)

更新系クエリでない場合も、:writing を強制するか？

レプリカへの変更が反映されていない可能性への対応であるならば、 :writing へのクエリ発行直後であっても、そのクエリが INSERT などの更新系クエリでなければ :reading でよいのではないか？そう考えて試してみます。

ログによると、 PATCH により :writing が SELECT に使われたことがわかりますが、変更がなかったため UPDATE は発行されませんでした。
しかし、それでも次の GET では :writing を使っています。

このことから、更新系クエリでない場合も「 :writing を使ったら、しばらく :reading 」と考えて良いようです。

app_1          | Started PATCH "/posts/2" for 172.21.0.1 at 2019-10-03 19:36:14 +0000
app_1          | Cannot render console from 172.21.0.1! Allowed networks: 127.0.0.0/127.255.255.255, ::1
pg_primary_1   | 2019-10-03 19:36:14.234 UTC [91] LOG:  statement: SELECT 1
app_1          | Processing by PostsController#update as HTML
app_1          |   Parameters: {"authenticity_token"=>"gcEcnplYdoDMYrET0VxW3hBpLPqEtETt56OSj+QPHZ9bnSNo+JfmMHCz9o+BGOCOOrLTPCfS/73nLxHH8w1Qkg==", "post"=>{"title"=>"Hello Rails 6.0!", "body"=>""}, "commit"=>"Update Post", "id"=>"2"}
pg_primary_1   | 2019-10-03 19:36:14.236 UTC [91] LOG:  statement: SHOW search_path
pg_primary_1   | 2019-10-03 19:36:14.236 UTC [91] LOG:  execute a1: SELECT "posts".* FROM "posts" WHERE "posts"."id" = $1 LIMIT $2
pg_primary_1   | 2019-10-03 19:36:14.236 UTC [91] DETAIL:  parameters: $1 = '2', $2 = '1'
app_1          |   Post Load (0.4ms)  SELECT "posts".* FROM "posts" WHERE "posts"."id" = $1 LIMIT $2  [["id", 2], ["LIMIT", 1]]
app_1          |   ↳ app/controllers/posts_controller.rb:67:in `set_post'
app_1          | Redirected to http://localhost:3000/posts/2
app_1          | Completed 302 Found in 4ms (ActiveRecord: 0.7ms | Allocations: 1312)
app_1          |
app_1          |
app_1          | Started GET "/posts/2" for 172.21.0.1 at 2019-10-03 19:36:14 +0000
app_1          | Cannot render console from 172.21.0.1! Allowed networks: 127.0.0.0/127.255.255.255, ::1
pg_primary_1   | 2019-10-03 19:36:14.247 UTC [91] LOG:  statement: SELECT 1
app_1          | Processing by PostsController#show as HTML
app_1          |   Parameters: {"id"=>"2"}
pg_primary_1   | 2019-10-03 19:36:14.249 UTC [91] LOG:  execute a1: SELECT "posts".* FROM "posts" WHERE "posts"."id" = $1 LIMIT $2
pg_primary_1   | 2019-10-03 19:36:14.249 UTC [91] DETAIL:  parameters: $1 = '2', $2 = '1'
app_1          |   Post Load (0.4ms)  SELECT "posts".* FROM "posts" WHERE "posts"."id" = $1 LIMIT $2  [["id", 2], ["LIMIT", 1]]
app_1          |   ↳ app/controllers/posts_controller.rb:67:in `set_post'
app_1          |   Rendering posts/show.html.erb within layouts/application
app_1          |   Rendered posts/show.html.erb within layouts/application (Duration: 0.3ms | Allocations: 94)
app_1          | Completed 200 OK in 9ms (Views: 7.5ms | ActiveRecord: 0.4ms | Allocations: 6364)

HTTP GET でも :writing 使いたいとき

実務ではいろいろあるものです。
コネクションを手動で切り替えることで、明示的に :writing を使うことはできます。

  # GET /posts/1
  # GET /posts/1.json
  def show
    ActiveRecord::Base.connected_to(role: :writing) do
      @post = Post.lock.find(params[:id])
      @post.touch
    end
  end

app_1          | Started GET "/posts/2" for 172.21.0.1 at 2019-10-03 20:34:20 +0000
app_1          | Cannot render console from 172.21.0.1! Allowed networks: 127.0.0.0/127.255.255.255, ::1
pg_primary_1   | 2019-10-03 20:34:20.236 UTC [35] LOG:  statement: SELECT 1
app_1          | Processing by PostsController#show as HTML
app_1          |   Parameters: {"id"=>"2"}
pg_primary_1   | 2019-10-03 20:34:20.239 UTC [35] LOG:  execute <unnamed>: SELECT "posts".* FROM "posts" WHERE "posts"."id" = $1 LIMIT $2 FOR UPDATE
pg_primary_1   | 2019-10-03 20:34:20.239 UTC [35] DETAIL:  parameters: $1 = '2', $2 = '1'
app_1          |   Post Load (0.5ms)  SELECT "posts".* FROM "posts" WHERE "posts"."id" = $1 LIMIT $2 FOR UPDATE  [["id", 2], ["LIMIT", 1]]
app_1          |   ↳ app/controllers/posts_controller.rb:14:in `block in show'
pg_primary_1   | 2019-10-03 20:34:20.240 UTC [35] LOG:  statement: BEGIN
app_1          |    (0.3ms)  BEGIN
app_1          |   ↳ app/controllers/posts_controller.rb:15:in `block in show'
pg_primary_1   | 2019-10-03 20:34:20.242 UTC [35] LOG:  execute <unnamed>: UPDATE "posts" SET "updated_at" = $1 WHERE "posts"."id" = $2
app_1          |   Post Update (0.5ms)  UPDATE "posts" SET "updated_at" = $1 WHERE "posts"."id" = $2  [["updated_at", "2019-10-03 20:34:20.240568"], ["id", 2]]
pg_primary_1   | 2019-10-03 20:34:20.242 UTC [35] DETAIL:  parameters: $1 = '2019-10-03 20:34:20.240568', $2 = '2'
app_1          |   ↳ app/controllers/posts_controller.rb:15:in `block in show'
pg_primary_1   | 2019-10-03 20:34:20.243 UTC [35] LOG:  statement: COMMIT
app_1          |    (0.3ms)  COMMIT
app_1          |   ↳ app/controllers/posts_controller.rb:15:in `block in show'
app_1          |   Rendering posts/show.html.erb within layouts/application
app_1          |   Rendered posts/show.html.erb within layouts/application (Duration: 0.3ms | Allocations: 90)
app_1          | Completed 200 OK in 10ms (Views: 4.3ms | Allocations: 8429)

次回

DatabaseSelector のコードリーディングを通して、なぜ今回のような挙動になったか？その仕組みを紐解きます。

参考

Active Record で複数のデータベース利用 - Railsガイド