Cermont.backend.prisma V7

Project Fit

[!NOTE] Cermont currently uses Prisma v6. This skill documents v7 patterns for future migration.

Guardrails

Safety Checklist:

pnpm --filter @cermont/backend prisma validate
pnpm --filter @cermont/backend prisma generate
pnpm --filter @cermont/backend test
# Rollback: git restore -SW .

Core v7 Changes

1. ES Modules (ESM) Required

Prisma v7 ships as an ES module. Your project must use ESM:

package.json:

{
  "type": "module"
}

tsconfig.json:

{
  "compilerOptions": {
    "module": "ESNext",
    "moduleResolution": "Node",
    "target": "ES2023",
    "strict": true,
    "esModuleInterop": true
  }
}

2. Driver Adapters Required

All databases now require driver adapters. The Rust-free client provides better performance and smaller bundle sizes.

Available Adapters:

• PostgreSQL: @prisma/adapter-pg (with pg driver)
• MySQL/MariaDB: @prisma/adapter-mariadb (with mariadb driver)
• SQLite: @prisma/adapter-better-sqlite3 (with better-sqlite3)
• CockroachDB: @prisma/adapter-pg (with pg driver)
• Neon: @prisma/adapter-neon (with @neondatabase/serverless)
• PlanetScale: @prisma/adapter-planetscale (with @planetscale/database)
• D1 (Cloudflare): @prisma/adapter-d1
• MSSQL: @prisma/adapter-mssql

3. Generator Configuration

The output field is now required and the new prisma-client provider is standard:

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

Note: Client is no longer generated in node_modules by default.

4. Prisma Config File (prisma.config.ts)

Database URLs and CLI configuration now live in prisma.config.ts instead of the schema file.

Basic setup:

import 'dotenv/config'
import { defineConfig, env } from 'prisma/config'

export default defineConfig({
  schema: 'prisma/schema.prisma',
  migrations: {
    path: 'prisma/migrations',
  },
  datasource: {
    url: env('DATABASE_URL'),
  },
})

Note: For Bun, skip import 'dotenv/config' as it auto-loads .env files.

5. Schema Datasource Block

Remove url, directUrl, and shadowDatabaseUrl from schema.prisma:

v7 schema.prisma:

datasource db {
  provider = "postgresql"
  // url field removed - now in prisma.config.ts
}

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

Installation & Setup

New Project

# Install dependencies
npm install prisma@latest @prisma/client@latest

# Choose appropriate adapter
npm install @prisma/adapter-pg pg          # PostgreSQL
npm install @prisma/adapter-mariadb mariadb # MySQL/MariaDB
npm install @prisma/adapter-better-sqlite3 better-sqlite3 # SQLite

# Install dev tools
npm install -D tsx dotenv

# Initialize Prisma (creates prisma.config.ts automatically)
npx prisma init

Upgrading from v6

# Update packages
npm install prisma@latest @prisma/client@latest

# Install adapter for your database
npm install @prisma/adapter-pg pg  # for PostgreSQL

# Install dotenv if not using Bun
npm install dotenv

# Regenerate client
npx prisma generate

Client Instantiation

PostgreSQL Example

import { PrismaClient } from './generated/prisma/client'
import { PrismaPg } from '@prisma/adapter-pg'
import 'dotenv/config'

const adapter = new PrismaPg({
  connectionString: process.env.DATABASE_URL
})

const prisma = new PrismaClient({ adapter })

export default prisma

MySQL/MariaDB Example

import { PrismaClient } from './generated/prisma/client'
import { PrismaMariaDb } from '@prisma/adapter-mariadb'
import 'dotenv/config'

const adapter = new PrismaMariaDb({
  host: 'localhost',
  port: 3306,
  connectionLimit: 5
})

const prisma = new PrismaClient({ adapter })

export default prisma

SQLite Example

import { PrismaClient } from './generated/prisma/client'
import { PrismaBetterSqlite3 } from '@prisma/adapter-better-sqlite3'
import 'dotenv/config'

const adapter = new PrismaBetterSqlite3({
  url: process.env.DATABASE_URL || 'file:./dev.db'
})

const prisma = new PrismaClient({ adapter })

export default prisma

Prisma Accelerate (Caching/Pooling)

If using Prisma Accelerate for caching, do NOT use a driver adapter:

import { PrismaClient } from './generated/prisma/client'
import { withAccelerate } from '@prisma/extension-accelerate'

