Database Integration (Prisma/SQL)

Difficulty: Intermediate

Question

How do you integrate a database with a Node.js application using Prisma? Explain the schema, migrations, and query patterns.

Answer

Prisma is a modern ORM for Node.js and TypeScript that provides type-safe database access, automatic migrations, and a declarative schema language. It replaces raw SQL queries with a fluent, type-safe API.

The Prisma schema (schema.prisma) defines your data models, relations, and database configuration. From this schema, Prisma generates a typed client with methods like findUnique, findMany, create, update, and delete.

Migrations track database schema changes over time. When you modify the schema, prisma migrate dev generates SQL migration files and applies them. Each migration is versioned and can be applied in sequence on any environment.

Prisma supports relations (one-to-one, one-to-many, many-to-many), nested writes, transactions, raw SQL queries, and connection pooling. The generated types ensure that your queries match your schema at compile time.

Code examples

Prisma Schema Definition

// 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)
  posts     Post[]
  profile   Profile?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

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

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

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

enum Role {
  USER
  ADMIN
}

The schema defines models, relations, and constraints. Prisma generates TypeScript types and a query client from this file.

CRUD Operations with Prisma Client

const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();

// Create with relation
const user = await prisma.user.create({
  data: {
    email: 'alice@example.com',
    name: 'Alice',
    password: hashedPassword,
    profile: {
      create: { bio: 'Developer' }  // Nested create
    }
  },
  include: { profile: true }  // Include relation in response
});

// Find with filtering and pagination
const users = await prisma.user.findMany({
  where: {
    role: 'USER',
    name: { contains: 'ali', mode: 'insensitive' },
  },
  select: { id: true, name: true, email: true },
  orderBy: { createdAt: 'desc' },
  skip: 0,
  take: 10,
});

// Update
const updated = await prisma.user.update({
  where: { id: 1 },
  data: { name: 'Alice Smith' },
});

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

// Transaction
const [post, count] = await prisma.$transaction([
  prisma.post.create({ data: { title: 'New Post', authorId: 1 } }),
  prisma.post.count({ where: { authorId: 1 } }),
]);

Prisma provides type-safe CRUD with nested operations, filtering, pagination, and transactions. select/include control what data is returned.

Advanced Queries and Raw SQL

const { PrismaClient, Prisma } = require('@prisma/client');
const prisma = new PrismaClient();

// Aggregation
const stats = await prisma.post.aggregate({
  _count: true,
  _avg: { views: true },
  where: { published: true },
});

// Group by
const byRole = await prisma.user.groupBy({
  by: ['role'],
  _count: { id: true },
});

// Upsert (create if not exists, update if exists)
const user = await prisma.user.upsert({
  where: { email: 'bob@example.com' },
  update: { name: 'Bob Updated' },
  create: { email: 'bob@example.com', name: 'Bob', password: hash },
});

// Raw SQL when needed
const result = await prisma.$queryRaw`
  SELECT u.name, COUNT(p.id) as post_count
  FROM "User" u
  LEFT JOIN "Post" p ON p."authorId" = u.id
  WHERE u.role = ${Prisma.sql`'USER'`}
  GROUP BY u.name
  ORDER BY post_count DESC
  LIMIT 10
`;

// Connection cleanup
process.on('beforeExit', async () => {
  await prisma.$disconnect();
});

Prisma handles aggregations, group by, and upserts natively. Raw SQL is available for complex queries that the ORM cannot express.

Key points

• Run 'npx prisma migrate dev' from the directory with prisma.config.ts to create migrations
• Use select to return specific fields (exclude password!) and include for relations
• Prisma Client is a singleton - create one instance and reuse it across the app
• Use $transaction for operations that must succeed or fail together

Concepts covered

Prisma, ORM, Migrations, Relations, Raw SQL