Prisma ORM: Building a Type-Safe Database Layer

Most TypeScript developers write careful, type-checked application code, then the moment they touch the database, all of that discipline disappears. A query returns any. A column gets accessed with a string key. A typo in a field name silently returns undefined instead of a compile error.

Prisma changes that relationship. Your database schema becomes the source of truth for your TypeScript types — the same way an interface shapes an object, schema.prisma shapes every query result you’ll ever write.

I’m going to walk through the building blocks: the schema file, the generated client, CRUD operations, relations, and migrations. The thread running through all of it is how each piece contributes to the type-safety story.

On this page

• The schema file: one place to describe your data
• Generating the client: where TypeScript types come from
• CRUD operations and what the generated types look like
• Relations: querying connected models without losing types
• Migrations: keeping your schema and database in sync
• Where Prisma’s type safety has limits
• The schema as your contract

The schema file: one place to describe your data

Prisma’s entry point is a file called schema.prisma. It sits in a prisma/ directory at your project root. Everything flows from it — the database connection, the generated client, and the shape of your data.

A minimal schema for a blog looks like this:

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

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

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

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

Three blocks do three different jobs. The datasource block points Prisma at your database — in this case PostgreSQL, with the connection string pulled from an environment variable. The generator block tells Prisma what to generate when you run prisma generate (the standard prisma-client-js generates the TypeScript client). The model blocks define your tables.

Each field in a model has a name, a type, and optional attributes. The @id attribute marks the primary key. @default(autoincrement()) sets a database-level default. The ? after String makes a field nullable. The @unique attribute adds a unique constraint on the email column.

If you’ve read my introduction to PostgreSQL for developers, that vocabulary will feel familiar — Prisma’s field types map directly to database column types.

Generating the client: where TypeScript types come from

Once your schema is defined, you run:

npx prisma generate

This reads schema.prisma and writes a typed TypeScript client into node_modules/@prisma/client. The generation step is what connects your schema definition to your TypeScript code.

What gets generated isn’t a generic database driver — it’s a client shaped exactly like your schema. For the models above, you get a PrismaClient with prisma.user and prisma.post namespaces, and every method on those namespaces knows the exact shape of its inputs and outputs.

The generated types look like this for the User model:

export type User = {
  id: number;
  email: string;
  name: string | null;
  createdAt: Date;
};

Prisma derives this type directly from your schema. The field name name is String? in Prisma’s schema language, so it becomes string | null in TypeScript. createdAt is DateTime, which maps to Date. These are not annotations you write — they’re generated every time you run prisma generate.

Before you continue: What do you expect the TypeScript type to be for a Boolean @default(false) field in your schema?

It becomes boolean — never boolean | null, because the field has no ? modifier. Prisma’s type derivation is strict about optional versus required.

Check this before moving on

• Your schema.prisma has a datasource block with a valid provider and url
• Your schema.prisma has a generator client block
• You’ve run npx prisma generate after the last schema change
• @prisma/client is in your dependencies, not devDependencies

CRUD operations and what the generated types look like

You instantiate the client once at the module level:

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

const prisma = new PrismaClient();

Every operation on prisma.user is now typed against the User model. Here’s what the four most common operations look like:

Reading records:

// findMany returns User[]
const users = await prisma.user.findMany();

// findUnique returns User | null
const user = await prisma.user.findUnique({
  where: { email: "alice@example.com" },
});

Creating a record:

// The data argument is typed — missing required fields or wrong types are compile errors
const newUser = await prisma.user.create({
  data: {
    email: "bob@example.com",
    name: "Bob",
  },
});
// newUser is typed as User

Updating a record:

const updated = await prisma.post.update({
  where: { id: 42 },
  data: { published: true },
});
// updated is typed as Post

Deleting a record:

await prisma.post.delete({
  where: { id: 42 },
});

The key detail is that data in create and update is not Record<string, any>. It’s a generated input type that knows exactly which fields are required, which are optional, and which types each field accepts. If you try to pass published: "yes" instead of published: true, TypeScript catches it before the code runs.

The same applies to where. The where argument for findUnique only accepts fields marked @unique or @id. If you pass where: { name: "Bob" } and name has no unique constraint, TypeScript will reject it.

Relations: querying connected models without losing types

This is where Prisma’s type system gets interesting. When you query a User with their posts included, the return type changes based on what you asked for.

const userWithPosts = await prisma.user.findUnique({
  where: { id: 1 },
  include: { posts: true },
});
// userWithPosts is User & { posts: Post[] } | null

Without include, posts isn’t on the returned object at all — and TypeScript knows this. If you try to access userWithPosts.posts without the include, the compiler stops you.

You can go deeper with nested includes:

const user = await prisma.user.findUnique({
  where: { id: 1 },
  include: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: "desc" },
    },
  },
});

