Guia Practica de Prisma ORM - Dominando las Operaciones de Base de Datos con Tipado Seguro

2025.12.02

Prisma es un ORM de nueva generacion para Node.js/TypeScript. Cuenta con consultas con tipado seguro, definicion intuitiva de esquemas y potentes funciones de migracion, siendo ideal para el desarrollo de aplicaciones modernas. En este articulo, explicamos como utilizar Prisma de manera practica.

Estructura Basica de Prisma

flowchart TB
    App["Your Application<br/>(TypeScript)"]
    Client["Prisma Client<br/>(Generated Type-Safe API)"]
    Engine["Prisma Engine<br/>(Query Engine + Rust)"]
    DB["Database<br/>(PostgreSQL, MySQL, SQLite, etc.)"]

    App --> Client --> Engine --> DB

Configuracion

1. Instalacion e Inicializacion

# Instalacion de Prisma CLI y cliente
npm install prisma --save-dev
npm install @prisma/client

# Inicializacion de Prisma (para PostgreSQL)
npx prisma init --datasource-provider postgresql

2. Configuracion de Variables de Entorno

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

# Para produccion (con pool de conexiones)
DATABASE_URL="postgresql://user:password@db.example.com:5432/mydb?schema=public&connection_limit=10&pool_timeout=30"

Diseno de Esquemas

Definicion Basica de Esquema

// prisma/schema.prisma

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["fullTextSearch", "filteredRelationCount"]
}

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

// Modelo de Usuario
model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String?
  password  String
  role      Role     @default(USER)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  // Relaciones
  posts     Post[]
  comments  Comment[]
  profile   Profile?
  sessions  Session[]

  @@index([email])
  @@map("users")  // Personalizar nombre de tabla
}

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

  @@map("profiles")
}

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

  // Relaciones
  authorId    String
  author      User      @relation(fields: [authorId], references: [id])
  categories  Category[]
  tags        Tag[]
  comments    Comment[]

  @@index([authorId])
  @@index([slug])
  @@index([published, publishedAt])
  @@map("posts")
}

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

  @@map("categories")
}

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

  @@map("tags")
}

model Comment {
  id        String   @id @default(cuid())
  content   String
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  // Relaciones
  postId    String
  post      Post     @relation(fields: [postId], references: [id], onDelete: Cascade)
  authorId  String
  author    User     @relation(fields: [authorId], references: [id])

  // Relacion auto-referencial (funcion de respuestas)
  parentId  String?
  parent    Comment?  @relation("CommentReplies", fields: [parentId], references: [id])
  replies   Comment[] @relation("CommentReplies")

  @@index([postId])
  @@index([authorId])
  @@map("comments")
}

model Session {
  id           String   @id @default(cuid())
  sessionToken String   @unique
  expires      DateTime
  userId       String
  user         User     @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@map("sessions")
}

// Enumeracion
enum Role {
  USER
  ADMIN
  MODERATOR
}

Funciones Avanzadas de Esquema

// Clave unica compuesta
model OrderItem {
  orderId   String
  productId String
  quantity  Int
  price     Decimal @db.Decimal(10, 2)

  order   Order   @relation(fields: [orderId], references: [id])
  product Product @relation(fields: [productId], references: [id])

  @@id([orderId, productId])  // Clave primaria compuesta
  @@map("order_items")
}

// Campos JSON y arrays
model Product {
  id          String   @id @default(cuid())
  name        String
  description String?
  price       Decimal  @db.Decimal(10, 2)
  metadata    Json?    // Campo JSON
  images      String[] // Array (PostgreSQL)

  orderItems  OrderItem[]

  @@map("products")
}

// Indice para busqueda de texto completo (PostgreSQL)
model Article {
  id      String @id @default(cuid())
  title   String
  content String

  @@index([title, content], type: Gin)  // Indice GIN
  @@map("articles")
}

Migraciones

Flujo Basico de Migraciones

