Chapter 06

PrismaからRDBを利用してみよう

oubakiou
oubakiou
2021.09.24に更新

MySQLコンテナを準備してみよう

ここまでの章ではソースコード上にハードコーディングされたデータを扱ってきました。しかし我々もそろそろデータベースを扱うべき時期です。FirebaseプラットフォームではFirestoreのようなドキュメント指向データベースを提供していますが、今回はリレーショナル・データベース(RDB)として代表的な製品の一つであるMySQLを扱います。

コラム:RDBとFirestore、どちらを使うべきか

伝統的なRDBにはデータの整合性を保証するための様々な機能、プログラミング無しでも宣言的にデータを扱える柔軟で強力なSQLなどFirestoreには無い利点が数多くあります。例えばFirestoreでは整合性制約やスキーマに相当するものはデータベースの機能として提供されていないため、データの整合性についてアプリケーション側でより多くの責任(や実装)を持つ事になります。

それでは逆にFirestoreを選ぶべき理由にはどういったものがあるのでしょうか。最も大きいものは2つあると筆者は考えています。

RDBよりもスケールアウトしやすい

極端に負荷が高い環境下では、RDBサーバーのインスタンス性能を上げていくスケールアップだけでは対応できなくなる場面があります。そういった場合にRDBではRead負荷であればレプリケーションと呼ばれる機能や、Write負荷であればテーブルの水平/垂直分割(シャーディング)で複数台構成にする事で対応していく事があります。しかしシャーディングを採用する場合には最初に挙げていたRDBの利点がかなり制限されます。

Firestoreではデータ設計にも影響するいくつかの独特な制限(例えば同一ドキュメントに対しては1秒当たり1回までの書き込みに抑える事が推奨されている等)はあるものの、利用者がサーバーの存在を意識する事はあまりなく性能に関しても比較的スムーズにスケールするようになっています。例えばチャットサービスのような極端に高いWrite負荷が予想される機能でかつ利用者数も多い想定あれば、最初からFirestoreで構築してしまうのも一つの手でしょう。

RDBよりも安い

Cloud SQLRDSのようないわゆるフルマネージドなRDBサービスでは、ほとんどDB問い合わせが発生しないサービスであっても月額1000円程度は最低額として発生してしまいます。(セルフマネージドが可能であればGCEEC2上でRDBを自力運用するのは比較的安価な選択肢になり得ます)また本格的なサービスでメモリやCPUを多く積んだハイスペックなRDBインスタンスを運用しようとすると一般的に高額になります。

Firestoreの場合はそもそも料金モデルが一般的なフルマネージドRDBとは異なり、実際に読み書きしたドキュメント(RDBにおけるレコード)の数などに基づいた、より実際の使用量に即したモデルになっています。アプリケーションやデータ構造の設計次第という部分も大きく単純比較が出来るわけでは有りませんが、筆者の経験上RDBに対して一桁や二桁安くなる事がままあります。ホビー用途であれば多くの場合は無料枠の中で収まるでしょう。

MySQLのオフィシャルイメージを利用します。Dockerがインストールされた状態で下記を実行しましょう。

docker pull mysql:8.0.26

続けてこのコンテナの設定として下記のdocker-compose.ymlを作成します。

docker-compose.yml
version: '3.8'
services:
  db:
    image: mysql:8.0.26
    # caching_sha2_passwordから旧来のmysql_native_passwordへ変更
    command: --default-authentication-plugin=mysql_native_password 
    restart: always
    env_file: .env
    ports:
    - 3306:3306      

またdocker-compose.ymlで扱う環境変数として下記を.envへ追加しましょう。

.env
XDG_CONFIG_HOME=.config
NEXT_PUBLIC_API_ROOT=http://localhost:3000
+MYSQL_ROOT_PASSWORD=example
+MYSQL_DATABASE=helloworld-db

それではdocker-compose.ymlのあるディレクトリへ移動して起動してみましょう。

docker-compose up

[Server] /usr/sbin/mysqld: ready for connections.というメッセージが表示されたらMySQL Workbenchを起動して接続の確認をします。

docker-compose.ymlの設定をもとに接続情報を入力しTest Connectionをクリックします。パスワードは.envで設定したものを入力しましょう。

Successfully made the MySQL connectionというメッセージが表示されれば接続成功です。

Prismaをセットアップしてみよう

Prismaは

  • Prisma Client(型安全なクエリービルダーを含むDBクライアント)
  • Prisma Migrate(DBマイグレーションツール)
  • Prisma Studio(DB操作用GUI)

などを提供しているプロジェクトです。本書ではPrisma ClientとPrisma Migrateを扱います。さっそく下記を実行してみましょう。

npm install --save-dev prisma