The select alternative lets you fetch only specific fields:

const emailsOnly = await prisma.user.findMany({
  select: { email: true },
});
// emailsOnly is { email: string }[]

Notice the return type isn’t User[] — it’s { email: string }[]. Prisma’s generated types narrow the result to exactly what you selected. This matters for runtime performance (you’re not loading columns you don’t need) and for TypeScript correctness (you can’t access fields you didn’t fetch).

For creating nested records, Prisma gives you a relational API that avoids raw foreign keys:

const newPost = await prisma.post.create({
  data: {
    title: "Understanding database indexes",
    author: {
      connect: { id: 1 },
    },
  },
});

The connect syntax links the new post to an existing user. If you prefer, create instead of connect creates the related record in the same query. Both are typed — connect accepts a where object valid for the related model, and create accepts the same input type as prisma.user.create.

If you want to understand the database concepts behind these relations — foreign keys, join tables — my PostgreSQL fundamentals post covers the underlying mechanics.

Migrations: keeping your schema and database in sync

Defining a model in schema.prisma doesn’t change your actual database. For that, you need Prisma Migrate.

The development workflow uses a single command:

npx prisma migrate dev --name add_post_status

This does three things: compares your current schema against the migration history, generates a SQL migration file in prisma/migrations/, and applies that file to your development database. The --name flag labels the migration — you’ll see it in the migration directory as something like 20250524143200_add_post_status.

If you add a new field to your Post model:

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  status    String   @default("draft")   // new field
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
  createdAt DateTime @default(now())
}

Running prisma migrate dev generates and applies an ALTER TABLE statement. The migration file is SQL that you can read and review before it runs. This is different from ORMs that apply migrations automatically without showing you the SQL.

For production deployments, you use a separate command that applies pending migrations without generating new ones:

npx prisma migrate deploy

The migration history lives in prisma/migrations/ and should be committed to version control. Each migration file is the record of exactly what changed and when.

Where Prisma’s type safety has limits

Prisma’s type safety is strong, but it isn’t total. A few situations break out of the typed model.

Raw queries. When you use prisma.$queryRaw, the return type is unknown[] by default. You can add a generic type parameter, but Prisma doesn’t verify that the type you provide matches what the query actually returns:

// The generic tells TypeScript the shape, but Prisma can't check it against the SQL
const result = await prisma.$queryRaw<{ count: bigint }[]>`
  SELECT COUNT(*) as count FROM "Post"
`;

JSON fields. If a model has a Json field, Prisma types it as Prisma.JsonValue — a union of all JSON-compatible types. You can store and retrieve anything that’s valid JSON, but reading a specific property requires a manual type assertion.

The select / include problem at the function boundary. When you pass a partial query result to a function that expects a full model type, TypeScript will refuse to accept it:

function processUser(user: User) {
  // ...
}

const partialUser = await prisma.user.findUnique({
  where: { id: 1 },
  select: { email: true },
});

processUser(partialUser); // TypeScript error: missing fields

This is correct behaviour — partialUser really is missing the id, name, and createdAt fields. The fix is either to broaden the function signature using Prisma’s generated utility types, or to ensure the query fetches the full model.

The Prisma namespace exports utility types for exactly this case. Prisma.UserGetPayload<T> lets you describe the exact shape a function expects, including which relations are included.

If you want to go deeper on utility types and type narrowing in TypeScript, the pattern here is the same as generic constraints — my post on TypeScript generics covers the underlying concept.

The schema as your contract

The interesting shift Prisma makes is moving the database description out of SQL comments and migration scripts and into a file that TypeScript can read.

Before Prisma (or with a raw query builder), the columns in your users table exist in the database, maybe in a migration file, maybe in separate TypeScript interface declarations — and those three things can drift apart without any warning. You rename a column in the database, forget to update the TypeScript interface, and the mismatch only surfaces at runtime.

With Prisma, schema.prisma is the single source of truth. The database gets its structure from Prisma Migrate’s SQL output. Your TypeScript code gets its types from prisma generate. Both derive from the same file, so they can’t drift without you explicitly changing the schema.

The practical effect: when you add a required field to a model, every create call in your codebase that doesn’t include that field becomes a compile error. You find those gaps at build time instead of in a 3am production alert.

Start with schema.prisma, keep it in version control alongside your migrations, and run prisma generate as part of your build step. If you’re coming from raw SQL, the REST API with Node.js and Express post is a good reference for the Express layer that typically sits on top of this kind of database client.

Sources

• What is Prisma ORM — Prisma documentation
• Models — Prisma v7 schema reference
• Type safety overview — Prisma v7 client documentation
• Getting started with Prisma Migrate — Prisma documentation
• CRUD operations reference — Prisma v7 client