Chapter 05

トランザクション

Thirosue

2021.05.17に更新

このチャプターの目次

トランザクション

トランザクション

Prismaでのトランザクション管理を確認していきます。

事前準備

本チャプターでも、prisma/index.tsファイルの以下のTODO箇所に処理を記載し、Prismaでのトランザクション管理機能を確認していく

prisma/index.ts

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

async function main() {
  // TODO ここに処理を記載する
}

main()
  .catch((e) => {
    throw e
  })
  .finally(async () => {
    await prisma.$disconnect()
  })

マイグレーション

prisma/schema.prisma を以下の通り修正

ユーザプロフィールを追加する

model User {
  id    Int     @id @default(autoincrement())
  email String  @unique
  name  String
  tel   String? @db.VarChar(11)
  posts   Post[]
  profile Profile? // <---追加
}

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

// 追加
model Profile {
  id     Int     @default(autoincrement()) @id
  bio    String?
  user   User    @relation(fields: [userId], references: [id])
  userId Int     @unique
}

変更反映

% npx prisma migrate dev --name add_profile
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "mydb", schema "postgres" at "localhost:5432"

The following migration(s) have been created and applied from new schema changes:

migrations/
  └─ 20210517205418_add_profile/
    └─ migration.sql

Your database is now in sync with your schema.

✔ Generated Prisma Client (2.22.1) to ./node_modules/@prisma/client in 85ms

自動トランザクション

ユーザを登録する。

プロフィール付きで登録する

  const john = await prisma.user.create({
      data: {
          name: 'john',
          email: 'john@exsample.com',
          profile: {
              create: {
                  bio: 'I like turtles',
              }
          } 
      }
  })

  console.log(john)

実行

% npx ts-node prisma/index.ts
{ id: 1, email: 'john@exsample.com', name: 'john', tel: null }

発行されたSQLは以下のとおり

ネストさせると自動的にトランザクションを発行し、ACID特性のAtomicity（原子性）を担保してくれる

2021-05-17 20:59:53.447 UTC [107] LOG:  statement: BEGIN
2021-05-17 20:59:53.451 UTC [107] LOG:  execute s0: INSERT INTO "postgres"."User" ("email","name") VALUES ($1,$2) RETURNING "postgres"."User"."id"
2021-05-17 20:59:53.451 UTC [107] DETAIL:  parameters: $1 = 'john@exsample.com', $2 = 'john'
2021-05-17 20:59:53.454 UTC [107] LOG:  execute s1: INSERT INTO "postgres"."Profile" ("bio","userId") VALUES ($1,$2) RETURNING "postgres"."Profile"."id"
2021-05-17 20:59:53.454 UTC [107] DETAIL:  parameters: $1 = 'I like turtles', $2 = '1'
2021-05-17 20:59:53.458 UTC [107] LOG:  execute s2: SELECT "postgres"."User"."id", "postgres"."User"."email", "postgres"."User"."name", "postgres"."User"."tel" FROM "postgres"."User" WHERE "postgres"."User"."id" = $1 LIMIT $2 OFFSET $3
2021-05-17 20:59:53.458 UTC [107] DETAIL:  parameters: $1 = '1', $2 = '1', $3 = '0'
2021-05-17 20:59:53.459 UTC [107] LOG:  statement: COMMIT

手動トランザクション

ユーザ作成と投稿を一気に行う。

ネスト記法（自動トランザクション）、件数カウントも織り交ぜて実行する

  const [user, post, totalPosts ] = await prisma.$transaction([
      prisma.user.create({
          data: {
              name: 'mike',
              email: 'mike@example.com',
              profile: {
                  create: {
                      bio: 'I like turtles',
                  }
              }
          }
      }),
      prisma.post.create({
          data: {
              title: 'sample post'
          }
      }),
      prisma.post.count(),
  ])

  console.log(user, post, totalPosts)

実行

% npx ts-node prisma/index.ts
{ id: 2, email: 'mike@example.com', name: 'mike', tel: null } {
  id: 1,
  title: 'sample post',
  content: null,
  published: false,
  authorId: null
} 1

発行されたSQLは以下のとおり

宣言したスコープでトランザクションが有効となり、自動トランザクション機能も無効になる。

2021-05-17 21:10:48.133 UTC [130] LOG:  statement: BEGIN
2021-05-17 21:10:48.138 UTC [130] LOG:  execute s0: INSERT INTO "postgres"."User" ("email","name") VALUES ($1,$2) RETURNING "postgres"."User"."id"
2021-05-17 21:10:48.138 UTC [130] DETAIL:  parameters: $1 = 'mike@example.com', $2 = 'mike'
2021-05-17 21:10:48.140 UTC [130] LOG:  execute s1: INSERT INTO "postgres"."Profile" ("bio","userId") VALUES ($1,$2) RETURNING "postgres"."Profile"."id"
2021-05-17 21:10:48.140 UTC [130] DETAIL:  parameters: $1 = 'I like turtles', $2 = '2'
2021-05-17 21:10:48.143 UTC [130] LOG:  execute s2: SELECT "postgres"."User"."id", "postgres"."User"."email", "postgres"."User"."name", "postgres"."User"."tel" FROM "postgres"."User" WHERE "postgres"."User"."id" = $1 LIMIT $2 OFFSET $3
2021-05-17 21:10:48.143 UTC [130] DETAIL:  parameters: $1 = '2', $2 = '1', $3 = '0'
2021-05-17 21:10:48.146 UTC [130] LOG:  execute s3: INSERT INTO "postgres"."Post" ("title","published") VALUES ($1,$2) RETURNING "postgres"."Post"."id"
2021-05-17 21:10:48.146 UTC [130] DETAIL:  parameters: $1 = 'sample post', $2 = 'f'
2021-05-17 21:10:48.148 UTC [130] LOG:  execute s4: SELECT "postgres"."Post"."id", "postgres"."Post"."title", "postgres"."Post"."content", "postgres"."Post"."published", "postgres"."Post"."authorId" FROM "postgres"."Post" WHERE "postgres"."Post"."id" = $1 LIMIT $2 OFFSET $3
2021-05-17 21:10:48.148 UTC [130] DETAIL:  parameters: $1 = '1', $2 = '1', $3 = '0'
2021-05-17 21:10:48.151 UTC [130] LOG:  execute s5: SELECT COUNT(*) FROM (SELECT "postgres"."Post"."id" FROM "postgres"."Post" WHERE 1=1 OFFSET $1) AS "sub"
2021-05-17 21:10:48.151 UTC [130] DETAIL:  parameters: $1 = '0'
2021-05-17 21:10:48.152 UTC [130] LOG:  statement: COMMIT