実行できるSQLiteドキュメント: マイグレーション

Q: SQLiteのスキーマはどうやってバージョン管理する？

SQLiteにはデータベースごとに32bit整数のスロットuserversionが用意されていて、PRAGMA userversionで読み書きできます。アプリ起動時にこの値を読み、コード側が知っている最新マイグレーション番号と比較して、未適用のものを順番に流すだけです。専用のテーブルは不要ですが、監査ログ目的で別途テーブルを足すケースもよくあります。

Q: SQLiteのマイグレーションはロールバックできる？

各マイグレーションをBEGIN; ... COMMIT;で囲んでおけば大丈夫です。途中で失敗すればROLLBACKでスキーマ変更もデータ変更もまとめて巻き戻せます。SQLiteのDDLはトランザクション対応なので安心です。ただしコミット済みのマイグレーションを後から戻したい場合は、自分でdownスクリプトを書いておく必要があります。SQLiteが自動生成してくれるわけではありません。

Q: SQLiteの`ALTER TABLE`が制限されているのはなぜ？

SQLiteはALTER TABLE ADD COLUMN、RENAME TABLE、RENAME COLUMN、DROP COLUMNには対応していますが、カラムの型や制約を変えるような任意の変更はできません。回避策はおなじみの「12ステップ」で、新しい形のテーブルを作ってINSERT INTO newtable SELECT ... FROM oldtableでデータをコピー、古いテーブルをDROPしてリネーム、という流れになります。

Q: マイグレーションツールを使うべき？それとも自作？

小規模なアプリなら、番号付きの.sqlファイルをPRAGMA user_versionで順番に流すだけのループを30行ほど書けば十分動きます。大きめのプロジェクトでは、Alembic（Python）、golang-migrate（Go）、Knex（Node）などのツールが、順序管理やロック、チーム開発のワークフローまで面倒を見てくれるので、再発明する手間を省けます。

スキーマは変わるもの。最初から想定しておこう

スキーマの最初のバージョンが、そのまま最後のバージョンになることはまずありません。カラムが追加され、テーブルが分割され、インデックスは見直される。問題は「スキーマが変わるかどうか」ではなく、「すでに古いバージョンを抱えた手元のラップトップ・サーバー・ユーザー端末すべてに、変更をきれいに反映できるかどうか」です。

そこで登場するのが SQLite マイグレーション です。データベースをバージョン N から N+1 へ進める、小さくて順序が決まったスクリプト群を用意しておく。順番に実行すれば、どんな状態のデータベースでも最新まで追いつけます。この規律をサボると、「自分の環境では動くんだけどな…」という、午後を丸ごと潰すタイプのバグに悩まされることになります。

SQLite がスキーマバージョン管理のために用意しているビルトインの仕組みは、ただひとつ。PRAGMA user_version です。これはデータベースが保持してくれる 32bit 整数で、SQLite 自身は中身に一切触れません。意味づけは自分で決めます。

PRAGMA user_version;
PRAGMA user_version = 1;
PRAGMA user_version;

新規作成のデータベースは 0 からスタートします。マイグレーションを適用したら、その番号に合わせて値を更新します。アプリ起動時にこの値を読めば、現在どこまで進んでいるかが一目で分かります。

マイグレーションループの最小構成

考え方はシンプルです。各マイグレーションを番号付きのSQLスクリプトとして用意しておき、アプリは現在の user_version を読み取って、それより大きい番号のスクリプトを順番に実行し、1つ実行するごとに user_version を更新します。

それでは、マイグレーション1で最初のスキーマを作ってみましょう。

BEGIN;

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    name  TEXT NOT NULL
);

PRAGMA user_version = 1;

COMMIT;

ここで2つポイントがあります。まず全体が BEGIN; ... COMMIT; で囲まれているので、処理がアトミックになります。仮に CREATE TABLE がコケても user_version は上がらないので、原因を直して再実行すればOKです。そして PRAGMA user_version = 1 がコミット直前の最後の文に置かれているので、他のすべてが成功したときだけバージョンが切り替わるわけです。

では次に、created_at カラムを追加したいケースを考えてみましょう。これがマイグレーション2になります。

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE
);

BEGIN;

ALTER TABLE users ADD COLUMN created_at TEXT
    NOT NULL DEFAULT (datetime('now'));

PRAGMA user_version = 2;

COMMIT;

SELECT sql FROM sqlite_master WHERE name = 'users';

バージョン 0 のデータベースは両方を実行します。バージョン 1 のデータベースは 2 つ目だけを実行し、バージョン 2 のデータベースは何も実行しません。この順序こそが契約（コントラクト）です。

ALTER TABLE でできること・できないこと

SQLite の ALTER TABLE は意図的に機能が絞られています。サポートされているのは次のとおりです。

