Sqitch というツールの紹介です。

ふつうのコーディング作業では、テキストファイルにコードを書いて git で共有や版管理を行うというのが当たり前の世の中になりました。しかしデータベースの世界ではまだコンソールから直接 SQL コードを打ち込んでテーブルの定義や変更を行うことがあります。これでは誰かに引き継いだり前のバージョンに戻すのが難しい。特に最近はふつうのコードをステートレスなコンテナで動かすようになったので、ステートフルなデータベースの管理の汚さが目立ちます。

Sqitch を使ったデータベース管理では、SQL コードを直接入力する代わりに一旦 SQL コードをファイルに保存します。また、以前の状態への復帰方法や状態の確認方法もファイルに記述する事により安心してデータベースの定義を変更出来るようになります。

Sqitch は deploy ディレクトリに置かれた各ファイルを順に実行してゆく事で SQL を SQL をデプロイします。また、全てをデプロイした後は revert ディレクトリに置かれた各ファイルを逆に実行して任意の時点の状態に戻します。Sqitch でテーブル定義などの変更を行うには、元の変更を別のファイル名で残しておいて、新しい変更を追加します。

インストール

Mac の場合 brew でインストール出来ます。--with-(DB 種類)-support で使いたいデータベースを指定します。例えば PostgreSQL の場合は以下のようにします。

brew tap sqitchers/sqitch
brew install sqitch --with-postgres-support

PostgreSQL サーバと pgAdmin の動作確認

本質的では無いですが、テストに便利なので Docker compose で PostgreSQL と pgAdmin を動かします。docker-compose.yaml という名前でこういうファイルを作り:

version: "3"
services:
  pg:
    image: postgres
    ports:
      - "5432:5432"
    environment:
      POSTGRES_PASSWORD: passwd
  pgadmin:
    image: dpage/pgadmin4
    ports:
      - 80:80
    environment:
      PGADMIN_DEFAULT_EMAIL: user
      PGADMIN_DEFAULT_PASSWORD: passwd

docker-compose up を実行すると動きます。

別のターミナルで PostgreSQL の動作確認。ポート: 5432, ユーザ: postgres, パスワード: passwd, データベース: postgres です。

psql -h localhost -p 5432 -U postgres -d postgres
postgres=# select 1;
 ?column? 
----------
        1
(1 row)

http://localhost を開いて pgAdmin の動作を確認します。

Username: user, Password: passwd で pgAdming にログイン
Add New Server で Host: pg, Port: 5432, Username: postgres, Password: passwd で PostgreSQL に接続する。

PostgreSQL を使った例

サンプルの仕様

https://sqitch.org/docs/manual/sqitchtutorial/ を参考にして適当な仕様を作ってみました。

myschema という schema を作る。
users というテーブルを作る。属性は uid, name
後で address という属性を追加する。

Sqitch プロジェクトの作成

sqitch init (プロジェクト名) --url (URL) --engine pg1 で新しいプロジェクトを作ります。 URL はプロジェクトの特定に使うだけなので何でもよいです。

sqitch init myproject --uri https://example.com/myproject/ --engine pg

これで必要なファイルとディレクトリが出来ます。

sqitch.conf
- Sqitch の色々な設定を置くところ。
sqitch.plan
- SQL 変更記録 change のリストである plan を定義するところ。
deploy/
- 変更に使う SQL を置くところ。sqitch deploy コマンドで実行される。
revert/
- 変更を deploy を実行する前に戻す SQL を置くところ。sqitch revert コマンドで実行される。
verify/
- 変更を確認する SQL を置くところ。sqitch verify コマンドで実行される。

特に sqitch.plan が重要なので、これから作るサンプルで出来た sqitch.plan を紹介します。

%syntax-version=1.0.0
%project=myproject
%uri=https://example.com/myproject/

add_schema 2020-06-26T09:12:07Z propella <propella@example.com> # Add schema for the project.
add_table [add_schema] 2020-06-26T09:41:59Z propella <propella@example.com> # Add table for the project.
add_address [add_table] 2020-06-26T10:07:07Z propella <propella@example.com> # Alter users to add address.

先頭の % はメタ情報で、その後に変更 (change) の履歴が続きます。add_schema などの先頭のキーワードは change の名前です。change に対応する SQL スクリプトが deploy/ revert/ verify/ それぞれのディレクトリに格納されます。[] は依存を表します。

sqitch deploy を行うとこのリストに従って deploy/ 内のスクリプトを実行します。

sqitch revert を行うと、revert/ 内のスクリプトを逆順に実行します。sqitch revert add_table のようにどこまで戻すかを chante 名で指定する事も出来ます。・

Sqitch プロジェクトの設定

Sqitch プロジェクトの動作は、プロジェクト内の ./sqitch.conf または各ユーザホームディレクトリの ~/.sqitch/sqitch.conf から読み込みます。sqitch config で設定を変更しますが、デフォルトでプロジェクト内の ./sqitch.conf、--user をつけるとホームの ~/.sqitch/sqitch.conf を変更します。例えば以下のようにしてユーザごとの設定を変更します:

