achreftlili/prisma-schema-starter

Prisma Schema Starter

Scaffold a Prisma 6.x project with schema.prisma (datasource, generator, models, relations, enums), migrations, seed script, Prisma Client usage, type-safe queries, and middleware.

Prerequisites

• Node.js >= 20.x
• TypeScript >= 5.3
• PostgreSQL, MySQL, or SQLite database
• npm or pnpm

Scaffold Command

npm install @prisma/client
npm install -D prisma typescript @types/node tsx
npx prisma init --datasource-provider postgresql

# Creates:
#   prisma/schema.prisma
#   .env (with DATABASE_URL placeholder)

Database Initialization

# Create the database and apply schema
npx prisma migrate dev --name init

# Seed initial data (if seed script exists)
npx prisma db seed

# Reset database (development only — drops and recreates)
npx prisma migrate reset

Project Structure

prisma/
  schema.prisma                   # Data model, datasource, generators
  migrations/                     # Auto-generated migration SQL files
  seed.ts                         # Database seed script
src/
  lib/
    prisma.ts                     # Singleton Prisma Client instance
  modules/
    users/
      users.repository.ts         # Data access using Prisma Client

Key Conventions

• One schema.prisma file at prisma/schema.prisma (default path).
• Use @id with @default(uuid()) or @default(cuid()) for primary keys. Avoid auto-increment integers for distributed systems.
• Define explicit relation names when a model has multiple relations to the same target.
• Use enums for fixed-value fields (status, role, etc.).
• Run npx prisma generate after every schema change to regenerate the client.
• Run npx prisma migrate dev during development; npx prisma migrate deploy in CI/production.
• Use a singleton pattern for Prisma Client to avoid connection pool exhaustion in serverless/hot-reload environments.
• Add seed script to package.json for prisma db seed.

Essential Patterns

Schema (prisma/schema.prisma)

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

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

enum Role {
  USER
  ADMIN
}

enum PostStatus {
  DRAFT
  PUBLISHED
  ARCHIVED
}

model User {
  id        String   @id @default(uuid())
  email     String   @unique
  name      String
  password  String
  role      Role     @default(USER)
  posts     Post[]
  profile   Profile?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@map("users")
}

model Profile {
  id     String  @id @default(uuid())
  bio    String?
  avatar String?
  user   User    @relation(fields: [userId], references: [id], onDelete: Cascade)
  userId String  @unique

  @@map("profiles")
}

model Post {
  id        String     @id @default(uuid())
  title     String
  content   String?
  status    PostStatus @default(DRAFT)
  author    User       @relation(fields: [authorId], references: [id], onDelete: Cascade)
  authorId  String
  tags      Tag[]
  createdAt DateTime   @default(now())
  updatedAt DateTime   @updatedAt

  @@index([authorId])
  @@index([status])
  @@map("posts")
}

model Tag {
  id    String @id @default(uuid())
  name  String @unique
  posts Post[]

  @@map("tags")
}

Prisma Client Singleton (src/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", "warn", "error"]
        : ["error"],
  });

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

Prisma Client with Extensions (Middleware Replacement)

import { PrismaClient } from "@prisma/client";

// Soft-delete extension
const prisma = new PrismaClient().$extends({
  query: {
    $allModels: {
      async findMany({ args, query }) {
        args.where = { ...args.where, deletedAt: null };
        return query(args);
      },
      async delete({ args, query }) {
        return (query as any)({
          ...args,
          data: { deletedAt: new Date() },
        });
      },
    },
  },
});

export { prisma };

Repository Pattern (src/modules/users/users.repository.ts)

import { prisma } from "@/lib/prisma";
import type { Prisma } from "@prisma/client";

export async function createUser(data: Prisma.UserCreateInput) {
  return prisma.user.create({
    data,
    select: {
      id: true,
      email: true,
      name: true,
      role: true,
      createdAt: true,
    },
  });
}

export async function findUserByEmail(email: string) {
  return prisma.user.findUnique({
    where: { email },
    include: { profile: true },
  });
}

