DEV Community: Forb Yuan

What‘s the Real Problem for ORMs

Forb Yuan — Thu, 19 Dec 2024 14:21:35 +0000

The designers and developers of ORMs have never truly understood the real problems they need to address. Once this problem is solved, the ORM will no longer be an ORM.

The Dynamic Query Problem and Combinatorial Mathematics

Let's revisit the dynamic query problem first.

In an information system, developers provide users with a query interface containing n query parameters. The user fills out k of these parameters to make a request $(k \in [0, n])$. The system constructs a query statement based on these k parameters. Developers need to combine the corresponding query conditions for every possible combination of k query parameters, which is known as the dynamic query problem.

The core of this problem is the introduction of an uncertain variable k by the end-user. SQL, in its original design, was only intended for developers and did not account for user input. Therefore, each SQL statement is static, corresponding to only one set of query conditions. Since the ORM framework does not address this issue, developers need to combine the corresponding query conditions based on the user's input.

Assuming the n query parameters are independent and the order of the query conditions is fixed, this problem can be described using combinatorial mathematics as a subset selection problem: choosing k elements from a set of n elements provided by the query interface to form a subset.

$$
\sum_{k=0}^{n} \binom{n}{k} = 2^n
$$

From the above formula, we can see that for a query interface with n query parameters, there are $2^n$ possible query clauses. For example, if there are 3 query parameters, 8 query clauses need to be written; if there are 10 query parameters, 1024 query clauses are needed. Both writing and maintaining this are extremely difficult.

ORM frameworks have not paid attention to this problem, nor have they provided a solution. A natural way is to use if statements to check the query parameters and determine whether the corresponding query condition should be concatenated. Since each if statement produces two possible outcomes (TRUE or FALSE), corresponding to whether or not to execute the query condition concatenation within the if block, the n if statements result in $2^n$ possible concatenation outcomes.

At first glance, this problem seems to be solved, but the issue with this approach is that developers must write an if statement for each query parameter. As the number of query parameters increases, the number of if statements grows, and code maintenance still remains difficult.

This is the real problem that needs to be solved in database access, instead of just mapping object models to relational models.

Query Object Mapping Method

When we use the Introduce Parameter Object refactoring method to group all the query parameters into a single object, we get an object with n fields, and the construction of query clauses depends solely on this object.

For an object with n fields, each field can either be assigned or unassigned, resulting in $2^n$ possible combinations of assignments. If each field maps to a query condition, we can use the $2^n$ combinations of assignments to construct $2^n$ query clauses.

Thus, we shift our approach to map query clauses through an object instead of mapping objects to relations. We call this object used to construct query clauses a Query Object, and the method of combining query conditions into query clauses based on the field assignments of the Query Object is called the Query Object Mapping Method.

Object Mapping

For programming languages that support reflection, we can use reflection to read the values of the fields and determine whether each field's assignment should be mapped to a query condition. The algorithm is as follows:

Traverse the fields of the Query Object instance.
Use reflection to obtain the value of each field and map the assigned fields to query conditions.
Use logical operators such as AND to combine the query conditions into query clauses.

One simple way to map fields to query conditions is by using annotations to declare query conditions along with the fields.

Reflection and annotation declarations help us encapsulate the query clause construction code into a framework that is available for all developers to use, greatly simplifying their work.

For example, for a UserQuery object defined by developers, the framework can use the algorithm described above to construct the corresponding query clause based on the UserQuery assignment:

public class UserQuery {
    @Condition("name LIKE CONCAT('%', ?, '%')")
    private String nameLike;
    @Condition("age > ?")
    private Integer ageGt;
    @Condition("valid = ?")
    private Boolean valid;
}
// a possible query clause: WHERE age > ? and valid = ?

After confirming the advantages of object mapping over the if statement concatenation approach, we further discuss how to construct different query conditions based on field characteristics to avoid maintaining string conditions.

Field Mapping

In SQL, query conditions mainly include the following three types:

Here is the translation:

