Data Access Object (DAO) Pattern

Overview

The Data Access Object (DAO) Pattern separates low-level data access operations from high-level business logic. A DAO gives the rest of the application a clean interface for creating, reading, updating, and deleting entities, while it owns the SQL, the connection handling, and the row-to-object mapping.

A call like userDao.findById(42) says nothing about where the row comes from. PostgreSQL, MongoDB, an in-memory fake during a test — the business side never learns which, and hiding precisely that detail is the pattern’s whole job.

The Problem

Most codebases start out fine: a query here, a bit of row mapping there. Take the DAO away — or never write one — and that database code spreads until every module knows SQL. A typical failure mode looks like this: the order service runs SELECT * FROM orders WHERE status = 'pending' inline, the reporting module duplicates the same query with a slightly different filter, and a third place hard-codes JOIN logic that only makes sense for MySQL. Three consequences follow:

• Testing gets painful. You can’t unit-test a business rule without standing up a real database, because the query is glued to the rule.
• Changes scatter. Rename a column and you’re hunting through every file that ever touched the table.
• Swapping storage is a rewrite. Moving one entity to Redis or MongoDB means editing business code that never should have known about SQL.

The DAO pattern fixes this by drawing a line: everything database-shaped lives on one side, everything business-shaped on the other.

When to Use

Use the DAO Pattern when:

• Your tests need a persistence seam they can mock or stub out
• The data source may change: SQL today, MongoDB or a cache layer tomorrow
• The same CRUD operations against the same tables show up in several places
• You’d rather centralize query construction and connection handling in one file

When to Avoid

• A throwaway script or prototype where three lines of SQL do the job
• A project already on a full ORM (SQLAlchemy, Hibernate, Django models) giving you the same abstraction for free. Adding a DAO on top is often a second, thinner layer doing the same job
• Teams for whom the extra interface + implementation boilerplate costs more than it saves

Solution

The shape is always the same: a domain-facing interface, one implementation per storage technology, and business code that only sees the interface. All three examples below receive their database connection through the constructor. That’s deliberate, and it’s the detail that makes the pattern testable.

Python

from dataclasses import dataclass
from typing import Optional, List
import sqlite3


@dataclass
class User:
    id: int
    name: str
    email: str


class UserDao:
    """Receives an open sqlite3.Connection; callers own its lifecycle."""

    def __init__(self, conn: sqlite3.Connection):
        self.conn = conn

    def find_by_id(self, user_id: int) -> Optional[User]:
        row = self.conn.execute(
            "SELECT id, name, email FROM users WHERE id = ?", (user_id,)
        ).fetchone()
        return User(*row) if row else None

    def find_all(self) -> List[User]:
        rows = self.conn.execute(
            "SELECT id, name, email FROM users"
        ).fetchall()
        return [User(*row) for row in rows]

    def save(self, user: User) -> User:
        cursor = self.conn.execute(
            "INSERT INTO users (name, email) VALUES (?, ?)",
            (user.name, user.email)
        )
        user.id = cursor.lastrowid
        self.conn.commit()
        return user

    def delete(self, user_id: int) -> None:
        self.conn.execute("DELETE FROM users WHERE id = ?", (user_id,))
        self.conn.commit()


# Usage — the same DAO works against a file DB or an in-memory one for tests
conn = sqlite3.connect("app.db")
dao = UserDao(conn)
user = dao.save(User(id=0, name="Alice", email="alice@example.com"))
found = dao.find_by_id(user.id)
print(found)

Java

import java.sql.*;
import java.util.*;

// One file for reading convenience. In a real project these go in
// User.java, UserDao.java and SqlUserDao.java as separate public types.
record User(int id, String name, String email) {}

interface UserDao {
    Optional<User> findById(int id);
    List<User> findAll();
    User save(User user);
    void delete(int id);
}

class DataAccessException extends RuntimeException {
    DataAccessException(Throwable cause) { super(cause); }
}

public class SqlUserDao implements UserDao {
    private final Connection conn;

    public SqlUserDao(Connection conn) {
        this.conn = conn;
    }

