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

Transcript

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

2. 自己紹介 • 名前: 神速 • 会社: メドピア株式会社 • GitHub: @sinsoku

   (画像右上) • Twitter: @sinsoku_listy (画像右下) • Rails歴: 7年くらい • 好きなDB: PostgreSQL, Redis • 嫌いなもの: NULL 2

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

4. 1. NULLについて 4

5. NULLの特性と問題 • 四則演算できない • 比較できない • 集合関数で無視される • ORDER BY

   はDB依存 • 3値論理 5

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

   => NULL NULL * 1 => NULL NULL / 1 => NULL 6

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

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

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

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

11. 3値論理 • TRUE, FALSE, NULLの3値が入るカラム • SELECT COUNT(*) WHERE users.active

    != TRUE • !=だとNULLのレコードが含まれない • NULLの考慮が漏れていてバグの原因になる 11

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

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

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

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

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

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

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

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

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

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

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

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

    非公開者、非公開日時を記録する 23

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

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

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

27. この設計の問題点 • NOT NULL制約がついていない • バリデーションが複雑になる • 認可のgem(Punditなど）と相性が悪い • Fat

    Controllerになりがち • UPDATEはデッドロックの危険がある 27

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

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

    29

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

31. モデルを分ける 31

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

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

    end def published_at article_publication&.created_at end end 33

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

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

    35

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

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

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

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