skip to content

Schema & Data Model

The Prisma schema file itself — model blocks, scalar and enum fields, attributes such as @id, @unique, @default and @map, multi-field ids, and the datasource and generator blocks. Interviewers start here because the schema is the single source of truth: the client API and every migration are derived from it.

on this pageshow

explore

questions

6

In a Prisma 7 schema, how does a model block describe a table, and what do @id, @default, @unique and the ? modifier each do?

level: juniorimportance: must knowfreq 72%

answer

  1. one block, one table
  2. name, type, modifiers, attributes
  3. @ on a field, @@ on the block
  4. required unless marked
  5. at least one unique criterion

basics

~20 s

A Prisma model block declares one table; each line is a field with a type, modifiers and attributes. @id marks the primary key, @default fills an omitted value, @unique forbids duplicates, and ? makes the field nullable.

solid answer

~40 s

The schema file is Prisma's source of truth: a `model User { ... }` block maps to a `User` table, and each line reads `name Type modifiers attributes`. Scalar types such as `String`, `Int`, `Boolean`, `DateTime` and `Json` become columns, and an `enum Role { ... }` block becomes a native enum type on PostgreSQL. A field is required (`NOT NULL`) unless it carries `?`; `[]` makes it a list, and the two cannot be combined. Field attributes start with `@`: `@id` marks the primary key, `@default(autoincrement())`, `@default(now())` or `@default(READER)` supply a value when a create omits one, and `@unique` adds a unique index. Block attributes start with `@@` and span several fields. Every model needs at least one unique criterion built only from required fields, or `prisma validate` rejects the schema.

code

sql · 17 lines
sql
-- CreateEnum
CREATE TYPE "Role" AS ENUM ('READER', 'AUTHOR', 'ADMIN');

