Prisma Setup | Bootspring Docs

Complete PostgreSQL database setup with Prisma ORM.

Dependencies#

npm install prisma @prisma/client
npm install -D prisma

Environment Variables#

# .env
DATABASE_URL="postgresql://user:password@localhost:5432/mydb?schema=public"

# For Neon (serverless)
DATABASE_URL="postgresql://user:password@ep-xxx.us-east-1.aws.neon.tech/mydb?sslmode=require"

Initialize Prisma#

npx prisma init

Schema Definition#

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")
  directUrl = env("DIRECT_URL") // For migrations with connection pooling
}

// User model
model User {
  id        String   @id @default(cuid())
  clerkId   String   @unique
  email     String   @unique
  name      String?
  imageUrl  String?
  role      Role     @default(USER)

  // Relations
  posts     Post[]
  comments  Comment[]

  // Timestamps
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@index([email])
}

enum Role {
  USER
  ADMIN
  MODERATOR
}

// Post model
model Post {
  id        String   @id @default(cuid())
  title     String
  slug      String   @unique
  content   String?
  published Boolean  @default(false)

  // Relations
  authorId  String
  author    User     @relation(fields: [authorId], references: [id], onDelete: Cascade)
  comments  Comment[]
  tags      Tag[]

  // Timestamps
  publishedAt DateTime?
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt

  @@index([authorId])
  @@index([slug])
  @@index([published, createdAt])
}

// Comment model
model Comment {
  id      String @id @default(cuid())
  content String

  // Relations
  postId   String
  post     Post   @relation(fields: [postId], references: [id], onDelete: Cascade)
  authorId String
  author   User   @relation(fields: [authorId], references: [id], onDelete: Cascade)

  // Self-relation for replies
  parentId String?
  parent   Comment?  @relation("CommentReplies", fields: [parentId], references: [id])
  replies  Comment[] @relation("CommentReplies")

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@index([postId])
  @@index([authorId])
}

// Tag model (many-to-many)
model Tag {
  id    String @id @default(cuid())
  name  String @unique
  slug  String @unique
  posts Post[]

  @@index([slug])
}

Prisma Client Setup#

// lib/prisma.ts
import { PrismaClient } from '@prisma/client';

const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined;
};

export const prisma =
  globalForPrisma.prisma ??
  new PrismaClient({
    log: process.env.NODE_ENV === 'development'
      ? ['query', 'error', 'warn']
      : ['error'],
  });

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

Generate and Push#

# Generate Prisma Client
npx prisma generate

# Push schema to database (development)
npx prisma db push

# Create migration (production)
npx prisma migrate dev --name init

# Apply migrations in production
npx prisma migrate deploy

Common Queries#

Create#

// Create user
const user = await prisma.user.create({
  data: {
    clerkId: 'clerk_123',
    email: 'user@example.com',
    name: 'John Doe',
  },
});

// Create with relations
const post = await prisma.post.create({
  data: {
    title: 'My Post',
    slug: 'my-post',
    content: 'Content here...',
    author: {
      connect: { id: userId },
    },
    tags: {
      connectOrCreate: [
        {
          where: { slug: 'javascript' },
          create: { name: 'JavaScript', slug: 'javascript' },
        },
      ],
    },
  },
  include: {
    author: true,
    tags: true,
  },
});

Read#

// Find unique
const user = await prisma.user.findUnique({
  where: { email: 'user@example.com' },
});

// Find with relations
const post = await prisma.post.findUnique({
  where: { slug: 'my-post' },
  include: {
    author: {
      select: { id: true, name: true, imageUrl: true },
    },
    comments: {
      include: { author: true },
      orderBy: { createdAt: 'desc' },
      take: 10,
    },
    tags: true,
  },
});

// Find many with filters
const posts = await prisma.post.findMany({
  where: {
    published: true,
    author: { role: 'ADMIN' },
    tags: { some: { slug: 'javascript' } },
  },
  orderBy: { createdAt: 'desc' },
  take: 20,
  skip: 0,
});

