Prisma ORM Basics: Type-Safe Database Access for Node.js

Quick Answer

Prisma is an ORM for Node.js and TypeScript. You describe your tables in a schema.prisma file, run prisma migrate to build them, and prisma generate creates a fully typed client. Every findMany, create, or update call is checked against the schema, so a misspelled column or a wrong type fails when you compile, not in production.

What Prisma actually does

An ORM (object-relational mapper) lets you read and write database rows as ordinary objects instead of writing SQL by hand. Prisma is an ORM, but it works differently from older tools like Sequelize or TypeORM, where your models are classes you write and keep in sync with the database yourself.

Prisma has three parts:

• The schema - a single schema.prisma file describing every model, field, and relation. This is the source of truth.
• Prisma Migrate - turns each schema change into a timestamped SQL migration file you commit to git and replay on every environment.
• Prisma Client - a query builder Prisma generates from the schema. Because it is generated, the types cannot drift from the real columns.

The trade-off is a code-generation step: every schema change means re-running prisma generate. In return you get autocomplete for every field and a compile error the moment a query stops matching the database. Prisma supports PostgreSQL, MySQL, SQLite, SQL Server, CockroachDB, and MongoDB. The examples here use SQLite because it needs zero setup - just a file on disk.

The schema and your first migration

Install Prisma and create the starter files:

npm install prisma --save-dev
npm install @prisma/client
npx prisma init --datasource-provider sqlite

That writes prisma/schema.prisma and a .env file holding DATABASE_URL="file:./dev.db". Recent versions of prisma init scaffold a newer client generator that expects a bundler; for a plain Node project, set the generator to prisma-client-js and define two models:

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

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

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  createdAt DateTime @default(now())
  posts     Post[]
}

model Post {
  id        Int     @id @default(autoincrement())
  title     String
  published Boolean @default(false)
  author    User    @relation(fields: [authorId], references: [id])
  authorId  Int
}

@id marks the primary key, @default(autoincrement()) auto-numbers it, @unique adds a unique index, and a trailing ? makes a field nullable. The posts Post[] field and the @relation block define a one-to-many link. Now create the database:

npx prisma migrate dev --name init

This writes prisma/migrations/<timestamp>_init/migration.sql, applies it to dev.db, and runs prisma generate for you.

Querying with the generated client

Import the client, create one instance, and reuse it across your app:

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

const prisma = new PrismaClient();

// Create a user and two posts in one call
const asha = await prisma.user.create({
  data: {
    email: "asha@example.com",
    name: "Asha",
    posts: {
      create: [
        { title: "Hello Prisma", published: true },
        { title: "Draft post" },
      ],
    },
  },
  include: { posts: true },
});

// Read with a filter
const livePosts = await prisma.post.findMany({
  where: { published: true },
  orderBy: { id: "desc" },
});

// Update one row, delete another
await prisma.post.update({ where: { id: 1 }, data: { published: true } });
await prisma.post.delete({ where: { id: 2 } });

await prisma.$disconnect();

Every result is typed from the schema. asha.email is string, asha.name is string | null because the field is optional, and asha.posts exists only because you passed include. Ask for a field that is not in the schema and the code will not compile; pass a number where a string belongs and you get an error before the query runs. In a long-running server, create the PrismaClient once at module scope - a new instance per request will exhaust the database connection pool.

Relations: include vs select

Two options control which related data comes back, and they behave differently:

• include keeps all of the model's normal fields and adds the relation. include: { posts: true } gives you the whole user plus their posts.
• select replaces the field list - you get only what you name. select: { email: true, posts: { select: { title: true } } } returns an email and a list of titles, nothing else.

const summary = await prisma.user.findMany({
  select: {
    email: true,
    _count: { select: { posts: true } },
  },
});
// summary[0] is { email: string, _count: { posts: number } }

You cannot use include and select at the same level - Prisma throws a PrismaClientValidationError. Nested writes go through the relation too: update a user with posts: { create: {...} } to add a post, posts: { connect: { id } } to link an existing one, or posts: { deleteMany: {} } to clear them. Prefer select in real endpoints so you return only the columns the client needs.

The N+1 query trap

This is the performance bug everyone writes at least once. You fetch a list, then loop over it and query again for each row:

const users = await prisma.user.findMany();

for (const user of users) {
  const posts = await prisma.post.findMany({
    where: { authorId: user.id },
  });
}

With 10 users that is 11 queries: one for the list, then one per user. At 1,000 rows it is 1,001 round trips, each carrying network latency. Turn on logging with new PrismaClient({ log: ["query"] }) and you will watch them scroll past.

The fix is to let Prisma load the relation in one step:

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

That is 2 queries no matter how many users come back - Prisma fetches the users, fetches all their posts with a single WHERE authorId IN (...), and stitches them together in memory. The rule: if you are calling a Prisma method inside a map or for over earlier results, you almost always want include, select, or one query with an in filter instead.

Two things that will bite you

Editing the schema does nothing on its own. Add a field to schema.prisma and your app will not see it until you run npx prisma migrate dev - which updates the database - and the client is regenerated. migrate dev does both. But if you only run prisma db push, or pull a schema change from git, or edit the file and restart the server, run npx prisma generate yourself. Reference a field the client does not know about and Prisma throws PrismaClientValidationError: Unknown argument.

findUnique returns null, it does not throw. When nothing matches, findUnique and findFirst return null:

const user = await prisma.user.findUnique({
  where: { email: "nobody@example.com" },
});
// user is null - reading user.name here throws
//   "Cannot read properties of null"

Either handle the null, or use findUniqueOrThrow, which throws a PrismaClientKnownRequestError with code P2025 when the row is missing. Related: inserting a duplicate value into a @unique column throws that same error type with code P2002 and error.meta.target naming the field - catch it to return a clean "email already registered" message instead of a 500.

Frequently Asked Questions