# Desarrollo: detectar cambios de esquema y crear migracion
npx prisma migrate dev --name init

# Solo crear migracion (sin aplicar)
npx prisma migrate dev --create-only --name add_user_avatar

# Produccion: aplicar migraciones
npx prisma migrate deploy

# Verificar estado de migracion
npx prisma migrate status

# Reiniciar base de datos (solo desarrollo)
npx prisma migrate reset

Personalizacion de Migraciones

-- prisma/migrations/20250102_add_full_text_search/migration.sql

-- Agregar SQL manualmente
CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX posts_title_trgm_idx ON posts USING gin (title gin_trgm_ops);
CREATE INDEX posts_content_trgm_idx ON posts USING gin (content gin_trgm_ops);

-- Crear funcion de busqueda
CREATE OR REPLACE FUNCTION search_posts(search_query TEXT)
RETURNS SETOF posts AS $$
  SELECT *
  FROM posts
  WHERE
    title ILIKE '%' || search_query || '%'
    OR content ILIKE '%' || search_query || '%'
  ORDER BY
    CASE
      WHEN title ILIKE search_query THEN 0
      WHEN title ILIKE search_query || '%' THEN 1
      WHEN title ILIKE '%' || search_query || '%' THEN 2
      ELSE 3
    END,
    published_at DESC NULLS LAST;
$$ LANGUAGE SQL STABLE;

Uso del Cliente Prisma

Operaciones CRUD Basicas

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

// Usar instancia global (para hot reload en desarrollo)
const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined;
};

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

if (process.env.NODE_ENV !== 'production') {
  globalForPrisma.prisma = prisma;
}
// services/user.service.ts
import { prisma } from '@/lib/prisma';
import { Prisma, User, Role } from '@prisma/client';
import bcrypt from 'bcryptjs';

// Definicion de tipos
type UserCreateInput = {
  email: string;
  name?: string;
  password: string;
};

type UserWithProfile = Prisma.UserGetPayload<{
  include: { profile: true };
}>;

// Crear usuario
async function createUser(data: UserCreateInput): Promise<User> {
  const hashedPassword = await bcrypt.hash(data.password, 12);

  return prisma.user.create({
    data: {
      email: data.email,
      name: data.name,
      password: hashedPassword,
      profile: {
        create: {},  // Crear perfil vacio simultaneamente
      },
    },
  });
}

// Obtener usuario individual
async function getUserById(id: string): Promise<UserWithProfile | null> {
  return prisma.user.findUnique({
    where: { id },
    include: {
      profile: true,
    },
  });
}

// Obtener usuario por email
async function getUserByEmail(email: string): Promise<User | null> {
  return prisma.user.findUnique({
    where: { email },
  });
}

// Obtener lista de usuarios (con paginacion)
async function getUsers(options: {
  page?: number;
  limit?: number;
  role?: Role;
  search?: string;
}): Promise<{ users: User[]; total: number }> {
  const { page = 1, limit = 10, role, search } = options;
  const skip = (page - 1) * limit;

  const where: Prisma.UserWhereInput = {
    ...(role && { role }),
    ...(search && {
      OR: [
        { email: { contains: search, mode: 'insensitive' } },
        { name: { contains: search, mode: 'insensitive' } },
      ],
    }),
  };

  const [users, total] = await Promise.all([
    prisma.user.findMany({
      where,
      skip,
      take: limit,
      orderBy: { createdAt: 'desc' },
    }),
    prisma.user.count({ where }),
  ]);

  return { users, total };
}

// Actualizar usuario
async function updateUser(
  id: string,
  data: Prisma.UserUpdateInput
): Promise<User> {
  return prisma.user.update({
    where: { id },
    data,
  });
}

// Eliminar usuario (eliminacion en cascada)
async function deleteUser(id: string): Promise<void> {
  await prisma.user.delete({
    where: { id },
  });
}

export const userService = {
  createUser,
  getUserById,
  getUserByEmail,
  getUsers,
  updateUser,
  deleteUser,
};