Comparison Query Conditions: For example, age > ?, this condition is represented using Predicate Logic. Predicate logic is used to express basic comparison operations, such as equality (=), greater than (>), less than (<), greater than or equal to (≥), less than or equal to (≤), and not equal to (≠).
Logical Query Conditions: These are formed using Boolean Algebra, combining multiple conditions with logical operators such as AND, OR, and NOT. These conditions express the logical relationships between multiple query conditions. For example, age > 30 AND valid = true is a logical query condition where two conditions are combined using the "AND" operator.
Subquery Conditions: These conditions involve a nested query, typically based on Relational Algebra. Relational algebra provides the mathematical foundation for database queries, used to express relationships between tables and nested queries. For example, age > (SELECT avg(age) FROM t_user) is a subquery condition, where avg(age) is a subquery that returns a result and is compared with a field in the outer query.

Based on these mathematical principles, we design three types of fields to construct query conditions.

Mapping Comparison Query Conditions via Predicate Suffix Fields: Comparison query conditions are usually composed of column names, comparison operators, and parameters. In DSLs (domain-specific languages), predicate phrases are often used to represent comparison operators. For example, eq represents equality (=), gt represents greater than (>), and so on. condition.gt("age", 30) represents the query condition age > 30.

We append the predicate phrase to the column name to represent the query condition. For example, the field ageGt represents the query condition age > ?. Similar suffixes include Eq, Ne, Ge, Lt, Le, In, NotIn, Null, Like, etc., which allows us to map various comparison query conditions through field suffixes.

Constructing Logical Query Conditions via Logic Suffix Fields: Logical query conditions are a group of query conditions connected by logical operators such as AND or OR.

The type of logic suffix fields is either a collection or a query object used to construct multiple query conditions, where each element or field corresponds to one query condition.

The name of the logical suffix field includes the logical suffix And or Or, indicating the logical operator that connects multiple query conditions.

Constructing Subquery Conditions via Subquery Fields: Subquery fields should be query objects.

For example, for a subquery condition like age > (SELECT avg(age) FROM t_user), we can break it down into three parts:

For the condition part age >, we can reuse the mapping method for predicate suffix fields. However, to avoid naming conflicts with the existing predicate suffix field ageGt, we need to add distinguishing characters, such as ageGtAvg.
For the main subquery part SELECT avg(age) FROM t_user, we can declare the column name and table name using annotations like @Subquery(select = "avg(age)", from = "t_user"), or define it in the field name like ageGtAvgAgeOfUser.
For the WHERE clause of the subquery, we can reuse the query object mapping method.

With these three types of fields, we can automatically construct most query conditions. For other query conditions, we can continue developing new methods to support them.

Achievement

Through object and field mapping, the query object now has the following four key characteristics:

Constructs comparison query conditions;
Constructs logical query conditions;
Constructs subquery conditions;
Dynamically combines query conditions based on query parameters.

From a mathematical theory perspective, the query object mapping method effectively addresses the uncertainty introduced by end users and constructs various query conditions in SQL, forming an object-based dynamic query language.

Compared to the static query clauses in SQL, this dynamic query language provides the additional feature of dynamically combining query conditions. On this basis, we can continue constructing other parts of the SQL statement.

Implementations

The query object mapping method constructs corresponding query conditions solely through the field metadata, making it applicable to any object-oriented programming language. Here are examples in Java and Go.

Java Example:

public class UserQuery {// WHERE
  String nameLike       // AND name LIKE ?
  Integer ageGt;        // AND age > ?
  Integer ageLe;        // AND age <= ?
  Boolean valid;        // AND valid = ?
  UserQuery userOr;     // AND (age > ? OR age <= ? OR valid = ?)
  @Subquery(select = "avg(age)", from = "t_user")
  UserQuery ageGtAvg;   // AND age > (SELECT avg(age) FROM t_user [WHERE])
}

GitHub: http://github.com/doytowin/doyto-query

Go Example:

type UserQuery struct {   // WHERE
    NameLike *string       // AND name LIKE ?
    AgeGt    *int          // AND age > ?
    AgeLe    *int          // AND age <= ?
    Valid    *bool         // AND valid = ?
    UserOr   *[]UserQuery  // AND

 (age > ? OR age <= ? OR valid = ?)
                           // AND age > (SELECT avg(age) FROM t_user [WHERE])
    ScoreGtAvg *UserQuery `subquery:"select:avg(age),from:t_user"`
}

GitHub: http://github.com/doytowin/goooqo

Each field corresponds to one or more query conditions, and the conditions are combined based on field assignments to form the final query clause. Logical and subquery conditions can also be constructed by reusing query objects. These advantages are not available in SQL, which is a static language.

