[ES] Use ORM for CRUD

Usa un ORM para CRUD, relaciones y migraciones. Usa SQL crudo para agregaciones complejas, reportes y rutas críticas de rendimiento. Muchos proyectos usan ambos.

Usa eager loading: selectinload en SQLAlchemy, include en Prisma, FetchType.EAGER o JOIN FETCH en Hibernate. Monitorea el conteo de consultas en desarrollo.

Sí. SQLAlchemy usa Alembic, Prisma tiene migraciones integradas, y Hibernate puede autogenerar schemas con hbm2ddl. Sin embargo, las migraciones de producción deben revisarse y probarse.

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import declarative_base, sessionmaker, relationship, selectinload

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    email = Column(String, nullable=False, unique=True)
    role = Column(String, default='user')
    posts = relationship("Post", back_populates="author", cascade="all, delete-orphan")

class Post(Base):
    __tablename__ = 'posts'
    id = Column(Integer, primary_key=True)
    title = Column(String, nullable=False)
    user_id = Column(Integer, ForeignKey('users.id'))
    author = relationship("User", back_populates="posts")

engine = create_engine('postgresql://user:pass@localhost/mydb')
Session = sessionmaker(bind=engine)
session = Session()

# Eager loading con selectinload (evita N+1)
from sqlalchemy import select
stmt = (
    select(User)
    .options(selectinload(User.posts))
    .where(User.role == 'admin')
)
admins_with_posts = session.execute(stmt).scalars().all()

# Bulk insert
session.add_all([
    User(email=f'user{i}@example.com', role='user')
    for i in range(100)
])
session.commit()

# Bulk update con Core
from sqlalchemy import update
session.execute(
    update(User)
    .where(User.role == 'user')
    .values(role='member')
)
session.commit()

// schema.prisma
model User {
  id    Int     @id @default(autoincrement())
  email String  @unique
  role  String  @default("user")
  posts Post[]

  @@index([role])
}

model Post {
  id       Int    @id @default(autoincrement())
  title    String
  authorId Int
  author   User   @relation(fields: [authorId], references: [id])

  @@index([authorId])
}

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

// Crear con registros anidados
const user = await prisma.user.create({
  data: {
    email: 'alice@example.com',
    role: 'admin',
    posts: {
      create: [
        { title: 'First Post' },
        { title: 'Second Post' },
      ],
    },
  },
  include: { posts: true },
});

// Transacción con múltiples operaciones
const [newUser, updatedPost] = await prisma.$transaction([
  prisma.user.create({ data: { email: 'bob@example.com' } }),
  prisma.post.update({ where: { id: 1 }, data: { title: 'Updated' } }),
]);

// Upsert (crear o actualizar)
const result = await prisma.user.upsert({
  where: { email: 'carol@example.com' },
  update: { role: 'admin' },
  create: { email: 'carol@example.com', role: 'admin' },
});

// SQL crudo para consultas complejas
const topUsers = await prisma.$queryRaw`
  SELECT u.email, COUNT(p.id) AS post_count
  FROM users u
  LEFT JOIN posts p ON p.author_id = u.id
  GROUP BY u.email
  ORDER BY post_count DESC
  LIMIT 10
`;

# models.py
from django.db import models

class User(models.Model):
    email = models.EmailField(unique=True)
    role = models.CharField(max_length=20, default='user')

    class Meta:
        indexes = [models.Index(fields=['role'])]

class Post(models.Model):
    title = models.CharField(max_length=200)
    author = models.ForeignKey(User, on_delete=models.CASCADE, related_name='posts')

# Operaciones CRUD
user = User.objects.create(email='alice@example.com', role='admin')
users = User.objects.filter(role='admin').select_related('posts')  # Eager load
user.role = 'superadmin'
user.save()
user.delete()

# Bulk create
User.objects.bulk_create([
    User(email=f'user{i}@example.com') for i in range(100)
])

# Bulk update
User.objects.filter(role='user').update(role='member')

# Agregación
from django.db.models import Count, Avg
User.objects.annotate(post_count=Count('posts')).filter(post_count__gt=5)

public interface UserRepository extends JpaRepository<User, Integer> {

    // Métodos de consulta derivados
    Optional<User> findByEmail(String email);
    List<User> findByRole(String role);

    // Consultas personalizadas con @Query
    @Query("SELECT u FROM User u WHERE u.role = :role ORDER BY u.email")
    List<User> findByRoleOrdered(@Param("role") String role);

    // Consulta nativa
    @Query(value = "SELECT * FROM users WHERE email LIKE :pattern",
           nativeQuery = true)
    List<User> findByEmailPattern(@Param("pattern") String pattern);

    // Consultas de modificación
    @Modifying
    @Query("UPDATE User u SET u.role = :role WHERE u.id = :id")
    int updateRole(@Param("id") Integer id, @Param("role") String role);

    // Paginación y ordenamiento
    Page<User> findByRole(String role, Pageable pageable);
}

// Uso con paginación
Pageable pageable = PageRequest.of(0, 20, Sort.by("email").ascending());
Page<User> adminPage = userRepository.findByRole("admin", pageable);

import { Entity, PrimaryGeneratedColumn, Column, OneToMany, ManyToOne } from 'typeorm';

@Entity()
class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ unique: true })
  email: string;

  @Column({ default: 'user' })
  role: string;

  @OneToMany(() => Post, post => post.author)
  posts: Post[];
}

@Entity()
class Post {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  title: string;

  @ManyToOne(() => User, user => user.posts)
  author: User;
}

// Patrón repository
const userRepo = dataSource.getRepository(User);

// Crear
const user = userRepo.create({ email: 'alice@example.com', role: 'admin' });
await userRepo.save(user);

// Leer con relaciones
const users = await userRepo.find({
  where: { role: 'admin' },
  relations: ['posts'],
  order: { email: 'ASC' },
  take: 20,
  skip: 0,
});

// Operaciones bulk
await userRepo
  .createQueryBuilder()
  .update()
  .set({ role: 'member' })
  .where('role = :role', { role: 'user' })
  .execute();