If you’ve ever used raw SQL queries or traditional ORMs like Sequelize, you know the pain of debugging queries that error at runtime. Typo in a column name? Error only shows up when the application runs.

Prisma takes a different approach: type-safe database access with auto-generated types from your schema. This means if there’s a typo or wrong query, TypeScript immediately warns you before the code runs.

What is an ORM?

ORM (Object-Relational Mapping) is an abstraction layer between your application and database. Instead of writing raw SQL:

SELECT * FROM users WHERE id = 1;

You can use more readable syntax:

const user = await prisma.user.findUnique({ where: { id: 1 } });

Why Prisma?

Prisma isn’t an ordinary ORM. Here’s what makes Prisma stand out:

Feature	Prisma	Traditional ORM
Type Safety	100% type-safe	Partial or manual
Schema Definition	Prisma Schema Language	Decorators/JS Objects
Migrations	Automated & versioned	Manual or semi-auto
Query Building	Fluent API with autocomplete	String-based or builder
Tooling	Prisma Studio, CLI, VS Code extension	Varies

Installation and Setup

1. Init Project

mkdir prisma-tutorial && cd prisma-tutorial
npm init -y
npm install typescript ts-node @types/node -D
npx tsc --init

2. Install Prisma

npm install prisma -D
npm install @prisma/client

3. Initialize Prisma

npx prisma init

This will create:

prisma/schema.prisma - Main schema file
.env - Environment variables file

4. Configure Database

Edit .env with your database connection string:

# PostgreSQL
DATABASE_URL="postgresql://user:password@localhost:5432/mydb?schema=public"

# MySQL
DATABASE_URL="mysql://user:password@localhost:3306/mydb"

# SQLite (suitable for development)
DATABASE_URL="file:./dev.db"

Prisma Schema: The Heart of Prisma

The prisma/schema.prisma file is where you define your database structure:

// filepath: prisma/schema.prisma

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

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

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  password  String
  role      Role     @default(USER)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  posts    Post[]
  profile  Profile?
  comments Comment[]
}

enum Role {
  USER
  ADMIN
  MODERATOR
}

Attribute Explanation

Attribute	Function
`@id`	Primary key
`@unique`	Value must be unique
`@default()`	Default value
`autoincrement()`	Auto increment for integer
`now()`	Timestamp when record is created
`@updatedAt`	Auto update timestamp when record is updated
`?`	Optional field (nullable)

Relations: 1:1, 1:N, and N:M

One-to-One (1:1)

User has one Profile:

model User {
  id      Int      @id @default(autoincrement())
  email   String   @unique
  profile Profile?
}

model Profile {
  id     Int    @id @default(autoincrement())
  bio    String?
  avatar String?
  
  userId Int  @unique
  user   User @relation(fields: [userId], references: [id])
}

One-to-Many (1:N)

User has many Posts:

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

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

Many-to-Many (N:M)

Post has many Categories, Category belongs to many Posts:

model Post {
  id         Int        @id @default(autoincrement())
  title      String
  categories Category[]
}

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

Prisma automatically creates a junction table. If you need an explicit junction table with additional fields:

model Post {
  id         Int            @id @default(autoincrement())
  title      String
  categories PostCategory[]
}

model Category {
  id    Int            @id @default(autoincrement())
  name  String         @unique
  posts PostCategory[]
}

model PostCategory {
  postId     Int
  categoryId Int
  assignedAt DateTime @default(now())

  post     Post     @relation(fields: [postId], references: [id])
  category Category @relation(fields: [categoryId], references: [id])

  @@id([postId, categoryId])
}

Complete Schema Example

Here’s a complete schema for a blog platform:

// filepath: prisma/schema.prisma

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

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

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  password  String
  role      Role     @default(USER)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  posts    Post[]
  profile  Profile?
  comments Comment[]
}

model Profile {
  id     Int     @id @default(autoincrement())
  bio    String?
  avatar String?
  userId Int     @unique
  user   User    @relation(fields: [userId], references: [id], onDelete: Cascade)
}

model Post {
  id          Int        @id @default(autoincrement())
  title       String
  slug        String     @unique
  content     String?
  excerpt     String?
  published   Boolean    @default(false)
  publishedAt DateTime?
  createdAt   DateTime   @default(now())
  updatedAt   DateTime   @updatedAt

  authorId   Int
  author     User         @relation(fields: [authorId], references: [id])
  categories Category[]
  comments   Comment[]
}

model Category {
  id    Int    @id @default(autoincrement())
  name  String @unique
  slug  String @unique
  posts Post[]
}

model Comment {
  id        Int      @id @default(autoincrement())
  content   String
  createdAt DateTime @default(now())

  postId Int
  post   Post @relation(fields: [postId], references: [id], onDelete: Cascade)

  authorId Int
  author   User @relation(fields: [authorId], references: [id])
}

enum Role {
  USER
  ADMIN
  MODERATOR
}

Migrations

Create Migration

After modifying the schema:

npx prisma migrate dev --name init

This will:

Create a new migration in prisma/migrations/
Apply the migration to the database
Generate Prisma Client

Migrate in Production

npx prisma migrate deploy

Reset Database (Development Only)

npx prisma migrate reset

Prisma Client: CRUD Operations

Generate Client

npx prisma generate

Basic Queries

// filepath: src/index.ts
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

async function main() {
  // Create
  const user = await prisma.user.create({
    data: {
      email: 'john@example.com',
      name: 'John Doe',
      password: 'hashed_password',
    },
  });

  // Read
  const users = await prisma.user.findMany();
  const userById = await prisma.user.findUnique({
    where: { id: 1 },
  });

  // Update
  const updatedUser = await prisma.user.update({
    where: { id: 1 },
    data: { name: 'John Updated' },
  });

  // Delete
  const deletedUser = await prisma.user.delete({
    where: { id: 1 },
  });
}

main()
  .catch(console.error)
  .finally(() => prisma.$disconnect());

Query with Relations

// Include relations
const userWithPosts = await prisma.user.findUnique({
  where: { id: 1 },
  include: {
    posts: true,
    profile: true,
  },
});

// Select specific fields
const userWithPostTitles = await prisma.user.findUnique({
  where: { id: 1 },
  select: {
    id: true,
    name: true,
    posts: {
      select: {
        title: true,
        published: true,
      },
    },
  },
});

Filtering and Sorting

// Multiple filters
const posts = await prisma.post.findMany({
  where: {
    published: true,
    author: {
      email: {
        contains: '@example.com',
      },
    },
    OR: [
      { title: { contains: 'Prisma' } },
      { content: { contains: 'database' } },
    ],
  },
  orderBy: {
    createdAt: 'desc',
  },
  take: 10,
  skip: 0,
});

Create with Relations

// Create user with profile
const userWithProfile = await prisma.user.create({
  data: {
    email: 'jane@example.com',
    name: 'Jane Doe',
    password: 'hashed_password',
    profile: {
      create: {
        bio: 'Software developer',
        avatar: '/images/jane.jpg',
      },
    },
  },
  include: {
    profile: true,
  },
});

// Create post with existing categories
const post = await prisma.post.create({
  data: {
    title: 'My First Post',
    slug: 'my-first-post',
    content: 'Hello World!',
    authorId: 1,
    categories: {
      connect: [{ id: 1 }, { id: 2 }],
    },
  },
});

Transactions

// Interactive transaction
const result = await prisma.$transaction(async (tx) => {
  const user = await tx.user.create({
    data: {
      email: 'new@example.com',
      name: 'New User',
      password: 'hashed_password',
    },
  });

  const post = await tx.post.create({
    data: {
      title: 'First Post',
      slug: 'first-post',
      authorId: user.id,
    },
  });

  return { user, post };
});

Prisma Studio

Visual database browser:

npx prisma studio

Opens in browser at http://localhost:5555.

Performance Optimization

1. Select Only Needed Fields

// ❌ Fetches all fields
const users = await prisma.user.findMany();

// ✅ Only fetch needed fields
const users = await prisma.user.findMany({
  select: {
    id: true,
    name: true,
    email: true,
  },
});

2. Pagination

const page = 1;
const pageSize = 10;

const [users, total] = await Promise.all([
  prisma.user.findMany({
    skip: (page - 1) * pageSize,
    take: pageSize,
    orderBy: { createdAt: 'desc' },
  }),
  prisma.user.count(),
]);

const pagination = {
  page,
  pageSize,
  total,
  totalPages: Math.ceil(total / pageSize),
};

3. Avoid N+1 Queries

// ❌ N+1 problem
const posts = await prisma.post.findMany();
for (const post of posts) {
  const author = await prisma.user.findUnique({
    where: { id: post.authorId },
  });
}

// ✅ Single query with include
const posts = await prisma.post.findMany({
  include: {
    author: true,
  },
});

4. Connection Pooling

For serverless or high-traffic:

// filepath: src/lib/prisma.ts
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient({
  datasources: {
    db: {
      url: process.env.DATABASE_URL,
    },
  },
  log: process.env.NODE_ENV === 'development' 
    ? ['query', 'error', 'warn'] 
    : ['error'],
});

export default prisma;

For Vercel/serverless, use Prisma Accelerate or a connection pooler like PgBouncer.

5. Batch Operations

// ❌ Multiple round trips
for (const id of userIds) {
  await prisma.user.update({
    where: { id },
    data: { lastSeen: new Date() },
  });
}

// ✅ Single query
await prisma.user.updateMany({
  where: { id: { in: userIds } },
  data: { lastSeen: new Date() },
});

Best Practices

1. Project Structure

src/
├── lib/
│   └── prisma.ts          # Singleton Prisma client
├── repositories/          # Data access layer
│   ├── user.repository.ts
│   └── post.repository.ts
├── services/              # Business logic
│   ├── user.service.ts
│   └── post.service.ts
└── ...

