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.4k
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
Railsエンジニア向けDocker入門 / Docker description for Rails engineers
sinsoku
0
360
継続的Railsアップグレード / Continuous Rails Upgrade
sinsoku
8
5.7k
Introduce AR::Encryption
sinsoku
1
150
Deploy Rails apps in 2021
sinsoku
7
7.5k
⚡️Ruby、オブジェクト指向、デザイン / Ruby, OOP, Design
sinsoku
5
1.3k
Railsアプリの脆弱性パターン / vulnerability patterns for Rails app
sinsoku
7
3.7k
Railsアプリ開発の事例紹介 / A case study for a Rails App
sinsoku
6
1.8k
Rails on Docker
sinsoku
10
3.7k
Railsアプリの設計
sinsoku
24
8.9k
Other Decks in Technology
See All in Technology
GeoLocationAnchor and MKTileOverlay
toyship
0
110
雑な攻撃からELBを守る一工夫 +おまけ / Know-how to protect servers from miscellaneous attacks
hiroga
0
530
【Pythonデータ分析勉強会#33】「DearPyGuiに入門しました」の続き~Image-Processing-Node-Editor~
kazuhitotakahashi
0
160
220628 「Google AppSheet」タスク管理アプリをライブ作成 吉積情報伊藤さん
comucal
PRO
0
230
Custom GitHub Actions by Java
kazamori
0
290
Persistence in Serverless Applications - ServerlessDays NYC
marcduiker
0
250
ノーコードで Stripeを使いこなす3つの方法 / jp-stripes-online-vol-4
stripehideokamoto
0
300
UWBを使ってみた
norioikedo
0
430
JJUG2022_spring_Keycloak (Red Hat Single Sign-on)
tinoue
0
200
データ分析で切り拓け! エンジニアとしてのデータ分析職キャリア戦略
ksnt
0
170
Security Hub のマルチアカウント 管理・運用をサーバレスでやってみる
ch6noota
0
850
LINEのB2Bプラットフォームにおけるトラブルシューティング2選
line_developers
PRO
4
300
Featured
See All Featured
CSS Pre-Processors: Stylus, Less & Sass
bermonpainter
349
27k
Facilitating Awesome Meetings
lara
29
4k
Creatively Recalculating Your Daily Design Routine
revolveconf
207
10k
How to name files
jennybc
40
61k
Optimizing for Happiness
mojombo
365
63k
Three Pipe Problems
jasonvnalue
89
8.7k
Code Reviewing Like a Champion
maltzj
506
37k
Scaling GitHub
holman
451
140k
How to train your dragon (web standard)
notwaldorf
58
3.9k
Design and Strategy: How to Deal with People Who Don’t "Get" Design
morganepeng
105
16k
Put a Button on it: Removing Barriers to Going Fast.
kastner
56
2.3k
KATA
mclloyd
7
8.7k
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