DEV Community

Forb Yuan
Forb Yuan

Posted on

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

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
}
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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
}
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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"}}

Origin link: https://blog.doyto.win/post/introduction-to-goooqo-en/
© 2024 Yuan Zhen. All rights reserved.

Top comments (0)