Prisma ORM

Use Prisma to interact with your database using type-safe TypeScript instead of raw SQL

Written by Andrew Lee Jenkins16 min read1 activity

Pixl presents

Hand-writing raw SQL strings in your TypeScript? Bold. Prisma gives you typed queries and catches typos before they hit production.Hand-writing raw SQL strings in your TypeScript? Bold. Prisma gives you typed queries and catches typos before they hit production.

Pixl feeds a scribbled note into a brass machine that outputs neat coloured bars — Prisma turns your TypeScript into SQL for you

You know SQL now, and it's powerful. Writing raw SQL strings inside your TypeScript code is kind of a pain though. You get zero auto-complete and zero type checking, and a typo can sit there quietly until your app is actually running. Prisma fixes all of that by giving you a type-safe way to talk to your database in plain TypeScript.

What is an ORM?#

An ORM (Object-Relational Mapping) is a tool that lets you work with your database in your own programming language. You write TypeScript, and Prisma quietly turns it into optimized SQL behind the scenes.

// Without Prisma (raw SQL - no type safety!)
const result = await db.query(
  "SELECT * FROM users WHERE email = $1",
  [email]
);
 
// With Prisma (type-safe, auto-complete, error checking!)
const user = await prisma.user.findUnique({
  where: { email },
});

Installing Prisma (Pin It to Version 7)#

This lesson uses Prisma 7, which is the version the Prisma docs call fully supported right now. Prisma 8 is out as a release candidate, and here's the gotcha. A plain npm install prisma now grabs the version 8 tool, which works totally differently and doesn't have the commands in this lesson. So pin version 7 on purpose.

npm install --save-dev prisma@7
npm install @prisma/client@7 @prisma/adapter-pg pg dotenv
npx prisma init --datasource-provider postgresql --output ../generated/prisma

That last command creates your schema file, a .env file and a small Prisma config file (more on that in a sec).

The Prisma Schema#

Everything starts with the Prisma schema file (prisma/schema.prisma). That's where you define your database models, which are basically your tables.

// prisma/schema.prisma
 
generator client {
  provider = "prisma-client"
  output   = "../generated/prisma"
}
 
datasource db {
  provider = "postgresql"
}
 
model User {
  id        Int      @id @default(autoincrement())
  name      String
  email     String   @unique
  age       Int?
  posts     Post[]
  createdAt DateTime @default(now())
}
 
model Post {
  id        Int      @id @default(autoincrement())
  title     String
  body      String
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
  createdAt DateTime @default(now())
}

Here's what each piece of that schema is doing.

generator tells Prisma to generate a TypeScript client, and output says which folder to put it in
datasource says what kind of database you're using (PostgreSQL here)
model defines a table along with its columns and relationships
@id marks the primary key
@unique adds a unique constraint
@default sets a default value
Int? uses the ? to say the field is optional (nullable)
Post[] sets up a one-to-many relationship (a user has many posts)
@relation spells out how models connect through foreign keys

So where does the database address go? Not in the schema anymore. It lives in that Prisma config file prisma init made for you (newer 7.x releases name it prisma7.config.ts, older ones prisma.config.ts, and Prisma finds either one).

// prisma7.config.ts
import "dotenv/config";
import { defineConfig } from "prisma/config";
 
export default defineConfig({
  schema: "prisma/schema.prisma",
  migrations: {
    path: "prisma/migrations",
  },
  datasource: {
    url: process.env["DATABASE_URL"],
  },
});

The actual connection string goes in your .env file as DATABASE_URL, so the password stays out of your code.

Migrations: Evolving Your Database#

Whenever you change your Prisma schema, your actual database has to catch up. That's the job of migrations.

# Create a migration after changing your schema
npx prisma migrate dev --name add-posts-table
 
# This does two things:
# 1. Creates a SQL migration file you can review
# 2. Applies the migration to your development database
 
# Then regenerate the Prisma client so your types match
npx prisma generate

Don't skip that second command. In Prisma 7, migrate dev no longer regenerates the client for you, and if you forget, your code won't know about the new columns. (ask me how i know)

Migration files get saved in prisma/migrations/ and you should commit them to Git. That way you've got a complete history of every change your database ever went through.

Setting Up Prisma Client#

Once your schema is defined and you've run npx prisma generate, create an instance of the client. In Prisma 7 you hand it a driver adapter, which is the lil piece that actually talks to PostgreSQL.

// lib/db.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 });
 
export default prisma;

See how it imports from ../generated/prisma/client? That's the output folder from your schema, not the @prisma/client package older tutorials use.

Now you can import prisma anywhere in your app and start talking to the database.

CRUD Operations with Prisma#

Create (Insert)#

// Create a single user
const user = await prisma.user.create({
  data: {
    name: 'Alice',
    email: '[email protected]',
    age: 28,
  },
});
// user.id is automatically set!
 
