SQLAlchemyでマイグレーションした時にモデルとスキーマを同期する

pythonでテスト用DBを立てようと思ったが、1からの環境構築が面倒だったので
Docker＋マイグレーションツールでサクっと構築したいと思った。

ついでにDB周りのコードもほぼ手書きだったので、O/Rマッパーも導入
SQLAlchemyとSQLAlchemy-Migrateで実現できそうだったのでやってみた。


# 実行環境

- Windows10
- Python 3.6.3
- MySQL 14.14 (Docker Image)
- SQLAlchemy 1.2.2
- SQLAlchemy-Migrate 0.11.0
- PyMySQL 0.8.0
- mysqlclient 1.3.12

## インストール

```bash
pip install sqlalchemy
pip install sqlalchemy-migrate
pip install pymysql
pip install mysqlclient
```

## テスト用DB（MySQL）

DockerでサクっとローカルDBを立てます。

```bash
docker run --name mysql -e MYSQL_ROOT_PASSWORD=mysql -p 3306:3306 -d mysql
```

データベースは事前に用意する必要があるので`archive`データベースを作成します。

```bash
docker exec -it mysql bash

root@# mysql -uroot -pmysql
mysql> create database archive;
mysql> exit
root@# exit
```

# SQLAlchemy-Migrateでマイグレーション

DBマイグレーションにはSQLAlchemy-Migrateを使用します。

- github
    https://github.com/openstack/sqlalchemy-migrate

- Documentation
    https://sqlalchemy-migrate.readthedocs.io/en/latest/

マイグレーションプロジェクト用のファイル類をコマンド`migrate create <作成されるフォルダ名> "<リポジトリID>"`で生成します。
`db`は作業ディレクトリです。名前はなんでもいいです。

```bash
mkdir db
cd db
migrate create migrate "archive"
```

以下の構成で生成されます。

```bash:tree
project
|
└─db
  └─migrate
      │  manage.py
      │  migrate.cfg
      │  README
      │  __init__.py
      │
      └─versions
              __init__.py
```

## manage.pyの編集

