🤖

Multi DB を試す...!

2022/02/12に公開

Rails

multidb

tech

Multi DBを試しみる。

ここらへんの話
https://railsguides.jp/active_record_multiple_databases.html

DB準備

設定 ( config / database.yml )

Replica についてDB接続ユーザを分けておいた方が良いとのことだが、RDS AuroraでRead Endpointを指定する事ができるので、ユーザパスワードは共通でも良いという前提で Host のみそれぞれ指定する形にした。

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

development:
  primary:
    <<: *default
    database: multidb_primary
    host: 127.0.0.1
    port: 3306
  primary_replica:
    <<: *default
    database: multidb_primary
    host: 127.0.0.1
    port: 13306
    replica: true
  animals:
    <<: *default
    database: multidb_animals
    host: 127.0.0.1
    port: 3307
    migrations_paths: db/animals_migrate
  animals_replica:
    <<: *default
    database: multidb_animals
    host: 127.0.0.1
    port: 13307
    replica: true

Docker-compose

version: '3'

services:
  db-w1:
    image: mysql:5.7
    volumes:
      - db-volume-w1:/var/lib/mysql
    environment:
      MYSQL_ROOT_PASSWORD: password
    ports:
      - 3306:3306
  db-r1:
    image: mysql:5.7
    volumes:
      - db-volume-r1:/var/lib/mysql
    environment:
      MYSQL_ROOT_PASSWORD: password
    ports:
      - 13306:3306

  db-w2:
    image: mysql:5.7
    volumes:
      - db-volume-w2:/var/lib/mysql
    environment:
      MYSQL_ROOT_PASSWORD: password
    ports:
      - 3307:3306
  db-r2:
    image: mysql:5.7
    volumes:
      - db-volume-r2:/var/lib/mysql
    environment:
      MYSQL_ROOT_PASSWORD: password
    ports:
      - 13307:3306

volumes:
  db-volume-w1:
  db-volume-r1:
  db-volume-w2:
  db-volume-r2:

Primary Model 設定 ( app / models / application_record.rb )

class ApplicationRecord < ActiveRecord::Base
  self.abstract_class = true

  connects_to database: { writing: :primary, reading: :primary_replica }
end

Anime Model 設定 ( app / models / animal_record.rb )

class AnimalsRecord < ApplicationRecord
  self.abstract_class = true

  connects_to database: { writing: :animals, reading: :animals_replica }
end

手動で作成したが、 config/databases.yml で定義を追加して、 generate をすると自動でファイル生成される...？
- generate した時に _record.rb 作成されるぅぅぅぅぅ。
- と、いうことは別途手動で用意する必要ないやんか。
```
create    db/animals_migrate/20220212053210_create_animals.rb
create    app/models/animals_record.rb
create    app/models/animal.rb
```

確認

rails -T

db 周りで複数DB用がニョキニョキ生えた

rails db:create                          # Creates the database from DATABASE_URL or config/database.yml for the current RAILS_ENV (use db:create:all to create all databases in the config)....
rails db:create:animals                  # Create animals database for current environment
rails db:create:primary                  # Create primary database for current environment
rails db:drop                            # Drops the database from DATABASE_URL or config/database.yml for the current RAILS_ENV (use db:drop:all to drop all databases in the config). Witho...
rails db:drop:animals                    # Drop animals database for current environment
rails db:drop:primary                    # Drop primary database for current environment
rails db:environment:set                 # Set the environment value for the database
rails db:fixtures:load                   # Loads fixtures into the current environment's database
rails db:migrate                         # Migrate the database (options: VERSION=x, VERBOSE=false, SCOPE=blog)
rails db:migrate:animals                 # Migrate animals database for current environment
rails db:migrate:down                    # Runs the "down" for a given migration VERSION
rails db:migrate:primary                 # Migrate primary database for current environment
rails db:migrate:redo                    # Rolls back the database one migration and re-migrates up (options: STEP=x, VERSION=x)
rails db:migrate:redo:animals            # Rolls back animals database one migration and re-migrates up (options: STEP=x, VERSION=x)
rails db:migrate:redo:primary            # Rolls back primary database one migration and re-migrates up (options: STEP=x, VERSION=x)
rails db:migrate:status                  # Display status of migrations
rails db:migrate:status:animals          # Display status of migrations for animals database
rails db:migrate:status:primary          # Display status of migrations for primary database
rails db:migrate:up                      # Runs the "up" for a given migration VERSION
rails db:prepare                         # Runs setup if database does not exist, or runs migrations if it does
rails db:reset                           # Drops and recreates the database from db/schema.rb for the current environment and loads the seeds
rails db:rollback                        # Rolls the schema back to the previous version (specify steps w/ STEP=n)
rails db:rollback:animals                # Rollback animals database for current environment (specify steps w/ STEP=n)
rails db:rollback:primary                # Rollback primary database for current environment (specify steps w/ STEP=n)
rails db:schema:cache:clear              # Clears a db/schema_cache.yml file
rails db:schema:cache:dump               # Creates a db/schema_cache.yml file
rails db:schema:dump                     # Creates a database schema file (either db/schema.rb or db/structure.sql, depending on `config.active_record.schema_format`)
rails db:schema:dump:animals             # Creates a database schema file (either db/schema.rb or db/structure.sql, depending on `config.active_record.schema_format`) for animals database
rails db:schema:dump:primary             # Creates a database schema file (either db/schema.rb or db/structure.sql, depending on `config.active_record.schema_format`) for primary database
rails db:schema:load                     # Loads a database schema file (either db/schema.rb or db/structure.sql, depending on `config.active_record.schema_format`) into the database
rails db:schema:load:animals             # Loads a database schema file (either db/schema.rb or db/structure.sql, depending on `config.active_record.schema_format`) into the animals database
rails db:schema:load:primary             # Loads a database schema file (either db/schema.rb or db/structure.sql, depending on `config.active_record.schema_format`) into the primary database
rails db:seed                            # Loads the seed data from db/seeds.rb
rails db:seed:replant                    # Truncates tables of each database for current environment and loads the seeds
rails db:setup                           # Creates the database, loads the schema, and initializes with the seed data (use db:reset to also drop the database first)
rails db:structure:dump                  # Dumps the database structure to db/structure.sql
rails db:structure:dump:animals          # Dumps the animals database structure to db/structure.sql
rails db:structure:dump:primary          # Dumps the primary database structure to db/structure.sql
rails db:structure:load                  # Recreates the databases from the structure.sql file
rails db:structure:load:animals          # Recreates the animals database from the structure.sql file
rails db:structure:load:primary          # Recreates the primary database from the structure.sql file
rails db:version                         # Retrieves the current schema version number