ADD COLUMN — 新しいカラムを末尾に追加します（デフォルト値の指定も可）。
DROP COLUMN — カラムを削除します（3.35 以降）。
RENAME COLUMN — カラム名を変更します（3.25 以降）。
RENAME TO — テーブル自体の名前を変更します。

使えるのはこれだけです。カラムの型変更、NOT NULL の変更、CHECK 制約の変更、既存カラムへの FOREIGN KEY 追加などはできません。

-- サポートされていません:
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(255);
ALTER TABLE users ADD CONSTRAINT users_email_check CHECK (email LIKE '%@%');

SQLiteで直接できない変更が必要になったときは、公式が推奨する「テーブルの作り直し」が定番のやり方です。手順は多めですが、確実に動きます。

大きな変更はテーブルを作り直す

流れはシンプルです。まず欲しい形で新しいテーブルを作り、データをコピーし、古いテーブルを削除して、新しいテーブルを元の名前にリネームする。これを全部ひとつのトランザクションの中で実行します。

BEGIN;

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    name  TEXT NOT NULL
);
INSERT INTO users (email, name) VALUES ('a@x.com', 'Ada'), ('b@x.com', 'Boris');

-- email に CHECK 制約を追加したい。テーブルを再構築する:
CREATE TABLE users_new (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE CHECK (email LIKE '%@%'),
    name  TEXT NOT NULL
);

INSERT INTO users_new (id, email, name)
    SELECT id, email, name FROM users;

DROP TABLE users;
ALTER TABLE users_new RENAME TO users;

COMMIT;

SELECT * FROM users;

公式の SQLite ドキュメントではこの手順を「12ステップのレシピ」と呼んでいて、トリガーやビュー、外部キー参照まわりの注意点が追加で挙げられています。本番スキーマで実行する前に一度は目を通しておくとよいでしょう。とはいえ、ほとんどのケースでは上で紹介した4ステップ版で十分です。

ひとつ注意点。作り直したいテーブルを参照している外部キーがある場合は、マイグレーション前に PRAGMA foreign_keys = OFF を実行し、終わったら PRAGMA foreign_keys = ON で戻してください。これをやらないと、DROP TABLE の途中で参照整合性が壊れてしまうことがあります。

アプリケーション側からマイグレーションを制御する

SQLite のマイグレーションに必要な管理情報はシンプルなので、自作してしまうのが手っ取り早いです。Python なら標準ライブラリだけで次のように書けます。

-- 概念的には、アプリは起動時に次のような処理を行います:
--
--   current = SELECT user_version FROM PRAGMA;
--   for i in range(current + 1, len(MIGRATIONS) + 1):
--       run MIGRATIONS[i] in a transaction
--       PRAGMA user_version = i;
--
-- 各ステップで実行されるSQLは、上記のマイグレーションのようになります。

PRAGMA user_version;

重要なお約束ごとは次のとおりです。

マイグレーションは 1 から始めて連番で振る。欠番や順序の入れ替えはなし。
各マイグレーションは PRAGMA user_version = N の更新と一緒にトランザクションで包む。
一度コミットしてリリースしたマイグレーションは、二度と書き換えない。変更が必要なら新しいマイグレーションを追加する。

この最後のルールが、チーム開発でいちばん破られがちです。すでに同僚のデータベースに適用済みのマイグレーション 3 を後から編集してしまうと、相手のデータベースは気づかぬうちにあなたのものと永久にズレたままになります。

監査ログを残す

user_version でわかるのは、データベースが「いまどの段階にいるか」だけ。各ステップが「いつ」「何をした」のかまでは記録されません。そこで、ちょっとした管理用テーブルを用意しておくと便利です。

CREATE TABLE schema_migrations (
    version    INTEGER PRIMARY KEY,
    name       TEXT NOT NULL,
    applied_at TEXT NOT NULL DEFAULT (datetime('now'))
);

BEGIN;

CREATE TABLE posts (
    id     INTEGER PRIMARY KEY,
    title  TEXT NOT NULL,
    body   TEXT NOT NULL
);

INSERT INTO schema_migrations (version, name)
    VALUES (1, 'create_posts');
PRAGMA user_version = 1;

COMMIT;

SELECT * FROM schema_migrations;

これでマイグレーションごとに名前とタイムスタンプ付きの行が残ります。「なんでこのDB、コードが想定してないカラムが生えてるの?」とデバッグするときに地味に効いてきます。

ループの正解(source of truth)はあくまで PRAGMA user_version のままで、このテーブルは人間が読むためのものです。

ロールバック:トランザクションでできること、できないこと

SQLite の DDL はトランザクション対応です。たとえばマイグレーション 5 でテーブルを作って、データをコピーして、user_version を上げる——という途中でコピーがコケた場合、ROLLBACK するとすべて巻き戻ります。CREATE TABLE までなかったことになるので、データベースは BEGIN する前の状態にぴったり戻ります。

