DBスキーマ管理

DBスキーマ管理

DBスキーマ管理 • 問題 • 改善見込み • デモ

多拠点orステージ間の開発 拠点A,Bで並行して別機能を開発中・・・ 拠点A 拠点B テーブルAを作成 カラムAを追加 カラムCを追加 カラムEを追加 ・ ・ ・ カラムNを追加 テーブルAを作成 カラムBを追加 カラムBを修正 カラムAを追加 ・ ・ ・ カラムMを追加

多拠点orステージ間の開発 拠点A 拠点B テーブルAを作成 カラムAを追加 カラムCを追加 カラムEを追加 ・ ・ ・ カラムNを追加 テーブルAを作成 カラムBを追加 カラムBを修正 カラムAを追加 ・ ・ ・ カラムMを追加

多拠点orステージ間の開発 通常、AからBへDBの変更を反映する場合、 拠点A 拠点B ALTER文で一つずつ反映していくが、、、 テーブルAを作成 カラムAを追加 カラムCを追加 カラムEを追加 ・ ・ ・ カラムNを追加 テーブルAを作成 カラムBを追加 カラムBを修正 カラムAを追加 ・ ・ ・ カラムMを追加

問題点

問題点 ①スキーマの不整合 開発環境、本番環境、ステージング環境などで、DBスキーマが一 致しない場合が発生することがある。 手動で管理する場合、異なるバージョンのスキーマが存在する可 能性があり、テストがうまくいっても本番環境での動作が保証さ れないリスクが高まる。 拠点A 拠点B ②変更の追跡が困難 スキーマの変更履歴が記録されないため、誰がどの変更をいつ行 ったかが不明になりやすい。 これにより、障害発生時に原因追及が難しくなる。また、複数の 開発者やチームで開発を行う際に、変更の衝突やミスが起こりや すくなる。 ③手動操作によるミス テーブル追加、修正のsql文が一つでも漏れるとエラーの原因 →手動で適用させる場合、タイポや操作ミスなど、 人的ミスの入り込む余地がある →sqlが大量にある場合はミスのリスク増大！ テーブルAを作成 カラムAを追加 カラムCを追加 カラムEを追加 ・ ・ ・ カラムNを追加 テーブルAを作成 カラムBを追加 カラムBを修正 カラムAを追加 ・ ・ ・ カラムMを追加

問題点 ①スキーマの不整合 開発環境、本番環境、ステージング環境などで、DBスキーマが一致しない場合が発生することがある。 手動で管理する場合、異なるバージョンのスキーマが存在する可能性があり、テストがうまくいっても本番環境で の動作が保証されないリスクが高まる。 開発環境 CREATE TABLE sample ( id integer NOT NULL PRIMARY KEY, name varchar(100) NOT NULL, created_date_time timestamp, email varchar(150) NOT NULL, age numeric(2,2) ); テスト環境A CREATE TABLE sample ( id integer NOT NULL PRIMARY KEY, name varchar(100) NOT NULL, created_date_time timestamp, age numeric(2,2) ); テスト環境B CREATE TABLE sample ( id integer NOT NULL PRIMARY KEY, name char(150) NOT NULL, created_date_time timestamp, email varchar(150) NOT NULL, age int ); 開発環境にしか適用していなかった、 テスト環境Aには反映させたがBにはしていなかった・・・etc

問題点 ②変更の追跡が困難 スキーマの変更履歴が記録されないため、誰がどの変更をいつ行ったかが 不明になりやすい。 これにより、障害発生時に原因追及が難しくなる。また、複数の開発者や チームで開発を行う際に、変更の衝突やミスが起こりやすくなる。 なぜこのカラム追加されている？ どこでも使ってないけど テーブルAを作成 カラムAを追加 カラムCを追加 カラムEの型を変更 エラーになるから調べたら、 （整数3桁少数部2桁から、整数2桁少数部3桁） 実装は定義変更に合わせて修正してるが、 ・ ・ ・ カラムNを追加 テーブルが未反映だ！

