The declaration of Japanese

Recently, the author has been working on various solutions for easy interaction with databases. My main operation with the database is using SQLX, which makes it very easy to ungroup data from the database into structs. You can write SQL queries, use DB to mark structs, and then let SQLX handle the rest. However, the main problem I ran into was idiomatic query construction. This led me to research the issue and write down some of my thoughts in this article.

PS: Rich first-line technology, diversified forms of expression, all in “360 cloud computing”, point to pay attention!

1

GORM, layered complexity and ActiveRecord mode

Many Go developers use the GORM library when it comes to database operations. Of course, it is a fairly full-featured ORM, supporting migration, relationships, transactions, and so on. The use of GORM should be familiar to anyone who has used ActiveRecord or Eloquent.

The author has also used GORM briefly before, which is fine for simple CRUD-based applications. However, WHEN it comes to more layered complexity, I find it somewhat inadequate. Suppose we are developing a blog-like application that allows users to search for articles through the search query string in the URL. If this happens, we want to constrain the query with WHERE title LIKE, otherwise it won’t work.

posts := make([]Post, 0)search := r.URL.Query().Get("search")db := gorm.Open("postgres", "..." )if search ! = "" { db = db.Where("title LIKE ?" , "%" + search + "%")}db.Find(&posts)Copy the code

Nothing special, we just check to see if there is a value and modify the call to GORM itself. But what if we wanted to allow searching for articles after a certain date? We need to add more checks, first to see if the after query string exists in the URL, and if so, to modify the query accordingly.

posts := make([]Post, 0)search := r.URL.Query().Get("search")after := r.URL.Query().Get("after")db := gorm.Open("postgres", "..." )if search ! = "" { db = db.Where("title LIKE ?" , "%" + search + "%")}if after ! = "" { db = db.Where("created_at > ?" , after)}db.Find(&posts)Copy the code

Therefore, we add another check to determine whether the call should be modified. So far, so good, but things can get out of hand. Ideally, we would like to extend GORM with some custom callbacks that accept the search and after variables regardless of their values and defer the logic to custom callbacks. GORM does support a plug-in system for writing custom callbacks, but this seems more appropriate for modifying table state during certain operations.

As mentioned above, the biggest drawback I’ve found to GORM is that implementing layered complexity is cumbersome. You often need to do this when writing SQL queries. Try to determine whether to add a WHERE clause to a query based on some user input, or how records should be sorted.

2

Build customizable queries with Go

The Database/SQL package in the standard library is ideal for interacting with databases. SQLX is a great extension for handling data returns. However, this still does not completely solve the current problem. This is a common approach to how to programmatically build complex queries efficiently. What would it look like if we used SQLX for the same query above?

posts := make([]Post, 0)search := r.URL.Query().Get("search")after := r.URL.Query().Get("after")db := sqlx.Open("postgres", "..." )query := "SELECT * FROM posts"args := make([]interface{}, 0)if search ! = "" { query += " WHERE title LIKE ?" args = append(args, search)}if after ! = "" { if search ! = "" { query += " AND " } else { query += " WHERE " } query += "created_at > ?" args = append(args, after)}err := db.Select(&posts, sqlx.Rebind(query), args...)Copy the code

Not much better than what we did with GORM, uglier in fact. We will check if search exists twice to prepare the correct SQL syntax for the query, store the parameters in the []interface{} slice, and concatenate to a string. This is also not extensible or easy to maintain. Ideally, we would like to be able to build the query and hand it over to SQLX to handle the rest of the query. So what would an idiomatic query builder look like in Go? In my opinion, it will take one of two forms, the first using choice structures and the other using first-order functions.

Let’s look at Squirrel. This library provides the ability to build queries and execute them directly in a way that the authors consider quite idiomatic. We will focus only on the query building aspect here.

With squirrel, we can implement the above logic like this.

posts := make([]Post, 0)search := r.URL.Query().Get("search")after := r.URL.Query().Get("after")eqs := make([]sq.Eq, 0)if search ! = "" { eqs = append(eqs, sq.Like{"title", "%" + search + "%"})}if after ! = "" { eqs = append(eqs, sq.Gt{"created_at", after})}q := sq.Select("*").From("posts")for _, eq := range eqs { q = q.Where(eq)}query, args, err := q.ToSql()if err ! = nil { return}err := db.Select(&posts, query, args...)Copy the code

This is a little bit better than GORM, a little bit better than the string concatenation we did before. However, it still comes across as a bit verbose. Squirrel uses option structures for some clauses in SQL queries. Optional structures are a common pattern in the Go for API, and the goal is to be highly configurable.

An API for building queries in Go should meet these two requirements:

How do you achieve this with Go?

  • Conform to language conventions

  • extensible

3

The first class function for the query build

Here is an example of query construction:

posts := make([]*Post, 0)db := sqlx.Open("postgres", "..." )q := Select( Columns("*"), Table("posts"),)err := db.Select(&posts, q.Build(), q.Args()...)Copy the code

