Learning Prisma As If You Built It Yourself

If you have ever written raw SQL inside a real app, you already know the feeling. At first it looks fine, then a few weeks later you are staring at a 40 line query, scared to touch it because changing one comma might bring down half the website. Maintaining raw SQL across a growing app is just hard. The shape of the data changes, the team grows, the queries multiply, and suddenly you spend more time fixing strings than writing features.

That is exactly the gap an ORM fills.

What is an ORM, really

Think of an ORM as a friendly translator. You speak your favorite programming language, and the translator turns it into SQL on the fly. You say "give me all users with their pets", and the translator quietly walks over to the database, writes the proper SQL, brings back the rows, and hands you back nice objects you can actually work with.

No more building strings by hand. No more guessing if you forgot a JOIN. The translator knows the database, you stay in your language.

Let's pretend we are building one

We love JavaScript and TypeScript. So let's say we want to build our own ORM for the JS world. We will call it Prisma.

Before we write a single query, we need a few decisions. These decisions are basically the design of the whole library, and once you see them, every Prisma snippet on the internet starts to feel obvious.

Decision 1: We need a map of the database

The translator can only translate if it knows the language. So our ORM needs to know what tables exist, what columns they have, what types those columns are, and how tables relate to each other.

We could read this from the database every time, but that is slow and fragile. So instead we ask the developer to write it down once, in a file we will call schema.prisma. This becomes the single source of truth.

Here is what a tiny pet adoption app might look like:

// schema.prisma

generator client {
  provider = "prisma-client-js"
}

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

model User {
  id    Int     @id @default(autoincrement())
  name  String
  email String  @unique
  pets  Pet[]
}

model Pet {
  id        Int       @id @default(autoincrement())
  name      String
  species   String
  adoptedAt DateTime?
  owner     User?     @relation(fields: [ownerId], references: [id])
  ownerId   Int?
}

Three blocks, each with a clear job:

• generator tells Prisma what kind of client to build for us. We picked the JS one.
• datasource tells Prisma which database we are talking to and where it lives.
• model blocks describe our tables. Each line is a column, with a type and optional attributes.

A few cute things to notice:

• @id says this is the primary key.
• @default(autoincrement()) means the database fills it in for us.
• @unique adds a uniqueness constraint, like for emails.
• pets Pet[] says a user has many pets. The [] after Pet is what tells you it is a list, not just one.
• The ? after a type (like User? and Int?) means "this is optional, it can be empty". Without ?, the field is required.
• The matching owner and ownerId on Pet is how we say "each pet belongs to one user". @relation wires the foreign key.

About that ?: this is a pet adoption app, so a pet can sit in the shelter for a while with no owner yet. Making owner and ownerId optional says "a pet may or may not have a human". Same idea for adoptedAt: no adoption date until they are actually adopted. If we wanted to force every pet to have an owner from day one, we would drop the ? and the database would refuse to create a pet without one.

That is the whole schema. No SQL yet.

Decision 2: Turn the schema into actual code

Now the magic step. We run:

npx prisma migrate dev --name init

This does two things at once. It creates the SQL tables in the database to match our schema, and it generates a Prisma Client tailored to those exact models. The client is regular JS/TS code that lives in node_modules/@prisma/client (or a custom folder you point to). Every time the schema changes, you regenerate, and the client updates with it.

This is the part people miss when they first see Prisma. The client is not a generic library. It is custom built for your schema. That is why autocomplete knows your fields, your relations, and your types. Nothing is guessed.

Decision 3: Every call starts with prisma

We agreed early on that every query in our ORM should start with the same word, so the API feels predictable. We went with prisma, which is the instance of the generated client.

import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

What is prisma here? It is the client object we just generated. Under the hood, when you call something on it, the client builds a structured query, hands it to a query engine (a small native binary that ships with Prisma), and the engine talks to the actual database, gets the rows, and returns them as plain JS objects with full types attached. You never see the SQL unless you ask for it.

So when you read prisma.something.somethingElse(...), you can read it like a sentence:

Hey Prisma client, on the User table, please find many rows that match this.

Let's see it.

Decision 4: After prisma., you name the table

We called these "models" in the schema, and they map one to one with the lowercase property on the client. So model User becomes prisma.user, model Pet becomes prisma.pet. Simple rule.

prisma.user;  // the users table
prisma.pet;   // the pets table

Nothing happens yet. We are just pointing at a table. The action comes next.

Decision 5: After the model, you pick a command

Each model has the same set of commands, because every table can be read, written to, updated, and deleted. The names are boring on purpose, which is good, because boring is easy to remember.

prisma.user.findMany();    // get a list
prisma.user.findUnique();  // get one by a unique field
prisma.user.findFirst();   // get the first that matches
prisma.user.create();      // insert a row
prisma.user.update();      // change a row
prisma.user.delete();      // delete a row
prisma.user.upsert();      // update if it exists, otherwise create
prisma.user.count();       // how many match

That is basically the whole vocabulary. Once you learn it for user, you know it for every other model in your schema.

Decision 6: Inside the command, you describe what you want