`manage.py`を編集し、データベースurlとリポジトリパスを設定します。
データベースurlは`dialect+driver://username:password@host:port/database`の書式で記述し、データベース毎の具体的な記述方法は[Database Urls](http://docs.sqlalchemy.org/en/latest/core/engines.html#database-urls)に一覧があります。
今回はMySQLを使用するので以下のように`manage.py`を編集、リポジトリパスは`migrate`ディレクトリ配下ですが、`manage.py`から見たらカレントなので`'.'`を指定します。

```py3:manage.py
if __name__ == '__main__':
    main(debug='False', url='mysql+pymysql://root:mysql@localhost/archive?charset=utf8', repository='.')
```

## バージョン管理テーブルの生成

データベースにバージョン管理用のテーブルが必要になる為、以下コマンドで生成します。

```bash
cd migrate
python manage.py version_control
# --> migrate_versionテーブルが作られる
```

本来はデータベースurlとリポジトリパスが引数に必要ですが、`manage.py`に定義済みの為上記のように省略してOKです。

```bash
# manage.pyで未定義、もしくは別環境に作成する場合
python manage.py version_control mysql+pymysql://root:mysql@localhost/archive?charset=utf8 .
```

### バージョン確認方法

作成されたmigrate_versionテーブルを参照してもいいし、`migrate version .`コマンドでも確認できます。

## booksテーブル作成

マイグレーションはスキーマ変更のスクリプトを元に行いますが、以下のコマンドでテンプレートから生成することができます。

```bash
python manage.py script "add books table"
# --> versions/001_add_books_table.pyが作成される
```

生成された`001_add_books_table.py`にテーブルスキーマを定義し、テーブル追加のマイグレーションファイルとします。

```py3:001_add_books_table.py
from sqlalchemy import *
from migrate import *
from sqlalchemy.dialects.mysql import BIGINT, VARCHAR, DATETIME, TEXT

meta = MetaData()
table = Table(
    'books', meta,
    Column('id', BIGINT(unsigned=True), primary_key=True),
    Column('title', TEXT, nullable=False),
    Column('author', VARCHAR(64), nullable=False),
    Column('created_at', DATETIME, nullable=False),
    Column('updated_at', DATETIME, nullable=False))


def upgrade(migrate_engine):
    meta.bind = migrate_engine
    table.create()


def downgrade(migrate_engine):
    meta.bind = migrate_engine
    table.drop()
```

## マイグレーション実行

`upgrade`コマンドで実行です。

```bash
python manage.py upgrade
# --> booksテーブルが作成される
```

`downgrade`コマンドで元に戻せますし、どこまで戻るかも指定できます。

```bash
python manage.py downgrade 0
# --> booksテーブルが破棄される（バージョン0まで戻る）
```

# SQLAlchemyでDBアクセス

SQLAlchemyを使ってDBに触れてみます。

- SQLAlchemy公式
    http://www.sqlalchemy.org/

- チュートリアル
    http://docs.sqlalchemy.org/en/latest/orm/tutorial.html

チュートリアルを参考に、`db`ディレクトリ直下にファイルを作成していきます。

```py3:__init__.py
#空ファイルです
```

```py3:session.py
# !/usr/bin/env python3
# coding: utf-8
""" session.py """

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine(
    'mysql+pymysql://root:mysql@localhost/archive?charset=utf8', echo=True)
Session = sessionmaker(bind=engine)
```

`Book`クラスとしてテーブルを定義し、動作確認用に`find`メソッドを定義しています。
テーブルスキーマの部分はマイグレーションのスクリプト`001_add_books_table.py`からコピって整形しています。
ここちょっと**イケてない**実装な感じがしましたが、最初だけだと我慢して次に進みます。

```py3:book.py
# !/usr/bin/env python3
# coding: utf-8
""" book.py """

from sqlalchemy import Column
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.mysql import BIGINT, VARCHAR, DATETIME, TEXT
from db.session import Session

Base = declarative_base()


class Book(Base):
    __tablename__ = 'books'

    id = Column('id', BIGINT(unsigned=True), primary_key=True)
    title = Column('title', TEXT, nullable=False)
    author = Column('author', VARCHAR(64), nullable=False)
    created_at = Column('created_at', DATETIME, nullable=False)
    updated_at = Column('updated_at', DATETIME, nullable=False)

    @classmethod
    def find(cls, id):
        """ find record by id """
        session = Session()
        record = session.query(Book).filter(Book.id == id).first()
        session.close()
        return record

```

`tests`ディレクトリを作成して、テストも書いとく

```py3:tests/test_book.py
# !/usr/bin/env python3
# coding: utf-8
""" test_book.py """

import unittest
from db.book import Book


class TestBook(unittest.TestCase):
    """ test Book class """

    def test_book_find(self):
        """ Book.find return not None """
        ret = Book.find(1)
        self.assertIsNotNone(ret)


if __name__ == "__main__":
    unittest.main()
```

この時点でのディレクトリ構成

```bash
project
│
├─db
│  │  book.py
│  │  session.py
│  │  __init__.py
│  │
│  └─migrate
│      │  manage.py
│      │  migrate.cfg
│      │  README
│      │  __init__.py
│      │
│      └─versions
│              001_add_books_table.py
│              __init__.py
│
└─tests
        test_book.py
```

# テーブルスキーマの変更

せっかく（？）マイグレーションできるようにしたので、テーブルスキーマを変えてみたいと思います。

## isbnカラムの追加

マイグレーションファイルの作成

```bash
python manage.py script "add isbn column"
# --> 002_add_isbn_column.pyが作成される
```

```py3:002_add_isbn_column.py
from sqlalchemy import *
from migrate import *
from sqlalchemy.dialects.mysql import BIGINT


def upgrade(migrate_engine):
    meta = MetaData(bind=migrate_engine)
    table = Table('books', meta, autoload=True)
    isbn = Column('isbn', BIGINT(13),  nullable=False)
    isbn.create(table)


def downgrade(migrate_engine):
    meta = MetaData(bind=migrate_engine)
    table = Table('books', meta, autoload=True)
    table.c.isbn.drop()
```

マイグレーション実行

```bash
python manage.py upgrade
```

続けて`Book`クラスも修正します。

```py3:book.py

class Book(Base):
    __tablename__ = 'books'

    id = Column('id', BIGINT(unsigned=True), primary_key=True)
    title = Column('title', TEXT, nullable=False)
    author = Column('author', VARCHAR(64), nullable=False)
    isbn = Column('isbn', BIGINT(13),  nullable=False)          # <-- 追加
    created_at = Column('created_at', DATETIME, nullable=False)
    updated_at = Column('updated_at', DATETIME, nullable=False)

```

ここで突然**ブチキレ**ですよ

マイグレーションする度にテーブルクラスを手動で変更するのは**イケてない**です。
マイグレーションファイルにスキーマ定義を書いているので、テーブルクラスが持つスキーマ定義も`upgrade` `downgrade`に追従して欲しいのが期待するところです。

...

調べたらやっぱりありました。[Automap](http://docs.sqlalchemy.org/en/latest/orm/extensions/automap.html)を使えばできそうです。

# Automapを使用した実装

`declarative_base`の代わりに`automap_base`を使います。

```py3:book.py
# !/usr/bin/env python3
# coding: utf-8
""" book.py """

from sqlalchemy import Column
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.dialects.mysql import BIGINT, VARCHAR, DATETIME, TEXT
from db.session import Session, engine

Base = automap_base()


class Book(Base):
    __tablename__ = 'books'

    @classmethod
    def find(cls, id):
        """ find record by id """
        session = Session()
        record = session.query(Book).filter(Book.id == id).first()
        session.close()
        return record


Base.prepare(engine, reflect=True)
```

`Book`クラスからスキーマ情報が一切無くなりました！**イケてる！**
ポイントは`Base.prepare(engine, reflect=True)`です。ここで`engine`からスキーマ情報を読み出し、`Book`クラスに注入します。
注意点として、`Base.prepare(engine, reflect=True)`は`Book`クラス定義の後でなければなりません。
先に呼び出すと`InvalidRequestError: SQL expression, column, or mapped entity expected`を吐きます。

```py3:test_book.py
    def test_access_isbn(self):
        """ Book#isbn can access """
        ret = Book.find(1)
        self.assertEqual(9783161484100, ret.isbn)
```

追加のテストコードもきちんと通りました。


## 課題：セッション管理について

例として`find`メソッドを定義していましたが、メソッド内でセッションの`open`と`close`をしているのがやっぱり**イケてない**気がします。
`find`メソッドをトランザクション処理の一部として利用できませんし、[Session Frequently Asked Questions](http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#session-frequently-asked-questions)でアンチパターンとして紹介もされています。

```py3:book.py
    @classmethod
    def find(cls, id):
        """ find record by id """
        session = Session()
        record = session.query(Book).filter(Book.id == id).first()
        session.close()
        return record
```

どうしたらいいかな。。。

### 追記 Instance <Book at 0x697a4f0> is not bound to a Session

セッションをcommit、closeした後にセッション内のインスタンスにアクセスすると以下エラーが発生した。

```
sqlalchemy.orm.exc.DetachedInstanceError: Instance <Book at 0x697a4f0> is not bound to a Session; attribute refresh operation cannot proceed (Background on this error at: http://sqlalche.me/e/bhk3)
```

どうもデフォルトだとcommitするとセッション内のインスタンスが全て期限切れになるようだ。

> Another behavior of commit() is that by default it expires the state of all instances present after the commit is complete. This is so that when the instances are next accessed, either through attribute access or by them being present in a Query result set, they receive the most recent state. To disable this behavior, configure sessionmaker with expire_on_commit=False.

最後に書いてある通り、`sessionmaker`で`expire_on_commit=False`オプションを付けると回避可能

```py3
Session = sessionmaker(bind=engine, expire_on_commit=False)
```