PlanetScaleにPrismaを接続する際のschema.prismaの書き方
PlanetScaleでは外部キー制約をサポートしていない。
PlanetScale doesn't support FOREIGN KEY constraints.
そしてPrismaのドキュメントの外部キーに対するインデックスの作成に関する部分を見ていたら次の文章を見つけた。
One issue to be aware of is that implicit many-to-many relations cannot have an index added in this way. If query speed or cost is an issue, you may instead want to use an explicit many-to-many relation in this case.
つまり、PlanetScaleを使用する際は、暗黙的多対多の関係（Implicit many-to-many relations)では、@@index([フィールド名])を指定しただけではインデックスが正しく追加できない。
もしクエリー速度やインデックスが正しく設定できないことによるコストが問題として浮上してくる場合は、代わりに明示的な多対多の関係（explicit many-to-many）の関係を構築し、参照整合性をエミュレートする機能を使用する必要がある。
なので、今回行いたいことは、参照整合性をエミュレートする機能をオンにして、暗黙的な多対多の関係になっているモデルを明示的な関係に変換した上で、中間モデルに当たるリレーションテーブルに対してインデックスを作成する方法を忘備録として残しておく。*1
PlanetScaleとは
PlanetScale is a MySQL-compatible, serverless database powered by Vitess, which is a database clustering system for horizontal scaling of MySQL. PlanetScale brings many of the benefits of serverless to the database world, with limitless scaling, consumption based pricing, zero-downtime schema migrations, and a generous free tier.
Prisma & PlanetScale | ORM for the scaleable serverless database
使い方や詳細についてはtak001さんが投稿したこちらの記事でほとんど網羅できるはずなので、参照することをおすすめする。
Prismaにおけるm-nリレーション
前提として、m-nリレーションはリレーションテーブルを介してモデル化することができる。そしてPrismaではその際に明示的か暗黙的かをモデルの書き方によって選択することができる。
リレーションテーブルとは
ちなみに、リレーションテーブルとは、異なるエンティティ間の関係を表現するためのSQLのデータモデリングのテクニックである。
CategoryとPostの関係のように、m-nの関係が、データベース上ではそこにリレーションテーブルが仲介することにより、2つの1-n関係としてモデリングすることが可能となる。
暗黙的な多対多の関係に適用されるルール
暗黙的な多対多の関係をPrisma Schemaを使って構築しようとするときに従わないといけないルールが存在する。
そのルールは次の3つである。
リレーションテーブルの規約に従う
@relationを必要としない関係を結ぶモデルの両方のフィールドに必ず
@idが付与されたフィールドが存在する
このうち、具体的に1番目と3番目の項目について解説していく。
[1] リレーションテーブルの規約に従う
リレーションテーブルの規約はおおまかに分けて３つ存在する。
[1-1] テーブル名に関する規約
リレーションテーブルの名前には、アンダースコアを先頭に付けないといけない。
[1-2] カラムに関する規約
Aと名付けられた外部キーカラムとBと名付けられた外部キーカラムの２つのカラムを必ず用意しないといけない。
そしてAはアルファベットで二つのモデル名のうち最初にくるモデルを表し、Bは最後にくるモデルを指し示していないといけない
例えば CategoryモデルとPostモデルがimplicit many-to-manyの関係の場合はAとBへの対応は次のとおりである。
A => Category B => Post
[1-3] インデックスに関する規約
次に表されるインデックスを作成する必要がある
AとB、両方の外部キーカラムに対して定義されたユニークなインデックス
Bの外部キーカラムにのみ定義されたユニークではないインデックス
SQLでは次のように表現される（Prismaのドキュメントから抜粋）
CREATE TABLE "_CategoryToPost" ( "A" integer NOT NULL REFERENCES "Category"(id) , "B" integer NOT NULL REFERENCES "Post"(id) ); CREATE UNIQUE INDEX "_CategoryToPost_AB_unique" ON "_CategoryToPost"("A" int4_ops,"B" int4_ops); CREATE INDEX "_CategoryToPost_B_index" ON "_CategoryToPost"("B" int4_ops); CREATE TABLE "Category" ( id integer SERIAL PRIMARY KEY ); CREATE TABLE "Post" ( id integer SERIAL PRIMARY KEY );
このルールはリレーションテーブルにインデックスを作成するときにとても重要なので、理解しておくことを推奨する。
[3] 関係を結ぶモデルの両方のフィールドに必ず@idが付与されたフィールドが存在する
このルールに従う際、具体的に注意点が二点ほど存在する。
マルチフィールドID（
@@id）を使ってはいけない。@idを使うべき場所に@uniqueを使ってはならない。
マルチフィールドIDの詳細ついては次のドキュメントを参照されたし。
PlanetScaleの使用に関係なく、これらのルールに縛られたくない場合は、明示的な多対多の関係を使ってモデルを構築することが推奨されている。
実際に書き換えてみる
それでは、実際に書き換えていこうと思うが、今回は上述した暗黙的な多対多の関係のモデルが従っているルールに準拠しながら、明示的な多対多のモデルを作成していこうと思う。
PostgreSQLを使用していた時には、次のように暗黙的な書き方を一部で採用していた。
model User {
id String @id @default(uuid())
likes Post[] @relation(name: "MyRelationTable", references: [id])
@@map("users")
}
model Post {
id String @id @default(uuid())
likedBy User[] @relation(name: "MyRelationTable", references: [id])
@@map("posts")
}
しかし、PlanetScaleでは外部キー制約をサポートしていないなどの理由から、自動的にインデックスが付与されない。 なので、インデックスを付与するためには、参照整合性をエミュレートする機能を有効にし、かつ暗黙的な多対多の関係（Implicit many-to-mahy relations）から次のように明示的な多対多の関係（Explicit many-to-many relations）に手動で変換しないといけない。
generator client {
provider = "prisma-client-js"
binaryTargets = ["native"]
previewFeatures = ["referentialIntegrity"]
}
datasource db {
provider = "mysql"
url = env("DATABASE_URL")
referentialIntegrity = "prisma"
}
model User {
id String @id @default(uuid())
likes LikesOnPosts[]
@@map("users")
}
model LikesOnPosts {
user User @relation(fields: [userId], references: [id])
userId String @map("user_id")
post Post @relation(fields: [postId], references: [id])
postId String @map("post_id")
@@map("_likes_on_posts")
@@id([postId, userId])
@@unique([postId, userId])
@@index([userId])
}
model Post {
id String @id @default(uuid())
likedBy LikesOnPosts[]
@@map("posts")
}
そしてこのLikesOnPostsモデルは暗黙的多対多の関係のモデルが従うリレーション規約にも準じていることが確認できる。
特に気をつけないといけないのは、@@uniqueではUNIQUE INDEXと対応しているので、non-uniqueなINDEXを設定したい場合は@@indexを使用しないといけない点だ。このことは、マイグレーションを実行した際に作成されるsqlファイルを確認するとよくわかる。
CREATE TABLE `_likes_on_posts` ( `user_id` VARCHAR(191) NOT NULL, `post_id` VARCHAR(191) NOT NULL, INDEX `_likes_on_posts_user_id_idx`(`user_id`), UNIQUE INDEX `_likes_on_posts_post_id_user_id_key`(`post_id`, `user_id`), PRIMARY KEY (`post_id`, `user_id`) ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
さあ、これで実際に動かそう！と言いたいところなのだが、残念ながらこのままでは正常に動かずエラーが発生してしまう。
そのため、それを回避するためのテクニックが２つあるので、最後に解説させていただきたい。
referentialIntegrityプレビュー機能を有効化する際に参照アクションにNoActionを設定する方法
上述の通り、外部キーをアプリケーション側でサポートさせるため、参照整合性に対するエミュレーションであるreferentialIntegrityプレビュー機能を有効にしないといけないのはもちろん必須なのだが、普段のようにpost Post @relation(fields: [postId], references: [id], onDelete: Cascade)
のように参照アクションを指定してしまうと、対象のモデルのフィールドの内容を更新することができないという問題が発生する。
発生したエラーは次のとおり。
Failed to commit changes; Invalid `prisma.user.update()` invocation: The column `データベース名.users.id` does not exit in the current database. Type: undefined Message: Invalid `prisma.user.update()` invocation: The column `データベース名.users.id` does not exist in the current database. Code: P2022 Query: [object Object]
この時、生成されたPrisma Clientでは型エラーは発生していない。そして、多対多の関係ではない他のモデルに対する更新操作では問題は発生しなかった。
解決方法として、次のissueやドキュメントを参考にした。
まとめると、@relationアトリビュートに参照アクションを設定する際は、NoActionを設定することが問題を解決する手段として手っ取り早いというわけである。
NoActionをMySQLに対して指定すると、基本的にはデフォルトのRestrictを指定した時と同じで、OnDeleteの場合は参照されるレコードがまだ存在する場合は削除することができないことを制約でき、OnUpdateの場合は参照されているレコードのidを変更することができない制約を課すことができる。
それを踏まえた上での最終的なPrisma スキーマは次の通り。
// prisma/schema.prisma
generator client {
provider = "prisma-client-js"
binaryTargets = ["native"]
previewFeatures = ["referentialIntegrity"]
}
datasource db {
provider = "mysql"
url = env("DATABASE_URL")
referentialIntegrity = "prisma"
}
model User {
id String @id @default(uuid())
likes LikesOnPosts[]
@@map("users")
}
model LikesOnPosts {
user User @relation(fields: [userId], references: [id], onUpdate: NoAction, onDelete: NoAction)
userId String @map("user_id")
post Post @relation(fields: [postId], references: [id], onUpdate: NoAction, onDelete: NoAction)
postId String @map("post_id")
@@id([postId, userId])
@@unique([postId, userId])
@@index([userId])
@@map("_likes_on_posts")
}
model Post {
id String @id @default(uuid())
likedBy LikesOnPosts[]
@@map("posts")
}
NoActionを設定したくない人向けのテクニック
長々とNoActionを設定する方法が一番の解決策だとでもいうように力説してきたが、実はもう一つ方法がある。
それは一時的にreferentialIntegrity = "prisma"をスキーマから削除して、db pushを実行する前にクライアントを再生成することによりNoActionを設定しなくても解決するという方法である。
具体的な手順を説明する。
1. previewFeatures = ["referentialIntegrity"]とreferentialIntegrity = "prisma"をコメントアウトする。
generator client {
provider = "prisma-client-js"
binaryTargets = ["native"]
// reviewFeatures = ["referentialIntegrity"]
}
datasource db {
provider = "mysql"
url = env("DATABASE_URL")
// referentialIntegrity = "prisma"
}
2. 参照アクションに設定しているNoActionを任意のCascadeなどに設定する
model LikesOnPosts {
user User @relation(fields: [userId], references: [id], onUpdate: Cascade, onDelete: Cascade)
userId String @map("user_id")
post Post @relation(fields: [postId], references: [id], onUpdate: Cascade, onDelete: Cascade)
postId String @map("post_id")
@@id([postId, userId])
@@unique([postId, userId])
@@index([userId])
@@map("_likes_on_posts")
}
3. npx prisma generateを実行してdb pushよりも前にクライアントを作成する
$ npx prisma generate
4. 先ほどコメントアウトした設定部分を元に戻す。
generator client {
provider = "prisma-client-js"
binaryTargets = ["native"]
reviewFeatures = ["referentialIntegrity"]
}
datasource db {
provider = "mysql"
url = env("DATABASE_URL")
referentialIntegrity = "prisma"
}
5. npx prisma db push --force-reset --accept-data-loss --skip-generate && npx prisma db seedを実行する。
$ npx prisma db push --force-reset --accept-data-loss --skip-generate && npx prisma db seed
これに従って実行すれば、NoActionを設定しなくても更新できない問題を回避することができるのでお好きな方を選択していただきたい。
最終的なスキーマは次の通り
// prisma/schema.prisma
generator client {
provider = "prisma-client-js"
binaryTargets = ["native"]
reviewFeatures = ["referentialIntegrity"]
}
datasource db {
provider = "mysql"
url = env("DATABASE_URL")
referentialIntegrity = "prisma"
}
model User {
id String @id @default(uuid())
likes LikesOnPosts[]
@@map("users")
}
model LikesOnPosts {
user User @relation(fields: [userId], references: [id], onUpdate: Cascade, onDelete: Cascade)
userId String @map("user_id")
post Post @relation(fields: [postId], references: [id], onUpdate: Cascade, onDelete: Cascade)
postId String @map("post_id")
@@id([postId, userId])
@@unique([postId, userId])
@@index([userId])
@@map("_likes_on_posts")
}
model Post {
id String @id @default(uuid())
likedBy LikesOnPosts[]
@@map("posts")
}
まとめ
参照整合性をエミュレートするということは、データベース側での外部キー制約を作成せず、クライアント側での参照アクションのみを実装するということである。
その際に参照アクションにNoActionを指定するということは、それすなわち参照整合性が破壊されることを防ぐためのチェックも行われないということを意味する。
If you are emulating referential integrity in the Prisma client rather than using foreign keys in the database, you should be aware that currently Prisma only implements the referential actions. Foreign keys also create constraints, which make it impossible to manipulate data in a way that would violate these constraints: instead of executing the query, the database responds with an error. These constraints will not be created if you emulate referential integrity in the client, so if you set the referential action to NoAction there will be no checks to prevent you from breaking the referential integrity.
そしてreferentialIntegirtyはあくまでプレビュー機能なので、この問題が解決するまではNoActionを指定しておくことが策として考えられている。
なのでNoActionを設定する方法はあくまでこの問題が修正されるまでの対処療法であることを押さえた上で、参考程度として扱っていただきたいことを切に願う。
