David Paquet Pitts/Experiments
Interactive Tool

Database Access Patterns Explorer

Compare different database access patterns applicable to a wide range of applications. While examples reference Node.js, Next.js and Remix, these patterns translate across various tech stacks. Understand the tradeoffs in complexity, abstraction level, and separation of concerns.

Filters

Legend

Abstraction Level
Separation of Concerns
Complexity
Scalability
Testability
Showing 12 of 12 patterns

Repository Pattern

Abstracts data access layer from business logic

Abstraction: 4
Complexity: 3
Repository Pattern DiagramDomain Services/ControllersIRepository InterfaceRepository ImplementationDatabase

Resource Pattern

Organizes data access around resources mirroring REST endpoints

Abstraction: 3
Complexity: 3
Resource Pattern DiagramREST API EndpointsResource HandlersResource Service MethodsDatabaseGETPOSTPUTDELETE

Active Record Pattern

Combines data access methods and domain logic in a single class

Abstraction: 2
Complexity: 2
Active Record Pattern DiagramApplication Code(Controllers/Services)Active Record ObjectPropertiesDB MethodsDatabase

Data Mapper Pattern

Separates domain objects from database access logic using mapper classes

Abstraction: 5
Complexity: 4
Data Mapper Pattern DiagramApplication LogicDomain ObjectsData MappersDatabasemap tocreate from

Unit of Work Pattern

Tracks changes and coordinates writing to multiple repositories in a single transaction

Abstraction: 4
Complexity: 4
Unit of Work Pattern DiagramApplication CodeUnit of WorkRepository ARepository BRepository CDatabaseTransaction Boundary

DAO Pattern

Abstracts persistence operations for a specific entity type

Abstraction: 3
Complexity: 3
DAO Pattern DiagramBusiness Logic LayerData Transfer ObjectsData Access ObjectsDatabase

Query Builder Pattern

Provides a programmatic way to construct SQL queries

Abstraction: 2
Complexity: 2
Query Builder Pattern DiagramApplication CodeQuery Builder.select().where().orderBy()...Database.select('*').from('users').where('id', 1)

Service Layer Pattern

Defines application boundaries and available operations

Abstraction: 4
Complexity: 3
Service Layer Pattern DiagramUI / API ControllersService LayerDomain ModelRepositoriesExternal ServicesDatabase

Transaction Script Pattern

Organizes business logic as procedural scripts or functions

Abstraction: 1
Complexity: 1
Transaction Script Pattern DiagramUI / ControllerTransaction Script(Procedural Business Logic)DatabasefunctioncreateOrder() { // Logic}

Table Data Gateway Pattern

Provides a gateway class for all access to a single database table

Abstraction: 2
Complexity: 2
Table Data Gateway Pattern DiagramApplication LogicTable Data Gatewayfind()insert()update()(All operations for one table)Database Table

Row Data Gateway Pattern

Provides an object that acts as a gateway to a single database row

Abstraction: 2
Complexity: 1
Row Data Gateway Pattern DiagramApplication LogicRow Gateway 1Attributesupdate() / insert()Row Gateway 2Attributesupdate() / insert()Database RowsOne object per row

CQRS Pattern

Separates read operations from write operations

Abstraction: 5
Complexity: 5
CQRS Pattern DiagramClientCommandsQueriesCommand ModelWrite DatabaseQuery ModelRead DatabaseData Synchronization