Zenn
🕌

生SQLからの卒業：ORM（SQLAlchemy）・Alembicで始めるPythonのDB設計とマイグレーション管理

に公開
PostgreSQL
SQLAlchemy
alembic
psycopg2
tech

概要

本記事では、SQLAlchemyとAlembicを使ったマイグレーション管理を行う実践的なワークフローを紹介します。

また、筆者自身が「生SQL（psycopg2）での運用」から「ORMを用いた構造管理」へと移行する過程で得た気づきや学びについても記載します。

なぜ今さらORM？筆者の気づきと背景

筆者はこれまで、Pythonのバックエンド開発において psycopg2 を用いてSQLを直接実行し、DBアクセスを行ってきました。

実務で複数のテーブルを結合した処理や検索条件を構築する仕組みも、自前の関数で構築して運用していました。A5:SQL Mk-2でER図とDDLを作成し、PostgreSQLに適用してシステムを動かすこともできていました。

しかし、以下のような課題を抱えていました：

  • テーブル構造変更時にSQL文の修正が必要で、漏れやミスが発生しやすい
  • DB構造とコードの乖離が起きやすく、保守性が低下
  • 複数人での開発・レビュー時に、どこで何が変更されたのか把握しにくい

このような課題を経て、改めてSQLAlchemyやAlembicに触れたところ、「これを知らなかったこと自体が遠回りだった」と感じました。

とはいえ、「知らなかったことが恥」なのではなく、「気づき、学び、切り替えたこと」が最大の成果であると今は思っています。

本記事は、同じように「今までORMを使ってこなかった」人にとっての参考や共感になればと願って書いています。

1. psycopg2（生SQL）とSQLAlchemy（ORM）の違い

項目 psycopg2（生SQL） SQLAlchemy（ORM）
記述方法 直接SQLを書く Pythonクラスで表現
安全性 SQLインジェクション対策が必要 自動的に安全に処理
保守性 スキーマ変更時にSQL修正が必要 モデルを更新するだけ
学習コスト 低（ただし書く量が多い） 初期はやや高いが長期的に効率的

2. 環境構成（ローカルPostgreSQL + Python）

今回は、検証のためDockerを用いて、DB（PostgreSQL）とPythonのコンテナを構築する。
データベース（testdb）に、usersというテーブルを作るという操作をする。

筆者検証環境

  • OS Windows11（Home）
  • WSL2（ubuntu22.04）
  • Docker導入済みであること

使用ツール

  • PostgreSQL（Docker）
  • SQLAlchemy
  • Alembic
  • sqlacodegen
  • A5:SQL Mk-2（ER図・DDL生成）

ディレクトリ構成（例）

<project_root>
├── app/
│   ├── main.py             # 実際にDBにアクセスして処理を行う
│   ├── database.py         # DBとの接続設定とセッション管理
│   └── models/             # テーブル構造の定義
│       ├── __init__.py
│       └── user.py
├── alembic.ini
├── alembic/
│   ├── env.py
│   └── versions/
└── docker-compose.yml

※ alembic.iniやalembicディレクトリとそれ以下のファイルはAlembicを使ったマイグレーション管理の際に自動作成されるので、最初は作成不要

ファイルが多いですよね。これが少々とっつきにくい原因になるかもしれません。ここで、簡単に説明します。

ファイル/フォルダ 役割
app/models/ SQLAlchemyによるテーブル定義（= モデル定義）を置く場所
app/database.py DB接続のためのクライアント設定やセッション作成を担う
app/main.py 実行スクリプト。実際にCRUDを行う処理を記述
alembic/ Alembicによるマイグレーション管理の設定と履歴管理
docker-compose.yml DBやアプリをまとめて立ち上げるための構成ファイル

各ファイルの中身や使い方はこのあと丁寧に解説していきます。

ファイルの内容

docker-compose.yml

services:
  db:
    image: postgres:16.4-bullseye
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: testdb
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data

  app:
    build:
      context: .
      dockerfile: ./app/Dockerfile
    depends_on:
      - db
    volumes:
      - .:/application
    working_dir: /application
    environment:
      DATABASE_URL: postgresql+psycopg2://postgres:postgres@db:5432/testdb
      PYTHONPATH: /application
    tty: true

volumes:
  pgdata:

app/Dockerfile

FROM python:3.11-slim

WORKDIR /application
COPY app/requirements.txt ./
RUN pip install --no-cache-dir -r requirements.txt
COPY . .

app/main.py

from app.database import SessionLocal
from app.models import User


def main():
    db = SessionLocal()
    db.add(User(name="Taro", email="taro@example.com"))
    db.commit()
    for user in db.query(User).all():
        print(f"{user.id}: {user.name} ({user.email})")
    db.close()


if __name__ == "__main__":
    main()

※ dcoker-compose.yml の PYTHONPATH: /application によって from models.user import User が可能になっています。ローカル実行する場合は PYTHONPATH を設定するか、python -m app.main とする必要があります。

app/database.py

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, declarative_base
import os

DATABASE_URL = os.getenv("DATABASE_URL", "postgresql+psycopg2://postgres:postgres@localhost:5432/testdb")

