Prismaを触る

dak2

目的

Prismaの使用感を確認する

やること

https://v1.prisma.io/docs/1.34/get-started/ のチュートリアルを触る
様々なクエリを発行する

dak2

とりあえず関連ライブラリを入れる

npm init -y
npm install prisma typescript ts-node @types/node --save-dev

そしてprismaを初期化するところまで持っていく

npx prisma init

dak2

prismaの初期化設定が終わると、schema.prisma ファイルと接続先DBのサンプルURLが記載された.envファイルが作成される

接続先DBを立てて接続できるようにしていく

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

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

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

DATABASE_URL="postgresql://johndoe:randompassword@localhost:5432/mydb?schema=public"

dak2

とりあえず動けばなんでも良いので、そのままPostgreSQLを立てる

docker-compose up -d

version: '3'
services:
  postgres:
    image: postgres:13.4
    environment:
      POSTGRES_USER: user
      POSTGRES_PASSWORD: password
    ports:
      - 5432:5432
    volumes:
      - ./db:/var/lib/postgresql/data

その後、.envファイルの接続先URLを下記に変更しておく
パス名の構造は https://www.prisma.io/docs/concepts/database-connectors/postgresql#base-url-and-path に記載の通り

DATABASE_URL="postgresql://user:password@localhost:5432/mydb"

dak2

schema.prisma ファイルにDBのスキーマを定義する

model Post {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  title     String   @db.VarChar(255)
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
}

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

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

prisma migrateを実行

npx prisma migrate dev --name init

dak2

migrateに成功するとDDLが吐かれる