const prisma = new PrismaClient({
  accelerateUrl: process.env.DATABASE_URL // prisma:// or prisma+postgres:// URL
}).$extends(withAccelerate())

export default prisma

Important: Never pass prisma:// or prisma+postgres:// URLs to driver adapters.

Schema Best Practices

Complete Schema Example

// schema.prisma
datasource db {
  provider = "postgresql"
}

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

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  posts     Post[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@index([authorId])
}

Relations

One-to-Many:

model User {
  id    Int    @id @default(autoincrement())
  posts Post[]
}

model Post {
  id       Int  @id @default(autoincrement())
  author   User @relation(fields: [authorId], references: [id])
  authorId Int
  
  @@index([authorId])
}

Many-to-Many:

model Post {
  id         Int        @id @default(autoincrement())
  categories Category[]
}

model Category {
  id    Int    @id @default(autoincrement())
  posts Post[]
}

One-to-One:

model User {
  id      Int      @id @default(autoincrement())
  profile Profile?
}

model Profile {
  id     Int  @id @default(autoincrement())
  user   User @relation(fields: [userId], references: [id])
  userId Int  @unique
}

Common Commands

# Generate Prisma Client
npx prisma generate

# Create and apply migration
npx prisma migrate dev --name init

# Apply migrations in production
npx prisma migrate deploy

# Reset database (dev only)
npx prisma migrate reset

# Open Prisma Studio
npx prisma studio

# Format schema
npx prisma format

# Validate schema
npx prisma validate

# Pull schema from database
npx prisma db pull

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

# Seed database
npx prisma db seed

Prisma Client Queries

Basic CRUD

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

// Read
const user = await prisma.user.findUnique({
  where: { id: 1 },
})

const users = await prisma.user.findMany({
  where: { email: { contains: '@example.com' } },
  orderBy: { createdAt: 'desc' },
  take: 10,
})

// Update
const user = await prisma.user.update({
  where: { id: 1 },
  data: { name: 'Jane Doe' },
})

// Delete
const user = await prisma.user.delete({
  where: { id: 1 },
})

Relations

// Create with relations
const user = await prisma.user.create({
  data: {
    email: 'user@example.com',
    posts: {
      create: [
        { title: 'First Post', content: 'Content...' },
        { title: 'Second Post', content: 'More content...' },
      ],
    },
  },
})

// Query with relations
const userWithPosts = await prisma.user.findUnique({
  where: { id: 1 },
  include: {
    posts: true,
  },
})

// Select specific fields
const user = await prisma.user.findUnique({
  where: { id: 1 },
  select: {
    id: true,
    email: true,
    posts: {
      select: {
        id: true,
        title: true,
      },
    },
  },
})

Advanced Queries

// Filtering
const posts = await prisma.post.findMany({
  where: {
    OR: [
      { title: { contains: 'Prisma' } },
      { content: { contains: 'database' } },
    ],
    published: true,
    author: {
      email: { endsWith: '@prisma.io' },
    },
  },
})

// Aggregations
const result = await prisma.post.aggregate({
  _count: true,
  _avg: { authorId: true },
  _sum: { authorId: true },
})

// Group by
const groups = await prisma.post.groupBy({
  by: ['authorId'],
  _count: true,
  having: {
    authorId: { gt: 10 },
  },
})

// Transactions
const [user, post] = await prisma.$transaction([
  prisma.user.create({ data: { email: 'user@example.com' } }),
  prisma.post.create({ data: { title: 'Post', authorId: 1 } }),
])

// Interactive transactions
await prisma.$transaction(async (tx) => {
  const user = await tx.user.create({
    data: { email: 'user@example.com' },
  })
  
  await tx.post.create({
    data: { title: 'Post', authorId: user.id },
  })
})

Migration Workflow

Development

# Create migration and apply
npx prisma migrate dev --name add_user_table

# Apply pending migrations
npx prisma migrate dev

# Reset database
npx prisma migrate reset

Production

# Apply migrations
npx prisma migrate deploy

# Check migration status
npx prisma migrate status

Prototyping

# Push schema changes without creating migrations
npx prisma db push

Environment Variables

.env:

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

# MySQL
DATABASE_URL="mysql://user:password@localhost:3306/mydb"

# SQLite
DATABASE_URL="file:./dev.db"

# Prisma Accelerate
DATABASE_URL="prisma://accelerate.prisma-data.net/?api_key=..."

