Prisma Database Operations

Database Package Location

The Prisma schema and client are in packages/db/.

Schema Location

packages/db/
├── prisma/
│   ├── schema.prisma    # Database schema
│   ├── migrations/      # Migration history
│   └── seed.ts          # Seed script
└── src/
    └── lib/
        ├── prisma.service.ts           # NestJS Prisma service
        └── [model]/                    # Repository services
            └── [model]-repository.service.ts

Common Commands

# Generate Prisma client after schema changes
pnpm prisma:generate

# Create and apply migration (development)
pnpm prisma:migrate:dev

# Apply migrations (production)
pnpm prisma:migrate

# Seed the database
pnpm prisma:seed

# Open Prisma Studio
pnpm --filter @projectx/db exec prisma studio

Schema Patterns

Basic Model

model Product {
  id          String   @id @default(cuid())
  name        String
  description String?
  price       Decimal  @db.Decimal(10, 2)
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  // Relations
  categoryId  String
  category    Category @relation(fields: [categoryId], references: [id])
  orderItems  OrderItem[]

  @@index([categoryId])
  @@map("products")
}

PostGIS Geometry Support

model Location {
  id        String   @id @default(cuid())
  name      String
  // PostGIS geometry stored as Unsupported type
  point     Unsupported("geometry(Point, 4326)")

  @@map("locations")
}

Enums

enum OrderStatus {
  PENDING
  PROCESSING
  SHIPPED
  DELIVERED
  CANCELLED
}

Repository Pattern

Creating a Repository Service

// packages/db/src/lib/product/product-repository.service.ts
import { Injectable } from '@nestjs/common';
import { PrismaService } from '../prisma.service';
import { Prisma, Product } from '@prisma/client';

@Injectable()
export class ProductRepositoryService {
  constructor(private readonly prisma: PrismaService) {}

  async findAll(params?: {
    skip?: number;
    take?: number;
    cursor?: Prisma.ProductWhereUniqueInput;
    where?: Prisma.ProductWhereInput;
    orderBy?: Prisma.ProductOrderByWithRelationInput;
  }): Promise<Product[]> {
    return this.prisma.product.findMany(params);
  }

  async findById(id: string): Promise<Product | null> {
    return this.prisma.product.findUnique({
      where: { id },
      include: { category: true },
    });
  }

  async create(data: Prisma.ProductCreateInput): Promise<Product> {
    return this.prisma.product.create({ data });
  }

  async update(id: string, data: Prisma.ProductUpdateInput): Promise<Product> {
    return this.prisma.product.update({
      where: { id },
      data,
    });
  }

  async delete(id: string): Promise<Product> {
    return this.prisma.product.delete({ where: { id } });
  }
}

Query Patterns

Filtering and Pagination

const products = await prisma.product.findMany({
  where: {
    name: { contains: 'shirt', mode: 'insensitive' },
    price: { gte: 10, lte: 100 },
    category: { name: 'Clothing' },
  },
  skip: 0,
  take: 20,
  orderBy: { createdAt: 'desc' },
  include: { category: true },
});

Transactions

const [order, inventory] = await prisma.$transaction([
  prisma.order.create({ data: orderData }),
  prisma.inventory.update({
    where: { productId },
    data: { quantity: { decrement: quantity } },
  }),
]);

Raw SQL (for PostGIS)

const nearbyLocations = await prisma.$queryRaw`
  SELECT id, name, ST_AsGeoJSON(point) as geojson
  FROM locations
  WHERE ST_DWithin(
    point,
    ST_SetSRID(ST_MakePoint(${lng}, ${lat}), 4326)::geography,
    ${radiusMeters}
  )
`;

Migration Workflow

1. Modify schema.prisma with your changes
2. Generate migration: pnpm prisma:migrate:dev --name descriptive_name
3. Review migration in prisma/migrations/
4. Test locally before committing
5. Apply in production: pnpm prisma:migrate

Seeding

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

const prisma = new PrismaClient();

async function main() {
  // Upsert to avoid duplicates
  await prisma.category.upsert({
    where: { slug: 'electronics' },
    update: {},
    create: {
      name: 'Electronics',
      slug: 'electronics',
    },
  });
}

main()
  .catch(console.error)
  .finally(() => prisma.$disconnect());

Best Practices

1. Use repository services instead of direct Prisma calls in controllers
2. Always include @@map for explicit table names
3. Add indexes for frequently queried fields
4. Use transactions for related operations
5. Name migrations descriptively (e.g., addproductinventory)
6. Validate data at the application layer before database operations