Prisma ORM

O que é Prisma?

Prisma é um ORM (Object-Relational Mapping) moderno para Node.js e TypeScript que substitui ORMs tradicionais. Consiste em três ferramentas principais: Prisma Client (type-safe query builder), Prisma Migrate (sistema de migrações) e Prisma Studio (GUI para banco de dados).

Por que utilizamos Prisma na IngenioLab?

Type Safety: Tipos TypeScript gerados automaticamente
Developer Experience: Autocompletar inteligente e IntelliSense
Query Performance: Queries otimizadas e lazy loading
Schema-First: Schema único como fonte da verdade
Multi-database: Suporte para PostgreSQL, MySQL, SQLite, SQL Server
Migrations: Sistema de migrações robusto e versionado

Instalação e Configuração

1. Instalação:

# Prisma CLI e client
npm install prisma @prisma/client

# Inicializar projeto Prisma
npx prisma init

2. Estrutura gerada:

projeto/
├── prisma/
│   ├── schema.prisma      # Schema do banco
│   ├── migrations/        # Migrações SQL
│   └── seed.ts           # Script de seed
├── .env                  # Variáveis de ambiente
└── node_modules/
    └── .prisma/          # Cliente gerado

3. Configuração inicial do schema:

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
  output   = "../node_modules/.prisma/client"
}

datasource db {
  provider = "postgresql" // ou "mysql", "sqlite"
  url      = env("DATABASE_URL")
}

4. Variável de ambiente:

# .env
DATABASE_URL="postgresql://user:password@localhost:5432/ingeniolab?schema=public"

Schema Prisma - Padrões IngenioLab

1. Modelos Básicos:

// prisma/schema.prisma
model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String?
  password  String
  role      UserRole @default(USER)
  isActive  Boolean  @default(true)
  
  // Timestamps
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  
  // Relacionamentos
  posts     Post[]
  profile   Profile?
  
  @@map("users") // Nome da tabela no banco
}

model Profile {
  id       String  @id @default(cuid())
  bio      String?
  avatar   String?
  website  String?
  
  // Foreign Key
  userId   String  @unique
  user     User    @relation(fields: [userId], references: [id], onDelete: Cascade)
  
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  
  @@map("profiles")
}

model Post {
  id          String      @id @default(cuid())
  title       String
  content     String?
  slug        String      @unique
  published   Boolean     @default(false)
  publishedAt DateTime?
  
  // Foreign Keys
  authorId    String
  author      User        @relation(fields: [authorId], references: [id])
  categoryId  String?
  category    Category?   @relation(fields: [categoryId], references: [id])
  
  // Many-to-many
  tags        Tag[]
  
  createdAt   DateTime    @default(now())
  updatedAt   DateTime    @updatedAt
  
  @@map("posts")
  @@index([slug])
  @@index([authorId])
}

model Category {
  id          String @id @default(cuid())
  name        String @unique
  description String?
  
  posts       Post[]
  
  @@map("categories")
}

model Tag {
  id    String @id @default(cuid())
  name  String @unique
  
  posts Post[]
  
  @@map("tags")
}

enum UserRole {
  USER
  ADMIN
  MODERATOR
}

2. Relacionamentos Avançados:

model Company {
  id       String @id @default(cuid())
  name     String
  cnpj     String @unique
  
  employees Employee[]
  projects  Project[]
  
  @@map("companies")
}

model Employee {
  id       String @id @default(cuid())
  name     String
  email    String @unique
  position String
  salary   Decimal
  
  companyId String
  company   Company @relation(fields: [companyId], references: [id])
  
  // Self-relation (manager)
  managerId String?
  manager   Employee? @relation("ManagerEmployee", fields: [managerId], references: [id])
  subordinates Employee[] @relation("ManagerEmployee")
  
  // Many-to-many com tabela intermediária
  projectAssignments ProjectAssignment[]
  
  @@map("employees")
}

model Project {
  id          String   @id @default(cuid())
  name        String
  description String?
  startDate   DateTime
  endDate     DateTime?
  budget      Decimal?
  
  companyId   String
  company     Company  @relation(fields: [companyId], references: [id])
  
  assignments ProjectAssignment[]
  
  @@map("projects")
}