By defining query objects in this way, developers no longer need to explicitly write if statements to concatenate query conditions. The framework can use reflection to read the assignment of each field and implicitly include the assignment checks and query condition concatenation in the framework code, greatly simplifying the code writing and maintenance for dynamic queries.

Discussion

For pagination and sorting, we can still define the relevant parameters in the query object and construct the pagination and sorting clauses based on these parameters when generating the SQL statement. We simply need to declare that these parameters are not used to construct query conditions.

We can also generate the previously manually written if statement concatenation code for the query object through code generation. This would eliminate the performance impact of reflection and support programming languages that do not provide reflection.

Furthermore, the query object can also be used to construct MongoDB queries:

{
  "$and": [
    {"age": {"$gt": {}}},
    {"age": {"$lte": {}}},
    {"memo": null},
    {"memo": {"$regex": {}}},
    {"valid": {"$eq": {}}},
    {"$or": [{}, {}, {}]}
  ]
}

(The empty objects are placeholders similar to SQL.)

Since all query languages are designed based on the same mathematical principles, databases like MongoDB, Redis, and ElasticSearch have compared and converted their query languages to SQL. Some databases even directly support portions of the SQL standard. Therefore, the query object mapping method we designed based on these mathematical principles is not only applicable to all object-oriented programming languages, but also to all database query languages. This goes beyond the theoretical scope of ORM.

As for complex queries involving aggregation and joins, these can be constructed using a view object mapping method, which is beyond the scope of this paper.

Conclusion

This paper categorizes the dynamic query problem as a subset selection problem in combinatorial mathematics, proposes directly using query object assignments to handle the assignment combinations of query conditions, and fully utilizes the characteristics of objects and fields to construct various query conditions in SQL. This leads to the development of an object-based dynamic query language, becoming a more efficient database access solution than ORM.

What?! Just Two Lines to Handle CRUD Web APIs in Golang?

Forb Yuan — Mon, 26 Aug 2024 14:42:32 +0000

This is all thanks to the support of OQM (Object Query-Language Mapping) technology, we developed a Go version called GoooQo.

Introduction to OQM

The biggest difference between OQM technology and traditional ORM (object-relational mapping) technology is that OQM proposes to build CRUD statements directly through objects.

The core function of OQM is to build a query clause through a query object, which is the origin of the Q in the name of OQM.

Another significant discovery in OQM technology is that the field names in query objects and the conditions in query clauses can be converted interchangeably.

For example, in SQL, the query condition age > ? can be transformed by rewriting > as the string alias Gt (short for Greater Than), and then attaching Gt as a suffix to the column name age, resulting in ageGt, which can be used as a field name.

Then we can use OQM technology to parse such objects. When a field named ageGt is assigned a value, the corresponding query condition can be generated by reversing the above process.

In this way, we only need to create an entity object and a query object to build CRUD statements. The entity object is used to determine the table name and the column names, and the instance of the query object is used to control the construction of the query clause.

By encapsulating all the boilerplate code, the code to build a CRUD Web API for a single table is reduced to just two lines.

Demo Introduction

We developed a separate demo to demonstrate the features of GoooQo.

The file test.db is a SQLite database containing a table t_user with four rows:

id	username	password	email	mobile	nickname	memo	valid
1	f0rb	123456	f0rb@163.com	18888888881	test1		true
2	user2	123456	test2@qq.com	18888888882	test2		false
3	user3	123456	test3@qq.com	18888888883	test3	memo	true
4	user4	123456	test4@qq.com	18888888884	test4		true

Using GoooQo, we only need to define two structs, UserEntity and UserQuery, for the user table in user.go.

UserEntity is a traditional entity object with fields corresponding to the columns in the user table.

UserQuery is dedicated to constructing query clauses. The fields in UserQuery follow the format of column name plus suffix (refer to the suffix mapping table in the appendix) to generate the corresponding query conditions:

type UserQuery struct {
    goooqo.PageQuery
    IdGt         *int     // id > ?
    IdIn         *[]int   // id IN (?,?,?)
    EmailContain *string  // email LIKE "%value%"
    MemoNull     *bool    // memo is [NOT] NULL
}

In addition, the embedded goooqo.PageQuery defines three fields: PageNumber, PageSize, and Sort, which are used to construct the paging clause and the sorting clause.

After establishing a database connection in the main method, the following two lines of code are all we need to build the CRUD APIs for the user table:

userDataAccess := rdb.NewTxDataAccess[UserEntity](tm)
goooqo.BuildRestService[UserEntity, UserQuery]("/user/", userDataAccess)

The first line of the code creates a data access module for the user table, and the second line builds a web access module based on the created data access module for the user table. The web access module handles requests at http://localhost:9090/user/.

Running the Demo

Checkout the demo repository locally and run the main method in the file demo.go to start the program, and then access the following URLs to view the response:

For example, the query string emailContain=qq&memoNull=false will generate this SQL statement SELECT * FROM t_user WHERE email LIKE '%qq' AND memo IS NOT NULL and get a response like this:

{
  "data": {
    "list": [
      {
        "id": 3,
        "username": "user3",
        "email": "test3@qq.com",
        "mobile": "17778888883",
        "nickname": "test3",
        "memo": "memo",
        "valid": true
      }
    ],
    "total": 1
  },
  "success": true
}

There are also some sample HTTP requests provided in the file user.http to test POST/PUT/PATCH/DELETE operations. For example:

### Create new users
POST http://localhost:9090/user/
Content-Type: application/json

[{
  "Username": "Ada Wong",
  "Email": "AdaW@gmail.com",
  "Mobile": "01066666",
  "Nickname": "ada",
  "Memo": "An agent.",
  "Valid": true
}, {
  "Username": "Leon Kennedy",
  "Email": "LeonKennedy@gmail.com",
  "Mobile": "01077777",
  "Nickname": "leon",
  "Memo": "The hero.",
  "Valid": true
}]

### Update specified fields for the user 3

PATCH http://localhost:9090/user/3
Content-Type: application/json

{
  "Username": "Leon Kennedy",
  "Email": "LeonKennedy@gmail.com"
}

### Delete users where the memo field is not null
DELETE http://localhost:9090/user/?memoNull=false

Epilogue

GoooQo is the Golang version of OQM technology following the Java version framework DoytoQuery. Currently, it is only an MVP that has undergone preliminary evaluation, and it will take multiple rounds of iterations to implement all the functions proposed by OQM technology. Please stay tuned and support us!

Appendix: The Suffix Mapping Table

The suffixes supported by version v0.1.3 and the corresponding query conditions can be found in this suffix mapping table:

后缀名称	字段名称	字段赋值	SQL查询条件	MongoDB查询条件
(EMPTY)	id	5	id = 5	{"id":5}
Eq	idEq	5	id = 5	{"idEq":5}
Not	idNot	5	id != 5	{"idNot":{"$ne":5}}
Ne	idNe	5	id <> 5	{"idNe":{"$ne":5}}
Gt	idGt	5	id > 5	{"idGt":{"$gt":5}}
Ge	idGe	5	id >= 5	{"idGe":{"$gte":5}}
Lt	idLt	5	id < 5	{"idLt":{"$lt":5}}
Le	idLe	5	id <= 5	{"idLe":{"$lte":5}}
NotIn	idNotIn	[1,2,3]	id NOT IN (1,2,3)	{"id":{"$nin":[1, 2, 3]}}
In	idIn	[1,2,3]	id IN (1,2,3)	{"id":{"$in":[1, 2, 3]}}
Null	memoNull	false	memo IS NOT NULL	{"memo":{"$not":{"$type", 10}}}
Null	memoNull	true	memo IS NULL	{"memo":{"$type", 10}}
NotLike	nameNotLike	"arg"	name NOT LIKE '%arg%'	{"name":{"$not":{"$regex":"arg"}}}
Like	nameLike	"arg"	name LIKE '%arg%'	{"name":{"$regex":"arg"}}
NotStart	nameNotStart	"arg"	name NOT LIKE 'arg%'	{"name":{"$not":{"$regex":"^arg"}}}
Start	nameStart	"arg"	name LIKE 'arg%'	{"name":{"$regex":"^arg"}}
NotEnd	nameNotEnd	"arg"	name NOT LIKE '%arg'	{"name":{"$not":{"$regex":"arg$"}}}
End	nameEnd	"arg"	name LIKE '%arg'	{"name":{"$regex":"arg$"}}
NotContain	nameNotContain	"arg"	name NOT LIKE '%arg%’	{"name":{"$not":{"$regex":"arg"}}}
Contain	nameContain	"arg"	name LIKE '%arg%’	{"name":{"$regex":"arg"}}
Rx	nameRx	"arg\d"	name REGEXP 'arg\d’	{"name":{"$regex":"arg\d"}}

