JSON to Prisma Schema Generator

What is JSON to Prisma Schema Generator?

Prisma is a type-safe ORM for Node.js and TypeScript that uses a single declarative file — schema.prisma — as the source of truth for both your database tables and the generated TypeScript client. This tool reads a sample JSON document or an array of records and produces a Prisma model block, inferring each field's scalar type, marking nullable fields with the ? modifier, and adding the annotations a real schema needs (@id, @default, @unique, @updatedAt). There are two distinct scenarios where this is useful, and they lead to different schemas. The first is greenfield modeling: you have an API response or a sample payload and you want to bootstrap a brand-new table to store it. Here the generator gives you a starting model that you tweak. The second is the SQL-to-Prisma path: you already have a live database and you run prisma db pull (introspection) to reverse-engineer it. In that case the generated schema reflects existing columns, constraints, and relations exactly — and you would not hand-edit types because the next introspection would overwrite them. The trickiest part of any JSON-derived schema is what to do with nested objects and arrays. A relational database has no native concept of an embedded object, so Prisma offers the Json scalar type for PostgreSQL, MySQL, and SQLite. The generator maps any nested object or array to Json by default. That is correct for unstructured blobs (settings, metadata, raw webhook bodies) but wrong for data that should be a related table — so you often replace a Json field with a proper relation after generating.

How to Use

1. Decide your scenario first: bootstrapping a NEW table from a JSON sample (use this tool, then edit), or reverse-engineering an EXISTING database (prefer prisma db pull instead, which preserves real constraints)
2. Set the model name in PascalCase (Prisma convention) — e.g. "User", "OrderItem" — and pick the datasource provider, because Json support and native types differ between PostgreSQL, MySQL, and SQLite
3. Paste an ARRAY of several records rather than one object: a single sample cannot tell the generator whether a field is sometimes null, so multiple rows produce more accurate ? modifiers
4. After generating, replace any Json field that represents a real one-to-many or many-to-one relationship with a proper model and a relation (@relation), and add @@index on columns you will filter by
5. Run npx prisma format to normalize spacing and alignment, then npx prisma migrate dev --name init to create the migration and apply it

Tips & Best Practices

• Json fields are stored as opaque blobs — you CANNOT add @unique or @@index on their inner keys in MySQL/SQLite, and querying them requires the path/filter helpers. If you need to query a value, promote it to its own column
• @updatedAt is updated by the Prisma Client, NOT by the database. A raw SQL UPDATE outside Prisma will not bump the timestamp — this surprises people debugging stale updatedAt values
• @default(cuid()) and @default(uuid()) are generated application-side by Prisma; @default(autoincrement()) is generated by the database. Choose cuid()/uuid() if you need the id before the INSERT (e.g. to build relations in a single transaction)
• For an Int that exceeds 2^31 (timestamps in ms, large counters) use BigInt — the generator may infer Int from a small sample, so widen it manually or you will hit overflow in PostgreSQL
• Prisma requires every model to have a unique identifier (@id or @@unique). If your JSON has no id-like field, add one before migrating or the schema will not validate

Frequently Asked Questions

Real-world Examples

{
  "id": 1,
  "email": "ada@example.com",
  "name": "Ada Lovelace",
  "bio": null,
  "isVerified": true,
  "settings": { "theme": "dark", "emailDigest": "weekly" },
  "createdAt": "2024-03-01T12:00:00Z"
}

model User {
  id         Int      @id @default(autoincrement())
  email      String   @unique
  name       String
  bio        String?
  isVerified Boolean  @default(false)
  settings   Json
  createdAt  DateTime @default(now())
  updatedAt  DateTime @updatedAt
}

{
  "id": "ord_1001",
  "total": 49.98,
  "items": [
    { "sku": "TS-RED-M", "qty": 1, "price": 19.99 },
    { "sku": "MUG-01",   "qty": 1, "price": 29.99 }
  ]
}

model Order {
  id        String      @id
  total     Decimal     @db.Decimal(10, 2)
  items     OrderItem[]
  createdAt DateTime    @default(now())
  updatedAt DateTime    @updatedAt
}

model OrderItem {
  id      Int     @id @default(autoincrement())
  sku     String
  qty     Int
  price   Decimal @db.Decimal(10, 2)
  order   Order   @relation(fields: [orderId], references: [id])
  orderId String

  @@index([orderId])
}

Related Tools