    public Optional<User> findById(int id) {
        try (PreparedStatement ps = conn.prepareStatement(
            "SELECT id, name, email FROM users WHERE id = ?")) {
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                return Optional.of(new User(rs.getInt(1), rs.getString(2), rs.getString(3)));
            }
            return Optional.empty();
        } catch (SQLException e) {
            throw new DataAccessException(e);
        }
    }

    public List<User> findAll() {
        List<User> users = new ArrayList<>();
        try (Statement st = conn.createStatement();
             ResultSet rs = st.executeQuery("SELECT id, name, email FROM users")) {
            while (rs.next()) {
                users.add(new User(rs.getInt(1), rs.getString(2), rs.getString(3)));
            }
            return users;
        } catch (SQLException e) {
            throw new DataAccessException(e);
        }
    }

    public User save(User user) {
        try (PreparedStatement ps = conn.prepareStatement(
            "INSERT INTO users (name, email) VALUES (?, ?)", Statement.RETURN_GENERATED_KEYS)) {
            ps.setString(1, user.name());
            ps.setString(2, user.email());
            ps.executeUpdate();
            ResultSet keys = ps.getGeneratedKeys();
            keys.next();
            return new User(keys.getInt(1), user.name(), user.email());
        } catch (SQLException e) {
            throw new DataAccessException(e);
        }
    }

    public void delete(int id) {
        try (PreparedStatement ps = conn.prepareStatement(
            "DELETE FROM users WHERE id = ?")) {
            ps.setInt(1, id);
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new DataAccessException(e);
        }
    }
}

// Usage — hand the DAO a pooled connection (HikariCP, DataSource) in production
UserDao dao = new SqlUserDao(conn);
User saved = dao.save(new User(0, "Alice", "alice@example.com"));
System.out.println(dao.findById(saved.id()).orElseThrow());

JavaScript

// Uses the `sqlite` package, the async wrapper around sqlite3 that
// exposes .get()/.all()/.run(). For a sync API, use `better-sqlite3` instead.
import sqlite3 from 'sqlite3';
import { open } from 'sqlite';

class UserDao {
  constructor(db) {
    this.db = db;
  }

  async findById(userId) {
    const row = await this.db.get(
      'SELECT id, name, email FROM users WHERE id = ?', userId
    );
    return row || null;
  }

  async findAll() {
    return this.db.all('SELECT id, name, email FROM users');
  }

  async save(user) {
    const result = await this.db.run(
      'INSERT INTO users (name, email) VALUES (?, ?)',
      user.name, user.email
    );
    return { id: result.lastID, name: user.name, email: user.email };
  }

  async delete(userId) {
    await this.db.run('DELETE FROM users WHERE id = ?', userId);
  }
}

// Usage
const db = await open({ filename: 'app.db', driver: sqlite3.Database });
const dao = new UserDao(db);
const saved = await dao.save({ name: 'Alice', email: 'alice@example.com' });
console.log(await dao.findById(saved.id));

Explanation

Three roles make the pattern work:

• The interface declares CRUD operations in domain terms (findById, save, delete) with no SQL types leaking into the signature.
• The implementation (SqlUserDao, MongoUserDao, FakeUserDao in tests) owns the queries, the result mapping, and the storage-specific error handling.
• The consumer is your business logic. It sees only the interface and never learns which database answers.

Two details matter more than the shape:

Inject the connection, don’t create it. Every example above takes a ready connection in the constructor. That choice does two things at once: tests can hand the DAO an in-memory SQLite or a stubbed handle, and production code can pass a connection checked out from a pool. Opening a new connection inside every DAO method is exactly the “per-query connection” anti-pattern listed in the mistakes below. If your DAO needs a real pool (it usually does), wire it through database connection pooling and hand the DAO a pooled connection or DataSource.

Hide the plumbing exceptions. SQLException, MongoError, driver timeouts: none of those belong in your service layer’s catch blocks. Wrap them in one domain exception (DataAccessException above) so consumers handle “the save failed” instead of “JDBC failed with SQLState 23505”.

Consequences and Trade-offs

The pattern buys you testability (mock the interface, test business logic without a database), portability (swap the implementation, keep the callers), and a single place to audit all persistence: every query against users lives in one file.

The cost is real too:

• Boilerplate. Each entity gets an interface, an implementation, and mapping code, roughly 40–80 lines before you write the first interesting query. On a five-table app that’s a layer to maintain forever.
• The impedance mismatch doesn’t disappear. You still write SQL, still fight N+1 problems, still think in rows. The DAO contains the mess; it doesn’t remove it.
• Indirection cost. Reading a call chain goes one hop deeper: service → interface → implementation → driver. Junior devs onboarding spend their first days tracing it.

That’s why the honest framing is “DAO when you need the seam”, not “DAO everywhere”.

Variants