インストールが終わったら下記でセットアップを走らせます。

npx prisma init

initが終わるとprismaディレクトリが作成され、また.envには下記のような追記がされているはずです。

.env
XDG_CONFIG_HOME=.config
NEXT_PUBLIC_API_ROOT=http://localhost:3000
MYSQL_ROOT_PASSWORD=example
MYSQL_DATABASE=helloworld-db

+# This was inserted by `prisma init`:
+# Environment variables declared in this file are automatically made available to Prisma.
+# See the documentation for more detail: https://pris.ly/d/prisma-schema#using-environment-variables
+
+# Prisma supports the native connection string format for PostgreSQL, MySQL, SQLite, SQL Server (Preview) and MongoDB (Preview).
+# See the documentation for all the connection string options: https://pris.ly/d/connection-strings
+
+DATABASE_URL="postgresql://johndoe:randompassword@localhost:5432/mydb?schema=public"

DATABASE_URLは接続情報をURL形式で表現したものです。MySQL向けに修正しましょう。

.env
-DATABASE_URL="postgresql://johndoe:randompassword@localhost:5432/mydb?schema=public"
+DATABASE_URL="mysql://root:example@localhost:3306/helloworld-db"

Prismaスキーマを書いてみよう

PrismaではPrisma Schema Language(以下PSLと呼びます)という独自形式を用いてデータ構造を記述し、それを元に型安全なDBクライアントであるPrismaクライアントを生成したり、Prismaマイグレート経由でRDB上のテーブルを生成したりする事ができます。
(Introspectionという機能でRDBへ接続し、既存テーブル情報からPrismaスキーマを生成して利用するSQLファーストなワークフローも可能です)

それでは早速PSLでスキーマを書いてみましょう。

prisma/schema.prisma
datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

model User {
  id        String    @id @default(cuid())
  name      String
  createdAt DateTime? @default(now())
  Status    Status[]
}

model Status {
  id        String    @id @default(cuid())
  author    User      @relation(fields: [authorId], references: [id])
  authorId  String
  body      String
  createdAt DateTime? @default(now())
}

model BannerGroup {
  id        String    @id @default(cuid())
  name      String
  createdAt DateTime? @default(now())
  Banner    Banner[]
}

model Banner {
  id            String      @id @default(cuid())
  bannerGroup   BannerGroup @relation(fields: [bannerGroupId], references: [id])
  bannerGroupId String
  href          String?
  createdAt     DateTime?   @default(now())
}

なおVScodeでPrismaスキーマ保存時のオートフォーマットを有効化するため下記のようにsettings.jsonを変更しておきましょう。

.vscode/settings.json
{
  "editor.defaultFormatter": "esbenp.prettier-vscode",
  "editor.formatOnSave": true,
  "editor.codeActionsOnSave": {
    "source.fixAll.eslint": false
  },
  "typescript.tsdk": "node_modules/typescript/lib",
  "typescript.preferences.importModuleSpecifier": "non-relative",
+  "[prisma]": {
+    "editor.defaultFormatter": "Prisma.prisma"
+  }
}

schema.prismaが保存できたら下記を実行します。

npx prisma migrate dev --name init

実行が終わるとprisma/migrationsというディレクトリが作成されているはずです。

PSLからどういったSQLが生成されるか、その中のmigration.sqlを見てみましょう。

migration.sql
-- CreateTable
CREATE TABLE `User` (
    `id` VARCHAR(191) NOT NULL,
    `name` VARCHAR(191) NOT NULL,
    `createdAt` DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3),

    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `Status` (
    `id` VARCHAR(191) NOT NULL,
    `authorId` VARCHAR(191) NOT NULL,
    `body` VARCHAR(191) NOT NULL,
    `createdAt` DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3),

    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `BannerGroup` (
    `id` VARCHAR(191) NOT NULL,
    `name` VARCHAR(191) NOT NULL,
    `createdAt` DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3),

    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `Banner` (
    `id` VARCHAR(191) NOT NULL,
    `bannerGroupId` VARCHAR(191) NOT NULL,
    `href` VARCHAR(191),
    `createdAt` DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3),

    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- AddForeignKey
