ORMs: working with databases from code

model Customer {
  id     Int     @id @default(autoincrement())
  name   String
  city   String?
  orders Order[]
}

model Order {
  id         Int      @id @default(autoincrement())
  amount     Int
  customer   Customer @relation(fields: [customerId], references: [id])
  customerId Int
}

// Example only: Prisma Client (needs a database)
const puneCustomers = await prisma.customer.findMany({
  where: { city: 'Pune' },
  orderBy: { name: 'asc' },
  take: 20,
});

const order = await prisma.order.create({ data: { amount: 450, customerId: 2 } });

// Drizzle: the same idea, closer to SQL
const rows = await db.select().from(customers).where(eq(customers.city, 'Pune'));

N+1 (slow)                                  Fixed (fast)
-----------                                 ------------
Q1:  SELECT * FROM orders LIMIT 50          Q1: SELECT orders.*, customers.name
Q2:  SELECT * FROM customers WHERE id = 2       FROM orders
Q3:  SELECT * FROM customers WHERE id = 1       JOIN customers
Q4:  SELECT * FROM customers WHERE id = 3         ON customers.id = orders.customer_id
...                                             LIMIT 50
Q51: SELECT * FROM customers WHERE id = 9

51 round trips                              1 round trip

// N+1: one query per order inside the loop
const orders = await prisma.order.findMany({ take: 50 });
for (const order of orders) {
  order.customer = await prisma.customer.findUnique({ where: { id: order.customerId } });
}

// Fixed: ask the ORM to load the relation in the same request
const ordersWithCustomers = await prisma.order.findMany({
  take: 50,
  include: { customer: true },
});