DOYToWin

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:

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:

• http://localhost:9090/user/
• http://localhost:9090/user/3
• http://localhost:9090/user/?pageNumber=2&pageSize=2
• http://localhost:9090/user/?sort=id,desc
• http://localhost:9090/user/?sort=memo,desc%3Bemail,desc
• http://localhost:9090/user/?idIn=1,3
• http://localhost:9090/user/?emailContain=qq
• http://localhost:9090/user/?idGt=2&emailContain=qq
• http://localhost:9090/user/?emailContain=qq&memoNull=true
• http://localhost:9090/user/?emailContain=qq&memoNull=false

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 the 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:

© 2024 Yuan Zhen. All rights reserved.