Prisma Notes: Schema to Query, and Every Trap in Between

Scraps ended up with four models: User, Post, Category, and UserPreference for the digest opt-in. Every schema and code example below comes straight from that project, not invented for this post.

Prisma’s whole pitch is that you describe your data once, in one file, and two separate things get generated from it: the SQL that builds your actual tables, and the client your application code calls. Same input, two outputs, and that’s the shape worth holding in your head before any of the specific commands below make sense.

flowchart TD
    SCHEMA["schema.prisma<br/>models, relations, datasource"]
    MIGRATE["npx prisma migrate dev<br/>writes migration.sql"]
    GENERATE["npx prisma generate<br/>builds @prisma/client"]
    DB[("Postgres<br/>tables, indexes, constraints")]
    CLIENT["PrismaClient instance<br/>one per process"]
    QUERY["prisma.post.findMany({ ... })<br/>checked at compile time"]
    SQL["SQL the query engine builds<br/>SELECT ... JOIN ..."]

    SCHEMA --> MIGRATE --> DB
    SCHEMA --> GENERATE --> CLIENT
    CLIENT --> QUERY --> SQL --> DB
    DB -->|"rows back"| QUERY

Everything past this point is either the left branch, getting schema.prisma right and then migrating it, or the right branch, the client you actually write application code against. I’ll walk both, in the order I actually hit them.

Before any of the interesting stuff, Prisma needs a normal Node/TypeScript project to sit inside. Nothing about this part is Prisma-specific, it’s just the scaffolding that has to exist first.

1. npm init

   bash

   npm init -y
   

   Just a package.json to track dependencies and scripts.

2. install prisma and the dev tooling

   bash

   npm install --save-dev prisma typescript ts-node @types/node nodemon
   

   prisma is the CLI: schema management, migrations, generating the client. typescript because the generated client is fully typed and there’s no reason to fight that with plain JS. ts-node runs .ts files without a separate compile step. nodemon restarts the script on file changes so you’re not doing that by hand every time you tweak a query.

3. tsconfig.json

   json

   // tsconfig.json
   {
   	"compilerOptions": {
   		"sourceMap": true,
   		"outDir": "dist",
   		"strict": true,
   		"lib": ["esnext"],
   		"esModuleInterop": true,
   		"resolveJsonModule": true
   	}
   }
   

   strict is the one that actually matters here: Prisma’s generated types are only as useful as the strictness of the code consuming them. Turn it off and half the point of using Prisma evaporates.

4. prisma init

   bash

   npx prisma init --data-source-provider postgresql
   

   This creates a prisma/ folder with schema.prisma inside it, drops a .env file for your connection string, and adds .env and node_modules to .gitignore automatically. Passing --data-source-provider pre-fills the datasource block so you’re not hand-editing the provider name a minute later.

schema.prisma is where you define models, relationships, and the database connection, in Prisma’s own declarative language instead of raw SQL. Get this file right and everything downstream, migrations and the client both, follows from it. Get it wrong and you’re fighting it at every step after.

Install the Prisma VS Code extension. It gives you syntax highlighting, autocomplete, validation, and format-on-save, and you can also run npx prisma format from the CLI if you’d rather not wire up the editor integration.

The file splits into a handful of blocks, and the first two are just wiring.

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

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

The generator block says what to generate from the schema, almost always prisma-client-js, the type-safe query builder your app actually imports. You can define more than one generator if you also want, say, GraphQL types out of the same schema. The datasource block is the connection: which database engine, and the connection string, read from an environment variable so credentials never end up in the repo. There’s exactly one datasource block per project, since a project talks to one database.

# .env
DATABASE_URL="postgresql://postgres:password@localhost:5433/scraps?schema=public"

Swap in your actual user, password, host, port, and database name. One thing that trips people up here: the database itself, scraps in that string, has to already exist. Prisma creates tables and columns inside it, not the database.

Each model block maps to a table, and each field inside it maps to a column. A field has a name, a type, an optional modifier (? for nullable, [] for a list, mostly used for relations), and optional attributes starting with @ for a single field or @@ for the whole model.

The type list is short and most of it is what you’d expect:

Attributes are where the actual behavior lives. A handful cover almost everything:

And the block-level ones, written on their own line inside the model:

model User {
  id   String @id @default(uuid())
  name String
  age  Int

  @@unique([age, name]) // no two users can share both age AND name
  @@index([name, age])  // speeds up queries filtering or sorting on these together
}

@@unique enforces uniqueness across a combination of fields rather than one. @@index builds a database index, worth adding to anything you filter or sort by often. @@id([field1, field2]) defines a composite primary key across multiple fields, and if you use it you can’t also put a plain @id on any single field, it’s one or the other.

Enums round this out. Scraps used one for post visibility:

enum Role {
  BASIC
  ADMIN
  EDITOR
}

model User {
  id   String @id @default(uuid())
  name String
  role Role   @default(BASIC)
}

Now role can only ever be one of three literal values, checked at the database and the type level both.

This is the part of Prisma that’s genuinely nice to use, and also the part with the sharpest edge if you have more than one relation between the same two models.

