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
}

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