FastAPIとAlembicを使ってマイグレーションファイルを管理しよう！

## 概要
Webアプリケーション作成時にmodelの定義を反映させたり、管理する場合はマイグレーションファイルを使用するのが一般的です
今回はFastAPIととAlembicを使ってマイグレーションファイルを管理する方法について解説します

## 前提
- SQLAlchemyのバージョン2以上を使用
- Alembicをインストール済み
- DBはPostgresを使用

## 実装
まず、alembicの初期設定を行います
以下のコマンドで初期化します
```
alembic init {マイグレーションファイル名}
```

今回はマイグレーションファイルを管理するフォルダの名前をmigrationsにします

```
poetry run alembic init migrations
Creating directory '/code/migrations' ...  done
Creating directory '/code/migrations/versions' ...  done
Generating /code/alembic.ini ...  done
Generating /code/migrations/script.py.mako ...  done
Generating /code/migrations/env.py ...  done
Generating /code/migrations/README ...  done
Please edit configuration/connection/logging settings in '/code/alembic.ini' before proceeding.
```

以下のようにmigrationsフォルダ配下にファイルが作成されたら成功です

```
└── application
    ├── __init__.py
    ├── migrations
    │   ├── README
    │   ├── env.py
    │   ├── script.py.mako
    │   └── versions
    ├── alembic.ini
    ├── database.py
    └── models.py
```

database.pyにFastAPIのアプリケーションとDBとの接続設定を記載します

```database.py
import os

from sqlalchemy import create_engine
from sqlalchemy.orm import DeclarativeBase, sessionmaker

SQLALCHEMY_DATABASE_URL = os.environ.get("SQLALCHEMY_DATABASE_URL")

engine = create_engine(SQLALCHEMY_DATABASE_URL)

# DBセッションのオブジェクトを生成
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)


# Baseクラスを元にモデルを定義する
# SQLAlchemyのバージョン2以降からdeclarative_baseではなく、DeclarativeBaseクラスを使うことが推奨されている
class Base(DeclarativeBase):
    pass

```

Modelの定義を行います

```models.py
from database import Base
from sqlalchemy import Boolean, Column, Integer, String


class Users(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    email = Column(String, unique=True)
    username = Column(String, unique=True)
    first_name = Column(String)
    last_name = Column(String)
    password = Column(String)
    is_active = Column(Boolean, default=True)
    role = Column(String)
    phone_number = Column(String)


class Todos(Base):
    __tablename__ = "todos"

    id = Column(Integer, primary_key=True, index=True)
    title = Column(String)
    description = Column(String)
    priority = Column(Integer)
    complete = Column(Boolean, default=False)

```

env.pyにSQLALCHEMY_DATABASE_URLが入るよう修正します

```env.py
from logging.config import fileConfig

from alembic import context
from database import SQLALCHEMY_DATABASE_URL
from models import Base
from sqlalchemy import engine_from_config, pool

# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config

# Interpret the config file for Python logging.
# This line sets up loggers basically.
if config.config_file_name is not None:
    fileConfig(config.config_file_name)

# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
# target_metadata = mymodel.Base.metadata
target_metadata = Base.metadata

# other values from the config, defined by the needs of env.py,
# can be acquired:
# my_important_option = config.get_main_option("my_important_option")
# ... etc.
config.set_main_option('sqlalchemy.url', SQLALCHEMY_DATABASE_URL)
```

.envファイルにSQLALCHEMY_DATABASE_URLを記載します
```.env
SQLALCHEMY_DATABASE_URL=postgresql://dev_user:password@db:5432/dev_db
```


## 実際にmigrationを実行してみよう！
以下のコマンドを使用することでBase.Metaを使って定義したmodelを参照して自動的にマイグレーションファイルの作成を行います

```
poetry run alembic revision --autogenerate -m "initial migration"
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added table 'todos'
INFO  [alembic.autogenerate.compare] Detected added index ''ix_todos_id'' on '('id',)'
INFO  [alembic.autogenerate.compare] Detected added table 'users'
INFO  [alembic.autogenerate.compare] Detected added index ''ix_users_id'' on '('id',)'
  Generating /code/migrations/versions/21ff600fcc09_initial_migration.py ...  done
```

