PrismaでManyToManyなテーブルを作る

SQLの勉強がてら、CMSのDBっぽいものをPrismaで定義したりしています。投稿（Post）とカテゴリー（Category）で多対多のリレーションを作りたくなったので、覚書です。作業前のSchema QuickS […]

SQLの勉強がてら、CMSのDBっぽいものをPrismaで定義したりしています。

投稿（Post）とカテゴリー（Category）で多対多のリレーションを作りたくなったので、覚書です。

作業前のSchema

QuickStartを終えた状態から始めますので、UserとPostの２モデルが既に存在します。

model User {
  id    Int     @id @default(autoincrement())
  email String  @unique
  name  String?
  posts Post[]
}

model Post {
  id        Int     @id @default(autoincrement())
  title     String
  content   String?
  published Boolean @default(false)
  author    User    @relation(fields: [authorId], references: [id])
  authorId  Int
}

Categoryのモデルを追加する

まずはカテゴリー（Category）のモデルを追加します。

model PostCategory {
  id Int @id @default(autoincrement())
  name String
  description String?
}

Postのプレフィックスはなくてもいい気はしますが、後々いろんなカテゴリーが生まれる可能性もあるのでつけておきます。

中間テーブルを定義する

SQLの勉強に使った本によると、多対多になるリレーションには中間テーブルを作るとのことでした。

ということで作ります。


model PostOnPostCategories {
  post Post @relation(fields: [postId], references: [id])
  postId Int
  category PostCategory @relation(fields: [categoryId], references: [id])
  categoryId Int
  assignedAt DateTime @default(now())
  assignedBy String
  @@id([postId, categoryId])
}

IDをPostとPostCategoryのIDから生成するっぽいので、@@idを利用します。

Defines a multi-field ID (composite ID) on the model.

https://www.prisma.io/docs/reference/api-reference/prisma-schema-reference#id-1

postとpostId、categoryとcategoryIdのように２つ定義しないといけないっぽい。

中間テーブルの定義だけ済ませた状態だと、エラーが残ります。

Error validating field `post` in model `PostOnPostCategories`: The relation field `post` on Model `PostOnPostCategories` is missing an opposite relation field on the model `Post`. Either run `prisma format` or add it manually.

これは両端のテーブルで定義を追加すれば消えます。

model Post {
  id        Int     @id @default(autoincrement())
  title     String
  content   String?
  published Boolean @default(false)
  published_at DateTime?
  author    User    @relation(fields: [authorId], references: [id])
  authorId  Int
  categories PostOnPostCategories[]
}

model PostCategory {
  id Int @id @default(autoincrement())
  name String
  description String?
  posts PostOnPostCategories[]
}

migration用のSQL発行やmigration実行

DB定義を更新したので、migrationします。

SQLの中身を見たい場合は、--create-onlyをつけましょう。

% npx prisma migrate dev -n post-category-table --create-only
 Environment variables loaded from .env
 Prisma schema loaded from prisma/schema.prisma
 Datasource "db": SQLite database "dev.db" at "file:./dev.db"
 Prisma Migrate created the following migration without applying it 20220726163716_post_category_table
 You can now edit it and apply it by running prisma migrate dev.

生成されたSQLはこんな感じ。

-- CreateTable
CREATE TABLE "PostCategory" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "name" TEXT NOT NULL,
    "description" TEXT
);

-- CreateTable
CREATE TABLE "PostOnPostCategories" (
    "postId" INTEGER NOT NULL,
    "categoryId" INTEGER NOT NULL,
    "assignedAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "assignedBy" TEXT NOT NULL,

    PRIMARY KEY ("postId", "categoryId"),
    CONSTRAINT "PostOnPostCategories_postId_fkey" FOREIGN KEY ("postId") REFERENCES "Post" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT "PostOnPostCategories_categoryId_fkey" FOREIGN KEY ("categoryId") REFERENCES "PostCategory" ("id") ON DELETE RESTRICT ON UPDATE CASCADE
);

prisma migrate devでmigrationを実行。

px prisma migrate dev          Environment variables loaded from .env
 Prisma schema loaded from prisma/schema.prisma
 Datasource "db": SQLite database "dev.db" at "file:./dev.db"
 Applying migration 20220726163716_post_category_table
 The following migration(s) have been applied:
 migrations/
   └─ 20220726163716_post_category_table/
     └─ migration.sql
 Your database is now in sync with your schema.
 ✔ Generated Prisma Client (4.1.0 | library) to ./node_modules/@prisma/client in 
 68ms
 ✔ Generated Entity-relationship-diagram to ./ERD.png in 2.51s

データ投入

DBの用意ができたので、データを投入します。

import { Prisma, PrismaClient } from "@prisma/client";
import { faker } from '@faker-js/faker';

const prisma = new PrismaClient()

...

    const authorData = {
        name: faker.name.firstName(),
        email: faker.internet.email()
    }

    const postData: Prisma.PostCreateInput = {
        title: faker.lorem.text(),
        author: {
            create: authorData
        },
        categories: {
            create: [{
                assignedBy: authorData.name,
                assignedAt: new Date(),
                category: {
                    create: {
                        name: faker.lorem.word()
                    }
                }
            }]
        }
    }
    const postWithCategory = await prisma.post.create({
        data: postData
    })

