PrismaでMany-to-manyのテーブルを定義する

Prismaで多対多のテーブルを作る際は、Relations (Reference)に書かれているように、明示的な指定と暗黙的な指定の2つの方法が存在する。

今回、コード側をシンプルにするために暗黙的な指定を使おうとして、SQLの指定方法でハマったのでメモを残しておく。

MySQLは5.7で試した。

例として記事（Post）にタグ（Tag）付けをする際のモデル定義を以下に示す。

// schema.prisma
model Post {
  id          Int        @id @default(autoincrement())
  title       String
  tags        Tag[]      @relation("tag_posts")
  @@map("posts")
}

model Tag {
  id    Int    @id @default(autoincrement())
  name  String
  posts Post[] @relation("tag_posts")
  @@map("tags")
}

暗黙的な定義なのでモデル定義はシンプルになっている。

このモデルに対応するSQLを以下に示す。

CREATE TABLE `posts` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

CREATE TABLE `tags` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 1. テーブル名は_始まりにする必要がある
CREATE TABLE `_tag_posts` (
  -- 2. カラム名はA, B固定
  `A` int NOT NULL, 
  `B` int NOT NULL,
  UNIQUE INDEX `tag_post_idx`(`A`, `B`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 3. A, Bの指定順はアルファベット順にする必要がある
ALTER TABLE
  `_tag_posts`
ADD
  FOREIGN KEY (`A`) REFERENCES `posts`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE
  `_tag_posts`
ADD
  FOREIGN KEY (`B`) REFERENCES `tags`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ポイントは以下の3点

• 中間テーブルの名前は_始まりにする必要がある
• カラム名はA, B固定
• A, Bの指定順はアルファベット順にする必要がある

ドキュメントをちゃんと読めばこの暗黙のルールが書かれているが、見落としていたのでハマってしまった。

本来は手動でSQLを作るのではなく、Prisma Migrateを使ってSQLを自動生成するのが良いと思われる。

ただ、MySQLの場合だと自動生成されたSQLのcollationを変更することができなかったので今回は手動でSQLを作成した。

暗黙的な指定を使うことでコード側は中間テーブルを意識しなくて良いのでシンプルになったものの、DB単体で見た時にカラム名がA, Bだと何を示しているかが分かりづらいのでちゃんとしたサービスを作るときは明示的な指定を使ったほうが良いと思う。

ActiveRecordのhas many throughに対応する機能が実装されれば、明示的な中間テーブルを利用する場合のコード側もシンプルになりそう。