// Tabela intermediária com campos extras
model ProjectAssignment {
  id           String    @id @default(cuid())
  role         String
  hourlyRate   Decimal?
  startDate    DateTime  @default(now())
  endDate      DateTime?
  
  employeeId   String
  employee     Employee  @relation(fields: [employeeId], references: [id])
  projectId    String
  project      Project   @relation(fields: [projectId], references: [id])
  
  @@unique([employeeId, projectId])
  @@map("project_assignments")
}

Cliente Prisma - Instanciação

1. Configuração do Cliente:

// src/lib/prisma.ts
import { PrismaClient } from '@prisma/client'
import { env } from '../utils/env'

// Singleton pattern para evitar múltiplas instâncias
const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined
}

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

if (env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma

// Graceful shutdown
process.on('beforeExit', async () => {
  await prisma.$disconnect()
})

export default prisma

2. Extensões do Cliente (Middleware):

// src/lib/prisma-extended.ts
import { PrismaClient } from '@prisma/client'
import bcrypt from 'bcryptjs'

const prisma = new PrismaClient()

// Middleware para hash de senhas
prisma.$use(async (params, next) => {
  if (params.model === 'User') {
    if (params.action === 'create' || params.action === 'update') {
      const data = params.args.data
      if (data.password) {
        data.password = await bcrypt.hash(data.password, 10)
      }
    }
  }
  return next(params)
})

// Middleware para soft delete
prisma.$use(async (params, next) => {
  // Interceptar delete e transformar em update
  if (params.action === 'delete') {
    params.action = 'update'
    params.args.data = { deletedAt: new Date() }
  }
  
  // Filtrar registros deletados em find operations
  if (params.action === 'findMany' || params.action === 'findFirst') {
    if (!params.args.where) params.args.where = {}
    params.args.where.deletedAt = null
  }
  
  return next(params)
})

export { prisma }

Operações CRUD com Padrões

1. Service Layer Pattern:

// src/services/userService.ts
import { prisma } from '../lib/prisma'
import { Prisma } from '@prisma/client'
import bcrypt from 'bcryptjs'

export class UserService {
  // CREATE
  async createUser(data: Prisma.UserCreateInput) {
    try {
      const user = await prisma.user.create({
        data,
        select: {
          id: true,
          email: true,
          name: true,
          role: true,
          createdAt: true
        }
      })
      return user
    } catch (error) {
      if (error instanceof Prisma.PrismaClientKnownRequestError) {
        if (error.code === 'P2002') {
          throw new Error('Email já está em uso')
        }
      }
      throw error
    }
  }

  // READ with filtering and pagination
  async getUsers(params: {
    skip?: number
    take?: number
    where?: Prisma.UserWhereInput
    orderBy?: Prisma.UserOrderByWithRelationInput
  }) {
    const { skip, take, where, orderBy } = params
    
    const [users, total] = await prisma.$transaction([
      prisma.user.findMany({
        skip,
        take,
        where,
        orderBy,
        select: {
          id: true,
          email: true,
          name: true,
          role: true,
          isActive: true,
          createdAt: true,
          _count: {
            select: { posts: true }
          }
        }
      }),
      prisma.user.count({ where })
    ])

    return {
      users,
      pagination: {
        total,
        page: Math.floor((skip || 0) / (take || 10)) + 1,
        limit: take || 10,
        hasNext: (skip || 0) + (take || 10) < total
      }
    }
  }

  // READ by ID with relations
  async getUserById(id: string) {
    const user = await prisma.user.findUnique({
      where: { id },
      include: {
        profile: true,
        posts: {
          where: { published: true },
          orderBy: { createdAt: 'desc' },
          take: 5
        },
        _count: {
          select: {
            posts: true
          }
        }
      }
    })

    if (!user) {
      throw new Error('Usuário não encontrado')
    }

    return user
  }

  // UPDATE
  async updateUser(id: string, data: Prisma.UserUpdateInput) {
    try {
      const user = await prisma.user.update({
        where: { id },
        data,
        select: {
          id: true,
          email: true,
          name: true,
          role: true,
          updatedAt: true
        }
      })
      return user
    } catch (error) {
      if (error instanceof Prisma.PrismaClientKnownRequestError) {
        if (error.code === 'P2025') {
          throw new Error('Usuário não encontrado')
        }
      }
      throw error
    }
  }

  // DELETE (soft delete)
  async deleteUser(id: string) {
    await prisma.user.update({
      where: { id },
      data: { 
        isActive: false,
        deletedAt: new Date()
      }
    })
  }

  // Operações complexas
  async authenticateUser(email: string, password: string) {
    const user = await prisma.user.findUnique({
      where: { email },
      select: {
        id: true,
        email: true,
        password: true,
        name: true,
        role: true,
        isActive: true
      }
    })

    if (!user || !user.isActive) {
      throw new Error('Credenciais inválidas')
    }

    const isValidPassword = await bcrypt.compare(password, user.password)
    if (!isValidPassword) {
      throw new Error('Credenciais inválidas')
    }

    // Não retornar a senha
    const { password: _, ...userWithoutPassword } = user
    return userWithoutPassword
  }
}

export const userService = new UserService()

2. Queries Avançadas:

// src/services/postService.ts
import { prisma } from '../lib/prisma'
import { Prisma } from '@prisma/client'

export class PostService {
  // Busca com full-text search
  async searchPosts(query: string, filters: {
    categoryId?: string
    authorId?: string
    published?: boolean
    tags?: string[]
  }) {
    const posts = await prisma.post.findMany({
      where: {
        AND: [
          {
            OR: [
              { title: { contains: query, mode: 'insensitive' } },
              { content: { contains: query, mode: 'insensitive' } }
            ]
          },
          { published: filters.published ?? true },
          filters.categoryId ? { categoryId: filters.categoryId } : {},
          filters.authorId ? { authorId: filters.authorId } : {},
          filters.tags ? {
            tags: {
              some: {
                name: { in: filters.tags }
              }
            }
          } : {}
        ]
      },
      include: {
        author: {
          select: { name: true, email: true }
        },
        category: {
          select: { name: true }
        },
        tags: {
          select: { name: true }
        }
      },
      orderBy: [
        { publishedAt: 'desc' },
        { createdAt: 'desc' }
      ]
    })

    return posts
  }

  // Agregações e estatísticas
  async getPostStats() {
    const stats = await prisma.post.aggregate({
      _count: {
        id: true
      },
      _avg: {
        // Se tivesse campo views
        // views: true
      },
      where: {
        published: true
      }
    })

    const postsByCategory = await prisma.post.groupBy({
      by: ['categoryId'],
      _count: {
        id: true
      },
      where: {
        published: true
      }
    })

    const topAuthors = await prisma.user.findMany({
      select: {
        id: true,
        name: true,
        _count: {
          select: {
            posts: {
              where: { published: true }
            }
          }
        }
      },
      orderBy: {
        posts: {
          _count: 'desc'
        }
      },
      take: 10
    })

    return {
      totalPosts: stats._count.id,
      postsByCategory,
      topAuthors
    }
  }

  // Raw queries quando necessário
  async getPostsWithCustomSQL() {
    const posts = await prisma.$queryRaw`
      SELECT p.*, u.name as author_name, c.name as category_name
      FROM posts p
      LEFT JOIN users u ON p.author_id = u.id
      LEFT JOIN categories c ON p.category_id = c.id
      WHERE p.published = true
      ORDER BY p.created_at DESC
      LIMIT 10
    `
    
    return posts
  }
}

Migrations e Schema Evolution

1. Comandos de Migration:

# Gerar e aplicar migration
npx prisma migrate dev --name add-user-profile

# Aplicar migrations em produção
npx prisma migrate deploy

# Reset do banco (desenvolvimento)
npx prisma migrate reset

# Visualizar estado das migrations
npx prisma migrate status

# Resolver conflitos de migration
npx prisma migrate resolve --applied "20231201000000_migration_name"

2. Schema Changes - Exemplo de evolução:

// Adicionando campos opcionais (safe)
model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String?
  password  String
  
  // Novos campos opcionais
  phone     String?  // ✅ Safe - campo opcional
  avatar    String?  // ✅ Safe - campo opcional
  settings  Json?    // ✅ Safe - campo opcional
  
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  
  @@map("users")
}