One-to-many is the common case: one user writes many posts, each post has exactly one author.

model User {
  id           String @id @default(uuid())
  name         String
  writtenPosts Post[] @relation("WrittenPosts")
  favoritePosts Post[] @relation("FavoritePosts")
}

model Post {
  id            String  @id @default(uuid())
  title         String
  authorId      String
  author        User    @relation("WrittenPosts", fields: [authorId], references: [id])
  favoritedById String?
  favoritedBy   User?   @relation("FavoritePosts", fields: [favoritedById], references: [id])
}

The fields/references pair on @relation links the foreign key on the current model (authorId) to the primary key it points at (id on User). The part that’ll actually get you: the name argument, "WrittenPosts" and "FavoritePosts" here, is required the moment two relations connect the same pair of models. Scraps has users writing posts and users favoriting posts, both are User to Post, and without the name Prisma has no way to know which foreign key on Post corresponds to which array on User. Leave it out in that situation and you get a schema validation error, not a silent bug, but it’ll stop you cold the first time you hit it if you don’t already know the rule.

Many-to-many is the one where Prisma quietly saves you work:

model Post {
  id         String     @id @default(uuid())
  title      String
  categories Category[]
}

model Category {
  id    String @id @default(uuid())
  name  String
  posts Post[]
}

No join table anywhere in this schema. Prisma creates and manages the pivot table itself, so you never write or query it directly, you just work with post.categories and category.posts like they’re plain arrays.

One-to-one is where the digest preference lives:

model User {
  id             String           @id @default(uuid())
  name           String
  userPreference UserPreference?
}

model UserPreference {
  id           String  @id @default(uuid())
  emailUpdates Boolean
  userId       String  @unique
  user         User    @relation(fields: [userId], references: [id])
}

The @unique on userId is the actual constraint enforcing one-to-one, not the optional ? on the User side. Drop the @unique and you’ve silently got a one-to-many where one user could have several preference rows, which defeats the entire point of the model.

schema.prisma on its own is just a description. Nothing exists in the database until you run a migration.

npx prisma migrate dev --name init_schema

migrate dev diffs your schema against the database, writes the SQL needed to close that gap into prisma/migrations/<timestamp>_init_schema/migration.sql, and applies it. The name you give it, init_schema, add_categories, whatever, is what shows up when you’re scanning migration history later, so make it describe the actual change. After the SQL runs, Prisma also regenerates the client, so your types stay in sync with whatever the schema now looks like.

Prisma Client is the generated, type-safe layer your application code actually calls. It’s built directly from your schema, so its methods and return types match your models exactly, no manual mapping.

npm install @prisma/client

migrate dev regenerates the client automatically. If you change the schema without running a migration, say a field’s optionality shifted in a way that doesn’t need a database change, npx prisma generate regenerates it manually.

// script.ts
import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

async function main() {
	// database calls go here
}

main()
	.catch((e) => console.error(e))
	.finally(async () => {
		await prisma.$disconnect();
	});

The one rule that actually matters: use a single PrismaClient instance per process. Each instance manages its own connection pool, and instantiating a new one per request is the fastest way to exhaust your database’s connection limit. Everything is async, so async/await is the natural way to write this, and disconnecting on shutdown is good hygiene even though Node usually cleans it up on exit anyway.

prisma.model.create() inserts one record. This is where nested writes show up, which is one of the actually good parts of Prisma:

const user = await prisma.user.create({
	data: {
		name: "Kyle",
		email: "[email protected]",
		age: 27,
		userPreference: {
			create: { emailUpdates: true },
		},
	},
	include: {
		userPreference: true,
	},
});

create inside a relation writes a brand new related record in the same call. connect does the equivalent for linking to a record that already exists, by its unique identifier. Both save you a second round trip.

The trap is include versus select. include fetches the record plus every field of the specified relations. select picks exactly the fields you want, from the model and its relations both, which is worth doing once a query is on a hot path and you don’t need the whole row. You can use one or the other, never both in the same query, and Prisma throws if you try. I hit this the first week, copied an example that had both, and got a validation error before I even understood why they’d conflict.

For bulk inserts, createMany is the one to reach for:

const users = await prisma.user.createMany({
	data: [
		{ name: "Sally", email: "[email protected]", age: 32 },
		{ name: "Sally", email: "[email protected]", age: 13 },
		{ name: "Sally", email: "[email protected]", age: 12 },
	],
	// skipDuplicates: true
});

It returns { count: 3 }, not the created rows, and it doesn’t support include or select at all. That’s a deliberate tradeoff for bulk-insert performance, if you need the created records back with their relations, do individual create calls or a follow-up findMany.

Three methods cover almost everything, and picking the right one matters more than it looks like it should.

findUnique fetches by a genuinely unique field, the primary key, or anything marked @unique or @@unique:

const userByEmail = await prisma.user.findUnique({
	where: { email: "[email protected]" },
	include: { userPreference: true },
});

// composite unique keys are queried as an object, joined with an underscore
const userByAgeName = await prisma.user.findUnique({
	where: { age_name: { age: 27, name: "Kyle" } },
});

