Prisma is a modern database toolkit that provides a type-safe ORM (Object-Relational Mapping), making it easy for developers to interact with databases. With Prisma, you can automatically generate typed database queries, reducing the need to write raw SQL. It is an ideal choice for building efficient and scalable database applications.

1. Vercel Postgres Free Database

Vercel offers a database storage service. For free users, you get 100,000 database operations per month and 10 GB of storage, which is more than enough for a small personal site.

Create a Prisma Postgres data service under the Storage tab on the Vercel platform. Paste the Vercel database URL into your .env file as DATABASE_URL="************". All local database operations will directly affect the remote storage.

2. Initialize Prisma Project

Prisma is a Node.js and TypeScript-based ORM that helps developers manage databases with faster development speed and fewer errors.

For detailed steps, refer to the official guide: How to use Prisma ORM with Next.js. The client directory I generated is different from the official guide, but everything else is the same.

1. Install and Initialize Configuration

Install dependencies:

npm install prisma tsx --save-dev
npm install @prisma/extension-accelerate @prisma/client

Initialize configuration:

npx prisma init --db --output ../src/generated/prisma

This operation will:

• Create a prisma directory containing the schema.prisma file.
• Create a Prisma Postgres database.
• Add a .env file with DATABASE_URL in the project root.
• Set the generated Prisma Client output directory to src/generated/prisma.

2. Define Model Configuration

Modify the blog model as needed. Use cuid() for the id, which is more suitable than auto-incrementing numbers and less likely to be targeted by malicious crawlers.

// prisma/schema.prisma

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

generator client {
  provider = "prisma-client-js"
  output   = "../src/generated/prisma"
}

generator pothos {
  provider = "prisma-pothos-types"
}

model User {
  id        String    @id @default(cuid())
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
  email     String    @unique
  image     String?
  nickname  String    @default("")
  role      Role      @default(USER)
  bookmarks Link[]
  posts     Post[]
}

enum Role {
  USER
  ADMIN
}

model Post {
  id          String    @id @default(cuid())
  title       String
  summary     String    @default("")
  content     String
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt
  createdBy   User      @relation(fields: [createdById], references: [id])
  createdById String
}

3. Configure Prisma Client

npx prisma migrate dev --name init

This step will create the User and Post tables locally and generate the Prisma client files.

Whenever you modify the model, run npx prisma migrate dev --name xxxx to sync the table information.

To view the local database UI, run npx prisma studio. Saving changes will sync them to remote storage.

3. Using Prisma to Operate the Database

1. Set up the Prisma client

Import the client files generated in the previous step:

// src/lib/prisma.ts
import { PrismaClient } from '../generated/prisma'
import { withAccelerate } from '@prisma/extension-accelerate'

const globalForPrisma = global as unknown as {
  prisma: PrismaClient
}

const prisma =
  globalForPrisma.prisma || new PrismaClient().$extends(withAccelerate())

if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma

export default prisma

2. Query the list of posts

import prisma from '@/lib/prisma'
const posts = await prisma.post.findMany()

3. Create a new post

import prisma from '@/lib/prisma'
await prisma.post.create({
  data: {
    title,
    content,
  },
})

4. Update a post

import prisma from '@/lib/prisma'
const post = await prisma.post.update({
  where: { id },
  data: { title, summary, content },
})

5. Delete a post

import prisma from '@/lib/prisma'
await prisma.post.delete({
  where: { id },
})