Open7

Prisma Schemaの書き方あれこれ

okerraokerra

フィールド

String

types type attribute
VARCHAR @db.VarChar(X)
TEXT @db.Text
VARCHAR @db.VarChar(X)
CHAR @db.Char(X)
TINYTEXT @db.TinyText
MEDIUMTEXT @db.MediumText
LONGTEXT @db.LongText

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

Boolean

types type attribute
TINYINT(1) @db.TinyInt(1)

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

Int

types type attribute
INT @db. Int
INT UNSIGNED @db. UnsignedInt
YEAR @db. Year

https://www.prisma.io/docs/reference/api-reference/prisma-schema-reference#mysql-2

BigInt

types type attribute
BIGINT @db. BigInt

https://www.prisma.io/docs/reference/api-reference/prisma-schema-reference#mysql-3

Float

types type attribute
FLOAT @db. Float
DOUBLE @db. Double

https://www.prisma.io/docs/reference/api-reference/prisma-schema-reference#mysql-4

DateTime

types type attribute
DATETIME(x) @db. DateTime(x)
DATE(x) @db. Date(x)
TIME(x) @db. Time(x)
TIMESTAMP(x) @db. Timestamp(x)

https://www.prisma.io/docs/reference/api-reference/prisma-schema-reference#mysql-6

okerraokerra

フィールド修飾子

Optional

? modifier

model User {
  id   Int     @id @default(autoincrement())
  name String?
}
okerraokerra

Attributes

@id

単一フィールドIDを定義。
IDを自動生成する関数を使用する@default()値で注釈を付けることが可能。

  • autoincrement()
  • cuid()
  • uuid()
model User {
  id        Int      @id @default(autoincrement())
 }

@relation

model Profile {
  authorId Int    @id
  author   User   @relation(fields: [authorId], references: [id])
  bio      String
}

model User {
  id      Int      @id
  email   String   @unique
  name    String?
  profile Profile?
}

@@id

複合IDを定義する

model User {
  firstName String
  lastName  String
  email     String  @unique
  isAdmin   Boolean @default(false)

  @@id([firstName, lastName])
}

@default

model User {
  email  String @unique
  number Float  @default(1.1)
}
model User {
  email  String @unique
  number Decimal @default(22.99)
}
enum Role {
  USER
  ADMIN
}

model User {
  id      Int      @id @default(autoincrement())
  role    Role     @default(USER)
}

@unique

ユニーク属性

model User {
  email String @unique
  name  String
}

@@unique

複合ユニーク属性

model User {
  id        Int     @default(autoincrement())
  firstName String
  lastName  String
  isAdmin   Boolean @default(false)

  @@unique([firstName, lastName])
}

@@index

single-column index

model Post {
  id      Int     @id @default(autoincrement())
  title   String
  content String?

  @@index([title])
}

multi-column index

model Post {
  id      Int     @id @default(autoincrement())
  title   String
  content String?

  @@index([title, content])
}

@updatedAt

model Post {
  id        String   @id
  updatedAt DateTime @updatedAt
}
okerraokerra

Enum

enum Role {
  USER
  ADMIN
}

model User {
  id   Int  @id @default(autoincrement())
  role Role
}

# ex) using default

model User {
  id   Int  @id @default(autoincrement())
  role Role @default(USER)
}
okerraokerra

relationship

リレーションシップを定義する

model User {
  id           Int     @id @default(autoincrement())
  name         String?
  writtenPosts Post[]
  pinnedPost   Post?
}

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

https://www.prisma.io/docs/concepts/components/prisma-schema/relations#the-relation-attribute

また@relation属性の注釈を付け、name引数を指定することで明確に関係性を関連づけることができる

model User {
  id           Int     @id @default(autoincrement())
  name         String?
  writtenPosts Post[]  @relation("WrittenPosts")
  pinnedPost   Post?   @relation("PinnedPost")
}

model Post {
  id         Int     @id @default(autoincrement())
  title      String?
  author     User    @relation("WrittenPosts", fields: [authorId], references: [id])
  authorId   Int
  pinnedBy   User?   @relation(name: "PinnedPost", fields: [pinnedById], references: [id])
  pinnedById Int?
}
okerraokerra

@@map

個人的にはmysqlのテーブル名はスネークケースの複数形でやりたいが、Prismaのデフォルトは先頭大文字の単数系(model名)が自動で生成される。

そこでテーブル名を変更したい場合は@@map()を使って独自のテーブル名のマイグレーションファイルを生成する

model User {
  id           Int     @id @default(autoincrement())
  name         String?
  writtenPosts Post[]
  pinnedPost   Post?

  @@map('users')  // 例えばここをuser_postsなどにする
}

https://www.prisma.io/docs/concepts/components/prisma-schema/data-model#mapping-model-names-to-tables-or-collections