// Adicionando campos obrigatórios (requer default ou migration manual)
model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String?
  password  String
  
  // Campo obrigatório novo - precisa de default
  status    UserStatus @default(ACTIVE) // ✅ Com default
  
  @@map("users")
}

enum UserStatus {
  ACTIVE
  INACTIVE
  SUSPENDED
}

3. Data Migrations (Seeds):

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

const prisma = new PrismaClient()

async function main() {
  // Limpar dados existentes (desenvolvimento)
  await prisma.post.deleteMany()
  await prisma.user.deleteMany()
  await prisma.category.deleteMany()

  // Criar categorias
  const categories = await prisma.$transaction([
    prisma.category.create({
      data: { name: 'Tecnologia', description: 'Posts sobre tecnologia' }
    }),
    prisma.category.create({
      data: { name: 'Design', description: 'Posts sobre design' }
    })
  ])

  // Criar usuários
  const adminUser = await prisma.user.create({
    data: {
      email: 'admin@ingeniolab.com',
      name: 'Admin User',
      password: await bcrypt.hash('admin123', 10),
      role: 'ADMIN'
    }
  })

  // Criar posts
  await prisma.post.createMany({
    data: [
      {
        title: 'Primeiro Post',
        content: 'Conteúdo do primeiro post',
        slug: 'primeiro-post',
        published: true,
        authorId: adminUser.id,
        categoryId: categories[0].id
      },
      {
        title: 'Segundo Post',
        content: 'Conteúdo do segundo post',
        slug: 'segundo-post',
        published: false,
        authorId: adminUser.id,
        categoryId: categories[1].id
      }
    ]
  })

  console.log('Seed completed!')
}

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

