Golang Database Library Orm Example Dynamic List

April 23, 2022

Often when you are listing a resource, a client may want to perform various filter operations and sorting. They may want to retrieve all addresses in a specific postcode, or sort the users by last name. Common way of passing these requests is by using query parameters in a GET operation.

For example, GET /api/user?email=john@example.com only returns user record with john@example.com as its email. GET /api/users?sort=first_name,asc sorts users by first name in ascending order. We can add multiple columns to sort by adding more sort key: GET /api/users?sort=first_name,asc&sort=email,desc.

For pagination, we can either define offset and limit or use the convenience page key: GET /api/users?page=2. Please note that there is a huge penalty LIMIT and OFFSET when you are retrieving records where the offset is in the millions: Why You Shouldn’t Use OFFSET and LIMIT For Your Pagination ORDER BY also must be added.

In our pagination examples, we simply order by ID. This is important to constraint the result rows into a unique order https://www.postgresql.org/docs/14/queries-limit.html.

In this article, we will look at three common list operations

  1. Filter by field
  2. Sort by field and direction
  3. Pagination

Table of Contents

This is part of a series of blog posts including:

The runnable code is available in the repository https://github.com/gmhafiz/golang-database-library-orm-example.

Before going further into the implementation, we have a convenience struct to retrieve our custom parameter. Here, we declare what field(s) we accept for filtering the users. Then we use queries.Get() method to retrieve them.

type Filter struct {
	Base filter.Filter

	Email     string
	FirstName string
}
func filters(queries url.Values) *Filter {
	f := filter.New(queries)

	return &Filter{
		Base: *f,

		Email:     queries.Get("email"),
		FirstName: queries.Get("first_name"),
	}
}

The filter/base.go file which is shared by all, handles parsing of query parameters. To use it, we call the package’s filters() function and pass in URL query from handler’s http.Request.