// Count
const count = await prisma.post.count({
  where: { published: true },
});

Update#

// Update one
const updated = await prisma.post.update({
  where: { id: postId },
  data: {
    title: 'Updated Title',
    published: true,
    publishedAt: new Date(),
  },
});

// Update many
const result = await prisma.post.updateMany({
  where: { authorId: userId },
  data: { published: false },
});

// Upsert
const user = await prisma.user.upsert({
  where: { clerkId: 'clerk_123' },
  update: { name: 'Updated Name' },
  create: {
    clerkId: 'clerk_123',
    email: 'new@example.com',
    name: 'New User',
  },
});

Delete#

// Delete one
await prisma.post.delete({
  where: { id: postId },
});

// Delete many
await prisma.comment.deleteMany({
  where: { postId },
});

// Soft delete (using a field)
await prisma.post.update({
  where: { id: postId },
  data: { deletedAt: new Date() },
});

Transactions#

// Interactive transaction
const result = await prisma.$transaction(async (tx) => {
  const post = await tx.post.create({
    data: { title: 'New Post', slug: 'new-post', authorId: userId },
  });

  await tx.user.update({
    where: { id: userId },
    data: { postCount: { increment: 1 } },
  });

  return post;
});

// Batch transaction
const [posts, users] = await prisma.$transaction([
  prisma.post.findMany({ where: { published: true } }),
  prisma.user.findMany({ where: { role: 'ADMIN' } }),
]);

Middleware#

// lib/prisma.ts
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

// Soft delete middleware
prisma.$use(async (params, next) => {
  if (params.model === 'Post') {
    if (params.action === 'delete') {
      params.action = 'update';
      params.args.data = { deletedAt: new Date() };
    }
    if (params.action === 'deleteMany') {
      params.action = 'updateMany';
      params.args.data = { deletedAt: new Date() };
    }
  }
  return next(params);
});

// Query logging middleware
prisma.$use(async (params, next) => {
  const before = Date.now();
  const result = await next(params);
  const after = Date.now();

  console.log(`Query ${params.model}.${params.action} took ${after - before}ms`);

  return result;
});

export { prisma };

Scripts#

// package.json
{
  "scripts": {
    "db:generate": "prisma generate",
    "db:push": "prisma db push",
    "db:migrate": "prisma migrate dev",
    "db:migrate:deploy": "prisma migrate deploy",
    "db:studio": "prisma studio",
    "db:seed": "prisma db seed",
    "db:reset": "prisma migrate reset"
  },
  "prisma": {
    "seed": "tsx prisma/seed.ts"
  }
}

Seed Script#

// prisma/seed.ts
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

async function main() {
  // Create admin user
  const admin = await prisma.user.upsert({
    where: { email: 'admin@example.com' },
    update: {},
    create: {
      clerkId: 'admin_clerk_id',
      email: 'admin@example.com',
      name: 'Admin User',
      role: 'ADMIN',
    },
  });

  // Create tags
  const tags = await Promise.all(
    ['JavaScript', 'TypeScript', 'React', 'Next.js'].map((name) =>
      prisma.tag.upsert({
        where: { slug: name.toLowerCase().replace(/\./g, '') },
        update: {},
        create: {
          name,
          slug: name.toLowerCase().replace(/\./g, ''),
        },
      })
    )
  );

  // Create sample posts
  await prisma.post.createMany({
    data: [
      {
        title: 'Getting Started with Next.js',
        slug: 'getting-started-nextjs',
        content: 'Learn how to build apps with Next.js...',
        published: true,
        publishedAt: new Date(),
        authorId: admin.id,
      },
      {
        title: 'TypeScript Best Practices',
        slug: 'typescript-best-practices',
        content: 'Tips for writing better TypeScript...',
        published: true,
        publishedAt: new Date(),
        authorId: admin.id,
      },
    ],
    skipDuplicates: true,
  });

  console.log('Seed completed');
}

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