NULL嫌いのUPDATEしないDB設計 #DBSekkeiNight / DB design without updating

DB設計したいNight #6 正規化 [online]
https://dbnight.connpass.com/event/177859/

Transcript

1. NULL嫌いのUPDATEしないDB設計
   DB設計したいNight 2020/06/04(木)
   神速(@sinsoku)
   1
   

   View Slide

2. 自己紹介
   • 名前: 神速
   • 会社: メドピア株式会社
   • GitHub: @sinsoku (画像右上)
   • Twitter: @sinsoku_listy (画像右下)
   • Rails歴: 7年くらい
   • 好きなDB: PostgreSQL, Redis
   • 嫌いなもの: NULL
   2
   

   View Slide

3. 話すこと
   1. NULLについて
   2. NULLの進入を防ぐ
   3. 具体的な設計例
   4. おまけ（時間あれば）
   3
   

   View Slide

4. 1. NULLについて
   4
   

   View Slide

5. NULLの特性と問題
   • 四則演算できない
   • 比較できない
   • 集合関数で無視される
   • ORDER BY はDB依存
   • 3値論理
   5
   

   View Slide

6. 四則演算
   NULLは伝搬する。
   NULL + 1 => NULL
   NULL - 1 => NULL
   NULL * 1 => NULL
   NULL / 1 => NULL
   6
   

   View Slide

7. 比較できない
   NULL > 1 => NULL
   NULL = NULL => NULL
   NULL != NULL => NULL
   IS NULLかIS NOT NULLを使うとチェックできる。
   1 IS NULL => f
   1 IS NOT NULL => t
   7
   

   View Slide

8. 集合関数で無視される
   • NULLの行は無視される
   • 1, 2, NULLをAVG(*)すると1.5になる
   8
   

   View Slide

9. PostgreSQL(v12.3)の並び順
   • ORDER BY users.ageするとNULLが最後
   • 1, 2, NULLの並び
   • NULLを先頭にする方法は2つある
   • ORDER BY users.age NULLS FIRST
   • ORDER BY users.age IS NULL DESC, users.age
   9
   

   View Slide

10. MySQL(v8.0.19)の並び順
    • ORDER BY users.ageするとNULLが最初
    • NULL, 1, 2の並び
    • NULLを最後にする方法はIS NULLを使う
    • ORDER BY users.age IS NULL, users.age ASC
    10
    

    View Slide

11. 3値論理
    • TRUE, FALSE, NULLの3値が入るカラム
    • SELECT COUNT(*) WHERE users.active != TRUE
    • !=だとNULLのレコードが含まれない
    • NULLの考慮が漏れていてバグの原因になる
    11
    

    View Slide

12. http://mickindex.sakura.ne.jp/database/db_getout_null.html
    12
    

    View Slide

13. 2. NULLの進入を防ぐ
    13
    

    View Slide

14. テーブル books
    create_table :books do |t|
    t.references :author, foreign_key: { to_table: :users }
    t.string :title
    t.integer :price
    t.timestamps
    end
    14
    

    View Slide

15. ActiveRecordのバリデーション
    バリデーションでNULLが入るのを防ぐ。
    class Book
    # ActiveRecord v5.0͔ΒσϑΥϧτͰ `required: true` ʹͳΔ
    belongs_to :author, class_name: 'User'
    validates :title, presence: true
    validates :price, presence: true
    end
    15
    

    View Slide

16. ActiveRecord::RecordInvalid
    irb(main):001:0> User.first.books.create!
    User Load (0.9ms) SELECT "users".* FROM "users" ORDER BY \
    "users"."id" ASC LIMIT $1 [["LIMIT", 1]]
    (0.2ms) BEGIN
    User Load (0.3ms) SELECT "users".* FROM "users" WHERE \
    "users"."id" = $1 LIMIT $2 [["id", 1], ["LIMIT", 1]]
    (0.2ms) ROLLBACK
    Traceback (most recent call last):
    1: from (irb):1
    ActiveRecord::RecordInvalid (Validation failed: \
    Title can't be blank, Price can't be blank)
    16
    

    View Slide

17. バリデーションのすり抜け
    ActiveRecordの一部のメソッド（update_all など）はバリデー
    ションをスキップする。
    irb(main):001:0> Book.update_all(title: nil)
    Book Update All (1.4ms) UPDATE "books" SET "title" = $1 [["title", nil]]
    => 1
    バッチ処理などで踏みがち。
    17
    

    View Slide

18. NOT NULL制約
    マイグレーションで null: false を指定するとNOT NULL制約を
    つけられる。
    create_table :books do |t|
    t.references :author, null: false, foreign_key: { to_table: :users }
    t.string :title, null: false
    t.integer :price, null: false
    t.timestamps
    end
    18
    

    View Slide