-- CreateTable
CREATE TABLE "User" (
    "id" SERIAL NOT NULL,
    "email" TEXT NOT NULL,
    "name" TEXT,
    "role" "Role" NOT NULL DEFAULT 'READER',
    "tags" TEXT[],
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "User_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE UNIQUE INDEX "User_email_key" ON "User"("email");

go deeper

for a junior

Recall the field line order: name, type, optional ? or [], then attributes. Know that @id, @default and @unique are field attributes and that fields are required unless marked with ?.

for a middle

Explain what each attribute turns into: SERIAL and CURRENT_TIMESTAMP defaults, a unique index, NOT NULL, and how defaults and ? make create-input properties optional in the generated client.

for a senior

Show you read the schema as the contract for both migrations and types: spot a missing ? that will break inserts, or a unique field that should be required before it reaches production data.

for a principal

Treat the schema conventions as a team standard: naming, which defaults live in the database, and when a field may be nullable, so that every model reviewed later reads the same way.

## What a model block is A **Prisma schema** (usually `prisma/schema.prisma`) is a declarative file that describes the shape of your data. Prisma Client's types and Prisma Migrate's SQL are both derived from it, which is why interviewers treat it as the single source of truth. Inside it, a **model block** describes one entity of your domain and maps to one **table** in a relational database: ```prisma enum Role { READER AUTHOR ADMIN } model User { id Int @id @default(autoincrement()) email String @unique name String? role Role @default(READER) tags String[] createdAt DateTime @default(now()) } ``` By convention models are singular and PascalCase (`User`, not `users`); the table takes the model's name unless `@@map` says otherwise. Relation fields (a field whose type is another model, such as `posts Post[]`) also live in model blocks, but they are a subject of their own and are left out here. ## The anatomy of a field line Every field line has the same four parts, in this order: | Part | Example | What it controls | | --- | --- | --- | | Name | `email` | The property name in Prisma Client and, by default, the column name | | Type | `String`, `Int`, `Role` | The scalar type, or an enum declared in the same schema | | Modifier | `?` or `[]` | Optional (nullable) or list | | Attributes | `@unique`, `@default(...)` | Keys, defaults, mapping and native types | The everyday scalar types are `String`, `Boolean`, `Int`, `BigInt`, `Float`, `Decimal`, `DateTime`, `Json` and `Bytes`. The datasource's `provider` decides which column type each becomes: on PostgreSQL, `String` is `text` and `DateTime` is `timestamp(3)` unless a `@db.*` attribute picks another. ## The four basics - **`@id`** marks the primary key. It cannot be optional and cannot sit on a relation field. A model has at most one id: either one `@id` field or one `@@id([...])` block over several fields. - **`@default(...)`** supplies a value when a create call leaves the field out. It takes a static value (`false`, `0`, `"draft"`, an enum member such as `READER`) or a function: `autoincrement()`, `now()`, `uuid()`, `cuid()`, `ulid()`, `nanoid()` or `dbgenerated("...")`. On PostgreSQL, `autoincrement()` becomes a `SERIAL` column and `now()` becomes `DEFAULT CURRENT_TIMESTAMP`. - **`@unique`** forbids duplicate values in that column; on PostgreSQL the migration creates a unique index named `User_email_key`. A model may carry any number of unique fields, and `@@unique([a, b])` does the same across several fields. - **`?`** makes a field optional: the column is nullable and the generated TypeScript type becomes `string | null`. Without `?` the column is `NOT NULL` and the client type requires a value. The list modifier `[]` on a scalar (`tags String[]`) is a **scalar list**, which needs a database with native arrays, such as PostgreSQL. It can never be combined with `?`: `String[]?` is rejected with "Optional lists are not supported." ## Rules the validator enforces `prisma validate` and `prisma generate` check the schema before anything reaches the database. Mistakes juniors commonly make produce `P1012` validation errors: 1. **A model with no usable unique criterion.** If a model has no `@id`, no `@@id`, and no `@unique` or `@@unique` made only of required fields, the error reads "Each model must have at least one unique criteria that has only required fields." Prisma Client needs such a key so that `findUnique`, `update` and `delete` can address exactly one row. 2. **A default whose function does not fit the type.** `@default(autoincrement())` on a `String`, or `@default(uuid())` on an `Int`, is rejected at parse time. 3. **An optional list.** `String[]?` is rejected; use `String[]`, or a nullable scalar if the field holds one value. 4. **An optional id.** `@id` on a `String?` field is invalid, because a primary key cannot be null. ## What the generated client does with it After `prisma generate`, each model becomes a typed delegate (`prisma.user`) and a set of input types. The schema decides which properties a create must supply: in the generated `UserCreateInput`, `email` is required, while `name`, `role`, `tags` and `createdAt` are optional because they are nullable or have a default. The autoincremented `id` does not appear in that checked input at all, since the database assigns it. That link between attributes and generated types is the practical reason to get the basics right: a missing `?` or `@default` surfaces first as a TypeScript error at the call site, long before any SQL runs.

  • Why does Prisma 7 reject a model whose only unique field is optional?
    The validator requires at least one unique criterion made only of required fields. Prisma Client's single-row operations (`findUnique`, `update`, `delete`) need a key that always identifies one row, and a nullable column cannot: the database treats `NULL`s as distinct, so several rows may hold `NULL` in a unique column. Mark the field required, or add an `@id`.
  • How do @default and ? show up in the TypeScript types Prisma generates?
    They make properties optional in the create input. A required field without a default (`email String`) is a required property; a field with `?` becomes optional and accepts `null`; a field with `@default` becomes optional because the default fills it. On reads, the model type keeps `name: string | null` for a `?` field, so callers must handle `null`.

  • Prisma fields are optional by default; you mark the required ones.
  • @unique is only checked by Prisma Client; the database gets no index.
  • @default(autoincrement()) also works on a String id field.
  • An optional list is written String[]? and is fine on PostgreSQL.
  • A model can do without any unique field if it has an @@index.
open as a page

After a Prisma 6 project moves to Prisma 7, its schema's datasource url and output-less generator block fail; what must change in those two blocks?

level: seniorimportance: must knowfreq 48%

basics

~20 s

In Prisma 7 the datasource block keeps only provider and its options; url, directUrl and shadowDatabaseUrl move to prisma.config.ts. The generator becomes provider = "prisma-client" with a required output path, and imports switch from @prisma/client to that generated folder.

open as a page

In Prisma 7, how do @@id, @@unique and @@index differ, and how does a composite key show up in a findUnique call?

level: middleimportance: should knowfreq 40%

basics

~20 s

@@id makes several fields the primary key, @@unique adds a multi-column unique index, and @@index adds a plain lookup index. The first two give Prisma Client a compound selector such as authorId_slug for findUnique; @@index gives none.

open as a page

In Prisma 7, how do @map and @@map let a schema keep PascalCase models and camelCase fields over existing snake_case tables and columns?

level: middleimportance: should knowfreq 42%

basics

~20 s

@map("created_at") binds a field or enum value to a differently named column or stored value; @@map("users") does the same for a model's table or an enum's type. Prisma Client keeps the schema names; only SQL uses the mapped ones.

open as a page

In Prisma 7 on PostgreSQL, what column type does a plain String, DateTime or Decimal field get, and when do you add a @db attribute?

level: middleimportance: should knowfreq 34%

basics

~10 s

Without a native type attribute, Prisma's PostgreSQL connector maps String to text, DateTime to timestamp(3) and Decimal to decimal(65,30). An attribute such as @db.VarChar(255), @db.Timestamptz(6) or @db.Decimal(10, 2) pins the exact column type instead.

open as a page

A Prisma 7 model uses @default(uuid()) and @updatedAt; why do rows written by a plain SQL script fail on the id or keep a stale updatedAt?

level: seniorimportance: should knowfreq 30%

basics

~20 s

uuid(), cuid() and @updatedAt are implemented by Prisma ORM, not the database: the migration creates the columns with no DEFAULT or trigger, and Prisma Client fills them in. SQL that bypasses the client must supply them itself.

open as a page