Design Patterns & Structure Data Access Abstraction Martin Fowler PoEAA Interactive Workbench

Repository Pattern — Basic

Decouple business logic from database engines and storage details. Master Martin Fowler's collection-like mediator pattern, maintain safe parameterized queries, establish clear architectural boundaries between Services and Repositories, and refactor a messy backend application interactively.

Core Architectural Rule: A Repository is an in-memory collection abstraction that mediates between domain/application logic and physical data persistence. It is NOT merely a file storing raw SQL strings, and it is NOT mandatory for every simple CRUD app. The service orchestrates business workflows; the repository retrieves and stores entities.

The Modern 4-Layer Backend Request Pipeline

LAYER 01

Controller / Router

HTTP transport, body parsing, response status codes

LAYER 02

Service Layer

Pure business rules, calculations, workflow orchestration

LAYER 03

Repository Layer

Collection abstraction, parameterized queries, row mapping

LAYER 04

Database Storage

PostgreSQL, MySQL, SQLite, DynamoDB, or In-Memory

Martin Fowler Definition

"Mediates between the domain and data mapping layers using a collection-like interface for accessing domain objects."

Key Architectural Value

Enables effortless unit testing via mock repositories, insulates business logic from schema updates, and centralizes queries.

When to Skip It

Unnecessary for simple CRUD applications or rapid prototypes where no domain logic exists, preventing pointless over-engineering.

SECTION 01

What is the Repository Pattern?

Understand how repositories act as a collection-like illusion for data persistence, decoupling domain concepts from database storage mechanics.

The Collection-Like Illusion

In his classic work Patterns of Enterprise Application Architecture (PoEAA), Martin Fowler defined the Repository pattern as an abstraction that:

"Mediates between the domain and data mapping layers using a collection-like interface for accessing domain objects."

To the Service Layer, the repository feels like an in-memory list or array of objects: you can add(entity), get(id), filter(criteria), and remove(entity). The service has no idea whether records come from PostgreSQL, MongoDB, Redis, or an in-memory test stub.

Repository vs Database vs Service

Beginners frequently confuse these three components. Here is how they cleanly differ:

Database: The external, physical persistence storage system (e.g., PostgreSQL disk tables, connection pools, binary indexes).
Repository: The application-level code module that speaks SQL or ORM dialect, executes queries safely, maps table rows to domain objects, and hides persistence mechanics.
Service: The business coordinator. Evaluates business rules, computes prices, validates eligibility, and orchestrates calls to one or more repositories.

SECTION 02

Why Use a Repository? (Problems, Benefits & Trade-Offs)

See what happens when a service directly embeds raw database queries, and weigh the architectural trade-offs before introducing this abstraction.

The Problem: Database-Polluted Service

When a Service manages database connections and raw queries directly, several architectural problems emerge:

// ❌ Tightly coupled: OrderService directly executing SQL class OrderService { async checkout(customerId, cart) { // 1. Raw SQL mixed with business logic const client = await pool.connect(); const user = await client.query('SELECT * FROM users WHERE id = $1', [customerId]); // 2. Business calculation const total = cart.reduce((sum, item) => sum + item.price, 0); // 3. More SQL queries directly in the service await client.query('INSERT INTO orders (user_id, total) VALUES ($1, $2)', [user.id, total]); client.release(); } }

Hard to test: You cannot test discount calculations without booting a live database.
Duplicated queries: Other services needing user lookup duplicate the same SQL string.
Schema lock-in: If the table column changes from user_id to customer_id, business code breaks.

The Solution: Dedicated Repository

By extracting data operations into an OrderRepository and UserRepository, the service focuses solely on business logic:

// ✅ Clean: Service delegates persistence to repositories class OrderService { constructor(userRepo, orderRepo) { this.userRepo = userRepo; this.orderRepo = orderRepo; } async checkout(customerId, cart) { // Clean domain methods const user = await this.userRepo.findById(customerId); const total = this.calculateTotal(cart, user.isVip); return await this.orderRepo.create({ userId: user.id, total }); } }

Fast unit tests: Mock the repository with a JavaScript array; test in 2ms.
Centralized queries: SQL queries live in one place; update schema in one file.
Swappable engines: Switch from raw PostgreSQL to Prisma or SQLite without touching business rules.

Architectural Trade-Offs & When to Skip the Pattern

The Repository Pattern is a tool, not a religious mandate. Before adding repository files for every database table, consider:

1. Boilerplate Overhead:

Creating an interface, repository implementation, and service wrapper for a simple table with 3 fields adds ceremony without benefit.