Consultas con Relaciones

// services/post.service.ts
import { prisma } from '@/lib/prisma';
import { Prisma, Post } from '@prisma/client';

// Definicion de tipo de publicacion (con relaciones)
const postWithRelations = Prisma.validator<Prisma.PostDefaultArgs>()({
  include: {
    author: {
      select: {
        id: true,
        name: true,
        email: true,
        profile: {
          select: { avatar: true },
        },
      },
    },
    categories: true,
    tags: true,
    _count: {
      select: { comments: true },
    },
  },
});

type PostWithRelations = Prisma.PostGetPayload<typeof postWithRelations>;

// Obtener publicaciones publicadas
async function getPublishedPosts(options: {
  page?: number;
  limit?: number;
  categorySlug?: string;
  tagName?: string;
}): Promise<{ posts: PostWithRelations[]; total: number }> {
  const { page = 1, limit = 10, categorySlug, tagName } = options;

  const where: Prisma.PostWhereInput = {
    published: true,
    publishedAt: { lte: new Date() },
    ...(categorySlug && {
      categories: {
        some: { slug: categorySlug },
      },
    }),
    ...(tagName && {
      tags: {
        some: { name: tagName },
      },
    }),
  };

  const [posts, total] = await Promise.all([
    prisma.post.findMany({
      where,
      ...postWithRelations,
      skip: (page - 1) * limit,
      take: limit,
      orderBy: { publishedAt: 'desc' },
    }),
    prisma.post.count({ where }),
  ]);

  return { posts, total };
}

// Obtener publicacion por slug (incrementar vistas)
async function getPostBySlug(slug: string): Promise<PostWithRelations | null> {
  const post = await prisma.post.update({
    where: { slug, published: true },
    data: {
      viewCount: { increment: 1 },
    },
    ...postWithRelations,
  });

  return post;
}

// Crear publicacion
async function createPost(data: {
  title: string;
  content: string;
  authorId: string;
  categoryIds: string[];
  tagNames: string[];
}): Promise<Post> {
  const slug = generateSlug(data.title);

  return prisma.post.create({
    data: {
      title: data.title,
      slug,
      content: data.content,
      excerpt: data.content.slice(0, 200),
      author: { connect: { id: data.authorId } },
      categories: {
        connect: data.categoryIds.map(id => ({ id })),
      },
      tags: {
        connectOrCreate: data.tagNames.map(name => ({
          where: { name },
          create: { name },
        })),
      },
    },
  });
}

// Publicar publicacion
async function publishPost(id: string): Promise<Post> {
  return prisma.post.update({
    where: { id },
    data: {
      published: true,
      publishedAt: new Date(),
    },
  });
}

function generateSlug(title: string): string {
  return title
    .toLowerCase()
    .replace(/[^\w\s-]/g, '')
    .replace(/\s+/g, '-')
    .concat('-', Date.now().toString(36));
}

export const postService = {
  getPublishedPosts,
  getPostBySlug,
  createPost,
  publishPost,
};

Transacciones

// services/order.service.ts
import { prisma } from '@/lib/prisma';
import { Prisma } from '@prisma/client';

interface OrderItem {
  productId: string;
  quantity: number;
}