Generate Model

rails g model user name:string email:string               

Running via Spring preloader in process 48808
      invoke  active_record
      create    db/migrate/20220212053111_create_users.rb
      create    app/models/user.rb

rails g model animal name:string type:string --database animals

Running via Spring preloader in process 48847
      invoke  active_record
      create    db/animals_migrate/20220212053210_create_animals.rb
      create    app/models/animals_record.rb
      create    app/models/animal.rb
strsbn@MacBook-Air multidb %

Migration file

primary はいつもの DB 直下のところ
animals は migrations_paths: db/animals_migrate で指定したところに作成される。

db:create

一気に db:create しようとするとなぜか created 表示の後に Connection Error が出る謎
- と、思ったが、よくよくエラーをみると ENV:test については用意してないからあたりまえですね...🥶

rails db:create
Running via Spring preloader in process 48898
Created database 'multidb_primary'
Created database 'multidb_animals'
Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
Couldn't create 'multidb_test' database. Please check your configuration.
rake aborted!
ActiveRecord::ConnectionNotEstablished: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
<internal:/Users/strsbn/.rbenv/versions/3.0.3/lib/ruby/3.0.0/rubygems/core_ext/kernel_require.rb>:85:in `require'
<internal:/Users/strsbn/.rbenv/versions/3.0.3/lib/ruby/3.0.0/rubygems/core_ext/kernel_require.rb>:85:in `require'
-e:1:in `<main>'

Caused by:
Mysql2::Error::ConnectionError: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
<internal:/Users/strsbn/.rbenv/versions/3.0.3/lib/ruby/3.0.0/rubygems/core_ext/kernel_require.rb>:85:in `require'
<internal:/Users/strsbn/.rbenv/versions/3.0.3/lib/ruby/3.0.0/rubygems/core_ext/kernel_require.rb>:85:in `require'
-e:1:in `<main>'
Tasks: TOP => db:create
(See full trace by running task with --trace)

db 指定 db:create:primary, db:create:animals すると問題ない

rails db:create:primary
Running via Spring preloader in process 49131
Database 'multidb_primary' already exists

rails db:create:animals              
Running via Spring preloader in process 49114
Database 'multidb_animals' already exists

db:migrate

こちらは問題なく...？

rails db:migrate                     
Running via Spring preloader in process 49079
== 20220212053111 CreateUsers: migrating ======================================
-- create_table(:users)
   -> 0.0255s
== 20220212053111 CreateUsers: migrated (0.0256s) =============================

== 20220212053210 CreateAnimals: migrating ====================================
-- create_table(:animals)
   -> 0.0166s
== 20220212053210 CreateAnimals: migrated (0.0166s) ===========================

Model 見比べ

継承元が異なるので、どちらの database を使用しているのか Code から判別することができる。

ApplicationRecord を継承している

# == Schema Information
#
# Table name: users
#
#  id         :bigint           not null, primary key
#  email      :string(255)
#  name       :string(255)
#  created_at :datetime         not null
#  updated_at :datetime         not null
#
class User < ApplicationRecord
end

AnimalsRecord を継承している

# == Schema Information
#
# Table name: animals
#
#  id         :bigint           not null, primary key
#  name       :string(255)
#  type       :string(255)
#  created_at :datetime         not null
#  updated_at :datetime         not null
#
class Animal < AnimalsRecord
end

Join 制約

完全にできなかった異なる Database 間での Join がrails 7から一部緩和されたっぽい。
が、それでも制約があるのでなるべく切り離して管理できるかつ利用頻度の高いデータを分けないと意味がなさそう。（試してないので雰囲気）
https://railsguides.jp/active_record_multiple_databases.html#データベース間でjoinする関連付けを扱う

感想

途中から database を分けるのは大変じゃないかなー。
- でも　さいしょから、ぶんかつぜんてい、できないよ。
見越して設計できつつ、費用を抑えるならばインスタンス同居で、database_schema だけ先に分けておき負荷次第で別インスタンスに切り出すぐらいかなぁ...。

Multi DBを試しみる。

DB準備

設定 ( config / database.yml )

Docker-compose

Primary Model 設定 ( app / models / application_record.rb )

Anime Model 設定 ( app / models / animal_record.rb )

確認

Generate Model

Migration file

db:create

db:migrate

Model 見比べ

ApplicationRecord を継承している

AnimalsRecord を継承している

Join 制約

感想

Discussion