2. Trivial CRUD Apps:

If an endpoint just reads records and returns them with zero business logic, calling an ORM directly from a controller is often pragmatic and sufficient.

3. The "God Repository" Trap:

Building a monolithic repository that does complex joins across 15 unrelated tables creates tight coupling and defeats the pattern.

SECTION 03

Repository Responsibilities & Boundaries

Define precise architectural boundaries. Know what belongs inside a repository and what must stay out.

What BELONGS in a Repository

Standard Collection Operations: findById(id), findAll(filters), create(entity), update(id, data), delete(id).
Domain Queries: Semantic retrieval methods like findActiveByCustomerId(customerId), findByEmail(email), findPendingOrders().
Safe Parameterized SQL: Using query placeholders ($1, $2 in Postgres, ? in MySQL) to prevent SQL injection.
Data Mapping / Hydration: Translating database snake_case columns (created_at_epoch) into clean domain model objects (createdAt).
Database Error Translation: Catching driver error codes (e.g., unique key violation 23505) and throwing understandable domain persistence errors.

What DOES NOT Belong in a Repository

HTTP Concerns: Never pass Express req, res, headers, cookies, or HTTP status codes into a repository.
Authentication & Session Tokens: Decrypting JWTs or verifying session cookies belongs in middleware/controllers.
Business Logic & Calculations: Calculating discounts, shipping fees, tax rates, or evaluating user tiers belongs in the Service Layer.
External Side-Effects: Sending confirmation emails, processing Stripe credit cards, or dispatching WebSockets.
Leaky SQL Queries: Never let controllers or services pass raw SQL clauses (e.g., repo.findWhere("status = 'PAID'")).

SECTION 04

Service vs Repository (Side-by-Side Comparison)

Examine how the Service Layer and Repository Layer collaborate during a realistic e-commerce checkout flow.

Dimension	Service Layer	Repository Layer
Primary Question	"What business rules apply to this operation?"	"How do I retrieve or store this domain entity?"
Knows About	Business rules, workflows, calculations, repositories, email dispatchers	Database drivers, connection pools, table schemas, SQL queries, row mapping
Input & Output	Domain DTOs, business command parameters → returns business result	Entity IDs, query criteria → returns Domain Entities or null
Unit Testing	Mock the repositories with in-memory arrays; tests run in milliseconds	Integration tests against an ephemeral database (Docker/SQLite) to verify SQL
Reusable By	REST Controllers, GraphQL Resolvers, CLI commands, Kafka message consumers	Multiple services across the application needing data access

Collaboration in Action: E-Commerce Checkout Flow

Notice how the CheckoutService handles all decisions while Repositories handle all storage:

// services/checkoutService.js class CheckoutService { constructor(productRepo, orderRepo, customerRepo) { this.productRepo = productRepo; this.orderRepo = orderRepo; this.customerRepo = customerRepo; } async processCheckout({ customerId, cartItems, couponCode }) { // 1. Service uses customerRepo to get domain entity const customer = await this.customerRepo.findById(customerId); if (!customer) throw new Error('Customer does not exist'); // 2. Service uses productRepo to verify inventory for (const item of cartItems) { const product = await this.productRepo.findById(item.productId); if (product.stock < item.quantity) throw new Error(`Insufficient stock for ${product.title}`); } // 3. Service applies business logic (Discounts, Tax Math) const discount = this.calculateCouponDiscount(couponCode, cartItems); const total = this.computeFinalTotal(cartItems, discount, customer.taxExempt); // 4. Service delegates persistence to orderRepo const order = await this.orderRepo.create({ customerId, items: cartItems, total, status: 'CONFIRMED' }); // 5. Service updates inventory via productRepo for (const item of cartItems) { await this.productRepo.decrementStock(item.productId, item.quantity); } return order; } }

SECTION 05

Real Implementations: Express.js & FastAPI

See modern production implementations in Node.js (Express) and Python (FastAPI with Dependency Injection).

📁 repositories/productRepository.js (Safe Parameterized PostgreSQL Queries)Express / Node.js