BEGIN;

CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance INTEGER NOT NULL);
INSERT INTO accounts (id, balance) VALUES (1, 100);

-- ここで何か問題が起こったとしましょう:
ROLLBACK;

-- テーブルは存在しません:
SELECT name FROM sqlite_master WHERE type='table' AND name='accounts';

ここまでで扱ったのは 失敗した マイグレーションの話です。コミットが成功したあとで「やっぱり戻したい」というケースには触れていません。そういうときは、変更を取り消すための ダウンマイグレーション を別途書くことになります。SQLite には自動でリバースする仕組みはありません。マイグレーション 7 でカラムを追加したなら、ダウン側ではそのカラムを DROP します。逆にマイグレーション 7 でカラムを削除していた場合、ダウン側でデータを復元することはできません。せいぜい同名のカラムを空の状態で作り直すくらいです。

実際のところ、小規模なプロジェクトではダウンマイグレーションを書かず、「やり直し」はバックアップに任せてしまうケースも多いです。バックアップさえちゃんと取っていれば、これはこれで合理的な選択です。

あとで楽になる、ちょっとした習慣

1 マイグレーション = 1 つの論理的な変更にする。 関係のない 3 つのカラムを 1 つのマイグレーションで追加するより、3 つに分けたほうがレビューしやすく、巻き戻しもしやすくなります。
本番のコピーに対してマイグレーションを試す。 大きなテーブルではスキーマ変更が遅いことがあり、それを本番で初めて知るのは避けたいものです。
リリース済みのマイグレーションは絶対に編集しない。 新しいものを追加します。
先にバックアップを取る。 CLI で .backup を一発打つ、あるいはデータベースを閉じた状態でファイルをコピーするだけでも、それなりのマイグレーションをやる前の保険として安上がりです。
PRAGMA foreign_keys に注意。 テーブル再構築の間はオフにし、終わったらオンに戻します。

規模が大きくなってきたら、専用ツールに頼りましょう。SQLAlchemy なら Alembic、Go なら golang-migrate、Node なら Knex、汎用なら Flyway などです。実行順、複数ランナーの競合、チームでの運用ルール — 自前でやれば結局再発明することになる部分を、これらのツールが面倒を見てくれます。考え方は前述のループと同じで、ツールはボイラープレートを消してくれるだけです。

次回: WAL モードと並行性

マイグレーションはたいてい、アプリケーションを止めている間か、排他ロックを取った状態で実行します。それ以外の時間、データベースは複数のコネクションから同時に読み書きを受け続けるわけですが、SQLite のデフォルトのジャーナルモードがそのワークロードに最適とは限りません。次のページでは WAL モードを取り上げ、何が変わるのか、いつ切り替えるべきかを解説します。

よくある質問

SQLiteのスキーマはどうやってバージョン管理する？

SQLiteにはデータベースごとに32bit整数のスロットuser_versionが用意されていて、PRAGMA user_versionで読み書きできます。アプリ起動時にこの値を読み、コード側が知っている最新マイグレーション番号と比較して、未適用のものを順番に流すだけです。専用のテーブルは不要ですが、監査ログ目的で別途テーブルを足すケースもよくあります。

SQLiteのマイグレーションはロールバックできる？

各マイグレーションをBEGIN; ... COMMIT;で囲んでおけば大丈夫です。途中で失敗すればROLLBACKでスキーマ変更もデータ変更もまとめて巻き戻せます。SQLiteのDDLはトランザクション対応なので安心です。ただしコミット済みのマイグレーションを後から戻したい場合は、自分でdownスクリプトを書いておく必要があります。SQLiteが自動生成してくれるわけではありません。

SQLiteのALTER TABLEが制限されているのはなぜ？

SQLiteはALTER TABLE ADD COLUMN、RENAME TABLE、RENAME COLUMN、DROP COLUMNには対応していますが、カラムの型や制約を変えるような任意の変更はできません。回避策はおなじみの「12ステップ」で、新しい形のテーブルを作ってINSERT INTO new_table SELECT ... FROM old_tableでデータをコピー、古いテーブルをDROPしてリネーム、という流れになります。

マイグレーションツールを使うべき？それとも自作？

小規模なアプリなら、番号付きの.sqlファイルをPRAGMA user_versionで順番に流すだけのループを30行ほど書けば十分動きます。大きめのプロジェクトでは、Alembic（Python）、golang-migrate（Go）、Knex（Node）などのツールが、順序管理やロック、チーム開発のワークフローまで面倒を見てくれるので、再発明する手間を省けます。

SQLiteマイグレーション入門：user_versionでスキーマ管理