I know a simple example. But let’s look at how we implement such an API so that it can be used for query building. First, we should implement a query structure to track the state of the query at build time.

type statement uint8type Query struct {    stmt  statement    table []string    cols  []string    args  []interface{}}const (    _select statement = iota)Copy the code

The struct above will track the statement we are building, whether it be SELECT, UPDATE, INSERT, or DELETE, the table we are operating on, the columns we are using, and the parameters that will be passed to the final query. For the sake of simplicity, let’s focus on implementing SELECT statements for the query builder.

Next, we need to define a type to modify the query being built. This type will be passed many times as the first class function. Each time this function is called, it should return the newly modified query, if applicable.

type Option func(q Query) QueryCopy the code

Now we can implement the first part of the builder’s Select function. This will start building a query for the SELECT statement we want to build.

func Select(opts ... Option) Query { q := Query{ stmt: select_, } for _, opt := range opts { q = opt(q) } return q}Copy the code

You should now be able to see how everything slowly comes together and how the UPDATE, INSERT, and DELETE statements are implemented. The above function is pretty useless without actually implementing some options to pass to Select, so let’s do that.

func Columns(cols ... string) Option { return func(q Query) Query { q.cols = cols return q }}func Table(table string) Option { return func(q Query) Query { q.table = table return q }}Copy the code

As you can see, we implement these first class functions in such a way that they return the underlying option function that will be called. The option function is usually expected to modify the query passed to it and return a copy.

To make it useful for use cases building complex queries, we should implement the ability to add a WHERE clause to the query. This also requires tracking the various WHERE clauses in the query.

type where struct { col string op string val interface{}}type Query struct { stmt statement table []string cols []string  wheres []where args []interface{}}Copy the code

We define a custom type for the WHERE clause and add a WHERE attribute to the original query structure. Let’s implement two types of WHERE clauses as needed, the first being WHERE LIKE and the other WHERE >.

func WhereLike(col string, val interface{}) Option {    return func(q Query) Query {        w := where{            col: col,            op:  "LIKE",            val: fmt.Sprintf("$%d", len(q.args) + 1),        }        q.wheres = append(q.wheres, w)        q.args = append(q.args, val)        return q    }}func WhereGt(col string, val interface{}) Option {    return func(q Query) Query {        w := where{            col: col,            op:  ">",            val: fmt.Sprintf("$%d", len(q.args) + 1),        }        q.wheres = append(q.wheres, w)        q.args = append(q.args, val)        return q    }}Copy the code

When dealing with adding a WHERE clause to the query, we properly handle the binding variable syntax for the underlying SQL driver (Postgres in this case) and store the actual value itself in the ARGS slice of the query.

Therefore, since we implement very little, we should be able to implement what we want in a conventional way.

posts := make([]Post, 0)search := r.URL.Query().Get("search")after := r.URL.Query().Get("after")db := sqlx.Open("postgres", "..." )opts := []Option{ Columns("*"), Table("posts"),}if search ! = "" { opts = append(opts, WhereLike("title", "%" + search + "%"))}if after ! = "" { opts = append(opts, WhereGt("created_at", after))}q := Select(opts...) err := db.Select(&posts, q.Build(), q.Args()...)Copy the code

A little better, but still not very good. However, we can extend the functionality to get what we want. So, let’s implement functions that return options for specific requirements.

func Search(col, val string) Option {    return func(q Query) Query {        if val == "" {            return q        }        return WhereLike(col, "%" + val + "%")(q)    }}func After(val string) Option {    return func(q Query) Query {        if val == "" {            return q        }        return WhereGt("created_at", val)(q)    }}Copy the code

With the above two functions implemented, we can now build a slightly more complex query for our use case. These two functions only modify the query if the value passed to them is considered correct.

posts := make([]Post, 0)search := r.URL.Query().Get("search")after := r.URL.Query().Get("after")db := sqlx.Open("postgres", "..." )q := Select( Columns("*"), Table("posts"), Search("title", search), After(after),)err := db.Select(&posts, q.Build(), q.Args()...)Copy the code

conclusion

I’ve found this to be a fairly common approach to building complex queries in Go. Now, of course, you’ve done this much in this article and must be thinking, “That’s all well and good, but you didn’t implement the Build() or Args() methods.” That’s true. Not wanting to prolong this article unnecessarily, I did not proceed with the implementation. So if you’re interested in some of the ideas shown here, take a look at the code on GitHub.

  • https://github.com/andrewpillar/query

If you have any objections to what is said in this article, or would like to discuss the issue further, please leave a comment.

360 cloud computing

The technology sharing public account created by 360 Cloud platform team covers database, big data, micro services, containers, AIOps, IoT and many other technical fields. Through solid technology accumulation and rich front-line practical experience, it will bring you the most promising technology sharing