prisma/
├── schema.prisma
├── seed.ts
└── migrations/

2. Repository Pattern

// filepath: src/repositories/user.repository.ts
import prisma from '@/lib/prisma';
import { Prisma } from '@prisma/client';

export const userRepository = {
  async findById(id: number) {
    return prisma.user.findUnique({
      where: { id },
      select: {
        id: true,
        email: true,
        name: true,
        role: true,
        profile: true,
      },
    });
  },

  async findByEmail(email: string) {
    return prisma.user.findUnique({
      where: { email },
    });
  },

  async create(data: Prisma.UserCreateInput) {
    return prisma.user.create({ data });
  },

  async update(id: number, data: Prisma.UserUpdateInput) {
    return prisma.user.update({
      where: { id },
      data,
    });
  },

  async delete(id: number) {
    return prisma.user.delete({
      where: { id },
    });
  },

  async findManyWithPagination({
    page = 1,
    pageSize = 10,
    where,
    orderBy,
  }: {
    page?: number;
    pageSize?: number;
    where?: Prisma.UserWhereInput;
    orderBy?: Prisma.UserOrderByWithRelationInput;
  }) {
    const [users, total] = await Promise.all([
      prisma.user.findMany({
        where,
        orderBy: orderBy ?? { createdAt: 'desc' },
        skip: (page - 1) * pageSize,
        take: pageSize,
        select: {
          id: true,
          email: true,
          name: true,
          role: true,
          createdAt: true,
        },
      }),
      prisma.user.count({ where }),
    ]);

    return {
      users,
      pagination: {
        page,
        pageSize,
        total,
        totalPages: Math.ceil(total / pageSize),
      },
    };
  },
};

3. Error Handling

import { Prisma } from '@prisma/client';

async function createUser(data: Prisma.UserCreateInput) {
  try {
    return await prisma.user.create({ data });
  } catch (error) {
    if (error instanceof Prisma.PrismaClientKnownRequestError) {
      // P2002: Unique constraint violation
      if (error.code === 'P2002') {
        throw new Error('Email already exists');
      }
      // P2025: Record not found
      if (error.code === 'P2025') {
        throw new Error('User not found');
      }
    }
    throw error;
  }
}

4. Type-Safe Includes with Satisfies

import { Prisma } from '@prisma/client';

const postWithAuthor = {
  include: {
    author: {
      select: {
        id: true,
        name: true,
        email: true,
      },
    },
    categories: true,
  },
} satisfies Prisma.PostDefaultArgs;

type PostWithAuthor = Prisma.PostGetPayload<typeof postWithAuthor>;

async function getPost(id: number): Promise<PostWithAuthor | null> {
  return prisma.post.findUnique({
    where: { id },
    ...postWithAuthor,
  });
}

5. Environment-Specific Configuration

// filepath: src/lib/prisma.ts
import { PrismaClient } from '@prisma/client';

const prismaClientSingleton = () => {
  return new PrismaClient({
    log:
      process.env.NODE_ENV === 'development'
        ? ['query', 'error', 'warn']
        : ['error'],
    errorFormat: process.env.NODE_ENV === 'development' ? 'pretty' : 'minimal',
  });
};

declare global {
  var prisma: undefined | ReturnType<typeof prismaClientSingleton>;
}

const prisma = globalThis.prisma ?? prismaClientSingleton();

if (process.env.NODE_ENV !== 'production') {
  globalThis.prisma = prisma;
}

export default prisma;

Seeding the Database

// filepath: prisma/seed.ts
import { PrismaClient } from '@prisma/client';
import bcrypt from 'bcryptjs';

const prisma = new PrismaClient();

async function main() {
  // Create admin user
  const admin = await prisma.user.upsert({
    where: { email: 'admin@example.com' },
    update: {},
    create: {
      email: 'admin@example.com',
      name: 'Admin',
      password: await bcrypt.hash('password123', 12),
      role: 'ADMIN',
    },
  });

  // Create categories
  const categories = await Promise.all([
    prisma.category.upsert({
      where: { slug: 'technology' },
      update: {},
      create: { name: 'Technology', slug: 'technology' },
    }),
    prisma.category.upsert({
      where: { slug: 'lifestyle' },
      update: {},
      create: { name: 'Lifestyle', slug: 'lifestyle' },
    }),
  ]);

  console.log({ admin, categories });
}

main()
  .catch(console.error)
  .finally(() => prisma.$disconnect());

Add to package.json:

{
  "prisma": {
    "seed": "ts-node prisma/seed.ts"
  }
}

Run with:

npx prisma db seed

Conclusion

Prisma is a game-changer for database access in TypeScript:

Type safety - Errors detected at compile time
Developer experience - Autocomplete, Prisma Studio, excellent docs
Migrations - Versioned, automated, predictable
Performance - Query optimization, connection pooling support
Ecosystem - Prisma Accelerate, Pulse, and growing community

Start with a simple schema, understand relations and queries, then scale up with the best practices discussed.