Chapter 7
Data Source Architecture Design Patterns
Introduction
To organize the data source architecture, the patterns of this category can be divided into the following four main sections:
Active record: An object has the task of a gateway to a record from the database, and along with this task, it also implements the business logic related to the domain of that record.
Data mapper: A layer of mappers is placed between the domain and the database, and in this way, they eliminate the dependency on the database and the domain.
Row data gateway: An object acts as a gateway to a record in the data source, and there is a different object for each record in the data source.
Table data gateway: An object acts as a gateway to a database table or view, and communication with all the records of that table or view happens through this object.
The choice between these four design patterns depends on the level of logical complexity that we want to implement.
Structure
In this chapter, we will discuss the following topics:
Data source architecture design patterns
Table data gateway
Row data gateway
Active record
Data mapper
Objectives
In this chapter, you will learn to know data source architecture design patterns and how to simulate the data source structure in your programs using different design patterns. You will also learn how to facilitate the construction and management of various SQL queries. In this chapter, you will learn how to link the data source structure to the domain model using mapping mechanisms.
Data source architecture design patterns
The most important task of the data access layer is facilitating communication with different data sources so the program can do its work. Most of today's data sources are relational databases, and with the help of the SQL language, you can easily communicate with all types of data sources and exchange data. Along with all the advantages of SQL, there are still some problems related to data sources. One of these problems is sometimes the lack of sufficient knowledge of SQL among programmers, which leads to writing wrong or ineffective queries.
With the preceding explanation, putting the SQL-related codes in a separate layer from the domain seems useful. The best way to implement this type of data infrastructure is to implement it according to the structure of database tables. In this case, there will be one class for each table in the database, and these classes will form gateways.
There are also different ways to have a gateway. One of these ways is to have a gateway object for each table record. This method is what the row data gateway is trying to express. Another way is to have one gateway object for each table. This method is also what the table data gateway is trying to express. Usually, in the table data gateway method, the result of the query sent to the database is returned as a record set. The connection and coordination between the table data gateway and record set also help to use this design pattern in the table module.
When using the domain model, due to the existing complexities, we may want each domain model to have the task of loading and changing its related data. This is where active records can come in handy.
With the increase of complexity within the domain, the logic of the domain is broken and divided between several classes. This causes a mismatch between the domain model and the database. Because there is no feature like inheritance in the database, on the other hand, with increasing complexity, it is necessary to test the domain's logic without the need to connect with the database. All these points and problems make us go for an indirect communication method between the domain model and the data source. In this case, completely separating the domain model from the database would be a better method. However, an interface will be needed to map domain objects to database tables. The data mapper design pattern will be useful in this section.
Choosing between a data mapper and an active record is a choice that depends on the level of complexity. For simpler scenarios, the active record will be useful, and as the complexity increases, using a data mapper will be a better option.
Table data gateway
Name:
Table data gateway
Classification:
Data source architecture design patterns
Also known as:
---
Intent:
Using this design pattern, an object plays the role of a gateway to a database table or view, and communication with all the records of that table or view happens through this object.
Motivation, Structure, Implementation, and Sample code:
Suppose a requirement is raised, and we need to connect to the database and perform Create, Read, Update, Delete (CRUD) operations on the user table. For this purpose, functions such as adding a new user, changing the password, deleting a user, finding a specific user, and receiving a list of users should be implemented.
There are different ways to implement this requirement. One of the ways is to connect to the database using raw queries and do the desired work. Using this method, the placement of the queries is important. If these codes are placed among the business logic code of the application, they will create a significant problem. The problem is that programmers will need to write queries. Most programmers either do not have enough knowledge to write queries or if they do have enough knowledge, database administrators will have difficulty finding and improving the performance of these queries.
However, a better option is placing the queries outside the business logic location. Table data gateway design patterns can be important in achieving this goal. In this way, all the required queries are collected and defined in a separate business logic section:
public class UserTableDataGateway
{
private SqlConnection connection;
public UserTableDataGateway()
{
connection = new SqlConnection("...");
}
public async Task
{
return await new SqlCommand("" +
"SELECT * " +
"FROM Users", connection).ExecuteReaderAsync();
}
public async Task
{
return await new SqlCommand($"" +
$"SELECT * " +
$"FROM Users " +
$"WHERE UserName = N'{username}'", connection).ExecuteReaderAsync();
}
public async Task
string username, string newPassword)
{
return (await new SqlCommand($"" +
$"UPDATE Users " +
$"SET [Password] = N'{newPassword}' " +
$"WHERE UserName = N'{username}'"
, connection).ExecuteNonQueryAsync()) > 0;
}
}
As seen in the preceding code, the GetAllAsync method is used to get the list of all users. To find a specific user based on username, the FindByUsernameAsync method is used, and the ChangePasswordAsync method is used to change the password. Other operations, such as inserting a new user and deleting a user, can be defined similarly. Another noteworthy point in the preceding implementation is that for the sake of simplicity, in the definition of queries, things like SQL Injection and opening and closing the connection to the database have not been paid attention to. Still, in the real environment, it will be necessary to do such things. In the business logic layer, you can use this gateway if you need to work with the Users table. Also, database administrators must only check the gateways to monitor and improve queries.
Notes:
By using this design pattern, database queries are encapsulated in the form of a series of methods.
To implement this design pattern, you can also benefit from a Data set. While using this method, most of the operations will be similar, and you can also benefit from a public gateway and send things like the table name in the form of input parameters.
In implementing this design pattern, there is no requirement to communicate with the database tables. According to the requirement, it may be necessary to communicate with the views. If a public gateway is used for communication, it should be noted that it is often impossible to change the table or sub-tables through views1. Therefore, defining a public gateway for tables and a public gateway for views will probably be necessary. Also, to hide the structure of the tables, it is possible to benefit from the stored procedures in the database and create a gateway to communicate with these stored procedures.
The important point in using this design pattern is how to return the results to the user. For this purpose, Data Transfer Object (DTO) or methods such as mapping can be useful. Using the mapping method is often not a good option, as we will not be able to notice the problem at the time of compilation, which will cause a bug to appear. In using the DTO method, it should be noted that a new object must be created, and this method can be considered only if the created DTO can be reused. The results can be returned in the domain object format if this design pattern is used along with the domain model pattern.
This design pattern, table module, and transaction script patterns can be very useful. For example, this design pattern returns the record set required for the table module, so it can perform its processing on the results.
Consequences: Advantages
All queries are placed in the database separately from the business logic, making it easier to make changes to the queries.
The implementation of this design pattern is very simple.
Mapping between the gateway and database tables or views will be simple. Each table or view in the database is mapped to a gateway.
Consequences: Disadvantages
Using this design pattern next to the domain model will not be appropriate, and it is usually better to use the domain model design pattern and the data mapper pattern.
As the complexity in the domain increases, this design pattern will become more complex and does not have good scalability.
Applicability:
It can be useful for implementing small applications. If the business logic of the domain is simple, this design pattern can be very useful.
This design pattern and the table module design pattern can be useful.
Related patterns:
Some of the following design patterns are not related to the table data gateway design pattern, but to implement this design pattern, checking the following design patterns will be useful:
Table module
Transaction script
Domain model
Data mapper
Data transfer object
Row data gateway
Row data gateway
Name:
Row data gateway
Classification:
Data source architecture design patterns
Also known as:
---
Intent:
Using this design pattern, an object acts as a gateway to a record in the data source, and there is a different object for each record.
Motivation, Structure, Implementation, and Sample code:
Suppose we want to rewrite the example presented in the table data gateway design pattern with a row data gateway. In the table data gateway design pattern, we put all the queries related to the user's table in one class and connect to the database through that class. This section shows the difference between the row data gateway design pattern and the table data gateway pattern. In the row data gateway model, instead of a class having all the queries related to a table, we work through an object with a database record.
It is obvious that to find a record from the database and assign it to an object, and we will need a separate class. We name this class Finder. Finder is tasked to find and return a record from the table based on the provided condition. In other words, the finder tries to return a gateway for each record. The returned gateway has two main parts:
Properties: This section is the one-to-one mapping between the properties of the class and the columns of the table or data source. For example, ID, username, password, and so on.
Behaviors: This section is the queries that need to be executed on the data source. For example: insert, edit, delete, and so on.
In the following code, we have rewritten the scenario related to users with row data gateway:
public static class UserFinder
{
public static async Task
{
var reader = await new SqlCommand($"" +
$"SELECT * " +
$"FROM Users " +
$"WHERE UserName = N'{username}'",DB.Connection).ExecuteReaderAsync();
reader.Read();
return UserRowDataGateway.Load(reader);
}
public static async Task
{
var gatewayList = new List
var reader = await new SqlCommand(
"SELECT * FROM Users",DB.Connection).ExecuteReaderAsync();
while (reader.Read())
{
gatewayList.Add(UserRowDataGateway.Load(reader));
}
return gatewayList;
}
}
public class UserRowDataGateway
{
public string UserName { get; set; }
public string Password { get; set; }
public static UserRowDataGateway Load(IDataReader reader)
{
return new UserRowDataGateway()
{
UserName = reader["Username"].ToString(),
Password = reader["Password"].ToString()
};
}
public async Task
{
return (await new SqlCommand($"" +
$"UPDATE Users " +
$"SET [Password] = N'{this.Password}' " +
$"WHERE UserName = N'{this.UserName}'",
DB.Connection).ExecuteNonQueryAsync()) > 0;
}
}
In the preceding code, issues related to SQL injection or connection opening and closing are not mentioned, but in the real project, you must pay attention to these issues.
UserFinder is responsible for mapping a record to a gateway in the preceding code. It first retrieves the record from the database and then assigns the result to a specific object through the Load method.
Notes:
In using this design pattern, a finder class is often considered for each data source (table, view, and so on).
This design pattern is very similar to the active record design pattern. If the row data gateway contains domain business logic, this design pattern essentially becomes an active record and contains the business logic of the database.
Like the table data gateway design pattern, this design pattern can also communicate with a table, view, or stored procedure.
Consequences: Advantages
When mapping the columns of a record to class properties, type conversion may be required. Using this design pattern, this type of conversion happens only in one place (Load method).
Mapping between database objects and classes is simple, as each table has a gateway class. Depending on the type of implementation, a finder class will probably be needed for each table.
Consequences: Disadvantages
Using this design pattern, the maintenance cost sometimes increases because a series of clear codes must be written constantly.
Dependence on the database in using this design pattern is high because the object's properties are directly dependent on the table's columns.
The compatibility of this design pattern with the domain model design pattern is low, and the use of this design pattern along with the domain model design pattern will cause us to face three different data view methods. The first view is in the form of a domain model, the second is in the form of a gateway, and the third is in the form of a database. This is even though two different data views are needed, so using this design pattern alongside the domain model is not recommended. It is better to use other design patterns, such as active records.
Applicability:
Using this design pattern, the transaction scripts design pattern is a good combination.
This design pattern can be useful when the business logic of the domain is simple, and the probability of making changes to the data source is low. Type Safety is important when designing and coding.
Related patterns:
Some of the following design patterns are not related to the row data gateway design pattern, but to implement this design pattern, checking the following design patterns will be useful:
Table data gateway
Active record
Transaction scripts
Domain model
Active record
Name:
Active record
Classification:
Data source architecture design patterns
Also known as:
---
Intent:
In this design pattern, an object has the task of a gateway to a database record. In addition to this task, it also implements the business logic related to the domain of that record.
Motivation, Structure, Implementation, and Sample code:
An object has a series of data and behaviors, and data often needs to be persistent, stored, and retrieved in a database. This operation is presented and implemented in the form of data access logic. Using an active record design pattern, object-related behaviors, which are the business logic of the domain, are placed next to the data access business logic.
Suppose we want to rewrite the example presented in the row data gateway design pattern section with an active record design pattern. As seen in the row data gateway section, we used the finder class to find the desired record and shape the desired object, and through the gateway class, we implemented requirements such as changing the password. Now, if we add business logic related to the domain to that structure, the designed structure will become an active record:
public class UserActiveRecord
{
public string UserName { get; set; }
public string Password { get; set; }
public static UserActiveRecord Load(IDataReader reader)
{
return new UserActiveRecord()
{
UserName = reader["Username"].ToString(),
Password = reader["Password"].ToString()
};
}
public bool IsPasswordValid()
=> Password.Length > 6 &&
(Password.Contains('@') || Password.Contains('#'));
public async Task
{
if (IsPasswordValid())
{
return (await new SqlCommand($"" +
$"UPDATE Users " +
$"SET [Password] = N'{this.Password}' " +
$"WHERE UserName=N'{this.UserName}'",
DB.Connection).ExecuteNonQueryAsync())> 0;
}
else
throw new Exception("The password is not strong enough.");
}
public async Task
{
return (await new SqlCommand($"" +
$"UPDATE Users " +
$"SET [FailedLoginAttempt] = [FailedLoginAttempt] + 1 " +
$"WHERE UserName = N'{this.UserName}'",
DB.Connection).ExecuteNonQueryAsync()) > 0;
}
public async Task
{
return (await new SqlCommand($"" +
$"UPDATE Users " +
$"SET [FailedLoginAttempt] = 0 " +
$"WHERE UserName = N'{this.UserName}'",
DB.Connection).ExecuteNonQueryAsync()) > 0;
}
public async Task
{
return (int)await new SqlCommand($"" +
$"SELECT [FailedLoginAttempt] FROM Users " +
$"WHERE UserName = N'{this.UserName}'",
DB.Connection).ExecuteScalarAsync();
}
public async Task
{
return ((int)await new SqlCommand($"" +
$"SELECT COUNT(*) FROM Users " +
$"WHERE [Password] = N'{this.Password}' AND " +
$"UserName = N'{this.UserName}'",
DB.Connection).ExecuteScalarAsync()) > 0;
}
public async Task
{
var loginResult = await IsUserExistsAsync();
if (loginResult == false)
{
await IncreaseFailedLoginAttemptAsync();
if (await GetFailedLoginAttemptAsync() > 3)
throw new Exception("Your account has been locked.");
}
else
await ResetFailedLoginAttemptAsync();
return loginResult;
}
}
In the preceding implementation, for the sake of simplicity, things like SQL injection and opening and closing the connection to the database have not been considered in the definition of the queries. However, in real scenarios and production environments, it will be necessary to consider such things.
In the preceding code, as you can see, in addition to the data access logic such as ChangePasswordAsync, IncreaseFailedLoginAttemptAsync, ResetFailedLogin AttemptAsync, GetFailedLoginAttemptAsync or IsUserExistsAsync methods related to domain business logic such as IsPasswordValid and LoginAsync are also present. As it is clear in the preceding code, the design is completely similar to the design of the row data gateway design pattern, with the difference that the business logic of the domain is also included in this design.
Another point in this design is that the finder class is not included, and the design of this class will be the same as the design done in the row data gateway design pattern.
Notes:
Part of the domain logic may be written using the transaction script pattern in this design pattern, and data access logic or data-related codes may be implemented with Active Record.
The data structure presented in the active record must be the same as the data structure of the database.
In using this design pattern, it is possible to communicate with the view or stored procedures instead of communicating with the table.
This design pattern is very similar to the row data gateway.
Consequences: Advantages
Implementation and understanding of active records are very simple.
It is very compatible with transaction script design patterns.
Since the domain business logic related to a record is next to the data access logic, the design is more coherent.
Consequences: Disadvantages
Using this design pattern can be useful only when the active record object is the same as the structure of the tables in the database. This design pattern can be useful in structures of the same shape. Still, if the business logic becomes more complex and there is a need to use capabilities such as inheritance and so on in the code, this design pattern will not be useful.
Due to the high dependence of the database structure on the object structure, it will be difficult to perform refactor operations at the database or object level, and hence the scalability will decrease.
The presence of domain business logic, data access logic, and increasing design and code coherence reduces reusability. Data access logic can be placed in parent classes through inheritance to solve this problem.
Due to the high dependence of this design pattern on the database, it is difficult to write unit tests.
Due to data access logic next to domain logic, the Single Responsibility Principle (SRP) and Separation of Concern (SoC) are violated. For this reason, this design pattern is suitable for simple applications.
Applicability:
This design pattern can be useful for implementing simple domain business logic such as CRUD operations or almost complex logic in the case that the possibility of changing the data source is low.
Related patterns:
Some of the following design patterns are not related to active record design pattern, but to implement this design pattern, checking the following design patterns will be useful:
Transaction script
Row data gateway
Data mapper
Name:
Data mapper
Classification:
Data source architecture design patterns
Also known as:
---
Intent:
Using this design pattern, a layer of mappers is placed between the domain and the database, and in this way, the dependency on the database and the domain is eliminated. The task of these mappers is to transfer data from the domain to the database and vice versa.
Motivation, Structure, Implementation, and Sample code:
Suppose we want to rewrite the example of users mentioned in active record design patterns. As we saw in the active record design pattern, the principle of SRP and SoC was violated due to the high dependency on the database and the domain layer. Using this design pattern will be very simple. It is enough that the object related to the domain is not involved in issues related to the database or data source, and the data source or database is also not involved in the business logic of the domain. This makes these two areas not have mutual effects. For example, a domain-related object may have collections, inheritance, and so on.
The same features in the domain object will help you later in implementing the complex business logic of the domain. This is while these capabilities do not exist in the database, so this difference in different capabilities will cause two completely different data structures to exist. One structure conforms to the domain implementation, and the other to the database design. The part in charge of harmonizing and translating these two completely different structures is the same as the mapper. Consider the following code:
public class UserModel
{
public string UserName { get; set; }
public string Password { get; set; }
}
public class UserMapper
{
public static async Task
{
return (await new SqlCommand($"" +
$"INSERT INTO Users " +
$"(Username, [Password]) VALUES " +
$"N'{newUser.UserName}', N'{newUser.Password}'",
DB.Connection).ExecuteNonQueryAsync())>0;
}
}
public class UserDomain
{
public static bool IsPasswordValid(string password)
=> password.Length > 6 &&
(password.Contains('@') || password.Contains('#'));
public static async Task
{
if (IsPasswordValid(password))
{
return await UserMapper.Create(
new UserModel {UserName=username,Password=password });
}
else
{
throw new Exception("The password is not strong enough.");
}
}
}
In the preceding implementation, for the sake of simplicity, things like SQL injection and opening and closing the connection to the database have not been considered in the definition of the queries. Still, it will be necessary to consider such things in the real environment.
As it is clear in the preceding code, the UserDomain class is responsible for implementing the business logic of the domain. This class has no information about the structure of the data source and the database. Even this class has no information about the existence of data sources. The UserDomain class prepares a UserModel object according to its needs and delivers it to the UserMapper. UserMapper is responsible for communicating between the domain and the data source. Therefore, upon receiving a UserModel object, it prepares and sends it to the database.
Notes:
According to this design pattern, the one-to-one relationship between the database structure and the domain model has been lost, and this relationship may not necessarily be one-to-one.
The preceding code example is a simple data mapper design pattern implementation. In more complex design and implementation, to perform insertion, editing, or even deletion operations, the mapper will need to know the previous and new state of the object so that it can choose the appropriate operation based on that. Also, in order to be able to make these changes in the form of a transaction, the UnitOfWork design pattern can be used.
Considering the internal connections of the domain models with each other, it is necessary to know to what level the data should be fetched and placed in the model. If we do not have this information, we may have to retrieve the entire data, which of course, will not be the right thing to do. You can use the lazy load design pattern to solve this problem and improve the amount of fetched data.
There will be two ways to create domain model objects inside the mapper. The first way is to use the parametric builder for the domain model. In this way, to create a domain model object, it will be necessary to send the required information to the class constructor. The second way is to use the constructor without parameters. In this way, first, the object is created, and then its various characteristics are set. The first way is better in terms of object quality guarantee. The problem with using the first method is the possibility of a cyclic dependency problem. In circular dependency, object A needs object B to be created, and object B needs object A to be created.
Suppose the domain model is simple, and the task of designing and developing the database is in the hands of the domain developer team. In that case, the development process can be improved with direct access to the database. For this purpose, an active record design pattern can be used instead of this design pattern.
In the first step of software development, there is no need to produce a complete mapper with all possible features. Sometimes using a pre-produced and ready-made mapper is better than implementing a new mapper.
Consequences: Advantages
When developing the domain, the data source or database can be completely ignored during design, development, and testing.
There is a loose coupling between the data source and the domain.
It has very high compatibility with the domain model design pattern, and along with this design pattern, it helps the reusability of the code.
Consequences: Disadvantages
For simple business logic, using this design pattern will not be cost-effective because it increases the complexity by adding a new layer (mapper).
Applicability:
Using this design pattern can be useful if we want to design and develop the data source model independently of the domain model.
This design pattern is often used together with the domain model design pattern.
This design pattern can be useful when the business logic of the domain is complex.
When the probability of change in the data source or domain layer is high, using this design pattern will be useful.
Related patterns:
Some of the following design patterns are not related to the data mapper design pattern, but to implement this design pattern, checking the following design patterns will be useful:
Unit of work
Domain model
Active record
Lazy load
Conclusion
In this chapter, you got acquainted with different design patterns related to data source architecture. In this chapter, you also learned how to simulate the data source structure and map them to different objects.
In the next chapter, you will learn about object-relational behavior design patterns.
1 If the view contains a series of conditions or a series of triggers are used, data can also be changed through the view.
Join our book's Discord space
Join the book's Discord Workspace for Latest updates, Offers, Tech happenings around the world, New Release and Sessions with the Authors:
https://discord.bpbonline.com