Prisma Mastery: Automating Database Model Generation from JSON

JSON to Prisma Schema: Relational Modeling, Migrations, and Multi-Database TypeScript

Prisma takes a different approach than ORM libraries that use TypeScript for schema definition: it uses a dedicated schema language (PSL — Prisma Schema Language) that is database-agnostic, human-readable, and the single source of truth for both migrations and the generated TypeScript client. Converting your JSON to a Prisma schema is the first step toward a type-safe data layer where every query result is typed by default, relation traversal is safe, and your schema migrations are version-controlled and reviewable as SQL.

Live Example: Blog Post with Nested Author Object

// Input JSON
{
  "postId": 1,
  "title": "Learning Prisma",
  "content": "Automated modeling is great!",
  "isPublished": true,
  "author": {
    "id": "usr_001",
    "name": "Alice Chen",
    "email": "alice@example.com"
  },
  "tags": ["prisma", "typescript", "orm"],
  "publishedAt": "2024-01-15T08:30:00Z"
}

// Generated Prisma Schema (schema.prisma)
generator client {
  provider = "prisma-client-js"
}

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

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

  @@index([authorId])
  @@index([isPublished, publishedAt(sort: Desc)])
}

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

The @updatedAt directive tells Prisma to automatically update the field on every write — no application code needed. The @@index at the model level creates compound indexes; Prisma generates the SQL CREATE INDEX statement when you run migrations.

The PSL Type System: Mapping JSON to Prisma

model Product {
  // Integers
  id        Int    @id @default(autoincrement())
  stock     Int    @default(0)

  // Strings
  sku       String @unique
  name      String
  note      String?  // nullable String

  // Floats and Decimals
  weight    Float                     // 64-bit float (JSON number)
  price     Decimal @db.Decimal(10,2) // Exact decimal — use for money

  // Booleans
  isActive  Boolean @default(true)

  // Dates
  createdAt DateTime  @default(now())          // timestamp with timezone
  expiresAt DateTime?                           // nullable date
  archivedAt DateTime? @db.Date                // date-only (no time)

  // JSON column for flexible metadata
  metadata  Json?

  // Enum — must be defined separately
  status    ProductStatus @default(DRAFT)

  // Array (PostgreSQL only — use a relation table for MySQL)
  tags      String[]
}

enum ProductStatus {
  DRAFT
  ACTIVE
  ARCHIVED
}

Use Decimal with @db.Decimal(M,D) for monetary values — Float uses IEEE 754 binary representation and silently misrepresents values like 0.10.

Relations: One-to-Many, Many-to-Many, Self-Referential

// One-to-many: User has many Posts
model User {
  id    String @id @default(cuid())
  email String @unique
  posts Post[]
}

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

// Explicit many-to-many (recommended for additional fields on the join)
model Post {
  id   Int        @id @default(autoincrement())
  tags PostTag[]
}

model Tag {
  id    Int      @id @default(autoincrement())
  name  String   @unique
  posts PostTag[]
}

model PostTag {
  post      Post     @relation(fields: [postId], references: [id])
  postId    Int
  tag       Tag      @relation(fields: [tagId], references: [id])
  tagId     Int
  createdAt DateTime @default(now())

  @@id([postId, tagId])  // composite primary key
}

// Self-referential: employee → manager
model Employee {
  id         Int        @id @default(autoincrement())
  name       String
  managerId  Int?
  manager    Employee?  @relation("EmployeeManager", fields: [managerId], references: [id])
  reports    Employee[] @relation("EmployeeManager")
}

The Prisma Client: Type-Safe Queries

After npx prisma generate, every query is typed based on your schema:

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

const prisma = new PrismaClient();

// Create with nested write — single transaction
const post = await prisma.post.create({
  data: {
    title: 'New Post',
    content: 'Body here...',
    author: {
      connectOrCreate: {
        where:  { email: 'alice@example.com' },
        create: { name: 'Alice', email: 'alice@example.com' },
      }
    },
    tags: { set: ['typescript', 'prisma'] },
  },
  include: { author: true },  // include related User
});
// post.author.name — typed as string