export async function findUserWithPosts(userId: string) {
  return prisma.user.findUnique({
    where: { id: userId },
    include: {
      posts: {
        where: { status: "PUBLISHED" },
        orderBy: { createdAt: "desc" },
        take: 10,
      },
    },
  });
}

export async function listUsers(params: {
  page: number;
  limit: number;
  role?: "USER" | "ADMIN";
}) {
  const { page, limit, role } = params;
  const where: Prisma.UserWhereInput = role ? { role } : {};

  const [users, total] = await prisma.$transaction([
    prisma.user.findMany({
      where,
      skip: (page - 1) * limit,
      take: limit,
      orderBy: { createdAt: "desc" },
      select: {
        id: true,
        email: true,
        name: true,
        role: true,
        createdAt: true,
        _count: { select: { posts: true } },
      },
    }),
    prisma.user.count({ where }),
  ]);

  return { users, total, pages: Math.ceil(total / limit) };
}

NextAuth Adapter Models

// Required models for @auth/prisma-adapter (NextAuth v5)
model Account {
  id                String  @id @default(cuid())
  userId            String
  type              String
  provider          String
  providerAccountId String
  refresh_token     String?
  access_token      String?
  expires_at        Int?
  token_type        String?
  scope             String?
  id_token          String?
  session_state     String?
  user              User    @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@unique([provider, providerAccountId])
}

model Session {
  id           String   @id @default(cuid())
  sessionToken String   @unique
  userId       String
  expires      DateTime
  user         User     @relation(fields: [userId], references: [id], onDelete: Cascade)
}

model VerificationToken {
  identifier String
  token      String   @unique
  expires    DateTime

  @@unique([identifier, token])
}

Add these relations to the User model: accounts Account[] and sessions Session[].

Seed Script (prisma/seed.ts)

import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

async function main() {
  // Upsert to make seed idempotent
  const admin = await prisma.user.upsert({
    where: { email: "[email protected]" },
    update: {},
    create: {
      email: "[email protected]",
      name: "Admin User",
      password: "$2b$10$hashedpassword", // Use bcrypt in real seeds
      role: "ADMIN",
      profile: {
        create: {
          bio: "System administrator",
        },
      },
    },
  });

  const tags = await Promise.all(
    ["TypeScript", "Prisma", "Node.js"].map((name) =>
      prisma.tag.upsert({
        where: { name },
        update: {},
        create: { name },
      })
    )
  );

  await prisma.post.upsert({
    where: { id: "seed-post-1" },
    update: {},
    create: {
      id: "seed-post-1",
      title: "Getting Started with Prisma",
      content: "Prisma is a next-generation ORM for Node.js and TypeScript.",
      status: "PUBLISHED",
      authorId: admin.id,
      tags: { connect: tags.map((t) => ({ id: t.id })) },
    },
  });

  console.log("Seed completed");
}

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

package.json Seed Config

{
  "prisma": {
    "seed": "tsx prisma/seed.ts"
  }
}

Common Commands

# Generate Prisma Client after schema changes
npx prisma generate

# Create and apply a migration (development)
npx prisma migrate dev --name <migration-name>

# Apply pending migrations (CI/production)
npx prisma migrate deploy

# Reset database (drop, recreate, apply migrations, seed)
npx prisma migrate reset

# Seed database
npx prisma db seed

# Open Prisma Studio (visual database editor)
npx prisma studio

# Format schema file
npx prisma format

# Push schema without migrations (prototyping only)
npx prisma db push

# Pull schema from existing database
npx prisma db pull

# Check migration status
npx prisma migrate status

Integration Notes

• NestJS: Use @prisma/client directly in services or wrap in a NestJS module. Pair with nestjs-project-starter skill.
• NextAuth / Auth.js: Use @auth/prisma-adapter for session/account storage. Pair with nextauth-skill.
• Testing: Mock Prisma Client with jest-mock-extended or use a test database with prisma migrate reset before each test suite.
• Docker: Add a db service in docker-compose. Run npx prisma migrate deploy in the app entrypoint or init container.
• TypeORM migration: Prisma and TypeORM should not be mixed in the same project. Choose one ORM per project.