sqitch config --user user.name propella # ユーザの名前を変更
sqitch config --user user.email propella@example.com`  # ユーザのメールアドレスを変更

データベースの変更

sqitch add add_schema -n 'Add schema for the project.'

ようにして add_schema という change を作ります。deploy, revert, verify それぞれのディレクトリに add_schema.sql というファイルが出来て、データベース変更を始められる状態になります。チュートリアルでは以下のような行を BEGIN と COMMIT の間に追加する事によって、Schema の追加を設定します。このように sqitch では、change を plan に追加していく事で SQL の変更を表現します。

deploy (変更を行う SQL を追加)
- CREATE SCHEMA myschema;
revert (変更を戻す SQL を追加)
- DROP SCHEMA myschema;
verify (変更を確認する SQL を追加。確認出来ない時にエラーが起こる SQL を追加する)
- SELECT pg_catalog.has_schema_privilege('myschema', 'usage');

上記 Docker の PostgreSQL を使う場合、次のようにして change を反映させます。ここで、最後の引数は接続先を示す Database URI という物です。

sqitch deploy db:pg://postgres:passwd@localhost:5432/postgres

元に戻すコマンド

sqitch revert db:pg://postgres:passwd@localhost:5432/postgres

確認を行うコマンド

sqitch verify db:pg://postgres:passwd@localhost:5432/postgres

sqitch deploy ... を実行後 psql コマンドで確認しましょう。

$ psql -h localhost -p 5432 -U postgres -d postgres -c '\dn myschema'
   List of schemas
   Name   |  Owner
----------+----------
 myschema | postgres
(1 row)

デプロイ先の設定

毎回デプロイ先を指定するのは面倒なので、Database URI に名前をつけてみます。以下のようにすると、myproject_db という名前でデータベースに接続出来ます。

sqitch target add myproject_db db:pg://postgres:passwd@localhost:5432/postgres

また sqitch engine add pg で flipr_test をデフォルトに設定します。

sqitch engine add pg myproject_db

動作確認

$ sqitch status
# On database myproject_db
# Project:  myproject
# Change:   7be8ccab20172e7d189f8d87dbfe7ea2404de8e7
# Name:     add_schema
# Deployed: 2020-06-26 18:15:20 +0900
# By:       propella <propella@example.com>
# 
Nothing to deploy (up-to-date)

change の追加

次に、テーブルを作成する change を追加します。この change は先程作った add_schema に依存するので、-r で依存を設定します。

sqitch add add_table -r add_schema -n 'Add table for the project.'

deploy/add_table.sql はこんな感じ。

-- Deploy myproject:add_table to pg
-- requires: add_schema

BEGIN;
CREATE TABLE myschema.users (
    uid     TEXT    PRIMARY KEY,
    name    TEXT    NOT NULL
);
COMMIT;

revert/add_table.sql はこんな感じ。

-- Revert myproject:add_table from pg

BEGIN;
DROP TABLE myschema.users;
COMMIT;

verify/add_table.sql はこんな感じ。

-- Verify myproject:add_table on pg

BEGIN;
SELECT uid, name FROM myproject.users WHERE FALSE;
ROLLBACK;

change のやりなおし

さて、ようやく実用的な話です。一旦作ったテーブルを変更してみます。

sqitch add add_address -r add_table -n 'Alter users to add address.'

deploy/add_address.sql はこうかな?

-- Deploy myproject:add_address to pg
-- requires: add_table

BEGIN;
ALTER TABLE myschema.users ADD address TEXT;
COMMIT;

revert/add_address.sql はこうかな?

-- Revert myproject:add_address from pg

BEGIN;
ALTER TABLE myschema.users DROP address;
COMMIT;

verify/add_address.sql はこう。

-- Verify myproject:add_address on pg

BEGIN;
SELECT uid, name, address FROM myschema.users WHERE FALSE;
ROLLBACK;

sqitch deploy したり sqitch revert して遊んでみるとよいです。

実はこれはいまいちな例で、もしも実行する SQL が Idempotence (冪等、何度実行しても同じ結果になるような操作) であれば sqitch rework という多少スマートな技が使えます。ただ、SQL 環境の作成とやり直しに必要なすべての SQL 文をファイルとして保存するという点は同じです。

その他の機能

https://sqitch.org/docs/manual/sqitchtutorial/ に挙げられたその他の機能です。

sqitch deploy --verify
- sqitch deploy 実行後に自動的に sqitch verify を実行します。
sqitch config --bool deploy.verify true
- sqitch deploy 時に自動的に --verify を付ける設定。
sqitch config --bool rebase.verify true
- sqitch rebase 時に自動的に --verify を付ける設定。
sqitch log
- deploy と revert の記録を表示します。
sqitch add (変更2) --requires (変更1) -n (ノート)
- 変更1 に依存する新しい変更2 を作る。
sqitch revert --to バージョン
- 特定のバージョンに戻す
  - @HEAD 最新の変更
  - @HEAD^ 一つ手前の変更
  - @ROOT 最初の変更
  - appschema のような change の名前も使える。
sqitch tag (タグ名) (変更) -n (コメント)
- 指定の変更または最近の変更にタグを付ける。
sqitch bundle
- bundle ディレクトリにデプロイ用の Sqitch プロジェクトを作る。
- README.md や .git 等、Sqitch に関係ないファイル以外をコピーする。
sqitch rework (変更)
- 既存の変更を修正する。
- 元の修正は別名コピーされ、元の修正に戻すための revert 作られる。
- 元の修正はタグで見分けるので、あらかじめタグを設定しておく必要がある。

参考

Sqitch Homebrew Tap:
- Homebrew を使ったインストールオプション
sqitchtutorial:
- チュートリアル
Database URI
- 接続先を示す文法