aussiegingersnap/db-prisma

Prisma Database Skill

Production patterns for Prisma 7 ORM with PostgreSQL, including schema design, versioned migrations, seeding, and Railway-specific configuration.

When to Use This Skill

• Setting up Prisma in a new project
• Designing database schemas (models, relations, enums)
• Writing and running migrations
• Seeding development databases
• Deploying Prisma-backed services to Railway
• Troubleshooting Prisma/PostgreSQL issues

Project Setup

Installation

npm install prisma @prisma/client
npx prisma init --datasource-provider postgresql

Directory Structure

prisma/
  schema.prisma       # Schema definition
  migrations/         # Versioned SQL migrations
  seed.ts             # Database seeder

Environment

# .env (development)
DATABASE_URL="postgresql://postgres:postgres@localhost:5432/myapp?schema=public"

# Railway (production) — auto-wired from Postgres service
DATABASE_URL="postgresql://user:pass@host:port/railway?schema=public&connection_limit=5"

Schema Patterns

Base Schema

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

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

Model Conventions

Singular model names, PascalCase. Fields use camelCase. Database columns use snake_case via @map.

model User {
  id        String   @id @default(uuid())
  email     String   @unique
  name      String
  role      Role     @default(MEMBER)
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  sessions Session[]
  posts    Post[]

  @@map("user")
}

Timestamps

Every model gets createdAt and updatedAt:

createdAt DateTime @default(now()) @map("created_at")
updatedAt DateTime @updatedAt @map("updated_at")

Enums

enum Role {
  ADMIN
  MEMBER
  GUEST
}

enum Status {
  DRAFT
  PUBLISHED
  ARCHIVED
}

Relations

One-to-Many:

model User {
  id       String    @id @default(uuid())
  posts    Post[]

  @@map("user")
}

model Post {
  id       String   @id @default(uuid())
  title    String
  userId   String   @map("user_id")
  user     User     @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@index([userId])
  @@map("post")
}

Many-to-Many (explicit join table):

model Post {
  id    String     @id @default(uuid())
  tags  PostTag[]

  @@map("post")
}

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

  @@map("tag")
}

model PostTag {
  postId String @map("post_id")
  tagId  String @map("tag_id")
  post   Post   @relation(fields: [postId], references: [id], onDelete: Cascade)
  tag    Tag    @relation(fields: [tagId], references: [id], onDelete: Cascade)

  @@id([postId, tagId])
  @@map("post_tag")
}

Indexes

model User {
  id    String @id @default(uuid())
  email String @unique
  name  String

  @@index([name])
  @@map("user")
}

PostgreSQL-Specific Types

model Product {
  id       String   @id @default(uuid())
  metadata Json?
  tags     String[]
  price    Decimal  @db.Decimal(10, 2)

  @@map("product")
}

Migration Workflow

Development

# Create migration from schema changes
npx prisma migrate dev --name add_user_table

# Apply without creating (schema push for prototyping)
npx prisma db push

# Reset database (drops all data)
npx prisma migrate reset

# Check migration status
npx prisma migrate status

Production (Railway)

# Deploy pending migrations (non-interactive, no data loss prompts)
npx prisma migrate deploy

Key Rules

1. Never edit existing migrations — they may have run in production
2. Always review generated SQL before committing
3. Use migrate dev locally, migrate deploy in CI/production
4. Commit the migrations/ directory — it's your migration history
5. Use db push only for prototyping — no migration history

Client Usage

Generate Client

npx prisma generate

Run after every schema change. The build command should include this.

Basic CRUD

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

const prisma = new PrismaClient();

// Create
const user = await prisma.user.create({
  data: { email: '[email protected]', name: 'John' },
});

// Read one
const found = await prisma.user.findUnique({
  where: { email: '[email protected]' },
});

// Read many with filter
const users = await prisma.user.findMany({
  where: { role: 'MEMBER' },
  orderBy: { createdAt: 'desc' },
  take: 10,
});

// Update
await prisma.user.update({
  where: { id: user.id },
  data: { name: 'Jane' },
});

// Delete
await prisma.user.delete({ where: { id: user.id } });

Relations

// Include relations
const userWithPosts = await prisma.user.findUnique({
  where: { id: userId },
  include: { posts: true, sessions: true },
});

// Nested create
const user = await prisma.user.create({
  data: {
    email: '[email protected]',
    name: 'John',
    posts: {
      create: [
        { title: 'First Post' },
        { title: 'Second Post' },
      ],
    },
  },
  include: { posts: true },
});

// Select specific fields
const emails = await prisma.user.findMany({
  select: { id: true, email: true },
});

Transactions

const [user, post] = await prisma.$transaction([
  prisma.user.create({ data: { email: '[email protected]', name: 'A' } }),
  prisma.post.create({ data: { title: 'Hello', userId: 'known-id' } }),
]);

// Interactive transaction
await prisma.$transaction(async (tx) => {
  const user = await tx.user.create({
    data: { email: '[email protected]', name: 'A' },
  });
  await tx.post.create({
    data: { title: 'Hello', userId: user.id },
  });
});

Seeding

seed.ts

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

const prisma = new PrismaClient();

async function main() {
  await prisma.user.upsert({
    where: { email: '[email protected]' },
    update: {},
    create: {
      email: '[email protected]',
      name: 'Admin',
      role: 'ADMIN',
    },
  });

  console.log('Seed complete');
}

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

package.json config

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

Run seed

npx prisma db seed

Seed runs automatically after prisma migrate reset.

Railway Deployment

Connection String

Railway Postgres provides DATABASE_URL automatically when the backend service references the Postgres service. Append connection pooling for production:

DATABASE_URL="postgresql://user:pass@host:port/railway?schema=public&connection_limit=5"

Build Command

npx prisma generate && npx prisma migrate deploy && npm run build

This sequence:

1. Generates the Prisma client
2. Applies any pending migrations
3. Builds the application

railway.toml

[build]
builder = "nixpacks"
buildCommand = "npx prisma generate && npx prisma migrate deploy && npm run build"

[deploy]
startCommand = "node dist/main.js"
healthcheckPath = "/health"
healthcheckTimeout = 300
restartPolicyType = "ON_FAILURE"
restartPolicyMaxRetries = 10

Prisma Studio

npx prisma studio

Opens a browser-based GUI for viewing and editing data at http://localhost:5555.

Package Scripts

{
  "scripts": {
    "db:generate": "prisma generate",
    "db:migrate": "prisma migrate dev",
    "db:migrate:deploy": "prisma migrate deploy",
    "db:push": "prisma db push",
    "db:reset": "prisma migrate reset",
    "db:seed": "prisma db seed",
    "db:studio": "prisma studio"
  }
}

Troubleshooting

"Can't reach database server"

Check DATABASE_URL is set and Postgres is running:

echo $DATABASE_URL
docker compose ps  # if using Docker locally

"Migration failed"

Check migration status and logs:

npx prisma migrate status
npx prisma migrate resolve --rolled-back <migration-name>

"Prisma Client not generated"

Run generate after schema changes:

npx prisma generate

Connection pool exhaustion on Railway

Append &connection_limit=5 to DATABASE_URL. Railway instances have limited connections.

Type errors after schema change

Regenerate client and restart TypeScript server:

npx prisma generate
# Restart TS server in IDE: Cmd+Shift+P → "TypeScript: Restart TS Server"