Golang Database Library Orm Example Dynamic List

Comparison between popular go libraries and ORM for database access layer.

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 followed by first name. A common way of making this request is by passing them using query parameters in a GET operation in the URL request.

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. See Why You Shouldn’t Use OFFSET and LIMIT For Your Pagination. ORDER BY also must be added in all LIMIT and OFFSET.

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/15/queries-limit.html.

In short, we will look at these three common list operations

Filter by field
Sort by field and direction
Pagination

This is part of a series of blog posts including:

Introduction
Create - Create a single record
List
- Returns the first 30 records of a resource
- Use WHERE IN(?)
Get - Get a single record
Update - Update a single field
Delete - Delete a record
One-To-Many - Eager load one to many relationship between two tables
Many-To-Many - Eager load many to many relationships between two tables using a pivot table
Dynamic List - Return a record with a list of names, sorting, and pagination
Transaction - Handle transaction, rollback on error
SQL Injection - Try to list all users by using malicious query parameters
- and Conclusion

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

Before going further into the implementation, we add a convenience function in our handler(controller) layer that receives client requests. This function parse requests by its query parameters. Here, we declare what field(s) we accept for filtering the users. Then we use url.Values.Get() method to retrieve them. We decided that we can put multiple names separated by a comma on one query parameter, so we have a helper function that can turn a string seperated by a comma into a slice of strings.

package db

type Filter struct {
	Base filter.Filter

	Email           string
	FirstName       string
	FavouriteColour string

	LastName []string
}

func Filters(v url.Values) *Filter {
	f := filter.New(v)

	lastNames := param.ToStrSlice(v, "last_name")

	return &Filter{
		Base: *f,

		Email:           v.Get("email"),
		FirstName:       v.Get("first_name"),
		FavouriteColour: v.Get("favourite_colour"),

		LastName: lastNames,
	}
}

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

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

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

sqlx

First we show a dodgy way of doing dynamic filtering by a field by building the strings ourselves. Care must be taken that we do not concatenate users’ input into the query itself because it will be vulnerable to sql injection. Instead, we must use placeholders. In the subsequent sqlc section, we will show a better way - using conditional sql query.

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 e.g. ...collate utf8mb4_unicode_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 to perform these three types of operations. Care must be taken, so we do 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:

Query without filtering
Query with first_name filtering
Query with email filtering
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
            }
        }
    }
}

Obviously this only handles first_name. You need to repeat the col check for each column you want to sort. One thing I haven’t managed to do is conditional query on an enum column.

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. It is just a matter of getting the initial sql query correct first.

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

squirrel

Since squirrel is a query builder, it should shine with dynamic queries.

Filter by Email

Chaining the methods that we want is exactly how we build the query.

builder := r.db.
	Select("*").
	From("users").
	OrderBy("id")

Then we conditionally chain each filter that we want

if f.Email != "" {
	builder = builder.Where(sq.Eq{"email": f.Email})
}

if f.FirstName != "" {
	builder = builder.Where(sq.Eq{"first_name": f.FirstName})
}

if f.FavouriteColour != "" {
	builder = builder.Where(sq.Eq{"favourite_colour::text": f.FavouriteColour})
}

Notice that favourite_colour is casted to a postgres’ type called text because this column in an enum.

If we want to do an ILIKE operator, we use sq.ILike{} struct instead.

if f.Email != "" {
	builder = builder.Where(sq.ILike{"email": "%" + f.Email + "%"})
}

and finish it off with

rows, err := builder.QueryContext(ctx)
if err != nil {
	return nil, err
}

In all, scanning is the same - looping and appending.

Sort By First Name

As long as user input is sanitised, you can get away with chaining with

for col, order := range f.Base.Sort {
    builder = builder.OrderBy(col + " " + order)
}

Pagination

Squirrel provides Limit() and Offset() methods

rows, err := r.db.
	Select("*").
	From("users").
	Limit(uint64(f.Base.Limit)).
	Offset(uint64(f.Base.Offset)).
	OrderBy("id").
	QueryContext(ctx)

Building these simple queries are easy enough.

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 since Gorm does not perform LOWER() to the input or ILIKE to the column. 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). // Find() is called too early. 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
}

The above code 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))
}
mods = append(mods, qm.OrderBy(models.UserColumns.ID)) // don't forget to sort

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 each 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).
    Offset(0).
    Order(gen.Asc(user.FieldID)).
    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 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.
    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. Squirrel is straightforward. Finally, both sqlboiler and ent offer type safe query and easy query building.

Next > Golang Database Library ORM Example - Transaction

Golang Database Library Orm Example Dynamic List

Table of Contents

sqlx

1. Filter By Email

2. Sorting

3. Pagination

sqlc

1. Filter By Email

2. Sorting

3. Pagination

squirrel

Filter by Email

Sort By First Name

Pagination

gorm

Filter by Email

Sort By First Name

Pagination

sqlboiler

Filter by Email

Sort by First Name

Pagination

ent

Filter by Email

Sort by first_name

Pagination