The Prisma Schema

Prisma Fundamentals

Chapter 3 · The Prisma Schema

Chapter 2 added a one-line User model without explaining it. This chapter covers the schema language properly: models, field types, optional and list fields, the attributes that control defaults, keys and uniqueness, enums, and how to control the names Prisma uses in the database. By the end you'll have a realistic starting schema for a blog. Relations between models come in Chapters 7 and 8.

Models and Fields

Each model describes one table (or, in MongoDB, one collection). Each line inside it is a field: a name, a type, and optionally some attributes.

model User {
//name      type      attributes
  id        Int       @id @default(autoincrement())
  email     String    @unique
  name      String?
  createdAt DateTime  @default(now())
}

By convention, model names are singular and in PascalCase (User, BlogPost), and field names are camelCase (createdAt). Both must start with a letter. Prisma Client uses them directly: this model becomes prisma.user, with a TypeScript type User.

Scalar Types

Prisma type	TypeScript type	Use for
`String`	`string`	Text
`Boolean`	`boolean`	True/false flags
`Int`	`number`	Whole numbers (32-bit)
`BigInt`	`bigint`	Very large whole numbers
`Float`	`number`	Approximate decimals, such as measurements
`Decimal`	`Decimal` (a library type)	Exact decimals, especially money
`DateTime`	`Date`	Dates and times
`Json`	JSON values	Flexible structured data
`Bytes`	binary (`Uint8Array`)	Raw binary data

Never use Float for money

Float stores approximate binary values, so sums like 0.1 + 0.2 don't come out exactly. Use Decimal for prices and balances, or store whole pennies in an Int.

Optional and List Fields

Syntax	Meaning	TypeScript
`name String`	Required: every row must have a value	`string`
`name String?`	Optional: may be `NULL` in the database	`string \| null`
`tags String[]`	A list of values in one column	`string[]`

Lists need database support

Lists of plain values (scalar lists) only work on databases with a native array type — PostgreSQL and CockroachDB, plus MongoDB. On SQLite and MySQL, model a list as a separate table with a relation instead (Chapter 8).

Field Attributes

Attribute	What it does
`@id`	Marks the primary key: each row's unique identifier
`@default(...)`	Sets a value when you don't provide one
`@unique`	No two rows may have the same value
`@updatedAt`	Prisma sets it to the current time whenever the row is updated
`@map("...")`	Uses a different column name in the database
`@db.X`	Chooses a specific database column type, such as `@db.VarChar(255)`

Defaults and ID strategies

Default	Produces	Good for
`autoincrement()`	1, 2, 3, … (relational databases)	Simple internal IDs
`uuid()` / `uuid(7)`	A random UUID; version 7 is ordered by time	IDs that are hard to guess, or created on several servers
`cuid()`, `ulid()`, `nanoid()`	Other kinds of unique string IDs	Shorter or URL-friendly IDs
`now()`	The current date and time	`createdAt` timestamps
`dbgenerated("...")`	A default calculated by the database itself	Database-specific defaults
A literal: `@default(false)`	That fixed value	Flags, counters, statuses

Sequential IDs can leak information

With autoincrement(), a public URL like /orders/1042 tells anyone roughly how many orders you have, and invites them to try /orders/1043. For IDs that appear in URLs, a UUID is often the safer choice. (Real security still needs proper permission checks.)

Enums

enum Role {
  READER
  AUTHOR
  ADMIN
}

model User {
  id   Int  @id @default(autoincrement())
  role Role @default(READER)
}

An enum restricts a field to a fixed list of values, and Prisma generates a TypeScript type for it, so role: "EDITOR" is a compile error. Enums work on all the relational databases in this course, including SQLite since Prisma 6.2.

Model Attributes: Rules Across Several Fields

Attributes that start with @@ apply to the whole model rather than one field.

model Subscription {
  userId  Int
  topic   String
  created DateTime @default(now())

  @@id([userId, topic])        // composite primary key: one row per user per topic
}

model Post {
  id     Int    @id @default(autoincrement())
  blogId Int
  slug   String

  @@unique([blogId, slug])     // the same slug can appear in different blogs, but not twice in one
  @@index([blogId])            // speed up lookups by blog (Prisma Intermediate/Advanced, Chapter 7)
}

Controlling Database Names: @map and @@map

Many databases use lower-case, underscore-separated names (created_at, blog_posts), while TypeScript code prefers createdAt and Post. You don't have to choose:

model Post {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now()) @map("created_at")

  @@map("blog_posts")
}

Your code uses prisma.post and createdAt, while the database table is blog_posts with a column created_at. This is especially useful when you connect Prisma to an existing database whose names you can't change.

A Starting Schema for the Blog

This is the schema later chapters build on. Relations between users and posts are added in Chapter 7.

enum Role {
  READER
  AUTHOR
  ADMIN
}

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  role      Role     @default(READER)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model Post {
  id          Int       @id @default(autoincrement())
  title       String
  slug        String    @unique
  content     String?
  published   Boolean   @default(false)
  viewCount   Int       @default(0)
  publishedAt DateTime?
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt
}

# Check the schema for mistakes and tidy its layout
npx prisma validate
npx prisma format

Editor support

The official Prisma extension for VS Code adds syntax highlighting, error checking and autocompletion for .prisma files. prisma format lines the columns up the way the examples in this chapter are laid out.

Hands-On Exercises

Exercise 1

Replace your Chapter 2 schema with this chapter's blog schema, run prisma validate and prisma format, then apply it (npx prisma migrate dev --name blog-schema) and regenerate the client. Look at the generated SQL in prisma/migrations and find where each attribute ended up.

📄 View solution

Exercise 2

Design a Product model for a shop: a UUID ID, a name, a unique SKU, a price, a stock count starting at zero, an optional description, a status that can only be DRAFT, ACTIVE or DISCONTINUED, and created/updated timestamps. Store it in a table called products. Explain your choice of type for the price.

📄 View solution

Exercise 3

Find and fix the mistakes in this model: model users { ID int @id; Email String @unique @unique; tags String[]; price Float }, assuming it's for a SQLite database that stores prices.

📄 View solution

Chapter 3 Quick Reference

A model is a table; each field has a name, type and optional attributes; models are singular PascalCase, fields camelCase
Types: String, Boolean, Int, BigInt, Float, Decimal, DateTime, Json, Bytes; use Decimal for money
? makes a field optional; [] makes a list (PostgreSQL, CockroachDB, MongoDB only)
@id, @default(...), @unique, @updatedAt, @map, @db.X
Defaults: autoincrement(), uuid(), cuid(), now(), dbgenerated(), or a literal
enum limits a field to fixed values (on SQLite since Prisma 6.2)
@@id, @@unique, @@index and @@map apply to the whole model
npx prisma validate checks the schema; npx prisma format tidies it