Most commands take a single object as the argument, and the keys of that object are little instructions. Think of it as filling out a form.

The most useful keys are:

• where to filter
• data to write
• select to pick which fields come back
• include to pull related rows
• orderBy, take, skip for sorting and paginating

Let's adopt some pets.

Creating a user

const mia = await prisma.user.create({
  data: {
    name: "Mia",
    email: "[e-mail]",
  },
});

data is what we are putting into the row. Notice how we did not pass id or pets. The id is auto, and pets is a relation we have not used yet.

Finding a user

const someone = await prisma.user.findUnique({
  where: { email: "[e-mail]" },
});

where only accepts unique fields here, because findUnique is supposed to return at most one. If you want to filter on anything, use findMany or findFirst.

const miaLikes = await prisma.user.findMany({
  where: {
    name: { contains: "Mia" },
  },
});

That contains is one of many handy filters Prisma gives you. There is equals, not, in, startsWith, endsWith, gt, lt, and so on. Same idea: it is just a key in the form.

Updating

await prisma.user.update({
  where: { id: mia.id },
  data:  { name: "Mia Rose" },
});

Two keys here. where says which row, data says what to change. Reads like English.

Deleting

await prisma.user.delete({
  where: { id: mia.id },
});

Same shape. Just where.

Decision 7: Joining tables with include

Now to the fun part. We promised a translator that handles relations for us. So when we want a user with their pets, we should not have to write a JOIN by hand.

We added a key called include. You hand it the names of the relations you want pulled in, and it returns nested data:

const usersWithPets = await prisma.user.findMany({
  include: {
    pets: true,
  },
});

The result looks like this, naturally nested:

[
  {
    id: 1,
    name: "Mia Rose",
    email: "[e-mail]",
    pets: [
      { id: 1, name: "Whiskers", species: "cat", ownerId: 1, adoptedAt: ... },
      { id: 2, name: "Mochi",    species: "cat", ownerId: 1, adoptedAt: ... },
    ],
  },
  ...
]

Under the hood, Prisma does the right thing for you. It might use a JOIN, it might use two queries and stitch them together, depending on the relation and the database. You do not have to care.

You can go deeper too. Let's get every user, with their pets, and only the pet's name:

const cuteList = await prisma.user.findMany({
  include: {
    pets: {
      select: { name: true },
    },
  },
});

select is the cousin of include. While include says "give me the related rows on top of everything else", select says "give me ONLY these fields". You can mix them, and Prisma's types follow along, so the returned object literally has only the fields you picked. No more guessing what the API returned.

You can even create related rows together, in one call:

await prisma.user.create({
  data: {
    name: "Sam",
    email: "[e-mail]",
    pets: {
      create: [
        { name: "Biscuit", species: "dog" },
        { name: "Pepper",  species: "cat" },
      ],
    },
  },
});

One round trip, two tables, zero SQL written by you. That moment is when most people fall in love with Prisma.

Decision 8: Migrations are part of the story

Schemas change. We add a column, we rename a field, we add a new model. Our ORM should make that safe.

# After changing schema.prisma
npx prisma migrate dev --name added_age_to_pet

This generates a SQL migration file, applies it to your dev database, and regenerates the client so your code knows about the new field. In production you usually run prisma migrate deploy, which only applies already created migrations, no surprises.

Migrations are versioned files in your repo, so your database history lives next to your code history. Future you will be grateful.

A peek under the hood

So what really happens when you write this:

const u = await prisma.user.findUnique({ where: { email: "[e-mail]" } });

Roughly:

1. The Prisma Client (the generated JS) takes your call and builds a structured query, kind of like a JSON description of the request.
2. It sends that to the query engine, a small process or module that ships with Prisma.
3. The engine turns it into actual SQL for your database (Postgres, MySQL, SQLite, SQL Server, MongoDB, and so on).
4. It runs the SQL, gets the rows, and shapes them back into the typed object you expected.
5. You get a fully typed User (or null) back in your await.

That layered design is why Prisma can support different databases without changing your app code, and why the types are so accurate. The shape of every return value is computed from your schema and the exact select or include you used.

Tiny tips that will save you later

• Run npx prisma studio when you want a quick GUI to peek at your data. It feels like cheating.
• Keep one PrismaClient instance across your app. In dev with hot reload, stash it on globalThis so you do not open a hundred connections.
• If a query feels slow, log the SQL with new PrismaClient({ log: ["query"] }) and see what is happening. Indexes still matter.
• Use select aggressively in hot paths so you only fetch what you need.
• Read the error messages. Prisma errors are weirdly nice.

Wrapping up

So that is the whole story. We wanted to stop wrestling with raw SQL. We built a translator. We taught it our database with schema.prisma. We generated a custom client called prisma. We agreed every call follows the same little shape:

prisma.<model>.<command>({ where, data, select, include, ... });

Read it as: client, then table, then verb, then a small form describing the details. That single shape covers reads, writes, updates, deletes, filters, sorts, pagination, and even joins.

Once that shape clicks, Prisma stops feeling like a library you are learning, and starts feeling like a translator you trust.

Happy querying, and give your pets a treat from me.