How to Express SELECT * FROM t WHERE id = ? OR (name = ? AND age = ?) by Objects Only?

Forb Yuan — Sat, 24 Aug 2024 00:03:22 +0000

To answer this question, we need to understand a new concept first: the WHERE clause is not flat but hierarchical. Within this structure, query conditions connected by AND form one level, and query conditions connected by OR form another. The logical connector for each layer is either AND or OR. Typically, multiple query conditions are connected using AND, which is considered the first level by default. Therefore, in the query statement mentioned in the title, the two conditions connected by OR are at the second level, and the two conditions within the second OR condition connected by AND are at the third level.

Using JSON to represent this hierarchical structure might make it clearer:

{
  "userOr": {
    "id": 5,
    "userAnd": {
      "name": "John",
      "age": 30
    }
  }
}

When converting this JSON structure into a WHERE clause, we can determine the logical connectors between multiple query conditions based on the suffixes And/Or in the key names. Since there's only one key, userOr, at the first level, the AND connector can be omitted. The multiple conditions under userOr are connected using OR. The first key corresponds to the condition id = 5, while the multiple conditions of the second key, name = "John" and age = 30, are connected with AND according to the suffix And. These conditions are then enclosed in parentheses and combined with id = 5 using OR, resulting in the corresponding query clause: id = 5 OR (name = "John" AND age = 30). When constructing query conditions, the parameters can be replaced with placeholders.

Next, let's try to construct such a JSON object using Java:

@Getter
@Setter
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class UserQuery {
  private Integer id;
  private String name;
  private Integer age;
  private UserQuery userOr;
  private UserQuery userAnd;

  public static void main(String[] args) throws Exception {
    UserQuery userAnd = UserQuery.builder().name("John").age(30).build();
    UserQuery userOr = UserQuery.builder().id(5).userAnd(userAnd).build();
    UserQuery userQuery = UserQuery.builder().userOr(userOr).build();

    ObjectMapper objectMapper = new ObjectMapper().setSerializationInclusion(JsonInclude.Include.NON_NULL);
    System.out.println(objectMapper.writeValueAsString(userQuery));
    // Output: {"userOr":{"id":5,"userAnd":{"name":"John","age":30}}}
  }
}

Although this approach may be a bit cumbersome, it allows us to construct the same JSON object, which means we can generate the same query clause. Furthermore, since this query condition can be represented in JSON format, it implies that any programming language supporting JSON can be used to construct such query conditions. The next step is to attempt to implement this solution in different programming languages.

From ORM to OQM: An Object-Only SQL Construction Solution

Forb Yuan — Fri, 23 Aug 2024 13:58:30 +0000

Object/Relational Mapping (ORM) may lead the development of object-oriented applications based on relational databases in the wrong direction. A good technical solution should make things simpler rather than more complicated.

There are actually only two steps to perform relational database access: SQL construction and SQL execution. ORM frameworks support SQL execution well, for example, Spring JDBC is a typical SQL execution tool, however, we still need to handwrite SQL or SQL construction code wrapped in any format for complex query statements. What we need is a completely automated SQL construction solution.

Nowadays, most ORM frameworks can resolve the table name and column names through an entity object, and then build basic CRUD statements for a single table, while constructing the query clause requires users to provide several corresponding query parameters. According to the principle of high cohesion, we can define all query parameters of each table in one object, called a query object. Since each field in the query object can be built as a query condition, we can build a complete query clause through such a query object. Among them, these query parameters also include sorting parameters and paging parameters. In this way, the entity object and the query object constitute a pair of orthogonal objects for building CRUD statements for a single table.

In addition to single-table CRUD statements, SQL construction also includes complex query statements. However, entity objects cannot be used to build complex query statements, because the fields in an entity object correspond to the columns in a single table, and the columns in a complex query statement may come from multiple tables or contain expressions, so we need to design another object to build static parts such as aggregate expressions, grouping clauses, and join relationships, which I call it a view object. At the same time, we can reuse query objects to build query clauses in complex query statements. Theoretically, these three types of objects can implement the automatic construction of SQL statements. The following figure shows an example of the Java implementation. The SQL statement on the right is the third query statement of the TPC-H benchmark test.

This article shares the idea of automatically building SQL statements from objects only. I call this technology Object/Query-Language Mapping(OQM). In my next article, I will introduce the query object mapping method in detail.