-- CreateTable
CREATE TABLE "Post" (
    "id" SERIAL NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,
    "title" VARCHAR(255) NOT NULL,
    "content" TEXT,
    "published" BOOLEAN NOT NULL DEFAULT false,
    "authorId" INTEGER NOT NULL,

    CONSTRAINT "Post_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "Profile" (
    "id" SERIAL NOT NULL,
    "bio" TEXT,
    "userId" INTEGER NOT NULL,

    CONSTRAINT "Profile_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "User" (
    "id" SERIAL NOT NULL,
    "email" TEXT NOT NULL,
    "name" TEXT,

    CONSTRAINT "User_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE UNIQUE INDEX "Profile_userId_key" ON "Profile"("userId");

-- CreateIndex
CREATE UNIQUE INDEX "User_email_key" ON "User"("email");

-- AddForeignKey
ALTER TABLE "Post" ADD CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "Profile" ADD CONSTRAINT "Profile_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

dak2

DBを作り忘れていたので作る

❯ dcxec postgres /bin/sh
# psql -U user    
psql (13.4 (Debian 13.4-4.pgdg110+1))
Type "help" for help.

user=# create database mydb;

CREATE DATABASE
user=#

再度 npx prisma migrate devを実行

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

Applying migration `20230521054309_init`

The following migration(s) have been applied:

migrations/
  └─ 20230521054309_init/
    └─ migration.sql

Your database is now in sync with your schema.

✔ Generated Prisma Client (4.14.1 | library) to ./node_modules/@prisma/client in 475ms

dak2

Prisma Clientを入れる

npm install @prisma/client

Clientを通じてクエリするためにファイルを作成

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

async function main() {
  // ... you will write your Prisma Client queries here
}

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

下記diffを追記

async function main() {
  // ... you will write your Prisma Client queries here
+ const allUsers = await prisma.user.findMany()
+ console.log(allUsers)
}

実際にクエリを投げる

npx ts-node index.ts  
[]

dak2

データを書き込む

diff --git a/index.ts b/index.ts
index ed8563e..91de257 100644
--- a/index.ts
+++ b/index.ts
@@ -3,9 +3,26 @@ import { PrismaClient } from '@prisma/client'
 const prisma = new PrismaClient()
 
 async function main() {
-  // ... you will write your Prisma Client queries here
-  const allUsers = await prisma.user.findMany()
-  console.log(allUsers)
+  await prisma.user.create({
+    data: {
+      name: 'Alice',
+      email: 'alice@prisma.io',
+      posts: {
+        create: { title: 'Hello World' },
+      },
+      profile: {
+        create: { bio: 'I like turtles' },
+      },
+    },
+  })
+
+  const allUsers = await prisma.user.findMany({
+    include: {
+      posts: true,
+      profile: true,
+    },
+  })
+  console.dir(allUsers, { depth: null })
}

npx ts-node index.ts
[
  {
    id: 1,
    email: 'alice@prisma.io',
    name: 'Alice',
    posts: [
      {
        id: 1,
        createdAt: 2023-05-21T06:48:25.139Z,
        updatedAt: 2023-05-21T06:48:25.139Z,
        title: 'Hello World',
        content: null,
        published: false,
        authorId: 1
      }
    ],
    profile: { id: 1, bio: 'I like turtles', userId: 1 }
  }
]

dak2

チュートリアルを終えたので様々なクエリを発行してみる

dak2

findFirst()
- include オプションは事前にロードしておくリレーションを指定

async function findFirst() {
  const user = await prisma.user.findFirst({
    where: {
      posts: {
        some: {
          title: {
            contains: 'Hello'
          }
        }
      }
    },
    orderBy: {
      email: 'asc'
    },
    select: {
      id: true,
      email: true,
    }
  });
  console.log(user);
}

$ npx ts-node index.ts
{ id: 3, email: 'alice0@prisma.io' }

dak2

findMany()

async function findMany() {
  const result = await prisma.user.findMany({
    select: {
      id: true,
      name: true,
      email: true,
      posts: {
        select: {
          id: true,
          title: true,
          published: true,
        }
      }
    },
  })
  console.log(result);
}

$ npx ts-node index.ts
[
  {
    id: 1,
    name: 'Alice',
    email: 'alice@prisma.io',
    posts: [ [Object] ]
  },
  {
    id: 2,
    name: 'Alice_1',
    email: 'alice1@prisma.io',
    posts: [ [Object] ]
  },
  {
    id: 3,
    name: 'Alice_0',
    email: 'alice0@prisma.io',
    posts: [ [Object] ]
  }
]

ちゃんと型効く

dak2

発行されるSQLを見る

diff --git a/index.ts b/index.ts
index 2aaaf14..0f27712 100644
--- a/index.ts
+++ b/index.ts
@@ -1,11 +1,12 @@
 import { PrismaClient } from '@prisma/client'
 
-const prisma = new PrismaClient()
+const prisma = new PrismaClient({ log: ["query"] })

先ほどのfindMany()で発行されるクエリはusersテーブルのIDを全部取得して、postsのauthor_idにIN句で指定していることが分かる
JOINされる感じだと思ったけど2回にクエリが分けられるのか

$ npx ts-node index.ts
prisma:query SELECT "public"."User"."id", "public"."User"."name", "public"."User"."email" FROM "public"."User" WHERE 1=1 OFFSET $1
prisma:query SELECT "public"."Post"."id", "public"."Post"."title", "public"."Post"."published", "public"."Post"."authorId" FROM "public"."Post" WHERE "public"."Post"."authorId" IN ($1,$2,$3,$4,$5,$6,$7,$8) OFFSET $9
[
  {
    id: 1,
    name: 'Alice',
    email: 'alice@prisma.io',
    posts: [ [Object] ]
  },
  {
    id: 2,
    name: 'Alice_1',
    email: 'alice1@prisma.io',
    posts: [ [Object] ]
  },
  {
    id: 3,
    name: 'Alice_0',
    email: 'alice0@prisma.io',
    posts: [ [Object] ]
  }
]

dak2

 async function findMany() {
-  const result = await prisma.user.findMany({
-    select: {
-      id: true,
-      name: true,
-      email: true,
-      posts: {
-        select: {
-          id: true,
-          title: true,
-          published: true,
-        }
-      }
+  const result = await prisma.post.findMany({
+    select: { id: true, title: true },
+    where: {
+      author: {
+        email: "alice3@prisma.io",
+      },
     },
-  })
+  });

$ npx ts-node index.ts
prisma:query SELECT "public"."Post"."id", "public"."Post"."title" FROM "public"."Post" WHERE ("public"."Post"."id") IN (SELECT "t0"."id" FROM "public"."Post" AS "t0" INNER JOIN "public"."User" AS "j0" ON ("j0"."id") = ("t0"."authorId") WHERE ("j0"."email" = $1 AND "t0"."id" IS NOT NULL)) OFFSET $2
[ { id: 8, title: 'Hello World' } ]

これだとJOINが発行されているが、サブクエリでJOINした結果のIDをIN句に渡す感じになっている

dak2

使い方なんとなく掴めたのでなんか作りたい