// Transaccion interactiva
async function createOrder(
  userId: string,
  items: OrderItem[]
): Promise<Order> {
  return prisma.$transaction(async (tx) => {
    // 1. Verificar informacion del producto e inventario
    const products = await tx.product.findMany({
      where: {
        id: { in: items.map(item => item.productId) },
      },
    });

    // Verificacion de inventario
    for (const item of items) {
      const product = products.find(p => p.id === item.productId);
      if (!product) {
        throw new Error(`Product not found: ${item.productId}`);
      }
      if (product.stock < item.quantity) {
        throw new Error(`Insufficient stock for: ${product.name}`);
      }
    }

    // 2. Crear pedido
    const total = items.reduce((sum, item) => {
      const product = products.find(p => p.id === item.productId)!;
      return sum + product.price.toNumber() * item.quantity;
    }, 0);

    const order = await tx.order.create({
      data: {
        userId,
        status: 'PENDING',
        total: new Prisma.Decimal(total),
        items: {
          create: items.map(item => {
            const product = products.find(p => p.id === item.productId)!;
            return {
              productId: item.productId,
              quantity: item.quantity,
              price: product.price,
            };
          }),
        },
      },
      include: {
        items: {
          include: { product: true },
        },
      },
    });

    // 3. Reducir inventario
    await Promise.all(
      items.map(item =>
        tx.product.update({
          where: { id: item.productId },
          data: {
            stock: { decrement: item.quantity },
          },
        })
      )
    );

    return order;
  }, {
    maxWait: 5000,  // Tiempo maximo de espera
    timeout: 10000, // Timeout
    isolationLevel: Prisma.TransactionIsolationLevel.Serializable,
  });
}

// Transaccion por lotes (ejecucion multiple en una sola operacion)
async function transferPoints(
  fromUserId: string,
  toUserId: string,
  amount: number
): Promise<void> {
  await prisma.$transaction([
    prisma.user.update({
      where: { id: fromUserId },
      data: { points: { decrement: amount } },
    }),
    prisma.user.update({
      where: { id: toUserId },
      data: { points: { increment: amount } },
    }),
    prisma.pointTransaction.create({
      data: {
        fromUserId,
        toUserId,
        amount,
        type: 'TRANSFER',
      },
    }),
  ]);
}

Consultas Avanzadas

// Consulta de agregacion
async function getPostStats(authorId: string) {
  return prisma.post.aggregate({
    where: { authorId },
    _count: true,
    _sum: { viewCount: true },
    _avg: { viewCount: true },
    _max: { viewCount: true },
  });
}

// Agrupacion
async function getPostCountByCategory() {
  return prisma.post.groupBy({
    by: ['published'],
    where: { publishedAt: { not: null } },
    _count: { id: true },
    _avg: { viewCount: true },
    orderBy: { _count: { id: 'desc' } },
  });
}

// Consulta SQL cruda
async function searchPosts(query: string) {
  return prisma.$queryRaw<Post[]>`
    SELECT p.*,
           ts_rank(to_tsvector('japanese', title || ' ' || content),
                   plainto_tsquery('japanese', ${query})) as rank
    FROM posts p
    WHERE to_tsvector('japanese', title || ' ' || content)
          @@ plainto_tsquery('japanese', ${query})
    ORDER BY rank DESC
    LIMIT 20
  `;
}

// Busqueda de texto completo (funcion preview)
async function searchPostsFullText(query: string) {
  return prisma.post.findMany({
    where: {
      OR: [
        { title: { search: query } },
        { content: { search: query } },
      ],
    },
    orderBy: {
      _relevance: {
        fields: ['title', 'content'],
        search: query,
        sort: 'desc',
      },
    },
  });
}

Optimizacion de Rendimiento

Resolucion del Problema N+1

// Codigo que genera problema N+1
async function getPostsWithAuthorsBad() {
  const posts = await prisma.post.findMany();

  // Se genera una consulta por cada publicacion (N veces)
  for (const post of posts) {
    const author = await prisma.user.findUnique({
      where: { id: post.authorId },
    });
    console.log(post.title, author?.name);
  }
}

// Obtener relaciones en lote con include
async function getPostsWithAuthorsGood() {
  const posts = await prisma.post.findMany({
    include: {
      author: {
        select: { name: true },
      },
    },
  });

  for (const post of posts) {
    console.log(post.title, post.author.name);
  }
}

// Obtener solo campos necesarios con select
async function getPostTitlesWithAuthors() {
  return prisma.post.findMany({
    select: {
      id: true,
      title: true,
      author: {
        select: { name: true },
      },
    },
  });
}

