Prisma Tutorial: Type-Safe Database Access in Node.js

What is Prisma?

Prisma is an open-source ORM for Node.js and TypeScript that replaces hand-written SQL and loosely typed query builders with a single, declarative source of truth: the Prisma schema. From that one file Prisma generates a type-safe client, manages your migration history, and can introspect an existing database into models. It supports PostgreSQL, MySQL, MariaDB, SQL Server, SQLite, and CockroachDB. MongoDB is supported too, but at the time of writing MongoDB projects need to stay on Prisma 6.

The pitch is correctness at compile time. Because the client is generated from your actual schema, your editor knows every model, field, and relation. A typo in a column name or a wrong field type becomes a TypeScript error before the code ever runs, instead of an HTTP 500 error in production. You get autocomplete for queries, typed return values that match exactly what you selected, and refactors that the compiler can follow across the codebase.

Prisma has three core pieces: Prisma Client (the generated query API you import in your app), Prisma Migrate (schema-driven SQL migrations), and Prisma Studio (a visual table browser run with npx prisma studio). They all read the same schema, so the model you write and the queries you run never drift apart.

How Prisma works (key concepts)

Everything starts in prisma/schema.prisma. That file has three kinds of blocks. The datasource block names your database provider. The generator block tells Prisma what to emit - in Prisma 7 you use the prisma-client provider with a required output path. The rest of the file is your model definitions: each model maps to a table, each field to a column, and @relation attributes describe foreign keys.

When you change the schema, you run a migration. Prisma diffs your schema against the current migration history, generates a plain SQL migration file under prisma/migrations/, and applies it. Those SQL files are committed to source control, so every environment converges on the same structure. To verify the diff safely, Prisma Migrate uses a temporary shadow database during development.

After migrating you run prisma generate, which compiles the schema into the typed client. Your application imports that generated client and calls methods such as prisma.user.findMany(). Prisma 7 also separates configuration: the database connection URL lives in prisma.config.ts (or is supplied to a driver adapter in code), not hard-coded in the schema. Prisma 7 makes driver adapters mandatory, so you pass an adapter like PrismaPg to the client constructor and the underlying pg driver handles the actual connection.

Installing Prisma

Start from a Node.js project and add the Prisma CLI as a dev dependency plus the runtime client and a driver adapter. For PostgreSQL that means @prisma/adapter-pg and the pg driver. Prisma 7 no longer loads .env files by itself, so the examples below also install dotenv.

npm init -y
npm install prisma typescript tsx @types/node --save-dev
npm install @prisma/client @prisma/adapter-pg pg dotenv
npx prisma init --datasource-provider postgresql

prisma init scaffolds a prisma/ directory with a starter schema.prisma, a prisma.config.ts, and a .env file holding DATABASE_URL. Set that variable to your real connection string, for example postgresql://user:password@localhost:5432/mydb?schema=public.

The generated config file points Prisma at your schema and reads the connection URL from the environment:

// prisma.config.ts
import "dotenv/config";
import { defineConfig, env } from "prisma/config";

export default defineConfig({
  schema: "prisma/schema.prisma",
  migrations: {
    path: "prisma/migrations",
  },
  datasource: {
    url: env("DATABASE_URL"),
  },
});

Modeling and querying a blog schema

Define two related models in prisma/schema.prisma. Note the Prisma 7 generator block with its required output, and the one-to-many relation between User and Post.

generator client {
  provider = "prisma-client"
  output   = "../generated/prisma"
}

datasource db {
  provider = "postgresql"
}

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

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

Create and apply the first migration, then generate the client. In Prisma 7 migrate dev no longer triggers generate for you, so run it explicitly:

npx prisma migrate dev --name init
npx prisma generate

Now write to the database. Because driver adapters are required, you construct PrismaPg with the connection string and hand it to PrismaClient. Notice the import comes from the generated output directory, not from @prisma/client:

// src/main.ts
import "dotenv/config";
import { PrismaPg } from "@prisma/adapter-pg";
import { PrismaClient } from "../generated/prisma/client";

const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL });
const prisma = new PrismaClient({ adapter });

async function main() {
  // Create a user with a related post in one nested write
  await prisma.user.create({
    data: {
      email: "ada@example.com",
      name: "Ada",
      posts: {
        create: { title: "Hello Prisma", published: true },
      },
    },
  });

  // Read users back, including only their published posts
  const usersWithPosts = await prisma.user.findMany({
    include: {
      posts: { where: { published: true } },
    },
  });

  console.dir(usersWithPosts, { depth: null });
}

main()
  .then(() => prisma.$disconnect())
  .catch(async (e) => {
    console.error(e);
    await prisma.$disconnect();
    process.exit(1);
  });

Run it with npx tsx src/main.ts. The return type of findMany already includes the nested posts array because you passed include - hover over usersWithPosts and the editor shows the exact shape. This close link between schema, migration, and typed query is the main reason to use Prisma.

When to use Prisma

Reach for Prisma when you are building a TypeScript or JavaScript service and want database access that the compiler can verify. It works well for CRUD-heavy APIs, apps with evolving schemas that benefit from versioned migrations, and teams that value autocomplete and safe refactors over writing SQL by hand. The generated types help most as a project grows and more developers touch the data layer.

It is a weaker fit when you need exotic, database-specific SQL features, hand-tuned queries with complex window functions or CTEs, or when an existing codebase already standardizes on raw SQL or another ORM. Prisma does let you run raw SQL with prisma.$queryRaw (and TypedSQL can add types to those queries) for the cases the query API does not cover - but if most of your queries are raw SQL, the abstraction adds little. For new Node.js backends on a supported database, though, Prisma is one of the fastest ways to get a safe, maintainable data layer.