Skip to content
Tutorials Center
My learning

Topics

Lesson 9 of 36 ยท Schema and Models

Identifiers, Unique Constraints, Indexes and Name Mapping

Choose primary keys (autoincrement, uuid, cuid), build composite keys, add unique constraints and indexes, and map model and field names to existing table and column names.

Four small attribute families do a lot of work in a schema: identifiers (@id) that name each row, unique constraints that forbid duplicates, indexes that make lookups fast, and mappings that connect your pretty names to the real table and column names. Get these right and your queries are correct and fast; get them wrong and you ship duplicate rows and slow pages.

Every model needs an identifier

Prisma requires each model to have a unique way to identify a row, so findUnique, update and delete know what you mean. There are several ways:

PRISMA
model A { id Int    @id @default(autoincrement()) }   // 1, 2, 3 ...
model B { id String @id @default(uuid()) }             // random UUID
model C { id String @id @default(cuid()) }             // collision-resistant string
model D { id String @id @default(uuid(7)) }            // UUID v7: time-ordered
Strategy Pros Cons
autoincrement() small, fast, readable guessable (/users/2 exists), harder to merge databases
uuid() globally unique 36 characters, random order hurts index locality
uuid(7) unique and roughly time-ordered newer; check your Prisma version
cuid() compact, safe to expose generated by Prisma, not by the database

Use integers for internal tables and a random or time-ordered string for ids that appear in URLs and APIs. Whatever you choose, decide early: changing the type of a primary key later means rewriting every table that references it.

Composite identifiers

When identity is a combination of fields, use @@id:

PRISMA
model Enrollment {
  studentId Int
  courseId  Int
  joinedAt  DateTime @default(now())

  @@id([studentId, courseId])
}

A student can join many courses and a course has many students, but each pair appears once. In queries Prisma names the compound key by joining the fields: where: { studentId_courseId: { studentId: 1, courseId: 7 } }.

Unique constraints

PRISMA
model User {
  id       Int    @id @default(autoincrement())
  email    String @unique
  username String
  orgId    Int

  @@unique([orgId, username])
}
  • @unique on one field: no two rows may share the email.
  • @@unique([orgId, username]): the pair must be unique, so two organizations can each have a user called ana. The compound key is orgId_username in findUnique.
  • A unique field can be used in findUnique, update, delete and upsert.
  • Optional unique fields allow many null values on most databases, because null is not equal to null.

You can name the compound key, @@unique([orgId, username], name: "org_user"), so the query reads where: { org_user: { orgId: 1, username: "ana" } }.

This runs on Node's SQLite and shows duplicates being blocked, composite uniqueness and many nulls being allowed. It is plain SQL, the same constraints Prisma would create.

Node.js Unique, composite unique and null

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Indexes

A unique constraint creates an index automatically. For columns you filter or sort by but that are not unique, add an index yourself:

PRISMA
model Post {
  id        Int      @id @default(autoincrement())
  authorId  Int
  status    String
  createdAt DateTime @default(now())
  title     String

  @@index([authorId])
  @@index([status, createdAt])
}

Rules of thumb:

  1. Foreign keys (authorId) deserve an index: you will filter and join on them constantly. Some databases (MySQL) create one automatically; PostgreSQL does not.
  2. A composite index [status, createdAt] helps queries that filter by status and sort by createdAt. Column order matters: it serves status alone, or status + createdAt, but not createdAt alone.
  3. Indexes speed up reads but slow writes and take space. Do not index everything.
  4. You can set sort order and type: @@index([createdAt(sort: Desc)]), or @@index([title], type: Hash) on PostgreSQL.

The effect of an index is visible in the query plan. SQLite's EXPLAIN QUERY PLAN shows whether it scans the whole table or uses an index:

Node.js Seeing an index in the query plan

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Mapping names with @map and @@map

Your database may use snake_case table and column names, or you may inherit a legacy schema. Prisma lets the code keep clean names while the database keeps its own:

PRISMA
model BlogPost {
  id        Int      @id @default(autoincrement())
  authorId  Int      @map("author_id")
  createdAt DateTime @default(now()) @map("created_at")

  @@map("blog_posts")
}

In TypeScript you write prisma.blogPost.findMany({ where: { authorId: 1 } }); in SQL the table is blog_posts with a column author_id. You can also name constraints: @id(map: "pk_blog_posts"), @unique(map: "uq_users_email"), @@index([authorId], map: "idx_blog_posts_author"). Explicit names matter for databases where you manage constraint names by policy and make migrations from db pull stable.

After prisma db pull on a legacy database, a typical cleanup is: rename ugly models (tbl_usr to User) and add @@map("tbl_usr") so the database does not change.

Using a mapping idea in code

This stand-in converts between schema-style camelCase names and database snake_case names, the same translation @map performs when it builds a query.

Node.js Stand-in: what @map does

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A student can enroll in many courses, but only once per course. Which schema line enforces that for an Enrollment model?

    A composite identifier (or @@unique) on the pair allows many rows per student and per course, but only one per pair.
  2. 2. Which id strategy produces globally unique, random string ids generated by the database or client?

    uuid() generates random UUID strings. autoincrement() gives sequential integers.
  3. 3. What does @@index([status, createdAt]) NOT efficiently support?

    A composite index is ordered by its first column, so queries on only the second column cannot use it well.
  4. 4. What does @@map("blog_posts") on model BlogPost change?

    @@map sets the real table name while the code keeps using BlogPost.
  5. 5. Why index a foreign key such as authorId?

    Frequent filters and joins on foreign keys benefit from an index; PostgreSQL does not create one for you.

Summary

  • Every model needs a unique identifier: @id, @@id for composite, or @unique fields; pick the id strategy early.
  • @unique and @@unique stop duplicates; null values do not collide.
  • Add @@index for fields you filter and sort by, keeping column order in mind.
  • @map and @@map connect clean code names to real database names.

Next: Creating and Reading Records