ALTER TABLE `Status` ADD FOREIGN KEY (`authorId`) REFERENCES `User`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Banner` ADD FOREIGN KEY (`bannerGroupId`) REFERENCES `BannerGroup`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

prisma migrate devの実行によってこれらのSQL生成と同時にDBへの適用も行われています。MySQL workbenchからも実体を確認してみましょう。

なおマイグレーションの作成のみを実行しDBへの適用をしたくない場合は--create-onlyを付けると良いでしょう。

さて、それではschema.prismaに戻って上から見ていきます。

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

datasourceでは接続するRDBについて設定します。ここでは.evnで定義していたDATABASE_URLを利用しています。

generator client {
  provider = "prisma-client-js"
}

generatorはコード生成に関する設定です。ここでは前述した型安全なDBクライアントであるPrismaクライアントの生成を指示しています。また今回は利用しませんがDBクライアント以外にもコミュニティが提供しているprovider等を利用して様々なコードやドキュメント等を生成する事もできます。

model User {
  id        String    @id @default(cuid())
  name      String
  createdAt DateTime? @default(now())
  Status    Status[]
}

model Status {
  id        String    @id @default(cuid())
  author    User      @relation(fields: [authorId], references: [id])
  authorId  String
  body      String
  createdAt DateTime? @default(now())
}

model BannerGroup {
  id        String    @id @default(cuid())
  name      String
  createdAt DateTime? @default(now())
  Banner    Banner[]
}

model Banner {
  id            String       @id @default(cuid())
  bannerGroup   BannerGroup  @relation(fields: [bannerGroupId], references: [id])
  bannerGroupId String
  href          String?
  createdAt     DateTime?    @default(now())
}

modelはスキーマ記述の本体です。この定義に従ってPrismaクライアントが扱う型や、RDB上の実テーブルなどが生成される事になります。

PSLにおけるプリミティブ型について

例えばString型は、利用しているのがMySQLであればvarchar(191)としてデフォルトマッピングされます。これを変更したい場合は

body   String @db.MediumText

のように指定する事になります。(MEDIUMTEXTはMySQL固有のカラム型です)

コラム:なぜ191文字なのか

MySQL(InnoDB+Antelope)の場合、単一カラムインデックスを作る際に標準では767バイトまでのキー長しか扱えません。これを越えた場合

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

というエラーを目にする事になります。

絵文字も表現可能なutf8mb4を利用する場合は1文字で4バイトを消費するため、インデックスを作成する場合には191文字(4*191=764)が安全な最大文字数という事になります。なお扱うUTF-8が3バイト(utf8mb3)だった時代には255文字(3*255=765)がデフォルトの最大値として利用されていました。

PSLにおける@relationについて

@relationを使う事でmodel同士の関係性を記述する事ができ、RDB上は外部キーが作成されます。

model User {
  id        String    @id @default(cuid())
  name      String
  createdAt DateTime? @default(now())
  Status    Status[]
}

model Status {
  id        String    @id @default(cuid())
  author    User      @relation(fields: [authorId], references: [id])
  authorId  String
  body      String
  createdAt DateTime? @default(now())
}

例えば「1つのUserが複数(N個)のStatusを持っている」という1:Nの関係を表現する場合、Status側にはそのStatusの作者が誰なのかを示すUser型のフィールドを持ち、User側にはStatus[]型を持つことになります。

author    User     @relation(fields: [authorId], references: [id])

これはStatus.authorIdUser.idを突き合わせる事で具体的なUserを取ってくるという意味です。

Prismaでマイグレーションしてみよう

ここで少しスキーマ定義の変更を試してみましょう。まずはPSLを変更します。

model Status {
  id        String   @id @default(cuid())
  author    User     @relation(fields: [authorId], references: [id])
  authorId  String
-  body      String
+  body      String    @db.MediumText
  createdAt DateTime? @default(now())
}

schema.prismaの変更が保存できたら再びprisma migrate devコマンドを実行します。

npx prisma migrate dev --name to_mediumtext

今度はto_mediumtextディレクトリのmigration.sqlを確認してみましょう。

-- AlterTable
ALTER TABLE `Status` MODIFY `body` MEDIUMTEXT NOT NULL;

このようにスキーマ定義に変更があると、prisma migrate devは最新のマイグレーションファイルからの差分として追加のマイグレーションファイルを作成してくれます。特にチーム開発であれば、スキーマ定義の変更がマイグレーションファイルとしてソースコードと同様に管理できるのは便利でしょう。他人が行ったスキーマ定義変更を自身の手元にあるDBへ適用したい場合にはnpx prisma migrate deployを実行しましょう。

Prismaクライアントでseed(初期テストデータ)を作ってみよう

そろそろPrismaクライアントを使って実際にDBからデータを取り出してみたい所ですが、そのためにはまずDBへデータを追加する必要があります。入力フォームを利用したDBへのデータ保存については次のセクションで説明しますが、今回はseedと呼ばれる仕組みで初期テストデータを準備しましょう。

package.json
  "main": "firebaseFunctions.js",
+  "prisma": {
+    "seed": "ts-node prisma/seed.ts"
+  },
  "scripts": {

まずはseedファイルの場所と実行方法をpackage.jsonに追加します。

npm install --save-dev ts-node

またseedをTypeScriptで書いて実行するためts-nodeを入れます。

prisma/seed.ts
import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient()

async function main() {
  const jack = await prisma.user.create({
    data: {
      name: 'jack',
      Status: {
        create: [
          {
            body: 'just setting up my app',
            createdAt: new Date('2006/03/22 11:00:00'),
          },
          {
            body: 'inviting coworkers',
            createdAt: new Date('2014/03/22 12:00:00'),
          },
          { body: 'MySQL server has gone away...?' },
        ],
      },
    },
  })
  console.log(`Created user with id: ${jack.id}`)
}

main()
  .catch((e) => {
    console.error(e)
    process.exit(1)
  })
  .finally(async () => {
    await prisma.$disconnect()
  })

package.jsonの設定に従ってnpx prisma migrate resetなどのタイミングでこのseedが実行されるようになります。seed.ts自体は単なるTypeScriptファイルです。PrismaClientを利用した通常のプログラムと同様にデータを作成するためのコードを記述します。

このPrismaClientにはPSLを元に生成された実装と型情報とが含まれています。

node_modules/.prisma/client/index.d.ts
  export type UserCreateInput = {
    name: string
    createdAt?: Date | string | null
    Status?: StatusCreateNestedManyWithoutAuthorInput
  }

このため例えばUserに存在しないフィールドをprisma.user.createへ渡そうとするとちゃんと警告されます。

さてそれではseedを実行してみましょう。

npx prisma migrate reset

以下のように表示されればseed処理は終了です。

Running seed from seed.ts ...
Result:
Created user with id: 1

MySQL workbenchからも直接データを確認してみましょう。

コラム:主キーをどのように生成するべきか

そもそも「主キーにはナチュラルキーを使用するべきかサロゲートキーを使用するべきか」という有史以来のホットな議論がありますが、筆者の見解としては「絶対にサロゲートキーを使うべき」です。そのサロゲートキーをどのように生成するべきか、というのがここでの本題です。

RDBの機能で発番する

おそらく最もシンプルでメジャーな手法はRDB自身が持つ機能で発番する方法です。MySQLやOracle12c以降であればAUTO_INCREMENT、PostgreSQLであればSERIAL型といったものがあります。この手法の大きなデメリットとしては単一のDBが管理する発番機構ではスケールしないという点があります。(またGraphQLを前面で扱う場合の話ですが、GraphQLのID型はString型である必要があるのに対しAUTO_INCREMENTで発番された主キーはIntになるため、IDを全てキャストする必要が発生するなどの相性の悪さもあります)

衝突耐性のあるアルゴリズムでアプリケーションが発番する

そこで単一のDBに発番させるのではなく、個々のアプリケーションに衝突耐性のあるロジックで分散発番させるという手法がとられる事があります。有名なものとしてはSnow Flake(現在はメンテナンス停止)、ULIDUUIDといったアルゴリズムがあります。

なおUUIDについては衝突耐性はあるものの、連続性が低くMySQLでの主キー利用にはあまり向いていないという注意点があります。(アクセスするリーフページの局所性がないためインデックスサイズが大きくなるとINSERT時にキャッシュ外アクセスが頻発しやすい)

Prismaでのサポート

現在のPrismaではUUIDとCUID標準サポートされています。本書ではこのうちCUIDを利用しています。

Prismaクライアントでデータを取り出してみよう

「GraphQLを使ってみよう」の章ではソースコード上にハードコーディングされたデータを扱っていました。この章の締めくくりとして、これをPrismaClientでMySQLから取得するよう変更してみましょう。

src/graphql/resolvers.ts
import { Resolvers } from '@gql/generated/resolvers-types'
import { PrismaClient } from '@prisma/client'

export const resolvers: Resolvers = {
  Query: {
    status(_parent, args) {
      return getStatus(args.id) ?? null
    },
    statuses() {
      return listStatuses()
    },
    banners(_parent, args) {
      return listBanners(args.groupId)
    },
  },
  Status: {
    author: (parent) => {
      return getAuthor(parent.authorId) ?? null
    },
  },
}

const prisma = new PrismaClient()

const listStatuses = async () =>
  await prisma.status.findMany({ orderBy: { createdAt: 'desc' } })

const getStatus = async (id: string) =>
  await prisma.status.findUnique({ where: { id: id } })

const getAuthor = async (id: string) =>
  await prisma.user.findUnique({ where: { id: id } })

const listBanners = async (groupId: string) =>
  await prisma.banner.findMany({
    where: { bannerGroupId: groupId },
  })

GraphQL側のスキーマ(SDL)に変更はないため内部実装であるResolverを差し替えるだけです。

npm run dev

Next.jsを起動してhttp://localhost:3000/にアクセスしてみましょう。