Tutorial 3.3.2

Data Mapper, Relationships & Optimization

Chapter 9 · Data Management
~2.5 hours Advanced Data Mapper · N+1 · Migrations · Transactions

Overview

Beyond basic CRUD, ORMs provide powerful tools for handling complex domain logic. This tutorial covers the Data Mapper pattern, how to define and query relationships (one‑to‑one, one‑to‑many, many‑to‑many), the infamous N+1 query problem and how to solve it with eager loading, and essential database management tasks like migrations and transactions.

Why this matters: Real‑world applications have complex relationships and high concurrency. Understanding these advanced ORM features allows you to build scalable, maintainable data layers that keep your application fast and reliable.

1. Data Mapper Pattern

Unlike Active Record, the Data Mapper pattern separates the domain model (pure business logic) from the persistence logic. Models are plain objects (POJOs/POCOs) with no knowledge of the database. A separate repository or mapper handles the conversion between objects and database rows.

Examples: Hibernate (Java), Entity Framework Core (C#), Prisma (with generated client), SQLAlchemy (classical mapping).

Advantages

  • Domain purity: Your business logic is decoupled from infrastructure.
  • Flexibility: You can map to multiple data sources or different schemas.
  • Testability: Pure domain objects are easier to unit test.

In Prisma, the generated client acts as a data mapper. You define models in the schema, and Prisma generates a type‑safe client that maps rows to objects.

// Prisma schema definition (not a class) model User { id Int @id @default(autoincrement()) name String posts Post[] } // Usage in code (mapper) const user = await prisma.user.findUnique({ where: { id: 1 } }); // user is a plain object, not an instance of a class with save() method

2. Mapping Relationships

ORM handles relationships through foreign keys and join tables. Here’s how you define them.

One‑to‑One (1:1)

// User has one Profile (Prisma example) model User { id Int @id @default(autoincrement()) profile Profile? } model Profile { id Int @id @default(autoincrement()) userId Int @unique user User @relation(fields: [userId], references: [id]) }

One‑to‑Many (1:N)

// User has many Posts model User { id Int @id @default(autoincrement()) posts Post[] } model Post { id Int @id @default(autoincrement()) userId Int user User @relation(fields: [userId], references: [id]) }

Many‑to‑Many (M:N)

// Post has many Tags (via join table) model Post { id Int @id @default(autoincrement()) tags Tag[] @relation(references: [id]) } model Tag { id Int @id @default(autoincrement()) posts Post[] @relation(references: [id]) }

Queries can traverse these relationships:

const userWithPosts = await prisma.user.findUnique({ where: { id: 1 }, include: { posts: true } });

3. N+1 Problem & Eager Loading

The N+1 query problem occurs when an ORM executes one query for the parent entities (1) and then one additional query for each child entity (N), resulting in N+1 queries instead of a single query with a JOIN.

Example of the problem

// Inefficient (N+1) const users = await prisma.user.findMany(); // 1 query for (const user of users) { const posts = await prisma.post.findMany({ where: { userId: user.id } }); // N queries }

Solution: Eager Loading

Most ORMs support eager loading to fetch related data in a single query.

// Efficient (1 query with JOIN) const usersWithPosts = await prisma.user.findMany({ include: { posts: true } // Eager load });
Performance tip: Always profile your ORM queries. Use tools like the DEBUG environment variable or logging middleware to see the raw SQL being executed.

4. Database Migrations

Migrations are version control for your database schema. They allow you to evolve your schema over time in a controlled, repeatable way.

  • Generate migration: Create a migration file based on changes to your models.
  • Apply migration (up): Apply the changes to the database.
  • Rollback (down): Revert changes if something goes wrong.
# Prisma migration workflow npx prisma migrate dev --name init npx prisma migrate deploy # Rollback (in some frameworks) npx prisma migrate reset

Best practices: Always test migrations in a staging environment before applying to production. Never use auto‑syncing in production.

5. Transactions with ORM

Transactions ensure a group of database operations either all succeed or all fail (Atomicity). ORMs provide a straightforward API for this.

// Prisma transaction await prisma.$transaction(async (tx) => { const user = await tx.user.create({ data: { name: 'Bob' } }); await tx.account.create({ data: { userId: user.id, balance: 100 } }); }); // Rollback automatically if any operation fails

Key use cases: Transferring money, creating an order with multiple line items, updating inventory while placing an order.

Note: Transactions lock database resources. Keep them short and avoid complex logic inside the transaction block to prevent deadlocks.

Quiz

Question 1

In the Data Mapper pattern, where does the persistence logic reside?

  • Inside the model class
  • In a separate repository or mapper
  • In the database trigger
  • In the controller
Show answer
B. In a separate repository or mapper.

Question 2

What is the N+1 query problem?

  • When you have N queries plus 1 more for total count
  • When an ORM executes one query for parent entities and N queries for their children
  • When the database has N connections and 1 is idle
  • When a migration takes N minutes plus 1 second
Show answer
B. When an ORM executes one query for parent entities and N queries for their children.

Question 3

Which ORM feature allows you to fetch related data in a single query instead of multiple?

  • Lazy loading
  • Eager loading
  • Migrations
  • Transactions
Show answer
B. Eager loading.

Exercises

Exercise 1

Given a schema with Category and Product (one‑to‑many), write an ORM query that fetches all categories and their products, avoiding the N+1 problem.

Sample answer
const categories = await prisma.category.findMany({ include: { products: true } });

Exercise 2

Explain the difference between prisma.$transaction with the interactive API and using the Promise.all approach for batch operations.

Sample answer

prisma.$transaction with an async callback ensures all operations are executed in a single transaction – if one fails, all are rolled back.

Promise.all runs multiple queries concurrently but does not wrap them in a single database transaction. If one fails, the others may still commit, causing partial updates.

Use the callback when atomicity (ACID) is required.

Homework

Homework 1

Imagine you are building a blog platform. You have a page that lists all posts, and for each post, you need to display the author's name and the number of comments. Describe how you would structure the ORM query to be performant (avoiding N+1). Write the query in your chosen ORM syntax.

Sample answer

Use eager loading with a count:

const posts = await prisma.post.findMany({
  include: {
    author: true, // eager load author
    _count: { select: { comments: true } } // count comments
  }
});

This executes a single query with JOINs and a subquery for the counts, avoiding N+1 for authors and comments.

Mini‑Project

Optimize an Order System

You are given the following schema: Customer (1:N) Order (1:N) OrderItem. An API endpoint lists customers and their orders, but it is currently doing N+1 queries causing a 5‑second response time.

  1. Identify what the N+1 queries are likely to be.
  2. Rewrite the query using eager loading to reduce it to 1 or 2 queries.
  3. Add a transaction that creates a new order for a customer, ensuring inventory is checked and updated.
Sample solution

1. N+1 identification: The endpoint likely fetches all customers, then loops through each to fetch their orders and order items.

2. Optimised query:

const customers = await prisma.customer.findMany({
  include: {
    orders: {
      include: { items: true }
    }
  }
});

3. Transaction for order creation:

await prisma.$transaction(async (tx) => {
  const product = await tx.product.findUnique({ where: { id: productId } });
  if (product.stock < quantity) throw new Error('Insufficient stock');
  await tx.product.update({ where: { id: productId }, data: { stock: { decrement: quantity } } });
  await tx.order.create({ data: { customerId, items: { create: [{ productId, quantity }] } } });
});

Tutorial Summary

This tutorial covered advanced ORM concepts: the Data Mapper pattern, defining and querying relationships, the critical N+1 problem and its solution via eager loading, schema migrations, and safe transactional operations. You learned how to build a scalable, high‑performance data layer that handles complex domain logic.

Key takeaway: Mastering these advanced features allows you to write code that is both clean and fast. Always keep an eye on the SQL your ORM generates, and use the right pattern (Active Record or Data Mapper) based on your application's complexity.