// repositories/productRepository.js const { pool } = require('../db/pool'); class ProductRepository { // Safe parameterized query ($1) prevents SQL injection async findById(id) { const query = 'SELECT id, title, price_cents, stock_qty, created_at FROM products WHERE id = $1'; const { rows } = await pool.query(query, [id]); return rows[0] ? this._toDomain(rows[0]) : null; } async create({ title, priceCents, stockQty }) { const query = ` INSERT INTO products (title, price_cents, stock_qty) VALUES ($1, $2, $3) RETURNING id, title, price_cents, stock_qty, created_at `; const { rows } = await pool.query(query, [title, priceCents, stockQty]); return this._toDomain(rows[0]); } async updateStock(id, newStock) { const query = 'UPDATE products SET stock_qty = $1 WHERE id = $2 RETURNING id, stock_qty'; const { rows } = await pool.query(query, [newStock, id]); return rows[0] || null; } // Hydrate raw database columns into clean domain entity _toDomain(row) { return { id: row.id, title: row.title, priceCents: row.price_cents, stock: row.stock_qty, createdAt: row.created_at }; } } module.exports = { ProductRepository };

📁 controllers/productController.js (Thin Controller)Express Router

// controllers/productController.js const { ProductRepository } = require('../repositories/productRepository'); const { ProductService } = require('../services/productService'); const productRepo = new ProductRepository(); const productService = new ProductService(productRepo); async function getProduct(req, res) { try { const product = await productService.getProductDetails(req.params.id); if (!product) return res.status(404).json({ error: 'Product not found' }); return res.status(200).json(product); } catch (err) { return res.status(500).json({ error: 'Server error' }); } } module.exports = { getProduct };

SECTION 06

Interactive Refactoring Workbench & Simulator

Hands-on coding exercise: Extract direct database queries from orderService.js into orderRepository.js. Run verification tests and send simulated HTTP requests to verify complete decoupling.

Refactoring Studio: Decouple Service from Database

// ❌ UNREFACTORED: orderService.js
// PROBLEM: Directly imports db pool, writes raw SQL strings,
// executes queries, maps table columns, and calculates business discounts all together!

const { pool } = require('./mockDb');

class OrderService {
  async createOrder({ customerId, items, promoCode }) {
    // 1. Direct DB Query to find customer
    const client = await pool.connect();
    const userResult = await client.query(
      'SELECT id, name, is_vip, email FROM customers WHERE id = $1',
      [customerId]
    );
    const customer = userResult.rows[0];
    if (!customer) {
      client.release();
      throw new Error('CUSTOMER_NOT_FOUND');
    }

    // 2. Direct DB Query for product availability
    const verifiedItems = [];
    for (const item of items) {
      const prodResult = await client.query(
        'SELECT id, title, unit_price, stock FROM products WHERE id = $1',
        [item.productId]
      );
      const product = prodResult.rows[0];
      if (!product || product.stock < item.qty) {
        client.release();
        throw new Error('INSUFFICIENT_STOCK');
      }
      verifiedItems.push({
        id: product.id,
        title: product.title,
        price: product.unit_price,
        qty: item.qty
      });
    }

    // 3. Business Calculation (Belongs in Service)
    let subtotal = verifiedItems.reduce((acc, i) => acc + (i.price * i.qty), 0);
    let discount = 0;
    if (customer.is_vip) discount += subtotal * 0.05; // 5% VIP bonus
    if (promoCode === 'SAVE10') discount += subtotal * 0.10; // 10% promo
    const total = Math.max(0, subtotal - discount);

    // 4. Direct DB Insert for Order
    const orderInsert = await client.query(
      'INSERT INTO orders (customer_id, subtotal, discount, total, status) VALUES ($1, $2, $3, $4, $5) RETURNING *',
      [customerId, subtotal, discount, total, 'CONFIRMED']
    );
    const createdOrder = orderInsert.rows[0];

    // 5. Direct DB Update for Stock
    for (const item of verifiedItems) {
      await client.query(
        'UPDATE products SET stock = stock - $1 WHERE id = $2',
        [item.qty, item.id]
      );
    }

    client.release();

    return {
      orderId: createdOrder.id,
      customerId: createdOrder.customer_id,
      total: createdOrder.total,
      discountApplied: discount,
      status: createdOrder.status,
      itemsCount: verifiedItems.length
    };
  }
}

module.exports = { OrderService };

Execution Output & Architectural Test TracerNODE.JS REPL / SIMULATOR

System Ready. Clean separation: Controller (HTTP) → Service (Business Logic) → Repository (Data Access) → Database. Click 'Run Verification Tests' or 'Send Request (POST /orders)' to evaluate.

SECTION 07

Production Debugging: 7 Architectural Anti-Patterns

Identify and fix real-world architectural bugs where data access responsibilities leak across layers.

Scenario #1: Raw SQL Queries Embedded Directly Inside Service Layer

Service directly imports the database pool and executes raw SQL queries mixed with business logic.

