Closed24日前にクローズ3

PrismaとTypeORMを比較してみる

2,3日TypeORM使ってみて、クエリの書き辛さを感じたので、prismaを導入してみる
ただNestとの相性は良いので、正直このままでもいい気はしている。

prisma導入

npm install -w packages/app --save prisma

マイグレーション実行

npx prisma migrate dev --name init

shuntaka

Prismaのマイグレーション周りのライフサイクルがわからず、しょっちゅうdriftが検出されてテーブルがTruncateされてしまうのに疑問を感じた。

ドキュメントを見る限り開発環境では、基本prisma devを使っていくのが良いと感じている。devなし本番コマンドを開発むけに実行すると、想定外となりDriftが検出される恐れがある。

まずはリセットして、同期を取ります。npx prisma migrate devをしないと同期が取られないので注意です。

$ npx prisma migrate reset
Prisma schema loaded from schema.prisma
Datasource "db": MySQL database "prisma_sample" at "127.0.0.1:3306"

✔ Are you sure you want to reset your database? All data will be lost. … yes

Applying migration `20221030043937_`

Database reset successful

The following migration(s) have been applied:

migrations/
  └─ 20221030043937_/
    └─ migration.sql

✔ Generated Prisma Client (4.5.0 | library) to ./../../node_modules/@prisma/client in 51ms

_prisma_migrationsテーブルを確認します。

mysql> select * from _prisma_migrations;
+--------------------------------------+------------------------------------------------------------------+-------------+-----------------+------+----------------+-------------------------+---------------------+
| id                                   | checksum                                                         | finished_at | migration_name  | logs | rolled_back_at | started_at              | applied_steps_count |
+--------------------------------------+------------------------------------------------------------------+-------------+-----------------+------+----------------+-------------------------+---------------------+
| f7ed2439-f000-4684-a226-dcfc011ab068 | 141eeba4e8dbe53c4a20f3a0e2d9a3bba860f4956e88396461524d0591fbc8a9 | NULL        | 20221030043937_ | NULL | NULL           | 2022-10-30 05:45:01.405 |                   0 |
+--------------------------------------+------------------------------------------------------------------+-------------+-----------------+------+----------------+-------------------------+---------------------+
1 row in set (0.01 sec)

$ npx prisma migrate dev
Prisma schema loaded from schema.prisma
Datasource "db": MySQL database "prisma_sample" at "127.0.0.1:3306"

- The migration `20221030043937_` failed.
- Drift detected: Your database schema is not in sync with your migration history.

The following is a summary of the differences between the expected database schema given your migrations files, and the actual schema of the database.

It should be understood as the set of changes to get from the expected schema to the actual schema.

[+] Added tables
  - user
  - user_work_out
  - work_out

[*] Changed the `user_work_out` table
  [+] Added foreign key on columns (user_id)
  [+] Added foreign key on columns (work_out_id)


✔ We need to reset the MySQL database "prisma_sample" at "127.0.0.1:3306".
Do you want to continue? All data will be lost. … yes

Applying migration `20221030043937_`

The following migration(s) have been applied:

migrations/
  └─ 20221030043937_/
    └─ migration.sql

Your database is now in sync with your schema.

✔ Generated Prisma Client (4.5.0 | library) to ./../../node_modules/@prisma/client in 60ms

_prisma_migrationsテーブルを確認します。finished_at がNULLから日付が入ったことから先ほどのnpx prisma migrate devで初めて同期が取れた考えられます。

mysql> select * from _prisma_migrations;
+--------------------------------------+------------------------------------------------------------------+-------------------------+-----------------+------+----------------+-------------------------+---------------------+
| id                                   | checksum                                                         | finished_at             | migration_name  | logs | rolled_back_at | started_at              | applied_steps_count |
+--------------------------------------+------------------------------------------------------------------+-------------------------+-----------------+------+----------------+-------------------------+---------------------+
| c32330f3-8dae-4570-b471-f97df426b5b0 | 141eeba4e8dbe53c4a20f3a0e2d9a3bba860f4956e88396461524d0591fbc8a9 | 2022-10-30 05:46:25.388 | 20221030043937_ | NULL | NULL           | 2022-10-30 05:46:25.271 |                   1 |
+--------------------------------------+------------------------------------------------------------------+-------------------------+-----------------+------+----------------+-------------------------+---------------------+
1 row in set (0.01 sec)