19. ActiveRecord::NotNullViolation
    update_allでもNOT NULL制約でエラーになる。
    irb(main):001:0> Book.update_all(title: nil)
    Book Update All (1.3ms) UPDATE "books" SET "title" = $1 [["title", nil]]
    Traceback (most recent call last):
    1: from (irb):1
    ActiveRecord::NotNullViolation (PG::NotNullViolation: ERROR: \
    null value in column "title" violates not-null constraint)
    DETAIL: Failing row contains (1, 1, null, 0, 2020-06-04 08:24:26.588829, \
    2020-06-04 08:24:26.588829).
    19
    

    View Slide

20. 全てのNULLを
    生まれる前に消し去りたい
    20
    

    View Slide

21. どうすれば全てのカラムに
    null: falseをつけられるか？
    21
    

    View Slide

22. 3. 具体的な設計例
    22
    

    View Slide

23. 記事を下書き、公開、非公開する機能
    • 記事を下書きする
    • 記事を公開する
    • 公開者、公開日時を記録する
    • 記事を非公開にする
    • 非公開者、非公開日時を記録する
    23
    

    View Slide

24. ありそうなテーブル設計
    create_table :articles do |t|
    t.string :title, null: false
    t.integer :status, null: false, default: 0
    t.references :publisher, foreign_key: { to_table: :users }
    t.datetime, :published_at
    t.references, :archiver, foreign_key: { to_table: :users }
    t.datetime, :archived_at
    t.timestamps
    end
    24
    

    View Slide

25. ありそうなモデル
    class Article < ApplicationRecord
    belongs_to :publisher, class_name: 'User', optional: true
    belongs_to :archiver, class_name: 'User', optional: true
    enum status: { draft: 0, published: 1, archived: 2 }
    validates :title, presence: true
    validates :publisher_id, presence: true, unless: :draft?
    validates :archiver_id, presence: true, if: :archived?
    def publish_by(user)
    update(publisher: user, published_at: Time.current)
    end
    end
    25
    

    View Slide

26. ありそうなコントローラー
    class ArticlesController < ApplicationController
    # POST /articles/:id/publish
    def publish
    if @article.publish_by(current_user)
    redirect_to xxx_path, notice: 'ެ։͠·ͨ͠ɻ'
    else
    render :xxx
    end
    end
    end
    26
    

    View Slide

27. この設計の問題点
    • NOT NULL制約がついていない
    • バリデーションが複雑になる
    • 認可のgem(Punditなど）と相性が悪い
    • Fat Controllerになりがち
    • UPDATEはデッドロックの危険がある
    27
    

    View Slide

28. NOT NULL制約をつけた設計
    28
    

    View Slide

29. テーブルを分ける
    create_table :articles do |t|
    t.string :title, null: false
    end
    29
    

    View Slide

30. テーブルを分ける
    create_table :article_publications do |t|
    opts = { null: false, foreign_key: true }
    t.references :article, index: { unique: true }, **opts
    t.references :user, index: true, **opts
    t.timestamps
    end
    create_table :article_archives do |t|
    opts = { null: false, foreign_key: true }
    t.references :article, index: { unique: true }, **opts
    t.references :user, index: true, **opts
    t.timestamps
    end
    30
    

    View Slide

31. モデルを分ける
    31
    

    View Slide

32. class Article < ApplicationRecord
    has_one :article_publication
    has_one :article_archive
    scope :published, -> {
    left_joins(:article_publication, :article_archive)
    .where.not(article_publications: { id: nil })
    .where(article_archives: { id: nil })
    }
    scope :archived, -> {
    left_joins(:article_archive)
    .where.not(article_archives: { id: nil })
    }
    end
    32
    

    View Slide

33. モデルを分ける
    class Article < ApplicationRecord
    def published?
    !article_publication.nil? && article_archive.nil?
    end
    def published_at
    article_publication&.created_at
    end
    end
    33
    

    View Slide

34. モデルに対応したコントローラーを作る
    class ArticlePublicationsController < ApplicationController
    # POST /articles/:article_id/publications
    def create
    article = Article.find(params[:article_id])
    if @article.create_article_publication(user: current_user)
    redirect_to xxx_path, notice: 'ެ։͠·ͨ͠ɻ'
    else
    render :xxx
    end
    end
    end
    34
    

    View Slide

35. まとめ
    • できるだけNOT NULLをつける
    • コントローラーがシンプルになる
    • パフォーマンスが悪くなってから status を作る
    35
    

    View Slide

36. おまけ（時間あれば）
    AcitveRecord v6.1の新機能の紹介
    36
    

    View Slide

37. where.missing(:author)1
    論理削除を扱うときに便利そう。
    User.left_joins(:user_archive).where(user_archives: { id: nil })
    ↓
    User.where.missing(:user_archive)
    activerecord-missing2ですぐ使うことも可能。
    2 https://github.com/yujideveloper/activerecord-missing
    1 https://github.com/rails/rails/pull/34727
    37
    

    View Slide

38. check_constraint3
    schema.rbで検査制約をサポート。
    add_check_constraint :products, "price > 0", name: "price_check"
    remove_check_constraint :products, name: "price_check"
    3 https://github.com/rails/rails/pull/31323
    38
    

    View Slide

39. ご静聴ありがとうございました
    We are hiring4
    4 https://medpeer.co.jp/recruit/
    39
    

    View Slide