SQLite/PostgreSQL両対応のAlembic移行実践 — batch_alter_tableと循環FKのuse_alter

「開発はSQLite、本番はPostgreSQL」という構成、便利ですよね。セットアップは軽いし、本番は堅牢。ただしこの構成には、マイグレーションで一度は踏む罠があります。

SQLiteは既存テーブルの制約変更(ALTER)ができません。 そのため、PostgreSQLでは普通に通るAlembic移行が、SQLiteだと NotImplementedError で落ちます。

この記事では、実際のプロジェクトでこの罠を踏んで解決したときの記録をもとに、次の2つを紹介します。

• 制約変更を両DB対応で書く op.batch_alter_table
• 循環外部キーを解決する use_alter=True

なぜ batch_alter_table が必要なのか

SQLiteの ALTER TABLE がサポートするのは、列追加などごく一部の操作だけです。unique制約や外部キーの追加・削除はできません。Alembic公式ドキュメントにも、この制約が次のように明記されています。

The SQLite database presents a challenge to migration tools in that it has almost no support for the ALTER statement
(出典: Alembic公式ドキュメント batch.html)

実際、Alembicで普通に op.drop_constraint を書くと、SQLiteでは NotImplementedError になります。

Alembic側の答えが op.batch_alter_table です。SQLiteでは内部的に、

1. 新しい定義でテーブルを作る
2. データをコピーする
3. 旧テーブルを消して改名する

という「作り直し」(copy-and-move)を自動でやってくれます。うれしいのは、PostgreSQLのように制約ALTERができるDBでは同じコードが普通のALTER文になること。つまり1つの移行コードで両対応できます。

実装例: unique制約を張り替える

実際の移行ファイルを見てみましょう。unique制約を (metric_name) 単独から (metric_name, destination_id) の複合キーに変える移行です。

def upgrade() -> None:
    with op.batch_alter_table("metric_definitions") as batch_op:
        batch_op.add_column(sa.Column("destination_id", sa.String(), nullable=True))
        batch_op.drop_constraint("uq_metric_definition_name", type_="unique")
        batch_op.create_unique_constraint(
            "uq_metric_definition_name_destination", ["metric_name", "destination_id"]
        )
        batch_op.create_foreign_key(
            "fk_metric_definitions_destination_id", "destinations", ["destination_id"], ["id"]
        )

ポイントは1つだけ。制約には必ず名前を付けることです。無名の制約は、downgradeやbatch再構築のときに「どれを消すのか」を指定できず、移行の可逆性が壊れます。

downgrade側は同じ操作を逆順に書きます。書いたら upgrade→downgrade→re-upgrade の往復をSQLiteで実行して、全部通ることを確認しておきましょう(このプロジェクトでも実行して確認済みです)。

循環FKには use_alter=True

もう1つの罠が循環外部キーです。このプロジェクトでは、公開ジョブ(publication_jobs)と公開レコード(publications)がお互いを参照し合う設計でした。AとBが相互参照していると、「どちらのテーブルを先に作るか」が決められません。実際、alembicのautogenerate実行時にSAWarningが出ました。SQLAlchemy公式ドキュメントも、この種の相互依存について次のように説明しています。

This approach can't work when two or more foreign key constraints are involved in a 'dependency cycle', where a set of tables are mutually dependent on each other, assuming the backend enforces foreign keys
(出典: SQLAlchemy公式ドキュメント ForeignKeyConstraint.params.use_alter)

解決策はシンプルで、SQLAlchemyのFK定義に use_alter=True を付けるだけです。これで片方のFKがCREATE TABLE文から外れ、テーブルを全部作った後のALTER文として発行されます。

publication_id: Mapped[str | None] = mapped_column(
    ForeignKey("publications.id", use_alter=True,
               name="fk_publication_jobs_publication_id"),
    nullable=True,
)

ここでも名前の明示が効いてきます。use_alterで後付けされるFKは、名前がないとdropのときに特定できません。

検証は「往復」で

移行を書いたら、次の3工程をセットで実行します。

alembic upgrade head      # 1. 上げる
alembic downgrade -1      # 2. 戻す
alembic upgrade head      # 3. もう一度上げる

「upgradeが通ったからOK」で済ませた移行は、ロールバックが必要になった瞬間に破綻します。batch_alter_tableを使う移行はdowngrade側もbatch文脈で書く必要があるので、この往復検証で書き漏れが機械的に見つかります。このプロジェクトでは、新しい移行を追加するたびにこの往復を実行するのをルールにしています。

この記事を書くにあたっても、実際に隔離した検証用DBで対象移行の往復を試しました。alembic upgrade head で全21移行を適用したあと、対象移行の直前まで戻し、再適用したところエラーは出ませんでした。再適用後のスキーマを sqlite3 で直接検査すると、metric_definitions テーブルに destination_id 列が実在し、複合unique制約に対応する自動インデックスが2つ生成されていました。「往復してもスキーマが正しく戻る」ことを、コードを読むだけでなく手元で確かめておくと安心です。

ハマりどころ

実際に運用してみて気づいた注意点を3つ挙げておきます。

その1: 実行時間がDBごとに全然違う。 batch_alter_tableはSQLiteだとテーブル丸ごとコピー、PostgreSQLだと普通のALTERです。大きいテーブルでは所要時間の見積もりを分けて考えましょう。

その2: batch再構築はインデックスやトリガーも巻き込む。 再構築中は元テーブルのインデックス・トリガー・CHECK制約も作り直しの対象になります。alembic upgrade --sql で発行されるSQLを一度目視しておくと安心です。

その3: autogenerateは魔法ではない。 循環FKやbatchの必要性までは自動で解決してくれません。生成された移行をそのまま適用せず、SQLiteとPostgreSQLの両方で実行確認する運用をおすすめします。

まとめ

SQLite開発・PostgreSQL運用のAlembic移行は、この4点を押さえれば安定します。

1. 制約変更は op.batch_alter_table で書く
2. 循環FKはモデル定義に use_alter=True を付ける
3. 制約とFKには必ず名前を明示する
4. upgrade→downgrade→re-upgrade の往復検証を習慣にする

どれも実プロジェクトの移行ファイルと検証記録に基づく実践知です。同じ構成のプロジェクトの参考になればうれしいです。

---

注記(本記事の前提と限界)

• SQLAlchemy 2.x/Alembic 1.x系・SQLite/PostgreSQLの組み合わせでの経験に基づく
• batch移行はテーブル全体のコピーを伴うため、大規模テーブルでは実行コストとロック時間を事前に見積もる必要がある
• 掲載コードは対象リポジトリの実移行ファイルからの抜粋であり、nullable=Trueの新列追加を伴うケースに限る
• SQLiteでは外部キー制約の後付けALTER自体が実行されないため、循環FKの実効性の検証はPostgreSQL実機で行う必要がある
• 検証は隔離した使い捨てSQLite DBに対する単発実行であり、実行後は破棄した。PostgreSQL実機での往復検証は別途プロジェクト側で実施済み(progress.md参照)だが、本記事執筆時点で筆者自身が再実行したものではない。SQLiteでの往復成功はPostgreSQLでの成功を保証しない
• 注意点は本プロジェクトで実際に遭遇した範囲に基づくもので、網羅的なリストではない
• SQLAlchemy 2.x/Alembic 1.x系での経験に基づき、他のORM・移行ツールには直接は適用できない