Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Speaker Deck
PRO
Sign in
Sign up for free
NULL嫌いのUPDATEしないDB設計 #DBSekkeiNight / DB design without updating
Takumi Shotoku
June 04, 2020
Technology
19
6.6k
NULL嫌いのUPDATEしないDB設計 #DBSekkeiNight / DB design without updating
DB設計したいNight #6 正規化 [online]
https://dbnight.connpass.com/event/177859/
Takumi Shotoku
June 04, 2020
Tweet
Share
More Decks by Takumi Shotoku
See All by Takumi Shotoku
Advice for Ruby beginners
sinsoku
2
270
Railsエンジニア向けDocker入門 / Docker description for Rails engineers
sinsoku
0
450
継続的Railsアップグレード / Continuous Rails Upgrade
sinsoku
8
6k
Introduce AR::Encryption
sinsoku
1
160
Deploy Rails apps in 2021
sinsoku
7
7.8k
⚡️Ruby、オブジェクト指向、デザイン / Ruby, OOP, Design
sinsoku
5
1.4k
Railsアプリの脆弱性パターン / vulnerability patterns for Rails app
sinsoku
7
3.8k
Railsアプリ開発の事例紹介 / A case study for a Rails App
sinsoku
6
1.9k
Rails on Docker
sinsoku
10
3.9k
Other Decks in Technology
See All in Technology
Oracle Transaction Manager for Microservices Free 22.3 製品概要
oracle4engineer
PRO
5
110
💰年度末予算消化祭💰 Large Memory Instance で 画像分類してみた
__allllllllez__
0
100
マイクロサービス宣言から8年 振り返りとこれから / Eight Years After the Microservices Declaration A Look Back and A Look Ahead
eisuke
2
160
SPA・SSGでSSRのようなOGP対応!
simo123
2
150
Logbii(ログビー) 会社紹介
logbii
0
130
OPENLOGI Company Profile
hr01
0
12k
ChatGPT for Hacking
anugrahsr
0
4.4k
データ分析基盤の要件分析の話(202201_JEDAI)
yabooun
0
260
インフラ技術基礎勉強会 開催概要
toru_kubota
0
170
API連携に伴う規制と対応 / Regulations and responses to API linkage
moneyforward
0
160
CSS Variable をもっと活用する / Kyoto.js 18
spring_raining
2
380
OCI DevOps 概要 / OCI DevOps overview
oracle4engineer
PRO
0
490
Featured
See All Featured
Faster Mobile Websites
deanohume
295
29k
Code Reviewing Like a Champion
maltzj
508
38k
The Brand Is Dead. Long Live the Brand.
mthomps
48
2.9k
Helping Users Find Their Own Way: Creating Modern Search Experiences
danielanewman
10
1.3k
Art, The Web, and Tiny UX
lynnandtonic
284
18k
The MySQL Ecosystem @ GitHub 2015
samlambert
240
11k
Fontdeck: Realign not Redesign
paulrobertlloyd
74
4.3k
Gamification - CAS2011
davidbonilla
75
4.1k
Designing Dashboards & Data Visualisations in Web Apps
destraynor
224
50k
CSS Pre-Processors: Stylus, Less & Sass
bermonpainter
349
27k
Bash Introduction
62gerente
601
210k
Designing with Data
zakiwarfel
91
4.2k
Transcript
NULL嫌いのUPDATEしないDB設計 DB設計したいNight 2020/06/04(木) 神速(@sinsoku) 1
自己紹介 • 名前: 神速 • 会社: メドピア株式会社 • GitHub: @sinsoku
(画像右上) • Twitter: @sinsoku_listy (画像右下) • Rails歴: 7年くらい • 好きなDB: PostgreSQL, Redis • 嫌いなもの: NULL 2
話すこと 1. NULLについて 2. NULLの進入を防ぐ 3. 具体的な設計例 4. おまけ(時間あれば) 3
1. NULLについて 4
NULLの特性と問題 • 四則演算できない • 比較できない • 集合関数で無視される • ORDER BY
はDB依存 • 3値論理 5
四則演算 NULLは伝搬する。 NULL + 1 => NULL NULL - 1
=> NULL NULL * 1 => NULL NULL / 1 => NULL 6
比較できない NULL > 1 => NULL NULL = NULL =>
NULL NULL != NULL => NULL IS NULLかIS NOT NULLを使うとチェックできる。 1 IS NULL => f 1 IS NOT NULL => t 7
集合関数で無視される • NULLの行は無視される • 1, 2, NULLをAVG(*)すると1.5になる 8
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
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
3値論理 • TRUE, FALSE, NULLの3値が入るカラム • SELECT COUNT(*) WHERE users.active
!= TRUE • !=だとNULLのレコードが含まれない • NULLの考慮が漏れていてバグの原因になる 11
http://mickindex.sakura.ne.jp/database/db_getout_null.html 12
2. NULLの進入を防ぐ 13
テーブル books create_table :books do |t| t.references :author, foreign_key: {
to_table: :users } t.string :title t.integer :price t.timestamps end 14
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
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
バリデーションのすり抜け 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
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
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
全てのNULLを 生まれる前に消し去りたい 20
どうすれば全てのカラムに null: falseをつけられるか? 21
3. 具体的な設計例 22
記事を下書き、公開、非公開する機能 • 記事を下書きする • 記事を公開する • 公開者、公開日時を記録する • 記事を非公開にする •
非公開者、非公開日時を記録する 23
ありそうなテーブル設計 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
ありそうなモデル 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
ありそうなコントローラー 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
この設計の問題点 • NOT NULL制約がついていない • バリデーションが複雑になる • 認可のgem(Punditなど)と相性が悪い • Fat
Controllerになりがち • UPDATEはデッドロックの危険がある 27
NOT NULL制約をつけた設計 28
テーブルを分ける create_table :articles do |t| t.string :title, null: false end
29
テーブルを分ける 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
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
モデルを分ける class Article < ApplicationRecord def published? !article_publication.nil? && article_archive.nil?
end def published_at article_publication&.created_at end end 33
モデルに対応したコントローラーを作る 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
まとめ • できるだけNOT NULLをつける • コントローラーがシンプルになる • パフォーマンスが悪くなってから status を作る
35
おまけ(時間あれば) AcitveRecord v6.1の新機能の紹介 36
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
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
ご静聴ありがとうございました We are hiring4 4 https://medpeer.co.jp/recruit/ 39