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.

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 above it with 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.

gorm

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

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
}

For an ORM, listing a resource doesn’t feel like one. 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 will happily accept a slice or a single item

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

2. You are doing a 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.

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

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

Using the API requires a bit of discovery and careful reading of its documentation.

In my opinion, 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.

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) ([]*gen.User, error) {
    return r.db.User.Query().
        Order(gen.Asc(user.FieldID)).
        Limit(30).
        Offset(0).
        All(ctx)
}

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

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

To use a ‘WHERE’ clause, you also use the provided gen/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, …)