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
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