Symptom: Unit tests fail without a live PostgreSQL instance running; modifying database columns breaks business calculation tests.

Buggy Architecture SnippetFLAW DETECTED

// ❌ BUGGY: OrderService.js coupled directly to database driver & SQL const { pool } = require('../db/connection'); class OrderService { async createOrder({ customerId, items, promoCode }) { // Direct DB query inside service! const client = await pool.connect(); const userRes = await client.query('SELECT * FROM users WHERE id = $1', [customerId]); const customer = userRes.rows[0]; let total = items.reduce((acc, i) => acc + (i.price * i.qty), 0); if (promoCode === 'SAVE10') total *= 0.90; const orderRes = await client.query( 'INSERT INTO orders (customer_id, total, status) VALUES ($1, $2, $3) RETURNING *', [customerId, total, 'PENDING'] ); client.release(); return orderRes.rows[0]; } }

How would you fix this architectural violation?

SECTION 08

Mini Challenge & 5 Golden Architectural Rules

Test your intuition: classify realistic backend duties into Controller, Service, or Repository layers.

Responsibility Boundary Matrix

Assign each backend task to its correct architectural layer:

#1: Extract `req.params.id`, validate UUID format, and send HTTP 400 if malformed

#2: Apply a 10% holiday discount if order total exceeds $100 and customer is a subscriber

#3: Execute `SELECT * FROM products WHERE sku = $1` using safe parameter substitution

#4: Coordinate fetching user profile, checking credit limit, and logging an audit event

#5: Map raw PostgreSQL rows with snake_case keys (`unit_price`) to domain entity with camelCase (`unitPrice`)

#6: Serialize the created order entity into JSON and return HTTP 201 Created with Location header

5 Golden Rules of the Repository Pattern

RULE 01

Act Like a Collection

A repository exposes collection-like methods (findById, create, delete). It hides table columns and database connection pools from higher layers.

RULE 02

No HTTP Leaks

Repositories must never accept Express req/res or FastAPI HTTP objects. They are transport-agnostic and return pure domain entities or DTOs.

RULE 03

No Business Rules

Discounts, tax formulas, user tier eligibility, and workflow logic belong strictly in the Service Layer. Repositories only fetch and save data.

RULE 04

Always Parameterize

All SQL queries inside repositories must use safe parameters ($1, $2) to prevent SQL injection vulnerabilities. Never concatenate raw strings.

RULE 05

Don't Over-Engineer

Do not introduce repositories for trivial CRUD endpoints without business rules. Use the pattern when domain complexity or testing requirements justify it.

SECTION 09

Mastery Assessment Quiz (7 Concept Questions)

Verify your deep understanding of the Repository Pattern, data-access abstractions, and architectural boundaries.

QUESTION 1 OF 7Current Score: 0 / 7

According to Martin Fowler's architectural definition, what is the primary role of the Repository Pattern?

Select your answer to unlock the next question

Next Up in Backend Architecture

Project Structure & Clean Architecture Organization

Continue to Project Structure

Repository Pattern — Basic

The Modern 4-Layer Backend Request Pipeline

LAYER 01

Controller / Router

HTTP transport, body parsing, response status codes

LAYER 02

Service Layer

Pure business rules, calculations, workflow orchestration

LAYER 03

Repository Layer

Collection abstraction, parameterized queries, row mapping

LAYER 04

Database Storage

PostgreSQL, MySQL, SQLite, DynamoDB, or In-Memory

Martin Fowler Definition

"Mediates between the domain and data mapping layers using a collection-like interface for accessing domain objects."

Key Architectural Value

Enables effortless unit testing via mock repositories, insulates business logic from schema updates, and centralizes queries.

When to Skip It

Unnecessary for simple CRUD applications or rapid prototypes where no domain logic exists, preventing pointless over-engineering.

Dimension

Service Layer

Repository Layer

Primary Question

"What business rules apply to this operation?"

"How do I retrieve or store this domain entity?"

Knows About

Business rules, workflows, calculations, repositories, email dispatchers

Database drivers, connection pools, table schemas, SQL queries, row mapping

Input & Output

Domain DTOs, business command parameters → returns business result

Entity IDs, query criteria → returns Domain Entities or null

Unit Testing

Mock the repositories with in-memory arrays; tests run in milliseconds

Integration tests against an ephemeral database (Docker/SQLite) to verify SQL

Reusable By

REST Controllers, GraphQL Resolvers, CLI commands, Kafka message consumers

Multiple services across the application needing data access