# Direct URL (for migrations with Accelerate)
DIRECT_DATABASE_URL="postgresql://user:password@localhost:5432/mydb"

Connection Pooling

In v7, connection pooling is handled by the driver adapter, not by Prisma's URL parameters.

PostgreSQL with pg driver:

import { Pool } from 'pg'
import { PrismaPg } from '@prisma/adapter-pg'
import { PrismaClient } from './generated/prisma/client'

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10, // connection pool size
})

const adapter = new PrismaPg(pool)
const prisma = new PrismaClient({ adapter })

Seeding

package.json:

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

prisma/seed.ts:

import { PrismaClient } from '../generated/prisma/client'
import { PrismaPg } from '@prisma/adapter-pg'
import 'dotenv/config'

const adapter = new PrismaPg({
  connectionString: process.env.DATABASE_URL
})

const prisma = new PrismaClient({ adapter })

async function main() {
  const user = await prisma.user.create({
    data: {
      email: 'admin@example.com',
      name: 'Admin User',
    },
  })
  
  console.log('Seeded:', user)
}

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

Run with: npx prisma db seed

Troubleshooting

Module Resolution Errors

Problem: Cannot find module './generated/prisma/client'

Solution:

1. Ensure "type": "module" in package.json
2. Run npx prisma generate
3. Check output path in generator block
4. Restart TypeScript server

Connection Errors (P1017)

Problem: Cannot connect to database

Solution:

1. Verify DATABASE_URL is correct
2. Ensure import 'dotenv/config' is at the top of files
3. Check network connectivity
4. Verify database is running

Migration Fails

Problem: Migration cannot be applied

Solution:

1. Check schema syntax with npx prisma validate
2. Review migration file in prisma/migrations/
3. Use npx prisma migrate reset in development
4. For production, manually fix and use npx prisma migrate resolve

Performance Tips

1. Use select instead of include when you don't need all fields
2. Add indexes for frequently queried fields:

   @@index([email])
   @@index([authorId, createdAt])
   

3. Use connection pooling with appropriate pool sizes
4. Batch operations when possible:

   await prisma.user.createMany({
     data: [{ email: 'a@b.com' }, { email: 'c@d.com' }]
   })
   

5. Use raw queries for complex operations:

   await prisma.$queryRaw`SELECT * FROM users WHERE email LIKE ${'%@example.com'}`
   

Security Best Practices

1. Never commit .env files - add to .gitignore
2. Use environment variables for sensitive data
3. Validate input before passing to Prisma queries
4. Use parameterized queries (Prisma does this automatically)
5. Limit exposed fields with select/omit in production APIs
6. Set appropriate connection limits to prevent exhaustion
7. Use read replicas for scaling read operations

Key Differences from v6

Additional Resources

• Official Docs: https://www.prisma.io/docs
• Migration Guide: https://www.prisma.io/docs/orm/more/upgrade-guides/upgrading-versions/upgrading-to-prisma-7
• Driver Adapters: https://www.prisma.io/docs/orm/overview/databases/database-drivers
• Prisma Schema Reference: https://www.prisma.io/docs/orm/reference/prisma-schema-reference

Common Patterns

Singleton Pattern for Prisma Client

// lib/prisma.ts
import { PrismaClient } from './generated/prisma/client'
import { PrismaPg } from '@prisma/adapter-pg'
import 'dotenv/config'

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

const adapter = new PrismaPg({
  connectionString: process.env.DATABASE_URL
})

export const prisma = globalForPrisma.prisma ?? new PrismaClient({ adapter })

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

Type-safe Enums

enum Role {
  USER
  ADMIN
  MODERATOR
}

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

Usage:

import { Role } from './generated/prisma/client'

const admin = await prisma.user.create({
  data: {
    email: 'admin@example.com',
    role: Role.ADMIN,
  },
})

When to Use Prisma

Good fit:

• Type-safe database access
• Complex relations and queries
• Auto-generated migrations
• TypeScript projects
• Need for type safety and IntelliSense

Consider alternatives if:

• Need MongoDB (wait for v7 support)
• Existing complex SQL procedures
• Extreme performance requirements
• Very simple CRUD without relations

Notes

• Always run npx prisma generate after schema changes
• Use npx prisma studio to visualize your database
• Keep migrations in version control
• Test migrations on staging before production
• Use npx prisma format to keep schema clean
• The Rust-free client in v7 is faster and has a smaller bundle size
• Driver adapters enable serverless/edge deployment