Golang Database Library Orm Example - List

In this post, we will look at comparing listing of user resource. We limit the record to the first thirty records.

Basically in all of them we want to make the following query

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

Table of Contents

sqlx

In sqlx, listing an array of json looks similar to how sqlx creates a record. We use QueryxContext() method that allows us to use StructScan() that makes scanning easier - no longer do you need to type out all the field names.

const list = "SELECT * FROM users LIMIT 30 OFFSET 0;"

func (r *database) List(ctx context.Context) (users []*UserResponse, err error) {
	rows, err := r.db.QueryxContext(ctx, List)
	if err != nil {
		return nil, fmt.Errorf("error retrieving user records")
	}

	for rows.Next() {
		var u userDB
		err = rows.StructScan(&u)
		if err != nil {
			return nil, errors.New("db scanning error")
		}
		users = append(users, &UserResponse{
			ID:         u.ID,
			FirstName:  u.FirstName,
			MiddleName: u.MiddleName.String,
			LastName:   u.LastName,
			Email:      u.Email,
			UpdatedAt:  u.UpdatedAt.String(),
		})
	}
	return users, nil
}

We collect the records as an array of UserResponse. This is because we do not want to expose the password to the client.

This is true for returning users' details, but sometimes you want to play around with that protected field and only converting to UserResponse at handler layer, just before returning that data to client. So instead of scanning to UserResponse struct, you should scan into a struct with a Password field.

text[] data type

Since we work with postgres, we should also explore into one of its data type not present in mysql, sql server, and oracle, array. We basically want to select tags of a specific user.

In the users schema, we have a text[] data type. It is interesting to see how we can select this kind of data type.

