Saltar al contenido principal

🗄️ Prisma ORM

Visión General​

PuntoHack utiliza Prisma como ORM (Object-Relational Mapping) para interactuar con la base de datos PostgreSQL de Supabase de forma type-safe.

Configuración​

Schema​

Archivo: prisma/schema.prisma

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

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

model Profile {
  id        String   @id @default(cuid())
  userId    String   @unique // Clerk ID
  email     String   @unique
  name      String
  role      Role
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  
  @@index([userId])
  @@index([email])
}

Variables de Entorno​

DATABASE_URL=postgresql://user:password@host:port/database?sslmode=require

Generar Client​

pnpm db:generate

Aplicar Schema​

pnpm db:push

Uso Básico​

Singleton Pattern​

Archivo: src/core/db.ts

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

const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined;
};

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

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

Queries Básicas​

import { db } from '@/core/db';

// Find unique
const profile = await db.profile.findUnique({
  where: { id: 'profile-id' },
});

// Find many
const hackathons = await db.hackathon.findMany({
  where: { status: 'REGISTRATION' },
  orderBy: { createdAt: 'desc' },
});

// Create
const newHackathon = await db.hackathon.create({
  data: {
    name: 'My Hackathon',
    slug: 'my-hackathon',
    // ... otros campos
  },
});

// Update
const updated = await db.hackathon.update({
  where: { id: 'hackathon-id' },
  data: { status: 'RUNNING' },
});

// Delete
await db.hackathon.delete({
  where: { id: 'hackathon-id' },
});

Relaciones​

Include​

const hackathon = await db.hackathon.findUnique({
  where: { id: 'hackathon-id' },
  include: {
    organizer: true,
    criteria: true,
    teams: {
      include: {
        members: {
          include: {
            profile: true,
          },
        },
      },
    },
  },
});

Select​

const hackathon = await db.hackathon.findUnique({
  where: { id: 'hackathon-id' },
  select: {
    id: true,
    name: true,
    status: true,
    organizer: {
      select: {
        id: true,
        name: true,
      },
    },
  },
});

Transacciones​

Simple Transaction​

const result = await db.$transaction(async (tx) => {
  const hackathon = await tx.hackathon.create({
    data: { /* ... */ },
  });
  
  const criteria = await tx.criterion.createMany({
    data: criteriaData.map(c => ({
      ...c,
      hackathonId: hackathon.id,
    })),
  });
  
  return { hackathon, criteria };
});

Interactive Transaction​

await db.$transaction(async (tx) => {
  // Operaciones que dependen unas de otras
  const team = await tx.team.create({ data: { /* ... */ } });
  await tx.teamMember.create({
    data: {
      teamId: team.id,
      profileId: profileId,
      isLeader: true,
    },
  });
});

Filtros y Ordenamiento​

Where​

const hackathons = await db.hackathon.findMany({
  where: {
    status: 'REGISTRATION',
    organizerId: 'organizer-id',
    createdAt: {
      gte: new Date('2025-01-01'),
    },
  },
});

Order By​

const hackathons = await db.hackathon.findMany({
  orderBy: [
    { createdAt: 'desc' },
    { name: 'asc' },
  ],
});

Paginación​

const hackathons = await db.hackathon.findMany({
  skip: 0,
  take: 10,
  orderBy: { createdAt: 'desc' },
});

Agregaciones​

Count​

const count = await db.hackathon.count({
  where: { status: 'REGISTRATION' },
});

_count​

const hackathon = await db.hackathon.findUnique({
  where: { id: 'hackathon-id' },
  include: {
    _count: {
      select: {
        participations: true,
        teams: true,
        submissions: true,
      },
    },
  },
});

Upsert​

const score = await db.score.upsert({
  where: {
    submissionId_judgeId_criterionId: {
      submissionId: 'submission-id',
      judgeId: 'judge-id',
      criterionId: 'criterion-id',
    },
  },
  create: {
    submissionId: 'submission-id',
    judgeId: 'judge-id',
    criterionId: 'criterion-id',
    value: 8,
  },
  update: {
    value: 9,
  },
});

Mejores Prácticas​

1. Usar Include Selectivamente​

// ✅ Bueno: Solo incluir lo necesario
const hackathon = await db.hackathon.findUnique({
  where: { id },
  include: {
    criteria: true,
    _count: { select: { teams: true } },
  },
});

// ❌ Malo: Incluir todo
const hackathon = await db.hackathon.findUnique({
  where: { id },
  include: {
    organizer: true,
    criteria: true,
    teams: {
      include: {
        members: {
          include: {
            profile: true,
          },
        },
      },
    },
    // ... muchas más relaciones
  },
});

2. Usar Select para Campos Específicos​

// ✅ Bueno: Solo seleccionar campos necesarios
const profile = await db.profile.findUnique({
  where: { id },
  select: {
    id: true,
    name: true,
    email: true,
  },
});

3. Índices en Campos Frecuentes​

model Hackathon {
  @@index([status])
  @@index([organizerId])
  @@index([slug])
}

4. Validar Antes de Operaciones​

const existing = await db.hackathon.findUnique({
  where: { slug },
});

if (existing) {
  throw new Error('Slug already exists');
}

Próximos Pasos​

Siguiente: Supabase Realtime