Tutorial 3.3.1

ORM Fundamentals & Active Record Pattern

Chapter 9 · Data Management
~2.5 hours Intermediate ORM · Active Record · CRUD · Prisma

Overview

Object‑Relational Mapping (ORM) is a technique that bridges the gap between object‑oriented programming and relational databases. This tutorial introduces the core concepts of ORM, the popular Active Record pattern, and how to perform basic CRUD operations using an ORM. You will learn how to write cleaner, more maintainable database code without writing raw SQL.

Why this matters: ORMs reduce boilerplate, improve developer productivity, and provide abstraction that makes your code portable across different database systems. They are a cornerstone of modern full‑stack development.

1. What is an ORM?

An ORM is a library that maps database tables to programming language classes (models) and rows to objects. It translates between the relational world (SQL) and the object‑oriented world (classes, inheritance, and composition).

Key advantages

  • Productivity: Write database operations using familiar OOP syntax.
  • Abstraction: Switch databases (e.g., PostgreSQL to MySQL) with minimal changes.
  • Type safety: Modern ORMs (e.g., Prisma, TypeORM) provide TypeScript types.
  • Relationship management: Easily navigate relationships (e.g., user.posts).

Disadvantages

  • Performance overhead: ORM queries can be less efficient than hand‑tuned SQL.
  • Complexity: Understanding the generated SQL is essential for debugging.
  • Learning curve: Each ORM has its own API and quirks.

Popular ORMs: Prisma (Node.js/TS), TypeORM, Sequelize, Hibernate (Java), Entity Framework (C#), SQLAlchemy (Python), Eloquent (PHP/Laravel).

2. Active Record Pattern

The Active Record pattern is the most widely adopted ORM pattern. In Active Record, each model class corresponds to a database table, and the class instance represents a single row. The class itself contains methods for CRUD operations (e.g., save(), delete(), find()).

Examples: Rails ActiveRecord, Laravel Eloquent, Sequelize (Node.js), Yii2.

// Active Record example (pseudo) class User extends Model { // maps to 'users' table } // Create and save const user = new User(); user.name = 'Alice'; user.email = 'alice@ex.com'; await user.save(); // Find and update const user = await User.find(1); user.email = 'new@ex.com'; await user.save(); // Delete await user.delete();
Note: Prisma, while popular, does not strictly follow Active Record. It uses a Data Mapper‑like pattern with a generated client. We will cover Data Mapper in Tutorial 2.

3. Core CRUD Operations

Every ORM provides basic CRUD operations. Here's how they typically look using Prisma (a modern Node.js ORM) as a reference.

Create

const newUser = await prisma.user.create({ data: { name: 'Alice', email: 'alice@example.com', posts: { create: [{ title: 'First Post' }] } } });

Read (Find)

// Find all const allUsers = await prisma.user.findMany(); // Find one by ID const user = await prisma.user.findUnique({ where: { id: 1 } }); // Find with filters const activeUsers = await prisma.user.findMany({ where: { status: 'ACTIVE' } });

Update

const updatedUser = await prisma.user.update({ where: { id: 1 }, data: { email: 'alice.new@example.com' } });

Delete

await prisma.user.delete({ where: { id: 1 } });

4. Query Building

ORMs provide powerful query builders that allow you to construct complex queries programmatically. This reduces string concatenation and SQL injection risks.

Filtering (WHERE)

const users = await prisma.user.findMany({ where: { AND: [ { age: { gte: 18 } }, { status: 'ACTIVE' } ] } });

Sorting (ORDER BY)

const users = await prisma.user.findMany({ orderBy: { createdAt: 'desc' } });

Pagination (LIMIT / OFFSET)

const page = 2; const pageSize = 10; const users = await prisma.user.findMany({ skip: (page - 1) * pageSize, take: pageSize });

Selecting specific fields

const users = await prisma.user.findMany({ select: { id: true, name: true } // only return these fields });

Quiz

Question 1

In the Active Record pattern, where does the database logic (save, delete) reside?

  • In a separate repository class
  • Inside the model class itself
  • In the database schema
  • In the controller layer
Show answer
B. Inside the model class itself. Active Record models contain both data and persistence logic.

Question 2

What is the primary advantage of using an ORM over raw SQL?

  • It is always faster than SQL
  • It reduces boilerplate and improves developer productivity
  • It eliminates the need for indexes
  • It prevents all SQL injection attacks automatically
Show answer
B. It reduces boilerplate and improves developer productivity. (While ORMs help with SQL injection, they don't eliminate all risks if raw queries are used).

Question 3

Which ORM operation is used to retrieve a single record by its primary key?

  • create
  • findMany
  • findUnique
  • update
Show answer
C. findUnique (or findByPk in some ORMs).

Exercises

Exercise 1

Using an ORM of your choice (or pseudo‑code), write a query to fetch all posts that were created in the last 7 days and belong to a user with the email 'john@doe.com'.

Sample answer (Prisma style)
const posts = await prisma.post.findMany({
  where: {
    createdAt: { gte: new Date(Date.now() - 7 * 24 * 60 * 60 * 1000) },
    author: { email: 'john@doe.com' }
  }
});

Exercise 2

Explain two potential disadvantages of using an ORM.

Sample answer
  • Performance overhead: ORMs can generate inefficient SQL (e.g., selecting all columns when only a few are needed, or N+1 query problems).
  • Complexity and learning curve: Each ORM has its own API, and developers must understand the underlying SQL to debug performance issues.

Homework

Homework 1

Compare and contrast the Active Record pattern with the Data Mapper pattern. List their key characteristics, typical use cases, and provide one example ORM for each.

Sample answer

Active Record: The model represents both data and persistence logic. Example: Laravel Eloquent, Rails ActiveRecord. Suitable for simpler applications where domain logic is not complex.

Data Mapper: The model is a pure domain object (POJO/POCO) and persistence is handled by a separate repository or mapper. Example: Hibernate, Prisma (client pattern), SQLAlchemy (Classical mapping). Suitable for complex business logic where the domain should be decoupled from the database.

Mini‑Project

Bookstore CRUD with ORM

Design a simple bookstore application with a Book model (id, title, author, price, published_year). Using your preferred ORM (or pseudo‑code), implement:

  1. A function to insert a new book.
  2. A function to find all books published after 2020.
  3. A function to update the price of a book by its ID.
  4. A function to delete a book by its ID.
Sample implementation (Prisma style)

1. Create:

async function createBook(title, author, price, year) {
  return await prisma.book.create({ data: { title, author, price, published_year: year } });
}

2. Find after 2020:

async function getRecentBooks() {
  return await prisma.book.findMany({ where: { published_year: { gt: 2020 } } });
}

3. Update price:

async function updatePrice(id, newPrice) {
  return await prisma.book.update({ where: { id }, data: { price: newPrice } });
}

4. Delete:

async function deleteBook(id) {
  return await prisma.book.delete({ where: { id } });
}

Tutorial Summary

You learned the fundamentals of ORM, focusing on the Active Record pattern. We covered how ORMs map tables to classes, the core CRUD operations, and how to build queries programmatically. You now understand the trade‑offs between ORM and raw SQL, and have practiced implementing basic database operations in a type‑safe way.

Key takeaway: ORMs are powerful tools that boost productivity, but they are not a silver bullet. Always profile your ORM queries and be ready to fall back to raw SQL when performance demands it.