Slide 1
Slide 1 text
NULL嫌いのUPDATEしないDB設計
DB設計したいNight 2020/06/04(木)
神速(@sinsoku)
1
Slide 2
Slide 2 text
自己紹介
• 名前: 神速
• 会社: メドピア株式会社
• GitHub: @sinsoku (画像右上)
• Twitter: @sinsoku_listy (画像右下)
• Rails歴: 7年くらい
• 好きなDB: PostgreSQL, Redis
• 嫌いなもの: NULL
2
Slide 3
Slide 3 text
話すこと
1. NULLについて
2. NULLの進入を防ぐ
3. 具体的な設計例
4. おまけ（時間あれば）
3
Slide 4
Slide 4 text
1. NULLについて
4
Slide 5
Slide 5 text
NULLの特性と問題
• 四則演算できない
• 比較できない
• 集合関数で無視される
• ORDER BY はDB依存
• 3値論理
5
Slide 6
Slide 6 text
四則演算
NULLは伝搬する。
NULL + 1 => NULL
NULL - 1 => NULL
NULL * 1 => NULL
NULL / 1 => NULL
6
Slide 7
Slide 7 text
比較できない
NULL > 1 => NULL
NULL = NULL => NULL
NULL != NULL => NULL
IS NULLかIS NOT NULLを使うとチェックできる。
1 IS NULL => f
1 IS NOT NULL => t
7
Slide 8
Slide 8 text
集合関数で無視される
• NULLの行は無視される
• 1, 2, NULLをAVG(*)すると1.5になる
8
Slide 9
Slide 9 text
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
Slide 10
Slide 10 text
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
Slide 11
Slide 11 text
3値論理
• TRUE, FALSE, NULLの3値が入るカラム
• SELECT COUNT(*) WHERE users.active != TRUE
• !=だとNULLのレコードが含まれない
• NULLの考慮が漏れていてバグの原因になる
11
Slide 12
Slide 12 text
http://mickindex.sakura.ne.jp/database/db_getout_null.html
12
Slide 13
Slide 13 text
2. NULLの進入を防ぐ
13
Slide 14
Slide 14 text
テーブル books
create_table :books do |t|
t.references :author, foreign_key: { to_table: :users }
t.string :title
t.integer :price
t.timestamps
end
14
Slide 15
Slide 15 text
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
Slide 16
Slide 16 text
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
Slide 17
Slide 17 text
バリデーションのすり抜け
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
Slide 18
Slide 18 text
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
Slide 19
Slide 19 text
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
Slide 20
Slide 20 text
全てのNULLを
生まれる前に消し去りたい
20
Slide 21
Slide 21 text
どうすれば全てのカラムに
null: falseをつけられるか？
21
Slide 22
Slide 22 text
3. 具体的な設計例
22
Slide 23
Slide 23 text
記事を下書き、公開、非公開する機能
• 記事を下書きする
• 記事を公開する
• 公開者、公開日時を記録する
• 記事を非公開にする
• 非公開者、非公開日時を記録する
23
Slide 24
Slide 24 text
ありそうなテーブル設計
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
Slide 25
Slide 25 text
ありそうなモデル
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
Slide 26
Slide 26 text
ありそうなコントローラー
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
Slide 27
Slide 27 text
この設計の問題点
• NOT NULL制約がついていない
• バリデーションが複雑になる
• 認可のgem(Punditなど）と相性が悪い
• Fat Controllerになりがち
• UPDATEはデッドロックの危険がある
27
Slide 28
Slide 28 text
NOT NULL制約をつけた設計
28
Slide 29
Slide 29 text
テーブルを分ける
create_table :articles do |t|
t.string :title, null: false
end
29
Slide 30
Slide 30 text
テーブルを分ける
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
Slide 31
Slide 31 text
モデルを分ける
31
Slide 32
Slide 32 text
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
Slide 33
Slide 33 text
モデルを分ける
class Article < ApplicationRecord
def published?
!article_publication.nil? && article_archive.nil?
end
def published_at
article_publication&.created_at
end
end
33
Slide 34
Slide 34 text
モデルに対応したコントローラーを作る
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
Slide 35
Slide 35 text
まとめ
• できるだけNOT NULLをつける
• コントローラーがシンプルになる
• パフォーマンスが悪くなってから status を作る
35
Slide 36
Slide 36 text
おまけ（時間あれば）
AcitveRecord v6.1の新機能の紹介
36
Slide 37
Slide 37 text
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
Slide 38
Slide 38 text
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
Slide 39
Slide 39 text
ご静聴ありがとうございました
We are hiring4
4 https://medpeer.co.jp/recruit/
39