本文會介紹 prisma 這個非常好用的 ORM，比起 TypeORM 的語法，prisma 可謂是非常簡單易懂，程式碼的可讀性也相當之高。以下將會介紹 prisma model 的寫法和不同 relation 的 CRUD。
## One To Many
情境如下:
在遊戲攻略平台中，發文前需要選定一個遊戲版，而遊戲版需要由版主來創建，每個貼文需要歸屬於一個遊戲版，一個遊戲版可以有多個文章，因此relation為一對多

1. 版主創建遊戲版
2. 選擇某個遊戲版發文
#### Game model (parent)

model Game {
  id Int @default(autoincrement()) @id
  name String
  posts Post[]
}

#### Post model (children)

model Post {
  id Int @default(autoincrement()) @id
  name String
  body String
  game Game @relation(fields: [gameId], references: [id])
  gameId Int
}

#### Create game
版主創建遊戲版

async createGame(data: CreateGameDto) {
    const game = await this.prismaService.game.create({
      data
    })
    return game
}

#### Create Post
選擇某個遊戲版發文

async createPost(data: CreatePostDto) {
    const result = await this.prisma.post.create({
      data });
    return result;
  }

#### CreatePostDto
從 DTO 中可以看到我們是通過 post model 中的 fields 去進行關聯，且我們定義 fiels 為 gameId，因此在create 和 update 時透過 gameId 這個屬性進行關聯操作即可。
authorId 可以從 jwt token中解析

class CreatePostDto {
  name: string;

  body: string;

  authorId: number;
  
  gameId: number;
}

#### Get Post