Configuracion del Pool de Conexiones

// Configuracion para produccion
const prisma = new PrismaClient({
  datasources: {
    db: {
      url: process.env.DATABASE_URL,
    },
  },
  // La configuracion del pool de conexiones se especifica en parametros de URL
  // ?connection_limit=10&pool_timeout=30
});

// Para entornos serverless (Prisma Accelerate)
// Configurar en schema.prisma
// datasource db {
//   provider = "postgresql"
//   url      = env("DATABASE_URL")
//   directUrl = env("DIRECT_URL")  // Para migraciones
// }

Logs de Consultas y Depuracion

// Logs detallados de consultas
const prisma = new PrismaClient({
  log: [
    { emit: 'event', level: 'query' },
    { emit: 'stdout', level: 'error' },
    { emit: 'stdout', level: 'warn' },
  ],
});

// Escuchar eventos de consulta
prisma.$on('query', (e) => {
  console.log('Query:', e.query);
  console.log('Params:', e.params);
  console.log('Duration:', e.duration, 'ms');
});

// Deteccion de consultas lentas
prisma.$on('query', (e) => {
  if (e.duration > 100) {
    console.warn(`Slow query (${e.duration}ms):`, e.query);
  }
});

Estrategia de Pruebas

Configuracion para Pruebas

// tests/helpers/prisma.ts
import { PrismaClient } from '@prisma/client';
import { execSync } from 'child_process';

const TEST_DATABASE_URL = process.env.TEST_DATABASE_URL ||
  'postgresql://test:test@localhost:5432/test_db';

export function createTestPrismaClient(): PrismaClient {
  return new PrismaClient({
    datasources: {
      db: { url: TEST_DATABASE_URL },
    },
  });
}

export async function resetDatabase(prisma: PrismaClient): Promise<void> {
  // Limpiar tablas considerando orden de eliminacion
  const tablenames = await prisma.$queryRaw<
    Array<{ tablename: string }>
  >`SELECT tablename FROM pg_tables WHERE schemaname='public'`;

  for (const { tablename } of tablenames) {
    if (tablename !== '_prisma_migrations') {
      await prisma.$executeRawUnsafe(
        `TRUNCATE TABLE "public"."${tablename}" CASCADE;`
      );
    }
  }
}

// Factory para pruebas
export function createUserFactory(prisma: PrismaClient) {
  return {
    create: async (overrides: Partial<Prisma.UserCreateInput> = {}) => {
      return prisma.user.create({
        data: {
          email: `test-${Date.now()}@example.com`,
          name: 'Test User',
          password: 'hashed_password',
          ...overrides,
        },
      });
    },
  };
}

Pruebas de Integracion

// tests/integration/user.test.ts
import { describe, it, expect, beforeAll, afterAll, beforeEach } from 'vitest';
import { PrismaClient } from '@prisma/client';
import { createTestPrismaClient, resetDatabase, createUserFactory } from '../helpers/prisma';
import { userService } from '@/services/user.service';

describe('UserService', () => {
  let prisma: PrismaClient;
  let userFactory: ReturnType<typeof createUserFactory>;

  beforeAll(async () => {
    prisma = createTestPrismaClient();
    userFactory = createUserFactory(prisma);
  });

  beforeEach(async () => {
    await resetDatabase(prisma);
  });

  afterAll(async () => {
    await prisma.$disconnect();
  });

  describe('createUser', () => {
    it('should create a user with profile', async () => {
      const user = await userService.createUser({
        email: 'new@example.com',
        name: 'New User',
        password: 'password123',
      });

      expect(user.email).toBe('new@example.com');
      expect(user.name).toBe('New User');

      const userWithProfile = await prisma.user.findUnique({
        where: { id: user.id },
        include: { profile: true },
      });

      expect(userWithProfile?.profile).toBeDefined();
    });

    it('should throw on duplicate email', async () => {
      await userFactory.create({ email: 'exists@example.com' });

      await expect(
        userService.createUser({
          email: 'exists@example.com',
          password: 'password123',
        })
      ).rejects.toThrow();
    });
  });

  describe('getUsers', () => {
    it('should return paginated users', async () => {
      // Crear 15 usuarios
      await Promise.all(
        Array.from({ length: 15 }, (_, i) =>
          userFactory.create({ email: `user${i}@example.com` })
        )
      );

      const result = await userService.getUsers({ page: 1, limit: 10 });

      expect(result.users).toHaveLength(10);
      expect(result.total).toBe(15);
    });
  });
});

