Prisma

Schema Best Practice

@auth/prisma-adapter requires some models for PostgreSQL but some models like Session, VerificationToken are unused when using JWT strategy of next-auth. This template pre-deletes them.

// https://pris.ly/d/prisma-schema

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

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["fullTextSearchPostgres"]
}

generator erd {
  provider                  = "prisma-erd-generator"
  theme                     = "forest"
  output                    = "ERD.md"
  includeRelationFromFields = true
}

// https://authjs.dev/getting-started/adapters/prisma#schema
model Account {
  id                String   @id @default(cuid())
  userId            String   @map("user_id")
  type              String
  provider          String
  providerAccountId String   @map("provider_account_id")
  refresh_token     String?  @db.Text
  access_token      String?  @db.Text
  expires_at        Int?
  token_type        String?
  scope             String?
  id_token          String?  @db.Text
  user              User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  createdAt         DateTime @default(now()) @map("created_at")
  updatedAt         DateTime @updatedAt @map("updated_at")

  @@unique([provider, providerAccountId])
  @@map("accounts")
}

model User {
  id            String         @id @default(cuid())
  name          String?
  email         String?        @unique
  emailVerified DateTime?      @map("email_verified")
  image         String?
  accounts      Account[]
  createdAt     DateTime       @default(now()) @map("created_at")
  updatedAt     DateTime       @updatedAt @map("updated_at")
  // https://authjs.dev/guides/basics/role-based-access-control
  role          Role           @default(USER)
  items         Item[]
  stripeId      String?        @unique @map("stripe_id") // cus_XXXX
  subscriptions Subscription[]

  @@map("users")
}

model Subscription {
  id                String    @id @default(cuid())
  subscriptionId    String    @unique @map("subscription_id") // sub_XXXX
  status            String
  currentPeriodEnd  DateTime? @map("current_period_end")
  cancelAtPeriodEnd Boolean   @default(false) @map("cancel_at_period_end")
  createdAt         DateTime  @default(now()) @map("created_at")
  updatedAt         DateTime  @updatedAt @map("updated_at")
  userId            String    @map("user_id")
  user              User      @relation(fields: [userId], references: [id])

  @@map("subscriptions")
}

model Item {
  id        String   @id @default(cuid())
  content   String
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  userId    String   @map("user_id")
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  @@map("items")
}

enum Role {
  USER
  ADMIN
}

• Prisma Adapter#schema

ER diagram

prisma-erd-generator can generate ERD from the schema. The current template ERD is here.

See Full Code

```mermaid
erDiagram

        Role {
            USER USER
ADMIN ADMIN
        }

  "accounts" {
    String id "🗝️"
    String user_id
    String type
    String provider
    String provider_account_id
    String refresh_token "❓"
    String access_token "❓"
    Int expires_at "❓"
    String token_type "❓"
    String scope "❓"
    String id_token "❓"
    DateTime created_at
    DateTime updated_at
    }


  "users" {
    String id "🗝️"
    String name "❓"
    String email "❓"
    DateTime email_verified "❓"
    String image "❓"
    DateTime created_at
    DateTime updated_at
    Role role
    String stripe_id "❓"
    }


  "subscriptions" {
    String id "🗝️"
    String subscription_id
    String status
    DateTime current_period_end "❓"
    Boolean cancel_at_period_end
    DateTime created_at
    DateTime updated_at
    String user_id
    }


  "items" {
    String id "🗝️"
    String content
    String user_id
    DateTime created_at
    DateTime updated_at
    }

    "accounts" o|--|| "users" : "user"
    "users" o{--}o "accounts" : "accounts"
    "users" o|--|| "Role" : "enum:role"
    "users" o{--}o "items" : "items"
    "users" o{--}o "subscriptions" : "subscriptions"
    "subscriptions" o|--|| "users" : "user"
    "items" o|--|| "users" : "user"
```

Making single client instance in Development Best Practice

In development, prisma requires avoiding multiple Prisma Client instances but Hot Module Replacement creates them. So this template prepares code to resolve this issue.

prisma.ts

// https://www.prisma.io/docs/orm/more/help-and-troubleshooting/help-articles/nextjs-prisma-client-dev-practices

import { PrismaClient } from "@prisma/client";
import { createDBUrl } from "../_utils/db";

function prismaClientSingleton() {
  return new PrismaClient({
    datasources: {
      db: {
        url: createDBUrl({}),
      },
    },
  });
}

// biome-ignore lint: Do not shadow the global "globalThis" property.
declare const globalThis: {
  prismaGlobal: ReturnType<typeof prismaClientSingleton>;
} & typeof global;

export const prisma = globalThis.prismaGlobal ?? prismaClientSingleton();

if (process.env.NODE_ENV !== "production") {
  globalThis.prismaGlobal = prisma;
}

db.ts

export function createDBUrl({
  user = process.env.DATABASE_USER,
  password = process.env.DATABASE_PASSWORD,
  host = process.env.DATABASE_HOST,
  port = Number(process.env.DATABASE_PORT),
  db = process.env.DATABASE_DB,
  schema = process.env.DATABASE_SCHEMA,
}: {
  user?: string;
  password?: string;
  host?: string;
  port?: number;
  db?: string;
  schema?: string;
}) {
  return `postgresql://${user}:${password}@${host}:${port}/${db}?schema=${schema}`;
}

> `datasources.db.url` is always set for parallel execution by testing

• Comprehensive Guide to Using Prisma ORM with Next.js

Observability Best Practice

Prisma provides OpenTelemetry tracing feature. This template uses @prisma/instrumentation and you can view Prisma Query, Engine, and Database Query on Jeager via otel-collector. Learn more here

• OpenTelemetry tracing