func (h *handler) List(w http.ResponseWriter, r *http.Request) {
	f := filters(r.URL.Query())

Then we inject the f variable down to our data access layer.

sqlx

We achieve dynamic filtering by a field by building the strings ourselves. Care must be taken that we do not concatenate user’s input into the query itself. Instead, we must use a placeholder.

1. Filter By Email

We start by declaring a couple of sql clauses. We select all records from users table and limit it to the first 30 records.

So what we want is basically the following query but with dynamic WHERE according to what is passed in the URL query parameter.

SELECT * FROM users WHERE LOWER(email) = ? ORDER BY id LIMIT 30 OFFSET 0;

Note that there are spaces in the strings because we need some spacing when we combine the strings.

selectClause := "SELECT * FROM users "  // notice the space at the end?
paginateClause := " LIMIT 30 OFFSET 0" // and at the beginning?

Then we start appending LOWER(email) = ? clause to our empty whereClauses string. We had to use LOWER() function of postgres because our schema is not case-insensitive. Unlike mysql, a database can be built using ci (case-insensitive) collation with ...collate utf8mb4_general_ci;. We also use ? placeholder instead of using $ placeholder because we cannot be sure of the order when filtering by column is optional. We can always rebind the whole query with sqlx’s Rebind() method.

Once whereClauses concatenation are completed, we concatenate to the full query.

if len(whereClauses) > 0 {
    fullQuery += "WHERE "
    fullQuery += strings.Join(whereClauses, " AND ")
}

…do not forget to concatenate the final pagination query.

fullQuery += paginateClause

And finally, we order by id

fullQuery += " ORDER by id;" // space at beginning

There is a lot of concatenation, and it is easy to get wrong.

2. Sorting

Sorting is similar to doing filtering. You need to hard code the select and paginate clause

selectClause := "SELECT * FROM users "
paginateClause := " LIMIT 30 OFFSET 0;"

Then loop filters.Base.Sort to concatenate column and order string along with arguments. Looping filters.Base.Sort gives the flexibility to client to determine which column order comes first.

for col, order := range filters.Base.Sort {
    sortClauses += fmt.Sprintf(" %s ", col)
    sortClauses += fmt.Sprintf(" %s ", order)
}

Concatenating strings make me feel uneasy. Is this vulnerable to sql injection? You can try hitting the api with:

curl  'http://localhost:3080/api/sqlx/user?sort=id;select%20*%20FROM%20users;,-- '

And the code will create the following sql query:

SELECT * FROM users  ORDER BY  id;select * FROM users  --;  LIMIT 30 OFFSET 0;

Executing this sql query returns an error so we are safe.

ERROR: cannot insert multiple commands into a prepared statement (SQLSTATE 42601)

3. Pagination

Pagination using LIMIT and OFFSET is straightforward. We create a full query with both clauses, and we supply the arguments from our filters variable.

paginateClause := fmt.Sprintf(" LIMIT ? OFFSET ?")

rows, err := r.db.QueryxContext(ctx, fullQuery, filters.Base.Limit, filters.Base.Offset)

All in all, there is a lot of boilerplate perform these 3 types of operations. Care must be taken that we not allow any sql injection

sqlc

There is a terrible way to do it in sqlc by writing all permutations of columns. We do not really want to write them all because 2 columns means there are 2! == 4 permutation, for example:

  1. Query without filtering
  2. Query with first_name filtering
  3. Query with email filtering
  4. Query with both first_name and email filtering.

If we have 3 columns, 3 factorial is 6. And 4 factorial is 24 permutations!.

So we try with conditional SQL query:

-- name: ListDynamicUsers :many
SELECT id, first_name, middle_name, last_name, email, password, favourite_colour
FROM users
WHERE (@first_name::text = '' OR first_name ILIKE '%' || @first_name || '%')
  AND (@email::text = '' OR email = LOWER(@email) )
ORDER BY (CASE
              WHEN @first_name_desc::text = 'first_name' THEN first_name
              WHEN @email_desc::text = 'email' THEN email
    END) DESC,
         (CASE
              WHEN @first_name_asc::text = 'first_name' THEN first_name
              WHEN @email_asc::text = 'email' THEN email
             END),
         id
OFFSET @sql_offset LIMIT @sql_limit;

For example, whenever first_name is present, then the whole (@first_name::text = '' OR first_name ILIKE '%' || @first_name || '%') line will be true.

Doing order is tricky when we want to decide whether we are sorting a column by ascending or descending order. We use CASE ... THEN ... END to decide whether we want to include the clause or not.

One thing missing is WHERE and ordering by favourite colour in which sqlc doesn’t play well with pgx driver as far as I know.

1. Filter By Email

To use, we need to fill in the generated struct

p := ListDynamicUsersParams{
    FirstName: f.FirstName,
    Email:     f.Email,
    SqlOffset: int32(f.Base.Offset),
    SqlLimit:  int32(f.Base.Limit),
}

2. Sorting

Since we store sort information in a struct, we need to loop them and assign to the generated ListDynamicUsersParam struct

p := ListDynamicUsersParams{
    SqlOffset: int32(f.Base.Offset),
    SqlLimit:  int32(f.Base.Limit),
}

if len(f.Base.Sort) > 0 {
    for col, order := range f.Base.Sort {
        if col == "first_name" {
            if order == "ASC" {
                p.FirstNameAsc = col
            } else {
                p.FirstNameDesc = col
            }
        }
    }
}

3. Pagination

Not much drama with pagination. We simply provide the values from the base filter struct.

p := ListDynamicUsersParams{
    SqlOffset: int32(f.Base.Offset),
    SqlLimit:  int32(f.Base.Limit),
}

As you can see, using conditional greatly reduces the boilerplate as compared to our sqlx example.

This also means that we could have used conditional sql for sqlx too.

gorm

Filter by Email

In gorm, we pass the Find() with an address of a struct.

var users []*User
err := r.db.WithContext(ctx).
    Select([]string{"id", ...
    Find(&users).
    Error

It appears that gorm will happily generate correct sql query no matter if one value of a field is empty.

SELECT "id","first_name","middle_name","last_name","email" FROM "users" WHERE "users"."first_name" = 'Jake' LIMIT 30

Note that requesting with ?first_name=jake won’t yield any result because the case does not match. To fix this, we cannot use struct map, instead we need to use Where() and Or() methods.

Where("email = ? ", f.Email).
Or("first_name ILIKE ?", f.FirstName).
Or("favourite_colour = ?", f.FavouriteColour).
Find(&users).

Note that the method Find() is called near the end of the query building.

Like many Gorm operations, the order of First() (or Find()) is important. Gorm will ignore subsequent query building if you are doing it too early.

// don't do this!
err := r.db.WithContext(ctx).
    Select([]string{"id", "first_name", "middle_name", "last_name", "email", "favourite_colour"}).
    Offset(f.Base.Offset).
    Limit(int(f.Base.Limit)).
    
    Find(&users). // you'll get a wrong result!

    Where("email = ? ", f.Email).
    Or("first_name ILIKE ?", f.FirstName).
    Or("favourite_colour = ?", f.FavouriteColour).
    Error
if err != nil {
    return nil, err
}

yields incorrect query with many missing clauses.

SELECT "id","first_name","middle_name","last_name","email","favourite_colour" FROM "users" LIMIT 10

Sort By First Name

Since we store our sorting field in a map, we need to build a string clause. We add them to a slice:

var orderClause []string
for col, order := range f.Base.Sort {
    orderClause = append(orderClause, fmt.Sprintf("%s %s", col, order))
}

Then Order() can accept a string separated by a comma.

err = r.db.WithContext(ctx).
    Limit(int(f.Base.Limit)).
    Order(strings.Join(orderClause, ",")).
    Find(&users).
    Error

Pagination

Pagination is simple but the order still matters,

err = r.db.WithContext(ctx).Debug().
    Limit(int(f.Base.Limit)).
    Offset(f.Base.Offset).
    Order("id").
    Find(&users). // order matters!
    Error

sqlboiler

Filter by Email

In sqlboiler, you need to supply models.Users() method with a slice of query modifier call qm.QueryMod.

Just as we have done with sqlx, we check if the Filter variable has any of the fields being populated.

var mods []qm.QueryMod

if f.Email != "" {
    mods = append(mods, models.UserWhere.Email.EQ(strings.ToLower(f.Email)))
}

if f.FirstName != "" {
    mods = append(mods, qm.Where("first_name ILIKE ?", strings.ToLower(f.FirstName)))
}

We can make our input lowercase but, we still not be able to pass LOWER() sql clause like in sqlx above.

Do this for each parameter that we allow to filter on. Then we supply models.Users() with a variadic parameter.

all, err := models.Users(mods...).All(ctx, r.db)

Sort by First Name

We also make use of qm.QueryMod slice.

var mods []qm.QueryMod

for key, order := range f.Base.Sort {
    mods = append(mods, qm.OrderBy(fmt.Sprintf("%s %s", key, order)))
}

all, err := models.Users(mods...).All(ctx, r.db)

The order is passed using qm.OrderBy("first_name desc")

Pagination

Sqlboiler uses qm.Limit() and qm.Offset() for pagination.

if f.Base.Limit != 0 && !f.Base.DisablePaging {
    mods = append(mods, qm.Limit(f.Base.Limit))
}
if f.Base.Offset != 0 && !f.Base.DisablePaging {
    mods = append(mods, qm.Offset(f.Base.Offset))
}

Like everything so far, pass into All()

all, err := models.Users(mods...).All(ctx, r.db)

ent

Filter by Email

In ent, we supply a slice of ‘predicates’ to Where() method, similar to how sqlboiler uses qm.QueryMod.

First, we check if a filter is available

var predicateUser []predicate.User

if f.Email != "" {
    predicateUser = append(predicateUser, user.EmailEQ(f.Email))
}
if f.FirstName != "" {
    predicateUser = append(predicateUser, user.FirstNameContainsFold(f.FirstName))
}

Then passing it into Where()

return r.db.User.Query().
    Where(f.PredicateUser...).
    Limit(30).
    All(ctx)

There is no way you can mess up with the order like gorm.

There is no fear of leaking hashed password to the client on serialization because when we marked that column with Sensitive() in the ent schema.

// Fields of the User.
func (User) Fields() []ent.Field {
	return []ent.Field{
		...
		field.String("password").Sensitive(),
		...

Sort by first_name

Ent uses accepts a slice of gen.OrderFunc to Order() method.

First, we loop our f,Base.Sort to create the necessary query modifiers:

var orderFunc []gen.OrderFunc

for col, ord := range f.Base.Sort {
    if ord == SqlAsc {
        orderFunc = append(orderFunc, gen.Asc(col))
    } else {
        orderFunc = append(orderFunc, gen.Desc(col))
    }
}

Then we simply pass it into Order()

return r.db.User.Query().
    Order(orderFunc...).
    All(ctx)
}

Pagination

Ent uses Limit() and Offset() builder method

query := r.db.User.Query()
if f.Base.Limit != 0 && !f.Base.DisablePaging {
    query = query.Limit(f.Base.Limit)
}
if f.Base.Offset != 0 && !f.Base.DisablePaging {
    query = query.Offset(f.Base.Offset)
}
resp, err := query.
    // When using LIMIT, it is important to use an ORDER BY clause that
    // constrains the result rows into a unique order
    // https://www.postgresql.org/docs/14/queries-limit.html
    Order(gen.Asc(user.FieldID)).
    All(ctx)
return resp, nil

In conclusion only sqlc did not pass all functionalities we required like dealing with dynamic filtering and case-insensitive user input. Concatenating string in sqlx is potentially error-prone (and dangerous!). Order of methods are important in Gorm. Both sqlboiler and ent offer type safe query and easy query building.

Next > (coming!) Golang Database Library ORM Example - Transaction