SQLModelのcreated_atとupdated_atカラムを自動更新する２つの方法（MySQL側, アプリ側）

# はじめに

SQLModelはFastAPIと同じ製作者によって開発されている、SQLAlchemyとPydanticを組み合わせたPythonのORMです。

https://sqlmodel.tiangolo.com/

本記事では、created_atとupdated_atカラムをMySQL側で更新する方法と、アプリケーション側で更新する方法の２つのアプローチを紹介します。



# SQLModelのモデル定義

```item.py
from datetime import datetime
from sqlmodel import Field, SQLModel, text

class Item(SQLModel, table=True):
    id: int = Field(primary_key=True, nullable=False)
    name: str = Field(default=None)

    # --------------------------------
    # MySQL側で時刻設定するカラム
    # --------------------------------
    created_at_db: datetime = Field(
        nullable=False,
        sa_column_kwargs={
            "server_default": text("CURRENT_TIMESTAMP"),
        },
    )

    updated_at_db: datetime = Field(
        nullable=False,
        sa_column_kwargs={
            "server_default": text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"),
        },
    )

    # --------------------------------
    # アプリ側で時刻設定するカラム
    # --------------------------------
    created_at_app: datetime = Field(
        nullable=False,
        default_factory=datetime.now,
    )

    updated_at_app: datetime = Field(
        nullable=False,
        default_factory=datetime.now,
        sa_column_kwargs={"onupdate": datetime.now},
    )
```

上記のモデルからテーブルを作成するとき、次のSQLが生成されます。
```sql
CREATE TABLE item (
        id INTEGER NOT NULL AUTO_INCREMENT, 
        name VARCHAR(255), 
        created_at_db DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 
        updated_at_db DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
        created_at_app DATETIME NOT NULL, 
        updated_at_app DATETIME NOT NULL, 
        PRIMARY KEY (id)
)
```


## MySQL側で時刻設定するカラム
SQLに記述された句によりMySQLの機能を使って更新します。

```DEFAULT CURRENT_TIMESTAMP```はレコードが作成された時に、初期値として現在日時を設定します。
```ON UPDATE CURRENT_TIMESTAMP```はレコードが更新された時に、現在日時を設定します。

## アプリ側で時刻設定するカラム
Python側で時刻を設定します。

Fieldの引数の```default_factory```と、```sa_column_kwargs```の```onupdate```でそれぞれ、作成時と更新時に```datetime.now```関数を使う設定しています。

# 動作確認

SQLModelでレコードの作成と更新をおこない動作確認します。
MySQLの時刻はUTCに設定しておきます。


<details>

<summary>実行手順</summary>

## MySQLセットアップ
DockerでMySQLを起動します
```docker-compose.yml
version: "3"
services:
  db:
    image: mysql:5.7
    volumes:
      - ./volume:/var/lib/mysql
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: mydb
      TZ: "UTC"
    ports:
      - "3306:3306"
```

```sh
docker-compose up -d
```

## 実行コード

```main.py
import time
from item import Item
from sqlmodel import Session, SQLModel, create_engine

def main():
    mysql_url = "mysql+pymysql://root:root@localhost:3306/mydb?charset=utf8mb4"
    engine = create_engine(mysql_url, echo=True)
    # テーブル作成
    SQLModel.metadata.create_all(engine)

    with Session(engine) as session:
        # データ作成
        item = Item(name="test1")
        session.add(item)
        session.commit()

        # 5秒後にデータ更新
        time.sleep(5)
        item = session.get(Item, 1)
        item.name = "test1(updated)"
        session.add(item)
        session.commit()

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

```sh
$ python main.py
```

</details>

## 発行されるSQL
```created_at_app```と```updated_at_app```はアプリ側で値を設定しています。
MySQL側の機能で更新する方のカラムは、特にアプリ側での指定はありません。

作成時のSQL
```
INSERT INTO item (name, created_at_app, updated_at_app) VALUES (%(name)s, %(created_at_app)s, %(updated_at_app)s)
```

更新時のSQL
```
UPDATE item SET name=%(name)s, updated_at_app=%(updated_at_app)s WHERE item.id = %(item_id)s
```

## テーブル確認

```
$ docker-compose exec db mysql -u root -p -D mydb
Enter password: (root)

mysql> SELECT * FROM item;
+----+----------------+---------------------+---------------------+---------------------+---------------------+
| id | name           | created_at_db       | updated_at_db       | created_at_app      | updated_at_app      |
+----+----------------+---------------------+---------------------+---------------------+---------------------+
|  1 | test1(updated) | 2023-08-19 08:03:43 | 2023-08-19 08:03:48 | 2023-08-19 17:03:43 | 2023-08-19 17:03:48 |
+----+----------------+---------------------+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)
```

MySQL側は```UTC```、アプリ側は```Asia/Tokyo```の時刻を使って動作確認しました。
そのため、それぞれのカラムの時刻に9時間の差があることが確認できます。


# まとめ

SQLModelを使用する際にレコードの作成日時と更新日時を更新する方法として、MySQL側とアプリケーション側の２つのアプローチを紹介しました。

どちらを選択するかは用途や環境等によるかと思います。



# 参考記事
https://zenn.dev/alcnaka/articles/e16eed5c8e291e

https://stackoverflow.com/questions/6992321