// Filtered query with pagination
const posts = await prisma.post.findMany({
  where: {
    isPublished: true,
    publishedAt: { gte: new Date('2024-01-01') },
    author:      { email: { contains: '@company.com' } },
  },
  select: {
    id:          true,
    title:       true,
    publishedAt: true,
    author:      { select: { name: true, email: true } },
  },
  orderBy: { publishedAt: 'desc' },
  take:    20,
  skip:    40,  // cursor-based pagination preferred for large tables
});

// Atomic update
await prisma.post.update({
  where: { id: postId },
  data:  { isPublished: true, publishedAt: new Date() },
});

Migrations: prisma migrate vs. db push

# Development workflow — generates and applies SQL migration files
npx prisma migrate dev --name "add-product-status-enum"
# Creates: prisma/migrations/20240115_add_product_status_enum/migration.sql
# Applies it to dev DB immediately

# Production deployment — apply pending migrations only
npx prisma migrate deploy
# Reads prisma/migrations/ directory, applies unapplied migrations in order
# Safe: never regenerates or modifies existing migration files

# Prototype mode (no migration files — for rapid iteration)
npx prisma db push
# Syncs schema directly to DB, does NOT create migration files
# Use for development only — not for production deployments

# View migration history
npx prisma migrate status

Migration files are SQL checked into your repository. Every schema change creates a new, auditable migration file. The migrate deploy command applies them in order, making deployments reproducible across environments.

Multi-Database Support

// Switch databases by changing provider:
datasource db {
  provider = "sqlite"   // for local dev
  url      = "file:./dev.db"
}

datasource db {
  provider = "mysql"   // for MySQL/PlanetScale
  url      = env("DATABASE_URL")
}

datasource db {
  provider = "mongodb"  // for MongoDB (schema-like enforcement)
  url      = env("MONGODB_URL")
}

// Supabase PostgreSQL
datasource db {
  provider          = "postgresql"
  url               = env("DATABASE_URL")
  directUrl         = env("DIRECT_URL")  // for migrations (bypasses PgBouncer)
}

Best Practices for Production

• Use cuid() or uuid() for string IDs: @default(cuid()) generates collision-resistant IDs without a database sequence. Use @default(uuid()) for RFC 4122 standard UUIDs. Both are preferable to auto-increment for distributed systems.
• Always use Decimal for money: price Decimal @db.Decimal(10,2) — never Float. Prisma maps Decimal to JavaScript's Decimal.js (not a native number) to preserve precision.
• Add explicit @@index for foreign keys: Prisma does not automatically create indexes on foreign key columns (unlike some ORM generators). Add @@index([foreignKeyField]) explicitly for any relation field you query on.
• Use prisma migrate deploy in CI/CD: Never run migrate dev in production — it can reset the database if the shadow database gets out of sync. deploy only applies pending migrations, never generating new ones.

FAQ

Q: Prisma vs. Drizzle — which should I choose?
A: Prisma is the better choice if you want a managed migration workflow with a GUI (Prisma Studio), multi-database support from one schema, and a generated client that handles relation loading. Drizzle is the better choice for edge runtimes (Cloudflare Workers, Vercel Edge), serverless environments with cold-start constraints, or when you want schema-as-TypeScript without a separate DSL.

Q: Does Prisma work with existing databases?
A: Use npx prisma db pull (introspection) to generate a Prisma schema from an existing database. This is more reliable than converting from JSON for databases that already have tables with constraints and indexes.

Q: How do I handle soft deletes in Prisma?
A: Prisma doesn't have a built-in soft delete feature. Use Prisma's middleware (client extensions in Prisma 5+) to intercept findMany and findFirst calls and append { where: { deletedAt: null } } automatically: prisma.$extends({ query: { ... } }).

Q: How do I run transactions?
A: Use prisma.$transaction([]) for sequential queries in a transaction: await prisma.$transaction([prisma.user.create(...), prisma.post.create(...)]). For interactive transactions (read-then-write patterns), use the callback form: await prisma.$transaction(async (tx) => { ... }).