Golang Database Library Orm Example - List

December 14, 2021

In this post, we will look at comparing listing of user resource. We limit the record count to 30 and the offset is from 0. We will look at pagination in section 4 of the series, (coming!)Dynamic list filter from query parameter

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
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 active users, sort by field, and pagination
(coming!) Transaction - Handle transaction, rollback on error

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

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, fmt.Errorf("db scanning error")
		}
		users = append(users, &UserResponse{
			ID:         u.ID,
			FirstName:  u.FirstName,
			MiddleName: u.MiddleName.String,
			LastName:   u.LastName,
			Email:      u.Email,
		})
	}
	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. Otherwise, you can always omit selecting the password field from the sql query:

const List = "SELECT (id, first_name, middle_name, last_name, email) FROM users LIMIT 30 OFFSET 0;"

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 look at!

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 still need to copy to userResponse struct.

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

gorm

Listing user records looks deceptively simple. It looks like a normal query builder but there are many ways to 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

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.
- err = r.db.WithContext(ctx).Find(&user).Limit(30).Error <- passing &user returns one record.
You are doing a select ‘*’ operation, selecting all fields using *. If you want to select just a few fields:
- err := r.db.WithContext(ctx).Select([]string{"id", "first_name", "last_name"}).Find(&users).Limit(30).Error Gorm does not provide a constant for each of database field’s name.
If you want to do a ‘WHERE’ clause, put the User struct as a second parameter to the Find() method, not Where() which is more intuitive!
- err = r.db.WithContext(ctx).Find(&users, User{FirstName: "John"}).Limit(30).Error
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. Neither sqlx nor sqlc require typing any magic string for such a simple operation like this.

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) ([]*sqlx2.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 []*sqlx2.UserResponse
	for _, user := range users {
		userResponse = append(userResponse, &sqlx2.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 this a more type-safe alternative 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 discovering what to use requires you 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 copy to the new UserResponse struct.

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().Limit(30).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).
    Limit(30).
    All(ctx)

To use a ‘WHERE’ clause, you also use the provided gen/user package

return r.db.User.Query().
    Where(
        user.ID(1),
    ).
    Limit(30).
    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.

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

Next > Golang Database Library ORM Example Get

Golang Database Library Orm Example - List

Table of Contents

sqlx

sqlc

gorm

sqlboiler

ent