じゃあ、おうちで学べる

思考を呼び覚ます このコードに、君は抗えるか。

PostgreSQLのsqldefによるDBスキーマ管理で遊んだ。

はじめに

この記事では、データベーススキーママイグレーションツールであるsqldefで遊んだので使用方法とその特徴について解説します。sqldefRidgepoleに触発されて開発されたツールで、データベースのスキーマ変更を容易に行えるように設計されています。

github.com

1. sqldefとは

sqldefは「The easiest idempotent MySQL/PostgreSQL/SQLite3/SQL Server schema management by SQL」と謳われるDBスキーマ変更管理ツールです。GitHub上で公開されており(sqldef GitHubリポジトリ)、MySQLPostgreSQL、SQLite3、SQL Serverに対応しています。このツールを使用することで、CREATE TABLE文を書くだけで対象テーブルの比較とALTER TABLE文の生成・実行が可能になります。

sqldefは、データベースの現在のスキーマとユーザーが提供するCREATE TABLE文を比較し、必要な変更を自動的に検出します。このプロセスにより、データベースの変更をより簡単かつ迅速に行うことができます。また、sqldefはidempotent(冪等性)を持つため、同じ変更を何度適用しても、結果として得られるスキーマは同じになります。これにより、データベースの変更管理がより安全かつ予測可能になります。このツールは特に、開発環境やテスト環境での迅速な変更適用や、本番環境への安全な変更のロールアウトに非常に有用です。

2. sqldefの利点

従来のスキーママイグレーションツールではCREATE TABLE文とALTER TABLE文を二重管理する必要がありましたが、sqldefを使用すると新規作成DDL文のみを管理するだけで済むため、DBA(データベース管理者)および開発者の作業負担が大幅に軽減されます。また、CI/CDパイプラインにも簡単に組み込むことができるため、デプロイメントプロセスの自動化と整合性の向上が期待できます。

2.1 sqldefの最大の利点は、そのシンプルさと効率性

従来のツールでは、データベースの初期状態を構築するためのCREATE TABLE文と、既存のデータベースを変更するためのALTER TABLE文の両方を管理する必要がありました。しかし、sqldefを使えば、CREATE TABLE文のみを管理すれば十分で、ALTER TABLE文は自動的に生成されます。これにより、変更の管理が簡略化され、DBAと開発者の双方の作業負担が大幅に減少します。

2.2 CI/CDパイプラインへの組み込み

さらに、sqldefはCI/CDパイプラインとの統合が容易で、これによりデプロイメントプロセスの自動化が可能になります。データベースの変更をコードレビューとテストのプロセスに組み込むことで、変更の品質を向上させ、本番環境への変更のロールアウトをより安全に行うことができます。このように、sqldefは開発チームの生産性を高め、データベースの整合性を維持するための強力なツールです。

3. やってみる

実際にsqldefを使用するためには、適切な環境構築が不可欠です。このセクションでは、Dockerを活用してPostgreSQLサーバーをセットアップし、sqldefをダウンロードして設定する手順を具体的に説明します。その後、具体的なスキーマ定義を作成し、それをデータベースに適用してみます。

3.1 環境構築

Dockerを使用して、PostgreSQLのバージョン16を動作させる環境を構築します。以下のdocker-compose.yamlファイルを使用してPostgreSQLサーバーを立ち上げます。

version: '3'
services:
  postgres:
    container_name: postgres
    image: postgres:16
    restart: always
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: "postgres"
      POSTGRES_PASSWORD: "postgres"

3.2 sqldefのダウンロードと設定

sqldefの実行可能バイナリをGitHubからダウンロードし、設定します。これにより、任意のプラットフォームでsqldefを利用することが可能になります。

curl -LO https://github.com/sqldef/sqldef/releases/download/v0.16.15/psqldef_darwin_amd64.zip
unzip psqldef_darwin_amd64.zip
rm psqldef_darwin_amd64.zip
./psqldef --version

リリース情報はこちらから適切なアーキテクチャを選んでください。tarコマンドのオプションは覚えられず忘れているのでzipファイルを選びました。 github.com

3.3 スキーマの適用

v01_library.sqlファイルを用意し、図書館システムの基本的なテーブルを作成します。このスクリプトでは、PostgreSQLのserial型を使用して主キーの自動インクリメントを行い、外部キー制約を設定しています。これにより、データベースの整合性が保たれ、アプリケーションの安定性が向上します。ただし、sqldefを用いたスキーマの適用は効率的ですが、特に外部キー制約に関連する場合には注意が必要です。外部キー制約はデータベースの整合性を保つために重要な役割を果たしますが、これらの制約を含むテーブルの変更を管理する際には、特定の課題が生じることがあります。今回のケースでは、これらの課題を避けるための工夫を取り入れながら、スキーマを適用していきます。