It returns null if nothing matches, and throws if the field somehow isn’t actually unique in the data. findFirst is the one for “any single match, doesn’t need to be unique”:

const firstSally = await prisma.user.findFirst({
	where: { name: "Sally" },
	orderBy: { age: "asc" }, // the youngest Sally
});

findMany is the workhorse, and where where earns its keep:

const paginatedSallys = await prisma.user.findMany({
	where: { name: "Sally" },
	orderBy: { age: "desc" },
	take: 2,
	skip: 1,
	distinct: ["name", "age"],
});

The where clause supports the operators you’d expect. equals, not, in, notIn for exact matching, lt/lte/gt/gte for numbers and dates, contains/startsWith/endsWith for strings (case-sensitive unless you pass mode: "insensitive"), and OR/AND/NOT for combining conditions.

Relation filtering is the part that’s genuinely nice. From the one side:

const usersWithEmailUpdates = await prisma.user.findMany({
	where: { userPreference: { emailUpdates: true } },
	include: { userPreference: true },
});

From the many side, some, every, and none mean exactly what they sound like:

const usersWithSomePosts = await prisma.user.findMany({
	where: {
		writtenPosts: { some: { title: { contains: "My First" } } },
	},
	include: { writtenPosts: true },
});

And you can filter the many side by an attribute of the one side too, going the other direction:

const postsByAuthorAge = await prisma.post.findMany({
	where: { author: { is: { age: { gt: 25 } } } },
	include: { author: true },
});

update() and delete() both require a unique where, exactly like findUnique, for the same reason: Prisma needs to be certain it’s touching exactly one row.

const updatedUser = await prisma.user.update({
	where: { email: "[email protected]" },
	data: {
		email: "[email protected]",
		age: { increment: 1 }, // also: decrement, multiply, divide
		userPreference: {
			update: { emailUpdates: false },
		},
	},
	include: { userPreference: true },
});

The numeric operators (increment, decrement, multiply, divide) are atomic at the database level, worth using over a manual read-then-write whenever you’re bumping a counter.

Deleting a nonexistent record doesn’t fail quietly:

try {
	await prisma.user.delete({ where: { email: "[email protected]" } });
} catch (e: any) {
	if (e.code === "P2025") {
		console.warn("tried to delete a user that doesn't exist");
	} else {
		throw e;
	}
}

P2025 is Prisma’s “record not found” error code, and it’s worth wrapping deletes in a try/catch for exactly this if there’s any chance the row’s already gone.

updateMany and deleteMany are the batch versions, and like createMany, they don’t take include or select, they just return a count:

await prisma.user.updateMany({
	where: { name: "Sally" },
	data: { name: "New Sally" },
}); // { count: 3 }

await prisma.user.deleteMany({
	where: { age: { gt: 20 } },
}); // { count: 1 }

Passing an empty where: {} to deleteMany wipes the whole table, so that’s not a line you want to run by accident.

findMany paginates with two arguments.

async function fetchPaginatedUsers() {
	const pageNumber = 2;
	const pageSize = 10;
	const skipAmount = (pageNumber - 1) * pageSize;

	const users = await prisma.user.findMany({
		take: pageSize,
		skip: skipAmount,
		orderBy: { name: "asc" },
	});

	const totalUserCount = await prisma.user.count();
	const totalPages = Math.ceil(totalUserCount / pageSize);

	console.log(`fetched page ${pageNumber} of ${totalPages}`);
	return users;
}

Page 2 at size 10 skips 10 and takes the next 10, records 11 through 20. The gotcha is the orderBy, and it’s easy to skip because everything works fine locally without it. Without a stable sort, the database is free to return rows in whatever order is convenient for it, which usually looks stable while your table is small and static. Add enough writes, or run it against a table under real load, and pages start showing duplicate rows or skipping ones entirely between requests, because “page 2” is only a meaningful concept relative to a fixed order. I didn’t hit this until Scraps had a couple hundred posts in it, and by then it took a minute to figure out why the feed kept repeating itself.

Once queries got nested a few levels deep, I wanted to see what Prisma was actually sending, not guess.

const prisma = new PrismaClient({
	log: ["query"], // add 'info', 'warn', 'error' for more
});

With log: ["query"] on, every query Prisma runs prints its SQL to the console. It’s useful for three different reasons at once: checking a query is shaped the way you think it is, spotting one that needs an index, and just building an intuition for how your high-level .findMany() calls turn into actual SELECTs and JOINs. I left this on for most of building Scraps and it caught more than one query I’d written wrong.

Bottom line. Prisma’s actual value is that schema.prisma stays the single source of truth end to end: the migration SQL and the client types both come from the same file, so they can’t drift apart the way hand-rolled SQL and a hand-rolled types file eventually do. The tradeoffs that’ll actually bite you aren’t exotic, they’re the mutual exclusivity of include and select, remembering to name a relation the moment there are two between the same models, and setting orderBy on anything paginated before your table has enough rows to expose the gap. None of these show up in a five-minute demo. All of them show up the first time your data outgrows the demo.