```
└── application
    ├── __init__.py
    ├── migrations
    │   ├── README
    │   ├── env.py
    │   ├── script.py.mako
    │   └── versions
    │       └── 21ff600fcc09_initial_migration.py
    └── alembic.ini
```


以下のマイグレーションファイルが作成されます

```21ff600fcc09_initial_migration.py
"""initial migration

Revision ID: 21ff600fcc09
Revises: 
Create Date: 2025-02-22 05:59:10.430466

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision: str = '21ff600fcc09'
down_revision: Union[str, None] = None
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('todos',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('title', sa.String(), nullable=True),
    sa.Column('description', sa.String(), nullable=True),
    sa.Column('priority', sa.Integer(), nullable=True),
    sa.Column('complete', sa.Boolean(), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_todos_id'), 'todos', ['id'], unique=False)
    op.create_table('users',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('email', sa.String(), nullable=True),
    sa.Column('username', sa.String(), nullable=True),
    sa.Column('first_name', sa.String(), nullable=True),
    sa.Column('last_name', sa.String(), nullable=True),
    sa.Column('password', sa.String(), nullable=True),
    sa.Column('is_active', sa.Boolean(), nullable=True),
    sa.Column('role', sa.String(), nullable=True),
    sa.Column('phone_number', sa.String(), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('email'),
    sa.UniqueConstraint('username')
    )
    op.create_index(op.f('ix_users_id'), 'users', ['id'], unique=False)
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index(op.f('ix_users_id'), table_name='users')
    op.drop_table('users')
    op.drop_index(op.f('ix_todos_id'), table_name='todos')
    op.drop_table('todos')
    # ### end Alembic commands ###

```

以下のコマンドで作成されたマイグレーションファイルの内容をDBへ反映させます

```
poetry run alembic upgrade head
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> 21ff600fcc09, initial migration
```

以下のようにマイグレーションファイルの内容がDBに反映されたら成功です

```
psql -U dev_user

dev_user=# \c dev_db
You are now connected to database "dev_db" as user "dev_user".
dev_db=# \dt
              List of relations
 Schema |      Name       | Type  |  Owner   
--------+-----------------+-------+----------
 public | alembic_version | table | dev_user
 public | todos           | table | dev_user
 public | users           | table | dev_user
(3 rows)

dev_db=# \d
               List of relations
 Schema |      Name       |   Type   |  Owner   
--------+-----------------+----------+----------
 public | alembic_version | table    | dev_user
 public | todos           | table    | dev_user
 public | todos_id_seq    | sequence | dev_user
 public | users           | table    | dev_user
 public | users_id_seq    | sequence | dev_user
(5 rows)

dev_user=# \d todos
                                    Table "public.todos"
   Column    |       Type        | Collation | Nullable |              Default              
-------------+-------------------+-----------+----------+-----------------------------------
 id          | integer           |           | not null | nextval('todos_id_seq'::regclass)
 title       | character varying |           |          | 
 description | character varying |           |          | 
 priority    | integer           |           |          | 
 complete    | boolean           |           |          | 
Indexes:
    "todos_pkey" PRIMARY KEY, btree (id)
    "ix_todos_id" btree (id)

dev_user=# \d users
                                    Table "public.users"
    Column    |       Type        | Collation | Nullable |              Default              
--------------+-------------------+-----------+----------+-----------------------------------
 id           | integer           |           | not null | nextval('users_id_seq'::regclass)
 email        | character varying |           |          | 
 username     | character varying |           |          | 
 first_name   | character varying |           |          | 
 last_name    | character varying |           |          | 
 password     | character varying |           |          | 
 is_active    | boolean           |           |          | 
 role         | character varying |           |          | 
 phone_number | character varying |           |          | 
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "ix_users_id" btree (id)
    "users_email_key" UNIQUE CONSTRAINT, btree (email)
    "users_username_key" UNIQUE CONSTRAINT, btree (username)
```

## 参考
https://alembic.sqlalchemy.org/en/latest/tutorial.html#creating-an-environment

https://www.nashruddinamin.com/blog/how-to-use-alembic-for-database-migrations-in-your-fastapi-application