create table if not exists users
(
    ...
    tags   text[]   default '{}'::text[]   not null
    ...

To make it simple, we want to select its tags based on user ID:

SELECT tags FROM users WHERE users.id = $1

The result coming from the database for the column tags is now in binary instead of text (data type is text[]). So in the code, data coming from database is in bytes (bytes is an alias for uint8) but, we are scanning the result into a slice of string. So, we cannot simply scan using &values because it is a mismatched data type - *[]string vs []uint8. What we do instead is to use pq.Array(), which helps us into choosing the optimum data type for scanning.

func (r *repository) Array(ctx context.Context, userID int64) ([]string, error) {
	selectQuery := "SELECT tags FROM users WHERE users.id = $1"

	var values []string

	err := r.db.QueryRowContext(ctx, selectQuery, userID).Scan(pq.Array(&values))
	if err != nil {
		return nil, err
	}

	return values, nil
}

You write more code, but you have more control and a better performance dealing with this sql query with binary data.

sqlc

func (r *database) List(ctx context.Context) ([]ListUsersRow, error) {
	return r.db.ListUsers(ctx)
}

That’s it. Only 3 lines, or 1 line, however you may want to look at it!

Since in the query we omit selecting the password, this prevents that field from leaking. So I cheated a bit. If you require to play around with the password value, you will need to remember to transform to a userResponse struct.

-- name: ListUsers :many
SELECT (id, first_name, middle_name, last_name, email)
FROM users
LIMIT 30
OFFSET 0;

The sql query above is annotated with a comment that comprises a method name and the expected count, one or many.

Anytime you want to select a few columns, you will need to create a new sql query. sqlc in not flexible when it comes to dynamic queries at runtime.

squirrel

As squirrel is a query builder, we build the query that we wanted by using a builder pattern.

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

And since we now use methods instead of writing raw SQL, it suddenly becomes easier to create dynamic query as you will see in the dynamic list section.

Squirrel requires a finisher which will then return a sql rows and a possible error.

gorm

Listing user records looks deceptively simple. It looks like a normal query builder but there are many ways it can go wrong. Have a look at this:

func (r *repo) List(ctx context.Context) ([]*User, error) {
	var users []*User
	err = r.db.WithContext(ctx).Find(&users).Limit(30).Error
	if err != nil {
		return nil, fmt.Errorf(`{"message": "db scanning error"}`)
	}

	return users, nil
}

At a glance, there isn’t anything obviously wrong with the code above. In fact, if you run the code, you will get a result with no error. However, there are subtle mistakes or quirks compared to the query builder we have seen so far. You need to:

1. Tell the model you are asking for a list with []*User

   • But you can pass in a *User instead, and it will return 1 record.
   • The Find() method accepts an interface, so it will happily accept anything whether a slice or a single item (or something totally different).

var user User
err = r.db.WithContext(ctx).Find(&user).Limit(30).Error` <- passing `&user` returns one record.

2. You are doing an implied select operation, selecting all fields using *. If you want to select just a few fields:

   • Gorm does not provide a constant for each of database field’s name.
   • Instead of passing a struct, you pass a slice of string. Need to refer documentation because method’s signature does not help - its signature is saying it accepts a query interface and a list of arguments?! Select(query interface{}, args ...interface{}) (tx *DB)
   • You can also pass strings to Select() and it will give the same output even if it doesn’t conform to method signature and the IDE telling you are wrong.

// this
err := r.db.WithContext(ctx).Select([]string{"id", "first_name", "last_name"}).Find(&users).Limit(30).Error

// also works, look at below screenshot
err := r.db.WithContext(ctx).Select("id", "first_name", "last_name").Find(&users).Limit(30).Error

first argument is a query and the rest are args as expected, but it still returns the same result

3. If you want to do a ‘WHERE’ clause, you can use a Where() method. But you can also put the User struct as a second parameter to the Find() method!

err = r.db.WithContext(ctx).Find(&users, User{FirstName: "John"}).Limit(30).Error

4. Returning an error is optional.

On the upside, you do not have to worry about leaking password to client because in the User model, we set the json struct tag to -. Thus, no copying to a new struct is needed.

type User struct {
	ID         uint
	FirstName  string
	MiddleName string
	LastName   string
	Email      string
	Password   string `json:"-"`
}

5. WithContext() is optional.

In Go, we tend to use context for cancellation, deadline and timeout. WithContext() is optional which means we can lose these features by forgetting to chain it.

6. ‘Finisher’ method is optional, and the order is important.

Finisher methods like Find(), First(), and Save() are optional when building a query.

err = r.db.
   WithContext(ctx).
   Find(&users). // <- called to early! Subsequent `Limit()` will be ignored, so it'll return all records in the database.
   Limit(30).
   Error

If you do not include them, Gorm will do nothing, and thus will not return either an error or any result. The order when you call those finishers are also important. If you call it too early, Gorm will ignore subsequent methods. For example, the examples I have shown so far has Limit() called after Find(). That means, Gorm will ignore anything after Find() finisher and return all records in the database! If you scroll back to point number 2, you may have noticed that in the code screenshot, it returns 32 records (everything I have) in the database instead of returning 10 records. Unlike other libraries, calling other methods after a finisher is invalid.

Using the API requires a bit of discovery and careful reading of its documentation since its method signature does not help.

In my opinion, thanks to many pitfalls Gorm has, it is certainly the hardest to use so far.

sqlboiler

In sqlboiler, listing a resource is done with All() method. To limit the records, you have to use the query modifier (qm) package.

import "github.com/volatiletech/sqlboiler/v4/queries/qm"

func (r *database) List(ctx context.Context) ([]*db.UserResponse, error) {
	users, err := models.Users(qm.Limit(30)).All(ctx, r.db)
	if err != nil {
		return nil, fmt.Errorf("error getting users")
	}

	var userResponse []*db.UserResponse
	for _, user := range users {
		userResponse = append(userResponse, &db.UserResponse{
			ID:         uint(user.ID),
			FirstName:  user.FirstName,
			MiddleName: user.MiddleName.String,
			LastName:   user.LastName,
			Email:      user.Email,
		})
	}
	return userResponse, nil
}

If you want to use a ‘WHERE’ clause, you supply a UserWhere struct

users, err := models.Users(
    qm.Limit(30),
    models.UserWhere.FirstName.EQ("John"),
).
    All(ctx, r.db)

If you want to select for a few fields, you provide the generated string constant from the models package using {{model}}Columns pattern.

users, err := models.Users(
    qm.Limit(30),
    qm.Select(models.UserColumns.ID, models.UserColumns.LastName),
).
    All(ctx, r.db)

sqlboiler is a lot nicer than gorm for sure. You avoid any magic string which makes it more type-safe compared to gorm. It will complain if you do not provide a context. You cannot ignore the error return unless you explicit does so with underscore, _.

But, a fair bit of discovering is needed to know all qm helpers - {{model}}Where, and {{model}}Columns patterns. For example, in my opinion it is more intuitive to use models.Users().Limit(30).All(ctx, r.db) to limit instead of using a query modifier.

Password field is automatically generated, and you cannot set it to private. So you will need to remember to perform data transform object (DTO) before returning to client - as you should always do.

ent

In ent, it is similar to sqlc, but we do not have to worry about copying the fields to a new struct without password because that field is already protected (or in ent’s lingo, sensitive) from json marshalling.

Limiting the number of records is done with Limit() builder method instead of using another package like sqlboiler.

func (r *database) List(ctx context.Context) ([]*ent.User, error) {
    return r.db.User.Query().
        Order(ent.Asc(user.FieldID)).
        Limit(30).
        Offset(0).
        All(ctx)
}

To use a ‘SELECT’ for a few fields, you use the generated string constant in the ent/user package that gives a {{ model.Field{{FieldName}} }} pattern.

return r.db.User.Query().
    Select(user.FieldFirstName).
    Order(ent.Asc(user.FieldID)).
    Limit(30).
    Offset(0).
    All(ctx)

To use a ‘WHERE’ clause, you also use the generated ent/user package

return r.db.User.Query().
    Where(user.ID(1)).
    All(ctx)

The advantage with ent, like gorm, is we can continue using that sensitive field but, we are assured it will not be leaked to client. However, the API is a lot more intuitive and looks more like an ORM in other language.

In conclusion, sqlc and ent were the easiest to use. sqlc is pretty much a one-liner once you have written the correct sql query. ent gives the best API for query building. In both, we do not have to worry about leaking the password field to the client. However, you cannot customise sqlc queries at runtime.

While the examples here are very simple - list 30 records of the users at most - real world use case are usually more complex. We will see how we eager load a relationship and in the dynamic list section, we will explore at parsing query param for sorting and pagination.

Before going to those sections, we will finish of WHERE IN(?) and the rest of CRUD stuff.

Next > Golang Database Library ORM Example Where IN ($1, $2, …)