// Create a user with a post at the same time
const userWithPost = await prisma.user.create({
  data: {
    name: 'Bob',
    email: '[email protected]',
    posts: {
      create: {
        title: 'My First Post',
        body: 'Hello world!',
      },
    },
  },
  include: { posts: true },
});

Read (Query)#

// Find one user by unique field
const user = await prisma.user.findUnique({
  where: { email: '[email protected]' },
});
 
// Find many users with filtering
const activeUsers = await prisma.user.findMany({
  where: {
    age: { gte: 18 },
    posts: { some: { published: true } },
  },
  orderBy: { createdAt: 'desc' },
  take: 10,  // LIMIT 10
});
 
// Include related data
const userWithPosts = await prisma.user.findUnique({
  where: { id: 1 },
  include: { posts: true },
});

Update#

// Update a single user
const updated = await prisma.user.update({
  where: { id: 1 },
  data: { name: 'Alice Smith' },
});
 
// Update many records at once
const count = await prisma.post.updateMany({
  where: { authorId: 1, published: false },
  data: { published: true },
});

Delete#

// Delete a single user
await prisma.user.delete({
  where: { id: 1 },
});
 
// Delete many records
await prisma.post.deleteMany({
  where: { published: false },
});

Filtering with Prisma#

Prisma gives you type-safe filter operators that map straight onto SQL.

const products = await prisma.product.findMany({
  where: {
    // Comparison operators
    price: { gt: 10, lte: 100 },    // > 10 AND <= 100
    name: {
      startsWith: 'Smart',           // LIKE 'Smart%'
      contains: 'phone',             // AND LIKE '%phone%'
    },
    status: { in: ['active', 'sale'] }, // IN ('active', 'sale')
    deletedAt: null,                  // IS NULL
 
    // Logical operators
    OR: [
      { category: 'electronics' },
      { category: 'gadgets' },
    ],
    NOT: { status: 'archived' },
  },
});

Working with Relations#

Prisma makes related data feel pretty natural to work with.

// Include (eager loading) - fetches related data in one query
const user = await prisma.user.findUnique({
  where: { id: 1 },
  include: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: 'desc' },
      take: 5,
    },
  },
});
 
// Select (pick specific fields) - optimizes the query
const userSummary = await prisma.user.findUnique({
  where: { id: 1 },
  select: {
    name: true,
    email: true,
    posts: {
      select: { title: true, createdAt: true },
    },
  },
});

Upsert: Create or Update#

The upsert operation is made for "create it if it's not there, update it if it is."

// Create the preference if it doesn't exist, update if it does
const preference = await prisma.userPreference.upsert({
  where: { userId: 1 },
  create: {
    userId: 1,
    theme: 'dark',
    language: 'en',
  },
  update: {
    theme: 'dark',
  },
});

Transactions#

Group several operations together so they all succeed or all fail.

// Transfer money between accounts (all-or-nothing)
const transfer = await prisma.$transaction([
  prisma.account.update({
    where: { id: 1 },
    data: { balance: { decrement: 100 } },
  }),
  prisma.account.update({
    where: { id: 2 },
    data: { balance: { increment: 100 } },
  }),
]);

Quick Reference: SQL to Prisma#

SQL	Prisma
`SELECT * FROM users`	`prisma.user.findMany()`
`WHERE id = 1`	`where: { id: 1 }`
`WHERE age > 18`	`where: { age: { gt: 18 } }`
`ORDER BY name ASC`	`orderBy: { name: 'asc' }`
`LIMIT 10`	`take: 10`
`OFFSET 20`	`skip: 20`
`INSERT INTO`	`prisma.user.create({ data: {...} })`
`UPDATE ... SET`	`prisma.user.update({ where, data })`
`DELETE FROM`	`prisma.user.delete({ where })`
`JOIN`	`include: { posts: true }`
`COUNT(*)`	`prisma.user.count()`

TL;DR#

Pixl adds a floor to a wooden model building beside a tied stack of blueprints — Migrations record every change to your database's shape

Prisma is a type-safe ORM that turns your TypeScript into SQL
The Prisma schema (schema.prisma) defines your models, relationships and constraints
Use prisma migrate dev to apply schema changes while you're developing, then prisma generate to refresh the client
Pin Prisma to version 7 (prisma@7) while Prisma 8 is still a release candidate
The CRUD operations are create, findMany/findUnique, update and delete
Filter with type-safe operators like gt, contains, in, NOT and OR
Pull in related data with include, or trim it down with select
Use upsert when you need "create or update"
Transactions ($transaction) make sure several operations succeed or fail together

What's Next?#

You've got raw SQL AND Prisma under your belt now. In the last lesson of this chapter you'll pick up database best practices... indexing for speed, normalization for clean data, protection against SQL injection and backup plans that keep your data safe.

This lesson ends with a short activity.