async getPosts() {
    const result = await this.prisma.post.findMany({
      include: {
        game: {
          select: {
            name: true
          }
        }
      },
}

#### Update Post

async updatePostById(id: number, data: UpdatePostDto) {
    const result = await this.prisma.post.update({
      where: { id },
      data: {
        ...data,
      },
    });
    return result;
  }

## Many To Many
情境如下:
在遊戲攻略平台中，使用者可以發布文章，並且可以對文章加註tag，如 movie、foods 等等。一個 post 可以有多個 tag ，一個 tag 也可以在多個文章中出現，因此 relation 為多對多。在 prisma 中有兩種設計模式，一種是在兩個 table 中再使用一個多對多 table 作為中介將兩者關聯起來，另外也可以在 model 中直接用 [ ] 代表兩者為多對多關係，寫起來甚至比一對多更簡潔。
1. 創建文章時加上兩個tag
1. 透過 tag 或 name 來查詢文章
2. 其他使用者想要為文章新增 tag
3. 後來後悔了想要刪掉其中一個 tag
#### Post

model Post {
  id Int @default(autoincrement()) @id
  name String
  body String
  tags Tag[]
}

#### Tag

model Tag {
  id Int @default(autoincrement()) @id
  name String @unique
  posts Post[]
  Games Game[]
}

#### Create Post
創建文章時就指定 tag
透過以下的寫法便可以將data中的tag和post給關聯起來，connectOrCreate在tag不存在時可以創建tag並建立關聯，存在時則直接關聯，因為結構比較複雜，所以在create之前先將data 中的tag array給整理成規定的格式。

async createPost(data: CreatePostDto) {
    const result = await this.prisma.post.create({
      data: {
        ...data,
        tags: {
          connectOrCreate: data.tags.map((tag) => {
              return {
                    where: { name: tag },
                    create: { name: tag }
              };
    })
        },
      },
    });
    return result;
  }

這些程式在背後執行的SQL長這樣:
重要ㄟ，next generation orm，prisma會幫忙檢查tag是否存在

// 輸入的tag存在時
BEGIN
INSERT INTO "public"."Post" ("name","status","body","slug","gameId","authorId","views") VALUES ($1,$2,$3,$4,$5,$6,$7) RETURNING "public"."Post"."id"
SELECT "public"."Tag"."id" FROM "public"."Tag" WHERE ("public"."Tag"."name" = $1 AND 1=1) OFFSET $2
INSERT INTO "public"."_PostToTag" ("A","B") VALUES ($1,$2) ON CONFLICT DO NOTHING
SELECT "public"."Post"."id", "public"."Post"."name", "public"."Post"."status", "public"."Post"."body", "public"."Post"."slug", "public"."Post"."gameId", "public"."Post"."authorId", "public"."Post"."views" FROM "public"."Post" WHERE "public"."Post"."id" = $1 LIMIT $2 OFFSET $3
COMMIT

// 輸入的tag不存在時
BEGIN
INSERT INTO "public"."Post" ("name","status","body","slug","gameId","authorId","views") VALUES ($1,$2,$3,$4,$5,$6,$7) RETURNING "public"."Post"."id"
SELECT "public"."Tag"."id" FROM "public"."Tag" WHERE ("public"."Tag"."name" = $1 AND 1=1) OFFSET $2
INSERT INTO "public"."Tag" ("name") VALUES ($1) RETURNING "public"."Tag"."id"
INSERT INTO "public"."_PostToTag" ("A","B") VALUES ($1,$2) ON CONFLICT DO NOTHING
SELECT "public"."Post"."id", "public"."Post"."name", "public"."Post"."status", "public"."Post"."body", "public"."Post"."slug", "public"."Post"."gameId", "public"."Post"."authorId", "public"."Post"."views" FROM "public"."Post" WHERE "public"."Post"."id" = $1 LIMIT $2 OFFSET $3
COMMIT

#### Get posts
透過 tag 或 name 來查詢文章
1. post `name` like postName，
2. tag 為 whereIn [ 'tag1' , 'tag2']

透過這邊的例子就可以看出 prisma 能使用相對SQL及TypeORM來說，可讀性較高的語法來完成複雜的查詢。在撰寫語句時也可以直接查看source code的 input type 來撰寫。

async getPosts(tags: Array<string>, postName) {
    const result = await this.prisma.post.findMany({
      include: {
        tags: true,
      },
      where: {
        name:{
          contains: postName // LIKE %postName%
        },
        tags: {
          some: {    // orWhere  andWhere  
            name: {
              in: tags, // whereIn 
            },
          },
        },
      },
    });
    }
    return result;
 }

raw sql

SELECT "public"."Post"."id", "public"."Post"."name", "public"."Post"."status", "public"."Post"."body", "public"."Post"."slug", "public"."Post"."gameId", "public"."Post"."authorId", "public"."Post"."views" FROM "public"."Post" WHERE ("public"."Post"."name"::text LIKE $1 AND ("public"."Post"."id") IN (SELECT "t0"."A" FROM "public"."_PostToTag" AS "t0" INNER JOIN "public"."Tag" AS "j0" ON ("j0"."id") = ("t0"."B") WHERE ("j0"."name" IN ($2) AND "t0"."A" IS NOT NULL))) OFFSET $3
SELECT "public"."_PostToTag"."A", "public"."_PostToTag"."B" FROM "public"."_PostToTag" WHERE "public"."_PostToTag"."A" IN ($1)
SELECT "public"."Tag"."id", "public"."Tag"."name" FROM "public"."Tag" WHERE "public"."Tag"."id" IN ($1,$2) OFFSET $3
SELECT "public"."Game"."id", "public"."Game"."name" FROM "public"."Game" WHERE "public"."Game"."id" IN ($1) OFFSET $2

#### Add tags
其他使用者想要為文章新增 tag
其實跟create post大同小異，也是用createOrCreate來和tag進行關聯。

async addTagToPost(id: number, tags) {
    const tagsData = tags.map((tag) => {
      return {
        where: { name: tag },
        create: { name: tag }
      };
    });

    const result = await this.prisma.post.update({
      where: { id },
      data: {
        tags: {
          connectOrCreate: tagsData,
        },
      },
    });

    return result;
  }

raw sql

BEGIN
SELECT "public"."Post"."id" FROM "public"."Post" WHERE ("public"."Post"."id" = $1 AND 1=1)
SELECT "public"."Tag"."id" FROM "public"."Tag" WHERE ("public"."Tag"."name" = $1 AND 1=1) OFFSET $2
INSERT INTO "public"."Tag" ("name") VALUES ($1) RETURNING "public"."Tag"."id"
INSERT INTO "public"."_PostToTag" ("A","B") VALUES ($1,$2) ON CONFLICT DO NOTHING
SELECT "public"."Post"."id", "public"."Post"."name", "public"."Post"."status", "public"."Post"."body", "public"."Post"."slug", "public"."Post"."gameId", "public"."Post"."authorId", "public"."Post"."views" FROM "public"."Post" WHERE "public"."Post"."id" = $1 LIMIT $2 OFFSET $3
COMMIT

#### Update
後悔了想要刪掉其中一個tag，這邊採用的做法是直接把tag刪光再重新加回去

async updatePostById(id: number, data: UpdatePostDto) {
    const tagsData = data.tags.map((tag) => {
      return {
        where: { name: tag },
        create: { name: tag }
      };
    });
    const result = await this.prisma.post.update({
      where: { id },
      data: {
        ...data,
        tags: {
          disconnect: { name: ... },
          connectOrCreate: tagsData,
        },
      },
    });
    return result;
  }
                                                 

使用prisma語法時也可以直接查看input type來查找適合的語法，prisma的可讀性很高，因此透過這個方式幾乎不用去查文檔。