Variant	Abstraction Level	Use Case
Table DAO	One DAO per table	Simple CRUD applications
Generic DAO	BaseDao<T> with shared CRUD	Many entities, reduces boilerplate
Repository	Domain queries like findActiveSince	Aggregate-centric design, DDD
Active Record	Entity carries its own persistence	Frameworks (Rails, Django, Eloquent)
Data Mapper	Separate mapper moves data both ways	Complex schemas, legacy databases

DAO and Repository overlap enough that teams argue about them endlessly. The practical split is that a DAO mirrors the schema (one per table, CRUD-shaped) while a Repository mirrors the domain (one per aggregate, query-shaped). If your methods are insert, update, delete, you’ve got a DAO; if they’re findOverdueInvoices, you’ve got a Repository.

What Works

• Return domain objects, not result sets. Map rows to entities at the DAO boundary. ResultSet, cursors, and driver rows never cross it.
• Code against an interface. It means you can drop in FakeUserDao for tests and a second implementation when storage changes. In dynamic languages, keep the same method signatures and document them.
• Wrap storage exceptions once. One DataAccessException per implementation keeps catch clauses readable.
• Batch when volume justifies it. INSERT ... ON CONFLICT with a multi-row executemany() or JDBC addBatch() can cut a 10k-row import from minutes to seconds, but measure before batching anything under a few hundred rows.
• Prefer query builders for complex queries. Raw SQL strings turn unmaintainable once you add joins, subqueries, and optional filters. SQLAlchemy (Python), jOOQ (Java), or Knex.js (JavaScript) compose queries programmatically:

# SQLAlchemy — filters compose without string concatenation
from sqlalchemy import select, and_

def find_active_users_since(self, since: datetime) -> List[User]:
    stmt = select(User).where(
        and_(User.created_at >= since, User.is_active == True)
    )
    return self.session.execute(stmt).scalars().all()

• Paginate large result sets. find_all() is fine at 200 rows and a memory problem at 2 million. Add LIMIT/OFFSET or keyset pagination early, since retrofitting it later means changing every caller:

def find_paginated(self, page: int, page_size: int = 50) -> List[User]:
    offset = (page - 1) * page_size
    rows = self.conn.execute(
        "SELECT id, name, email FROM users LIMIT ? OFFSET ?",
        (page_size, offset)
    ).fetchall()
    return [User(*row) for row in rows]

Common Mistakes

• SQL scattered through business logic. The moment a service method writes a query string, the DAO boundary is broken. All query construction belongs inside the DAO.
• Returning driver types. Handing back ResultSet or a raw cursor makes every caller untestable and pins you to one database.
• No interface (or one that leaks SQL types). Without a real seam, consumers couple to the concrete class and the swap-the-database story dies.
• The God DAO. Fifty methods on UserDao (findByEmail, findByRegion, findActive, findRecent, findByEmailAndRegion…) is a domain-modeling problem. Split by aggregate or move query-shaped methods to a Repository.
• A connection per query. Opening new SqlConnection() inside findById() kills throughput under load. Inject the connection, let a pool own the lifecycle.

Real-World Examples

JDBC (Java)

JDBC isn’t a DAO itself; it’s the plumbing DAOs wrap. PreparedStatement, ResultSet, and Connection are the raw materials; frameworks like Spring JdbcTemplate add a thin DAO-friendly layer on top, and JPA/Hibernate replace hand-written DAOs entirely for many teams.

Django ORM (Python)

Django’s model managers are DAO-shaped: User.objects.filter(email="alice@example.com") hides SQL behind a domain API. The difference is that Django generates the DAO for you, so you get the pattern’s benefits without writing the boilerplate.

Knex.js (Node.js)

Knex is a query builder rather than a full DAO, but knex('users').where({ id: 42 }).first() occupies the same slot: business code talks to a domain-ish API, SQL stays out of it. Most Node DAO layers are thin classes over Knex, Prisma, or Drizzle.

Further Reading

• Martin Fowler — PoEAA catalog — DAO’s close relatives (Table Data Gateway, Row Data Gateway, Data Mapper) cataloged with their trade-offs.
• Oracle — JDBC Basics tutorial — the API behind every Java DAO.
• Django docs — Making queries — how a framework generates the DAO layer for you.
• Knex.js documentation — the Node query builder most JS DAOs wrap.
• Patterns: Unit of Work and Data Mapper — the patterns DAO most often gets confused with.

The runnable versions of these examples live in the companion repository: stack-practices-resources/resources/patterns/design/data-access-object-pattern.