問題点 ③手動操作によるミス テーブル追加、修正のsql文が一つでも漏れるとエラーの原因 →手動で適用させる場合、タイポや操作ミスなど、 人的ミスの入り込む余地がある →sqlが大量にある場合はミスのリスク増大！ CREATE TABLE table_name ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, email VARCHAR(100), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); 手動で作成する場合、 ・少数部本当は4だった ・セミコロンがないので実行時エラーになる などのリスクがある ALTER TABLE table_name ADD COLUMN address VARCHAR(255); ALTER TABLE table_name ADD COLUMN amount decimal(10,3) ・ ・ ・ ALTER TABLE table_name ADD COLUMN N_COLUMN TYPE(XXX); CREATE,ALTER文が管理仕切れていない、sql作成時点の 断面が異なる、などの場合、 どれを反映すべきか、 そもそも足りてるのか、などを Sql定義反映者が全て１つずつチェックしないといけない

改善効果

改善効果 DBスキーマ管理ツール ①スキーマの不整合↔環境間での一貫性の確保 ツールを使うことで、開発、ステージング、本番 など異なる環境間でのスキーマ反映がやりやすく なり、環境ごとの不整合が発生しにくくなる。 拠点A 拠点B ②変更の追跡が困難↔変更の可視化と管理 スキーマ変更の履歴を自動で記録・管理できるた め、誰がいつ何を変更したかが明確になる。 Gitのようにスキーマ変更をバージョン管理できる ため、変更の追跡やレビューが容易になる。 ③手動操作によるミス↔自動化によるミスの防止 スキーマの変更作業を自動化することで、手動で のミスを減らし、スムーズな運用が可能になる。 開発者はSQLの実行ミスを心配する必要がなくな り、ツールが自動的にスキーマを適用する。 テーブルAを作成 カラムAを追加 カラムCを追加 カラムEを追加 ・ ・ ・ カラムNを追加 テーブルAを作成 カラムBを追加 カラムBを修正 カラムAを追加 ・ ・ ・ カラムMを追加

DBスキーマ管理ツール

DBスキーマ管理ツールの種類 差分管理 DDL管理 データベースの現在の状態と、追加したい変更の差分を管理するア プローチ。 スキーマ全体を定義する「状態」（完全なDDL）を管理するアプロ ーチ。 変更が発生するたびに、スキーマの「変更履歴」をSQLやスクリプ トとして記録し、その差分（変更内容）を順次適用していく。 データベースの現在の状態（スキーマ全体の構造）を完全に表す DDLを使用して、スキーマを再構築する。差分は、現行のスキーマ と最新のDDLを比較することで生成される。 CREATE TABLE table_name ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, email VARCHAR(100), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); 順 に 実 施 さ れ る ALTER TABLE table_name ADD COLUMN address VARCHAR(255); ALTER TABLE table_name ADD COLUMN amount decimal(10,3); ・ ・ ・ ALTER TABLE table_name ADD COLUMN N_COLUMN TYPE(XXX); CREATE TABLE table_name ( id INT PRIMARY KEY, name VARCHAR(100), email VARCHAR(100), created_at TIMESTAMP) ; CREATE TABLE table_name ( id INT PRIMARY KEY, name VARCHAR(150), email VARCHAR(100), created_at TIMESTAMP), age INT ; DDLの差分を検知し、 ツールがALTER文などを生成する。 ALTER TABLE table_name MODIFY COLUMN name VARCHAR(150); ALTER TABLE table_name ADD COLUMN age int;

DBスキーマ管理ツールの種類 差分管理 仕組み: データベースの現在の状態と、追加したい変更の差分を管理するア プローチ。 変更が発生するたびに、スキーマの「変更履歴」をSQLやスクリプ トとして記録し、その差分（変更内容）を順次適用していく。 • スキーマの変更が発生するごとに、マイグレーションファイル（例えば、ALTER文、CREATE文、UPDATE • • • 文など）を手動で作成する。 各マイグレーションファイルには、どのような変更を加えるか（カラム追加、テーブル追加など）が記述 される。 スキーマはバージョンごとに追跡され、適用されていないマイグレーションがあれば順次適用される。 ツールによってはロールバックも可能。 代表的なツール: • Flyway や Liquibase など 特徴: CREATE TABLE table_name ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, email VARCHAR(100), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); 順 に 実 施 さ れ る ALTER TABLE table_name ADD COLUMN address VARCHAR(255); ALTER TABLE table_name ADD COLUMN amount decimal(10,3); ・ ・ ・ ALTER TABLE table_name ADD COLUMN N_COLUMN TYPE(XXX); • メリット: ◦ 変更の履歴がファイルに細かく残るため、いつ、どんな変更が加えられたかを簡単に追跡できる。 ◦ スキーマの変更が逐次的に適用されるため、データベースの現在の状態を常に把握しやすい。 ◦ ロールバック機能を使って、特定バージョンに戻すことができる。 • デメリット: ◦ 長期間にわたって多くのマイグレーションが発生すると、マイグレーションファイルの数が増え、管 理が煩雑になる。

