Design patterns for the database layer
An application has multiple layers, and every layer has its nuances. To ensure that the application is well designed and follows the critical design pillars, we should ensure that each layer implements the design pattern concerning the layer and as per the use case. By this, I mean that for a layer, i.e. database layer, multiple design patterns exist, and an application might incorporate multiple design patterns per the use case.
A design pattern describes a generic approach to common design problems that designers or developers face while developing code of a similar fashion across different projects or components in a project.
Below are a few common design patterns for the database layer:
- DAO pattern
- Repository pattern
- Active record pattern
- Table Module pattern
- Domain Model pattern
DAO Pattern
The Data Access Object (DAO) pattern allows us to isolate the service or business layer from the data layer. It abstracts the persistence logic/code by using managers, such as EntityManager.
Ideally, we should have one DAO and one entity per table.
Key Attributes
- It can be used for different data sources.
- It exposes one entity per table.
- It hides the database interaction complexity, i.e., via EntityManager.
- DAO pattern is close to the actual data store or the storage system
- This layer should not hold any business logic
Cons
- It interacts with one table only
Pseudocode
We should first create a Country entity
public class CountryEntity {
private Long id;
private String name;
private String abbreviation;
}Then, we should create a CountryDAO interface
public interface CountryDAO {
CountryEntity fetch(Long id);
Long save(CountryEntity country);
void update(CountryEntity country);
int delete(Long id);
}Then, we should create the CountryDAOImpl class:
public class CountryDAOImpl implements CountryDAO {
Private final EntityManager entityManager;
CountryEntity fetch(Long id) {
return entityManager.find(CountryEntity.class, id);
} Long save(CountryEntity country) {
return entityManager.persist(country);
} // ...
}
Use cases
- This should be used for all CRUD operations on a single table.
- For scenarios where the table mimics the domain object, it can directly expose the domain object instead of the entity.
Repository Pattern
This layer works on the domain object, which the business layer uses to apply different business logic per the business scenario and the use case.
The repository pattern empowers the application to bring the data from the persistent store (database) into the memory (via domain objects). Once the domain object is updated, we can redirect it to the database.
This layer is a combination of repository class & interface. For one aggregate class, one aggregate root should be created. This layer should not hold any business logic.
Key attributes
- It encapsulates the logic or code to interact with the actual data source.
- This layer might pull data from multiple data stores, aggregate them, and publish them as part of one domain object.
- It provides a well-documented way to work with the data source.
- It works on a domain class, i.e., Country, and not an entity.
- It transforms data between actual data fetched from the database while publishing it via the domain object and vice versa.
- The repositories and the aggregate root should always control the transaction boundary for the domain object.
Cons
It might invoke multiple database calls while performing operations like fetch, save, update or delete on a domain object.
Pseudocode
We should first create a Country object (the domain object)
public class Country {
private Long id;
private String name;
private String abbreviation;
private int statement;
}Then, we should create a CountryRepository interface
public interface CountryRepository {
Country fetch(Long id);
Long save(CountryEntity country);
void update(CountryEntity country);
int delete(Long id);
}Then, we should create CountryRepositoryImpl class:
public class CountryRepositoryImpl implements CountryRepository {
private CountryDAO countryDAO;
private StateDAO stateDAO;
Country fetch(Long countryId) {
CountryEntity countryEntity = countryDAO.fetch(countryId);
StateEntity stateEntity = stateDAO.count(countryId); // Country Mapper to copy data from the country entity and state entity and prepare country object (domain object)
} Long save(Country country) {
return countryDAO.save(country);
} // ...
}
Use case
This pattern should be used to prepare complex domain objects so the business layer can work on the domain object to apply business logic and perform business operations.
In most cases, this pattern will use more than one DAO to aggregate data and publish it via domain object.
In the above example, if the domain requirement is that state count should constantly be exposed as part of the country domain object, then we can use the repository pattern to achieve this via the DAO pattern.
Comparison with DAO pattern
Often, we might need clarification about when to use the DAO pattern versus the repository pattern.
Ideally, the DAO pattern should be used when interacting with one table to perform a CRUD operation. It should expose an entity for most cases but can tell a domain object for simple tables that mimic the domain object.
A repository pattern should be used where the need is to aggregate and transform the data from more than one entity and publish it as a domain object. A repository pattern can use the DAO code but not vice versa.
Active Record Pattern
This was coined by Martin Fowler (Uncle Bob) in 2003.
An object that wraps a row in a database table or view encapsulates the database access and adds domain logic to that data.
Key attributes
- One instance of an object represents one row of a table.
- We can query the database using this object as well.
- The ORM tools commonly used in it
Cons
- It can be considered an anti-pattern and encapsulates the object's data and data access logic.
- It represents the table row instead of the domain object.
- It violates the Single Responsibility Principle
Pseudocode
We should create a Country-class
public class Country {
private Long id;
private String name;
private String abbreviation; // getter and setters...
public static Country fetch(Long id) {
// Code to interact with the repository and fetch the data
return country;
} // another static method to operate on the country record in the database
}
Below are a few example codes to interact with the Country object:
Country country = new Country();
country.setName("India");
country.setAbbreviation("IN");Country.save(country);
Country country = Country.fetch(1);
Use case
In most cases, this pattern is used by the objects exposed via ORM. It can also be a good choice for domain objects that don’t have complex business logic but are not scalable beyond that. However, it should be used cautiously; if the business complexity around the thing increases, it might require a rewrite.
Comparison with DAO pattern
DAO pattern separates data (via entity) with the operation (part of DAO class). Still, in the case of Active record, we encapsulate the data and processes as part of the same object.
Table Module Pattern
Martin Flower also introduced this pattern. The key highlight of this design pattern is:
A Table Module organises domain logic with one class per table in the database, and a single instance of a class contains the various procedures that will act on the data
Key highlights
- One instance of class handles operations on the multiple records of the table.
- One instance of this class can hold multiple records from the database.
- It can be used to write migration scripts for the table.
- We can use it to scan tables and perform operations like fetch or update
Pseudocode
If the requirement is to search countries on a specific continent,
Then, we can have a Continent entity
public class ContinentEntity {
private Long id;
private String name;
private String abbreviation;
}We should have a Country entity
public class CountryEntity {
private Long id;
private String name;
private String continentId;
private String abbreviation;
}Then, we should create a CountryTableModule class:
public class CountryTableModule {
private final DataSource dataSource;
public UserTableModule(final DataSource userDataSource) {
this.dataSource = userDataSource;
}
List<CountryEntity> search(Long continentId, String namePrefix) {
// implementation to search in the country table based on the search criteria
} // ...
}
Use case
- It is suitable for scenarios where the requirement is to operate at table level instead of row level.
- It is unsuitable for scenarios where we need to do complex business solutions.
- Data migration and transformation scripts can be a good use case as well
Comparison with domain pattern
In the case of domain pattern, an object of the domain class holds one record per instance, while an example of a table module class holds multiple forms in the same model.
Domain Model Pattern
A Domain Model incorporates a simple or a complex object structure concerning one or more tables to interface domain requests.
Key Attributes
- Each row in the table is an object.
- This is the layer upon which the business logic works within the application.
- It can make up one domain object per table for simple use cases.
- A domain model can consist of multiple table rows in a complex domain object.
- One object handles one domain request.
- It should be distinct from the domain object used in the Repository pattern.
- It should not be interfaced with the database directly but should interface via Active record or DAO.
Cons
- As the object model incorporates data and behaviour, it violates the single responsibility principle.
- It can become overly complex if in case the domain scope increases with time
Pseudocode
We should create a Country-class
public class Country {
private Long id;
private String name;
private String abbreviation;
private int stateCount; // getter and setters...
public boolean addState(String name) {
// Code to add a stateconcerningo the
// Code to fetch the updated state count
// Update the state count in the domain model
return true/false;
} /another static method to operate on the country record in the database
}
Below are a few example codes to interact with the Country object:
Country country = new Country();
country.setName("India");
country.setAbbreviation("IN");country.addState("state 1");
Use case
This is good for application which has complex business logic in place
Comparison with Active Record and Repository Pattern
This is an extension of the active record pattern with a few differences; active records work per entity, and operation methods are static. At the same time, the process is non-static, and they might perform functions on multiple objects.
Let's compare it with the repository pattern. In the repository pattern, domain objects are kept separate from the database operation methods, and business complexity is not part of it.
Conclusion
Design patterns related to the database layer play a key role in developing high-quality and maintainable code. They help localise the impact of database changes and separate the business logic layer from the data and operations.
