Database Patterns
Data Access Patterns
Abstraction over data access with collection-like interface, centralizing data logic for improved testability.
- Centralized data access logic
- Query encapsulation
- Unit of Work integration
- Collection-like interface
- Entity repositories (UserRepository)
- CRUD operation abstraction
- Domain-driven design
- Testing with mock repositories
Separates domain objects from database persistence with bidirectional mapping and independent evolution.
- Object-relational mapping
- Independent domain/DB evolution
- Complex mapping logic support
- Bidirectional transformation
- ORMs (TypeORM, SQLAlchemy)
- Entity mapping layers
- Database abstraction
- Legacy database integration
Domain object with built-in persistence methods, simple CRUD operations, and direct database mapping.
- Self-contained model classes
- Built-in CRUD methods
- Simple query interface
- Association handling
- Rails ActiveRecord
- Laravel Eloquent
- Django ORM
- Simple CRUD applications
Abstract persistence mechanism with interface-based design, technology-agnostic, and separation of concerns.
- Persistence abstraction
- Interface-based design
- Multiple implementations
- Testability focus
- Java DAO pattern
- Service layer separation
- Testing with mocks
- Database technology switching
Transaction Patterns
Tracks object changes and batches commits with automatic change detection and transaction management.
- Automatic change tracking
- Deferred execution
- Transaction scope management
- Batch updates
- ORM session management
- Batch update operations
- Complex multi-object saves
- Rollback scenarios
Ensures one object instance per database row with caching and identity guarantee for consistency.
- Object identity cache
- Reference equality guarantee
- Duplicate load prevention
- Circular reference handling
- ORM entity caching
- Session-level cache
- Object identity preservation
- Graph navigation
Defers data loading until accessed with proxy objects, reducing initial load time and preventing N+1 issues.
- Proxy object usage
- Virtual property loading
- N+1 query prevention strategies
- On-demand fetching
- ORM relationship loading
- Large collection handling
- Optional data retrieval
- Performance optimization
Loads related data upfront with JOIN queries to prevent N+1 problem and optimize relationship loading.
- Include/preload directives
- JOIN query optimization
- Relationship batch loading
- N+1 problem elimination
- List page data loading
- API response optimization
- Report generation
- Relationship-heavy queries
Query Patterns
Encapsulated query logic with builder pattern, method chaining, and reusable composable queries.
- Builder pattern usage
- Method chaining support
- Named scope definitions
- Query composition
- Complex filtering logic
- Search functionality
- Reporting queries
- API query parameters
Business rules as combinable objects with AND/OR/NOT composition for validation and query building.
- Rule composition (AND/OR/NOT)
- Domain rule encapsulation
- Validation support
- Query criteria building
- Business rule validation
- Complex filtering
- Access control rules
- Domain-driven design
Precomputed query results with abstraction layer, materialized or regular views, and complex join simplification.
- Materialized vs regular views
- Refresh strategy management
- Index on view support
- Query simplification
- Reporting dashboards
- Complex aggregations
- Legacy system abstraction
- Performance optimization