-- 著者テーブル
create table authors (
  author_id serial primary key,
  name varchar(100) not null
);

-- 出版社テーブル
create table publishers (
  publisher_id serial primary key,
  name varchar(100) not null
);

-- 利用者テーブル
create table users (
  user_id serial primary key,
  user_name varchar(100) not null,
  email_address varchar(100),
  registration_date date not null
);

-- 書籍テーブル
create table books (
  book_id serial primary key,
  title varchar(255) not null,
  author_id integer not null,
  publisher_id integer not null,
  isbn varchar(20),
  year_published integer
);

-- 貸出記録テーブル
create table loans (
  loan_id serial primary key,
  book_id integer not null,
  user_id integer not null,
  loan_date date not null,
  return_date date
);

-- 外部キー制約の追加
alter table books add constraint fk_books_author_id foreign key (author_id) references authors(author_id);
alter table books add constraint fk_books_publisher_id foreign key (publisher_id) references publishers(publisher_id);
alter table loans add constraint fk_loans_book_id foreign key (book_id) references books(book_id);
alter table loans add constraint fk_loans_user_id foreign key (user_id) references users(user_id);

これを psqldef で以下のように適用します。

PGPASSWORD=postgres ./psqldef -h localhost -p 5432 -U postgres postgres < v01_library.sql

3.4 スキーマの変更

ユーザーテーブル(users)の構造を変更するための新しいDDLスクリプトを作成し、sqldefを使用して適用します。この変更には、列の追加、列の型変更、NOT NULL制約の追加が含まれ、データベースの設計を現代的な要件に合わせることができます。

-- v02_library.sql
create table users (
  user_id serial primary key,
  family_name varchar(100) not null,
  given_name varchar(100) not null,
  email_address varchar(254) not null,
  registration_date date not null
);

これを psqldef で以下のように適用します。

PGPASSWORD=postgres ./psqldef -h localhost -p 5432 -U postgres postgres < v02_library.sql
-- Apply --
ALTER TABLE "public"."users" ADD COLUMN "family_name" varchar(100) NOT NULL;
ALTER TABLE "public"."users" ADD COLUMN "given_name" varchar(100) NOT NULL;
ALTER TABLE "public"."users" ALTER COLUMN "email_address" TYPE varchar(254);
ALTER TABLE "public"."users" ALTER COLUMN "email_address" SET NOT NULL;
-- Skipped: DROP TABLE "public"."authors";
-- Skipped: DROP TABLE "public"."books";
-- Skipped: DROP TABLE "public"."loans";
-- Skipped: DROP TABLE "public"."publishers";
ALTER TABLE "public"."users" DROP COLUMN "user_name";

3.5 ユニークキー制約の追加

最後に、ユーザーテーブルにユニークキー制約を追加するためのDDLスクリプトを適用します。ユニークキー制約を追加することで、メールアドレスの重複を防ぎ、データの一意性を保証することができます。

-- v03_library.sql
-- ユーザーテーブルの作成
create table users (
  user_id serial primary key,
  family_name varchar(100) not null,
  given_name varchar(100) not null,
  email_address varchar(254) not null,
  registration_date date not null
);

-- メールアドレスに対するユニークキー制約(ユニークインデックス)
create unique index idx_users_email_address on users(email_address);

これを psqldef で以下のように適用します。

PGPASSWORD=postgres ./psqldef -h localhost -p 5432 -U postgres postgres < v03_library.sql
-- Apply --
-- メールアドレスに対するユニークキー制約(ユニークインデックス)
create unique index idx_users_email_address on users(email_address);
-- Skipped: DROP TABLE "public"."authors";
-- Skipped: DROP TABLE "public"."books";
-- Skipped: DROP TABLE "public"."loans";
-- Skipped: DROP TABLE "public"."publishers";

4. 結論

sqldefは、データベーススキーマの変更を簡単かつ効率的に行うことができる強力なツールです。特に、継続的インテグレーション/継続的デリバリー(CI/CD)パイプラインの一部としてスキーマ変更の自動化を行う際に非常に有効であり、開発プロセスの加速とデータベース整合性の向上に大きく寄与します。ただし、すべてのケースに適用可能なわけではなく、その特性と限界点を十分理解することが重要です。今回は実験的に使用してみた結果、このようなツールの有用性を実感しました。また、実際に運用経験のある方々の貴重なフィードバックが参考文献に掲載されていますので、より深い洞察を得るためにも、ぜひ参考にしていただくと良いでしょう。

5.参考文献