DBスキーマ管理ツールの種類 DDL管理 仕組み: スキーマ全体を定義する「状態」（完全なDDL）を管理するアプロ ーチ。 • スキーマの最新の状態を記述したDDLファイル（テーブル定義、インデックス、外部キーなど）を管理す る。 • 新しいスキーマを適用する際には、ツールが現在のスキーマとDDLを比較して差分を計算し、自動的に必 データベースの現在の状態（スキーマ全体の構造）を完全に表す DDLを使用して、スキーマを再構築する。差分は、現行のスキーマ と最新のDDLを比較することで生成される。 CREATE TABLE table_name ( id INT PRIMARY KEY, name VARCHAR(100), email VARCHAR(100), created_at TIMESTAMP) ; CREATE TABLE table_name ( id INT PRIMARY KEY, name VARCHAR(150), email VARCHAR(100), created_at TIMESTAMP), age INT ; DDLの差分を検知し、 ツールがALTER文などを生成する。 ALTER TABLE table_name MODIFY COLUMN name VARCHAR(150); ALTER TABLE table_name ADD COLUMN age int; • 要な変更を適用する。 スキーマ全体が管理されるため、バージョンごとのスナップショットのように扱える。 代表的なツール: • sqldef など 特徴: • メリット: ◦ スキーマ全体を一つのDDLとして管理するため、現在のスキーマを直感的に把握しやすい。 ◦ 大規模な変更が一度に適用できるため、新しい環境にスキーマを適用する際に便利。 ◦ 不要な要素（古いカラムやテーブルなど）の削除が容易。 • デメリット: ◦ 細かい変更履歴が差分としては保持されないため、過去にどんな変更が加えられたのかを追跡するの は工夫が必要。

DBスキーマ管理ツールの種類 差分管理 DDL管理 データベースの現在の状態と、追加したい変更の差分を管理するア プローチ。 スキーマ全体を定義する「状態」（完全なDDL）を管理するアプロ ーチ。 変更が発生するたびに、スキーマの「変更履歴」をSQLやスクリプ 差分管理アプローチは トとして記録し、その差分（変更内容）を順次適用していく。 ・ファイル管理が面倒 ・差分の開始時点を揃える必要がある データベースの現在の状態（スキーマ全体の構造）を完全に表す DDLを使用して、スキーマを再構築する。差分は、現行のスキーマ と最新のDDLを比較することで生成される。 などの理由で個人的には好まない。 CREATE TABLE table_name ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, email VARCHAR(100), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); ということで、 DDL管理アプローチのツールを説明！ 順 ALTER TABLE table_name ADD COLUMN address VARCHAR(255); に 実 施 さ れ る ALTER TABLE table_name ADD COLUMN amount decimal(10,3); ・ ・ ・ ALTER TABLE table_name ADD COLUMN N_COLUMN TYPE(XXX); CREATE TABLE table_name ( id INT PRIMARY KEY, name VARCHAR(100), email VARCHAR(100), created_at TIMESTAMP) ; CREATE TABLE table_name ( id INT PRIMARY KEY, name VARCHAR(150), email VARCHAR(100), created_at TIMESTAMP), age INT ; DDLの差分を検知し、 ツールがALTER文などを生成する。 ALTER TABLE table_name MODIFY COLUMN name VARCHAR(150); ALTER TABLE table_name ADD COLUMN age int;

デモ

DDL管理アプローチである sqldefを使用 https://github.com/sqldef/sqldef

• https://github.com/sqldef/sqldef