engine = create_engine(DATABASE_URL)
SessionLocal = sessionmaker(bind=engine, autoflush=False, autocommit=False)
Base = declarative_base()

app/models/user.py
今回はusersというテーブルを作るためのuser.pyというファイルですが他のテーブルを作ることももちろんできますし、テーブル数が少ないならば1ファイル内に複数のモデルを定義することができます

from sqlalchemy import Column, Integer, String
from app.database import Base


class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, nullable=False)
    email = Column(String, unique=True, index=True)

app/models/init.py

from .user import User

app/requirements.txt

alembic==1.16.1
greenlet==3.2.2
Mako==1.3.10
MarkupSafe==3.0.2
psycopg2-binary==2.9.10
SQLAlchemy==2.0.41
typing_extensions==4.13.2

検証環境立ち上げ

Docker導入済みであれば以下のコマンドで環境を立ち上げられる

docker compose build
docker compose up -d

3. マイグレーション（migration）とは？

  • モデル定義（SQLAlchemy）とDB実体の差分を検出し、
  • 差分に基づいた構造変更SQL（マイグレーション）を自動生成し、
  • DBに 安全に適用 する仕組み

手作業でALTER TABLEを書かずに、コードでDB構造を管理できます。

4. Alembicを使ったマイグレーション管理

コンテナの中に入る

現在稼働中のコンテナをdocker container lsで確認し、appが動いているほうのコンテナの名前を確認する（orm_sample-app-1）

docker container ls
CONTAINER ID   IMAGE                    COMMAND                   CREATED          STATUS          PORTS                                         NAMES
5c3225383cdc   orm_sample-app           "python3"                 36 minutes ago   Up 36 minutes                                                 orm_sample-app-1
86b952061410   postgres:16.4-bullseye   "docker-entrypoint.s…"   38 minutes ago   Up 38 minutes   0.0.0.0:5432->5432/tcp, [::]:5432->5432/tcp   orm_sample-db-1

以下のコマンドで、コンテナのbashを呼び出し各種操作をする（迷ったらexitで抜けられる）

docker exec -it orm_sample-app-1 bash

初期化

pip install alembic
alembic init alembic

alembic.iniとalembicディレクトリ内に設定ファイルが生成される。

コンテナ内で生成したファイルは、ユーザがrootになっているため権限がない

sudo chown <linux_username>:<linux_username> ./*
sudo chown <linux_username>:<linux_username> ./alembic/*

を実行してファイルの所有者を自分に変更をする

設定

ここまでの手順の通りにやっている場合、volumeマウントでホストとコンテナのファイルが同期しているので、ホスト側の任意のエディタ（vscodeとかでよい）で編集する。

  • alembic.ini に sqlalchemy.url を設定
    docker-compose.yml内のDATABASE_URLに設定した値と同じものを入れる
sqlalchemy.url = postgresql+psycopg2://postgres:postgres@db:5432/testdb
  • env.py に以下を追記：
from app.database import Base
import app.models
# target_metadata = Noneを以下に書き換え
target_metadata = Base.metadata

マイグレーションファイル作成と適用

# 以下実行するとalembic/versions下に`<乱数>_create_users_table.py`が生成される
alembic revision --autogenerate -m "create users table"
# 以下を実行するとデータベースにテーブルが作られる
alembic upgrade head

5. 動作確認

コンテナに入る

docker exec -it orm_sample-app-1 bash

レコード挿入コード実行

python app/main.py

psqlで動作確認する

接続

もしpostgresqlがホストにインストールされている場合はホストから以下が実行可能

psql -h 127.0.0.1 -p 5432 -U postgres
# データベースのパスワードを聞かれるのでpostgresと入力

もし、postgresqlがホストにインストールされていない場合は以下のようにDBに接続する

docker exec -it <dockerコンテナの名前> bash
psql -U postgres

DB内で動作確認

# 接続するデータベースを指定
\c testdb
You are now connected to database "testdb" as user "postgres".

# テーブル一覧の確認（usersがあればmigrationは成功している）
testdb=# \dt
              List of relations
 Schema |      Name       | Type  |  Owner
--------+-----------------+-------+----------
 public | alembic_version | table | postgres
 public | users           | table | postgres

# テーブルの中身確認
testdb=# select * from users;
 id | name |       email
----+------+-------------------
  1 | Taro | taro@example.com
(1 rows)

6. ER図→DDL→SQLAlchemyモデル化（sqlacodegen）

手順

  1. A5:SQL Mk-2でER図を描き、DDLをエクスポート
  2. ローカルのPostgreSQLに適用
  3. 以下のコマンドを実行（appコンテナ内で）：
pip install sqlacodegen
sqlacodegen postgresql+psycopg2://postgres:password@localhost:5432/testdb > generated_models.py
  1. 出力されたモデルを models/ に整理して利用

まとめ

ステップ 目的
DDL作成（A5） ER図ベースの設計
DB反映 PostgreSQLにテーブル作成
モデル生成 sqlacodegenでPythonクラス化
マイグレーション管理 Alembicで安全な構造変更

SQLとORM、そしてマイグレーションの連携によって、開発・保守の生産性が劇的に向上することが期待されます。

Discussion