Mejores Practicas para Produccion

Manejo de Errores

// lib/prisma-errors.ts
import { Prisma } from '@prisma/client';

export class DatabaseError extends Error {
  constructor(
    message: string,
    public code: string,
    public originalError?: Error
  ) {
    super(message);
    this.name = 'DatabaseError';
  }
}

export function handlePrismaError(error: unknown): never {
  if (error instanceof Prisma.PrismaClientKnownRequestError) {
    switch (error.code) {
      case 'P2002':
        throw new DatabaseError(
          `Unique constraint violation on ${error.meta?.target}`,
          'UNIQUE_CONSTRAINT',
          error
        );
      case 'P2025':
        throw new DatabaseError(
          'Record not found',
          'NOT_FOUND',
          error
        );
      case 'P2003':
        throw new DatabaseError(
          'Foreign key constraint failed',
          'FOREIGN_KEY_CONSTRAINT',
          error
        );
      default:
        throw new DatabaseError(
          `Database error: ${error.message}`,
          error.code,
          error
        );
    }
  }

  if (error instanceof Prisma.PrismaClientValidationError) {
    throw new DatabaseError(
      'Invalid data provided',
      'VALIDATION_ERROR',
      error
    );
  }

  throw error;
}

// Ejemplo de uso
async function safeCreateUser(data: UserCreateInput) {
  try {
    return await userService.createUser(data);
  } catch (error) {
    handlePrismaError(error);
  }
}

Health Check y Monitoreo

// lib/health.ts
import { prisma } from './prisma';

export async function checkDatabaseHealth(): Promise<{
  healthy: boolean;
  latency: number;
  error?: string;
}> {
  const start = Date.now();

  try {
    await prisma.$queryRaw`SELECT 1`;
    return {
      healthy: true,
      latency: Date.now() - start,
    };
  } catch (error) {
    return {
      healthy: false,
      latency: Date.now() - start,
      error: error instanceof Error ? error.message : 'Unknown error',
    };
  }
}

// Recoleccion de metricas
export async function getDatabaseMetrics() {
  const [
    userCount,
    postCount,
    activeConnections,
  ] = await Promise.all([
    prisma.user.count(),
    prisma.post.count(),
    prisma.$queryRaw<[{ count: bigint }]>`
      SELECT count(*) FROM pg_stat_activity
      WHERE datname = current_database()
    `.then(r => Number(r[0].count)),
  ]);

  return {
    userCount,
    postCount,
    activeConnections,
    timestamp: new Date().toISOString(),
  };
}

Resumen

Prisma es un ORM moderno que prioriza la seguridad de tipos y la experiencia del desarrollador.

Fortalezas de Prisma

CaracteristicaBeneficio
Consultas con tipado seguroDeteccion de errores en tiempo de compilacion
Esquema intuitivoGestion centralizada con Prisma Schema
Migracion automaticaCambios de esquema seguros
Manejo de relacionesFacil evitar el problema N+1

Consideraciones al Adoptar

  • Usar $queryRaw cuando se necesite SQL complejo
  • Usar createMany/updateMany para procesamiento masivo de datos
  • Prestar atencion al pool de conexiones en entornos serverless

Utilizando Prisma, puedes lograr operaciones de base de datos con tipado seguro y alta mantenibilidad.

Enlaces de Referencia

← Volver a la lista