1. Installation & Setup

Install Prisma and the client. The examples below are CLI commands you can run in your project root.

npm install prisma @prisma/client
npm install @prisma/client cockroachdb-server

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

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

postgresql://username:password@host:port/database?sslmode=require

2. Example Prisma schema

Below is a compact e-commerce schema that illustrates relations, enums, and indexes.

model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  posts     Post[]
  orders    Order[]
}

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

  @@index([authorId])
}

model Product {
  id          String   @id @default(cuid())
  name        String
  description String?
  price       Decimal
  stock       Int      @default(0)
  createdAt   DateTime @default(now())

  orders      Order[]
}

model Order {
  id        String   @id @default(cuid())
  userId    String
  productId String
  quantity  Int
  total     Decimal
  status    OrderStatus @default(PENDING)
  createdAt DateTime @default(now())

  user      User    @relation(fields: [userId], references: [id])
  product   Product @relation(fields: [productId], references: [id])

  @@index([userId])
  @@index([productId])
}

enum OrderStatus {
  PENDING
  COMPLETED
  CANCELLED
}

3. Basic CRUD & Transaction examples

Here are two practical examples taken from common application flows: creating a user and creating an order inside a transaction that checks stock.

Create a user

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

export async function createUser() {
  const user = await prisma.user.create({
    data: {
      email: 'alice@example.com',
      name: 'Alice',
    },
  })
  return user
}

Create order with transaction (stock check)

This example uses prisma.$transaction and demonstrates safe updates across related rows.

export async function createOrderWithTransaction(userId: string, productId: string, quantity: number) {
  return await prisma.$transaction(async (tx) => {
    const product = await tx.product.findUnique({ where: { id: productId } })

    if (!product || product.stock < quantity) {
      throw new Error('Insufficient stock')
    }

    const order = await tx.order.create({
      data: {
        userId,
        productId,
        quantity,
        total: product.price.times(quantity)
      }
    })

    await tx.product.update({
      where: { id: productId },
      data: { stock: product.stock - quantity }
    })

    return order
  })
}

Note: Decimal math uses the Decimal type from Prisma's generated client. Depending on your runtime you may need to import or handle arithmetic differently.

4. Advanced queries & analytics

Use Prisma's grouping, aggregations, pagination, and raw SQL when necessary.

export async function getSalesAnalytics() {
  const analytics = await prisma.order.groupBy({
    by: ['productId'],
    where: {
      status: 'COMPLETED',
      createdAt: {
        gte: new Date(Date.now() - 30 * 24 * 60 * 60 * 1000)
      }
    },
    _sum: { quantity: true, total: true },
    _count: { id: true },
    orderBy: { _sum: { total: 'desc' } }
  })
  return analytics
}

export async function getUsersWithPagination(cursor?: string, take: number = 10) {
  const users = await prisma.user.findMany({
    take,
    skip: cursor ? 1 : 0,
    cursor: cursor ? { id: cursor } : undefined,
    orderBy: { createdAt: 'desc' }
  })
  return users
}

export async function complexAnalytics() {
  const result = await prisma.$queryRaw`
    SELECT
                                    u.id,
                                    u.email,
                                    COUNT(o.id) as order_count,
                                    SUM(o.total) as total_spent
                                    FROM "User" u
                                    LEFT JOIN "Order" o ON u.id = o.user_id
                                    WHERE o.status = 'COMPLETED'
                                    GROUP BY u.id, u.email
    HAVING SUM(o.total) > 100
                                    ORDER BY total_spent DESC
                                    `
                                    return result
}

5. Connection pooling & graceful shutdown

CockroachDB benefits from connection pooling. Keep Prisma client usage as a singleton to avoid connection churn in serverless environments.

const prisma = new PrismaClient({
  datasources: { db: { url: process.env.DATABASE_URL } },
  log: ['query','info','warn']
})

process.on('beforeExit', async () => {
  await prisma.$disconnect()
})

6. Migration & deployment

Common Prisma CLI commands for development and production.

# Generate and apply migrations
npx prisma migrate dev --name init

# Generate the Prisma Client
npx prisma generate

# Deploy migrations to prod
npx prisma migrate deploy

# Open Studio
npx prisma studio

7. Best practices

Singleton Prisma client

import { PrismaClient } from '@prisma/client'

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

export const prisma = globalForPrisma.prisma || new PrismaClient()

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

Error handling

export async function safeDatabaseOperation<T>(operation: Promise<T>): Promise<T | null> {
  try {
    return await operation
  } catch (error: any) {
    console.error('Database operation failed:', error)

    if (error.code === 'P2002') {
      throw new Error('Record already exists')
    }

    if (error.code === 'P2025') {
      throw new Error('Record not found')
    }

    throw error
  }
}

8. CockroachDB-specific considerations

• Primary keys: Always define explicit primary keys for predictable performance.
• Indexes: Add indexes on frequently queried columns.
• Transactions: CockroachDB supports transactional semantics — use transactions for consistency-sensitive workflows.
• Geospatial: CockroachDB provides geospatial support when you need location-aware features.
• JSONB: Use JSONB for flexible, semi-structured storage.

Taken together, Prisma and CockroachDB give you a type-safe, scalable foundation for production databases. Use the patterns above to keep your app predictable, safe, and performant.