userテーブルにレコード入れてコミットします。

mysql> INSERT INTO user(id, name, created_at, modified_at) VALUES('e0224bac-5fe9-41a9-9cd6-19811922b055', 'shuntaka', '2022-08-02 23:53:36', '2022-08-02 23:53:36');
Query OK, 1 row affected (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye

$ mysql -u root -p -h 127.0.0.1 -P 3306 -D prisma_sample
(中略)                                                       
mysql> select count(*) from user;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.01 sec)

もう一回migrate devを実行しても同期されていることがわかります。

$ npx prisma migrate dev
Prisma schema loaded from schema.prisma
Datasource "db": MySQL database "prisma_sample" at "127.0.0.1:3306"

Already in sync, no schema change or pending migration was found.

✔ Generated Prisma Client (4.5.0 | library) to ./../../node_modules/@prisma/client in 50ms

ユーザーテーブルにカラムを追加します。

+++ b/packages/app/schema.prisma
@@ -12,6 +12,7 @@ model User {
   name String @unique
   createdAt DateTime @default(now()) @map("created_at")
   modifiedAt DateTime @default(now()) @map("modified_at")
+  addCol DateTime @default(now())
   workOuts UserWorkOut[]

正しく同期が取れていると以下のように出力されます。

$ npx prisma migrate dev --create-only
Prisma schema loaded from schema.prisma
Datasource "db": MySQL database "prisma_sample" at "127.0.0.1:3306"

✔ Enter a name for the new migration: … addCol
Prisma Migrate created the following migration without applying it 20221030055248_add_col

You can now edit it and apply it by running prisma migrate dev.

出力の通りprisma migrate devを実行前に、userテーブルの状態を確認します。まだ適用されていません。

+--------------------------------------+------------------------------------------------------------------+-------------------------+-----------------+------+----------------+-------------------------+---------------------+
| id                                   | checksum                                                         | finished_at             | migration_name  | logs | rolled_back_at | started_at              | applied_steps_count |
+--------------------------------------+------------------------------------------------------------------+-------------------------+-----------------+------+----------------+-------------------------+---------------------+
| c32330f3-8dae-4570-b471-f97df426b5b0 | 141eeba4e8dbe53c4a20f3a0e2d9a3bba860f4956e88396461524d0591fbc8a9 | 2022-10-30 05:46:25.388 | 20221030043937_ | NULL | NULL           | 2022-10-30 05:46:25.271 |                   1 |
+--------------------------------------+------------------------------------------------------------------+-------------------------+-----------------+------+----------------+-------------------------+---------------------+
1 row in set (0.01 sec)

mysql> desc user;
+-------------+--------------+------+-----+----------------------+-------------------+
| Field       | Type         | Null | Key | Default              | Extra             |
+-------------+--------------+------+-----+----------------------+-------------------+
| id          | varchar(191) | NO   | PRI | NULL                 |                   |
| name        | varchar(191) | NO   | UNI | NULL                 |                   |
| created_at  | datetime(3)  | NO   |     | CURRENT_TIMESTAMP(3) | DEFAULT_GENERATED |
| modified_at | datetime(3)  | NO   |     | CURRENT_TIMESTAMP(3) | DEFAULT_GENERATED |
+-------------+--------------+------+-----+----------------------+-------------------+
4 rows in set (0.00 sec)

$ npx prisma migrate dev
Prisma schema loaded from schema.prisma
Datasource "db": MySQL database "prisma_sample" at "127.0.0.1:3306"

Applying migration `20221030055248_add_col`

The following migration(s) have been applied:

migrations/
  └─ 20221030055248_add_col/
    └─ migration.sql

Your database is now in sync with your schema.

✔ Generated Prisma Client (4.5.0 | library) to ./../../node_modules/@prisma/client in 50ms

無事適用されました。userレコードも消えていないことを確認。

mysql> select * from _prisma_migrations;
+--------------------------------------+------------------------------------------------------------------+-------------------------+------------------------+------+----------------+-------------------------+---------------------+
| id                                   | checksum                                                         | finished_at             | migration_name         | logs | rolled_back_at | started_at              | applied_steps_count |
+--------------------------------------+------------------------------------------------------------------+-------------------------+------------------------+------+----------------+-------------------------+---------------------+
| 18531713-049e-4d02-ad5c-9558f029682b | 4fb436361aebbde7fead4c0c0003424f63c22fb88bd1d501772cb52e306af972 | 2022-10-30 05:54:58.553 | 20221030055248_add_col | NULL | NULL           | 2022-10-30 05:54:51.133 |                   1 |
| c32330f3-8dae-4570-b471-f97df426b5b0 | 141eeba4e8dbe53c4a20f3a0e2d9a3bba860f4956e88396461524d0591fbc8a9 | 2022-10-30 05:46:25.388 | 20221030043937_        | NULL | NULL           | 2022-10-30 05:46:25.271 |                   1 |
+--------------------------------------+------------------------------------------------------------------+-------------------------+------------------------+------+----------------+-------------------------+---------------------+
2 rows in set (0.00 sec)

mysql> desc user;
+-------------+--------------+------+-----+----------------------+-------------------+
| Field       | Type         | Null | Key | Default              | Extra             |
+-------------+--------------+------+-----+----------------------+-------------------+
| id          | varchar(191) | NO   | PRI | NULL                 |                   |
| name        | varchar(191) | NO   | UNI | NULL                 |                   |
| created_at  | datetime(3)  | NO   |     | CURRENT_TIMESTAMP(3) | DEFAULT_GENERATED |
| modified_at | datetime(3)  | NO   |     | CURRENT_TIMESTAMP(3) | DEFAULT_GENERATED |
| addCol      | datetime(3)  | NO   |     | CURRENT_TIMESTAMP(3) | DEFAULT_GENERATED |
+-------------+--------------+------+-----+----------------------+-------------------+
5 rows in set (0.01 sec)

mysql> select * from user;
+--------------------------------------+----------+-------------------------+-------------------------+-------------------------+
| id                                   | name     | created_at              | modified_at             | addCol                  |
+--------------------------------------+----------+-------------------------+-------------------------+-------------------------+
| e0224bac-5fe9-41a9-9cd6-19811922b055 | shuntaka | 2022-08-02 23:53:36.000 | 2022-08-02 23:53:36.000 | 2022-10-30 05:54:51.220 |
+--------------------------------------+----------+-------------------------+-------------------------+-------------------------+
1 row in set (0.01 sec)

$ npx prisma migrate dev
Prisma schema loaded from schema.prisma
Datasource "db": MySQL database "prisma_sample" at "127.0.0.1:3306"

Already in sync, no schema change or pending migration was found.

✔ Generated Prisma Client (4.5.0 | library) to ./../../node_modules/@prisma/client in 58ms

shuntaka

本番はどこかでやる

スキーマ定義を変更した場合

テーブル定義を適用。適用する前に何が適用されていないか確認した方が良い。

$ npx prisma migrate status
Prisma schema loaded from schema.prisma
Datasource "db": MySQL database "prisma_sample" at "127.0.0.1:3306"

2 migrations found in prisma/migrations

Following migration have not yet been applied:
20221030044848_

To apply migrations in development run prisma migrate dev.
To apply migrations in production run prisma migrate deploy.

npx prisma migrate deploy

このスクラップは24日前にクローズされました