DAO Pattern — Data Access Object for Database Operations

What You'll Learn

You will learn how the Data Access Object (DAO) pattern separates low-level database interaction code from business logic by providing a dedicated object for each data source.

Why It Matters

Embedding SQL queries in business code makes applications brittle. When the schema changes, every query scattered across the codebase must be updated. DAO centralises all SQL in one place per entity, providing a clean API that business logic calls without touching raw JDBC, SQLAlchemy, or connection management. Changing the schema from username to login_name means updating one DAO method instead of dozens of service files.

Real-World Use

Enterprise Java applications use DAOs to isolate JDBC or JPA code from EJB and servlet layers. DodaTech's Etl Pipelines use DAOs to read from source databases and write to target warehouses, with the DAO handling batching, retries, and connection pooling behind a simple interface.

The Pattern

DAO interface defines CRUD operations. ConcreteDAO implements them using a specific data source technology. The client (usually a service) uses only the DAO interface.

from abc import ABC, abstractmethod
from dataclasses import dataclass
from typing import Optional

@dataclass
class User:
    id: Optional[int]
    username: str
    email: str

class UserDAO(ABC):
    @abstractmethod
    def find_by_id(self, user_id: int) -> User:
        pass

    @abstractmethod
    def find_by_username(self, username: str) -> User:
        pass

    @abstractmethod
    def create(self, user: User) -> User:
        pass

    @abstractmethod
    def update(self, user: User) -> User:
        pass

    @abstractmethod
    def delete(self, user_id: int) -> bool:
        pass

    @abstractmethod
    def find_all(self) -> list[User]:
        pass

class InMemoryUserDAO(UserDAO):
    def __init__(self):
        self._users = {}
        self._next_id = 1

    def _to_user(self, data: dict) -> User:
        return User(id=data["id"], username=data["username"], email=data["email"])

    def find_by_id(self, user_id: int) -> User:
        data = self._users.get(user_id)
        return self._to_user(data) if data else None

    def find_by_username(self, username: str) -> User:
        for data in self._users.values():
            if data["username"] == username:
                return self._to_user(data)
        return None

    def create(self, user: User) -> User:
        user_id = self._next_id
        self._next_id += 1
        self._users[user_id] = {"id": user_id, "username": user.username, "email": user.email}
        return self.find_by_id(user_id)

    def update(self, user: User) -> User:
        if user.id in self._users:
            self._users[user.id] = {"id": user.id, "username": user.username, "email": user.email}
            return self.find_by_id(user.id)
        return None

    def delete(self, user_id: int) -> bool:
        if user_id in self._users:
            del self._users[user_id]
            return True
        return False

    def find_all(self) -> list[User]:
        return [self._to_user(data) for data in self._users.values()]

dao = InMemoryUserDAO()

alice = dao.create(User(id=None, username="alice", email="alice@example.com"))
bob = dao.create(User(id=None, username="bob", email="bob@example.com"))

print("All users:", [(u.id, u.username) for u in dao.find_all()])

alice.email = "alice@newdomain.com"
dao.update(alice)
print("Updated:", dao.find_by_id(alice.id))

dao.delete(bob.id)
print("After delete:", [(u.id, u.username) for u in dao.find_all()])

All users: [(1, 'alice'), (2, 'bob')]
Updated: User(id=1, username='alice', email='alice@newdomain.com')
After delete: [(1, 'alice')]

Structure

classDiagram
    class BusinessObject {
        +useDAO()
    }
    class DAO {
        <>
        +create(obj)
        +find(id)
        +update(obj)
        +delete(id)
        +findAll()
    }
    class ConcreteDAO {
        +create(obj)
        +find(id)
        +update(obj)
        +delete(id)
        +findAll()
    }
    class DataSource {
        +getConnection()
        +executeQuery()
    }
    BusinessObject --> DAO : depends on
    ConcreteDAO ..|> DAO
    ConcreteDAO --> DataSource : uses

Real-World Usage

• Java EE @Stateless DAOs — enterprise applications use session beans as DAOs behind remote interfaces.
• Python SQLAlchemy DAO Pattern — repositories built on top of sessions often follow DAO conventions.
• ASP.NET Core DAL — data access layers with dedicated DAO classes for each entity.
• MyBatis — a persistence framework where XML-mapped DAOs separate SQL from Java code.

Related Patterns

• Repository is a higher-level abstraction; DAO is more data-source-centric.
• DTO transports data between DAO and service layers.
• Factory Method can create DAO implementations for different databases.
• Abstract Factory selects DAO families for different persistence backends.
• Proxy can wrap a DAO to add caching or Lazy Loading.

Pros and Cons

Practice Questions

1. Implement a DAO that uses a Connection Pool and manages transactions via context managers.
2. How would you implement generic CRUD methods to avoid repeating the same pattern for every entity?
3. Compare DAO with Active Record — what are the trade-offs between the two approaches?
4. Implement a DAO that logs every query with execution time for monitoring purposes.

Use DodaTech's query profiler to trace every database call through your DAO layer, identifying slow queries and connection leaks.