4. Executar seed:

# Executar seed
npx prisma db seed

# Configurar no package.json
{
  "prisma": {
    "seed": "tsx prisma/seed.ts"
  }
}

Otimizações e Performance

1. Connection Pooling:

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

export const prisma = new PrismaClient({
  datasources: {
    db: {
      url: process.env.DATABASE_URL
    }
  }
})

// Para aplicações serverless (Lambda, Vercel)
export const prismaEdge = new PrismaClient({
  datasources: {
    db: {
      url: process.env.DATABASE_URL
    }
  }
})

2. Query Optimization:

// ❌ N+1 Problem
const users = await prisma.user.findMany()
for (const user of users) {
  const posts = await prisma.post.findMany({
    where: { authorId: user.id }
  })
}

// ✅ Include/Select optimization
const users = await prisma.user.findMany({
  include: {
    posts: {
      select: {
        id: true,
        title: true,
        published: true
      }
    }
  }
})

// ✅ Usando select apenas campos necessários
const users = await prisma.user.findMany({
  select: {
    id: true,
    name: true,
    email: true,
    posts: {
      select: {
        id: true,
        title: true
      },
      where: { published: true },
      take: 5
    }
  }
})

3. Índices Customizados:

model Post {
  id        String   @id @default(cuid())
  title     String
  content   String?
  published Boolean  @default(false)
  authorId  String
  
  @@index([published, authorId]) // Índice composto
  @@index([title(ops: raw("gin_trgm_ops"))], type: Gin) // Full-text search (PostgreSQL)
  @@fulltext([title, content]) // Full-text search (MySQL)
  @@map("posts")
}

Debugging e Monitoring

1. Query Logging:

const prisma = new PrismaClient({
  log: [
    { level: 'query', emit: 'event' },
    { level: 'error', emit: 'stdout' },
    { level: 'info', emit: 'stdout' },
    { level: 'warn', emit: 'stdout' }
  ]
})

prisma.$on('query', (e) => {
  console.log('Query: ' + e.query)
  console.log('Params: ' + e.params)
  console.log('Duration: ' + e.duration + 'ms')
})

2. Prisma Studio:

# Abrir interface visual do banco
npx prisma studio