実行結果はこんな感じ。

{
   id: 8,
   title: 'Voluptatibus enim tenetur assumenda aliquid.\n' +
     'Natus vero ea inventore.\n' +
     'Expedita iure accusamus tempora fugiat commodi.\n' +
     'Non eum corrupti.\n' +
     'Ut porro dolores iusto dolor quo porro et.',
   content: null,
   published: false,
   published_at: null,
   authorId: 12
 }

記事とカテゴリーをまとめて取得する

記事データから取得する

最後にSELECTも実行してみます。

    const posts = await prisma.post.findMany({
        where: {
            id: {
                gte: 8
            }
        },
        include: {
            categories: true,
            author: true,
        }
    })
    console.dir(posts, {depth: null})

WHEREがとても雑ですね。prisma migrateを実行する前にINSERTしたデータを避けるだけの内容なので、省いてOKです。

ただし、include.categories: trueを消すとカテゴリー情報が取れませんので注意です。

実行結果はこのようになります。

[
   {
     id: 8,
     title: 'Voluptatibus enim tenetur assumenda aliquid.\n' +
       'Natus vero ea inventore.\n' +
       'Expedita iure accusamus tempora fugiat commodi.\n' +
       'Non eum corrupti.\n' +
       'Ut porro dolores iusto dolor quo porro et.',
     content: null,
     published: false,
     published_at: null,
     authorId: 12,
     categories: [
       {
         postId: 8,
         categoryId: 1,
         assignedAt: 2022-07-26T16:49:38.349Z,
         assignedBy: 'Reinhold'
       }
     ],
     author: {
       id: 12,
       email: '[email protected]',
       name: 'Reinhold'
     }
   },
   {
     id: 9,
     title: 'Molestiae facere nemo. Vero voluptatem repudiandae eius quisquam. Mollitia architecto aut similique id et nemo veritatis nihil. Voluptatibus fugiat assumenda. Tempora voluptas soluta magni voluptas veniam. Rerum cumque esse earum et eos.\n' +
       'Rem adipisci molestiae. Libero veritatis ut autem doloribus. Ipsa incidunt neque labore. Quidem nihil nulla in vero sit ullam. Voluptas eius sit iste harum nam aut animi exercitationem modi.\n' +
       'Odit eos repellat. Quia dolor aut ea et quis odit enim amet. Est blanditiis aut beatae natus eveniet voluptate quas earum necessitatibus.',
     content: null,
     published: false,
     published_at: null,
     authorId: 13,
     categories: [
       {
         postId: 9,
         categoryId: 2,
         assignedAt: 2022-07-26T16:51:59.624Z,
         assignedBy: 'Ronaldo'
       }
     ],
     author: { id: 13, email: '[email protected]', name: 'Ronaldo' }
   }
 ]

カテゴリーデータから取得する

RDBなので逆からも取れます。

    const categories = await prisma.postCategory.findMany({
        include: {
            posts: true
        }
    })
    console.dir(categories, {depth: null})

実行結果はこちら。

[
   {
     id: 1,
     name: 'repudiandae',
     description: null,
     posts: [
       {
         postId: 8,
         categoryId: 1,
         assignedAt: 2022-07-26T16:49:38.349Z,
         assignedBy: 'Reinhold'
       }
     ]
   },
   {
     id: 2,
     name: 'dolores',
     description: null,
     posts: [
       {
         postId: 9,
         categoryId: 2,
         assignedAt: 2022-07-26T16:51:59.624Z,
         assignedBy: 'Ronaldo'
       }
     ]
   }
 ]

相手のテーブルのデータも取りたい場合

includeをネストさせましょう。

    const categories = await prisma.postCategory.findMany({
        include: {
            posts: {
                include: {
                    post: true
                }
            }
        }
    })
    console.dir(categories, {depth: null})

これでカテゴリに対するクエリでも投稿データが取得できます。

{
     id: 4,
     name: 'sit',
     description: null,
     posts: [
       {
         postId: 11,
         categoryId: 4,
         assignedAt: 2022-07-26T17:04:10.000Z,
         assignedBy: 'Ezekiel',
         post: {
           id: 11,
           title: 'Expedita veritatis architecto quae explicabo iure quo sed. Et sit assumenda sint accusamus autem vel. Non facere neque consequatur expedita eius cupiditate reprehenderit odit vero.',
           content: null,
           published: false,
           published_at: null,
           authorId: 15
         }
       }
     ]
   }
 ]

項目を絞りたい（SELECTしたい）場合は、includeではなくselectを使います。


    const categories = await prisma.postCategory.findMany({
        select: {
            id: true,
            name: true,
            posts: {
                select: {
                    post: {
                        select: {
                            title: true
                        }
                    }
                }
            }
        }
    })

コードのネストが深いですが、結果はシンプルになりました。

[{
     id: 3,
     name: 'minus',
     posts: [ { post: { title: 'consequatur corrupti et' } } ]
}]

参考にした記事

https://www.prisma.io/docs/concepts/components/prisma-schema/relations/many-to-many-relations

https://www.prisma.io/docs/guides/database/troubleshooting-orm/help-articles/working-with-many-to-many-relations