Golang Database Library Orm Example - Get

December 14, 2021

In this post, we will look at how these libraries and ORM deal with fetching a single record given an ID. There should not be any drama as we only be doing a simple query as follows:

 SELECT * FROM users WHERE id = $1;

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

sqlx

In sqlx, GetContext() is a convenience method that both fetches and scan the result into our custom userDB struct.

func (r *database) Get(ctx context.Context, userID int64) (*UserResponse, error) {
	var u userDB
	err := r.db.GetContext(ctx, &u, getSQLQuery, userID)
	if err != nil {
		return nil, fmt.Errorf("db error")
	}

	return &UserResponse{
		ID:         u.ID,
		FirstName:  u.FirstName,
		MiddleName: u.MiddleName.String,
		LastName:   u.LastName,
		Email:      u.Email,
		UpdatedAt:  u.UpdatedAt.String(),
	}, nil
}

For scanning to work, this struct needs db struct tag annotated on each field. middle_name is nullable, so we declare the type to be sql.NullString to allow value to be scanned as either a string or a null value.

type userDB struct {
	ID         uint           `db:"id"`
	FirstName  string         `db:"first_name"`
	MiddleName sql.NullString `db:"middle_name"`
	LastName   string         `db:"last_name"`
	Email      string         `db:"email"`
	Password   string         `db:"password"`
}

sqlc

While inflexible, sqlc continues to impress with its API usage. GetUser() method is generated for us by annotating the sql query with the following the name that we want and how many records it is returning.

-- name: GetUser :one
SELECT id, first_name, middle_name, last_name, email, favourite_colour
FROM users
WHERE id = $1;

To use, we simply call the generated method. The rest are simply error handling and DTO transform.

func (r *database) Get(ctx context.Context, userID int64) (*db.UserResponse, error) {
	res, err := r.db.GetUser(ctx, userID)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return &db.UserResponse{}, &db.Err{Msg: message.ErrRecordNotFound.Error(), Status: http.StatusNotFound}
		}
		return nil, err
	}

	return &db.UserResponse{
		ID:              uint(res.ID),
		FirstName:       res.FirstName,
		MiddleName:      res.MiddleName.String,
		LastName:        res.LastName,
		Email:           res.Email,
		FavouriteColour: string(res.FavouriteColour),
		UpdatedAt:       res.UpdatedAt.String(),
	}, nil
}

gorm

Gorm is also relative simple by using a basic First() method to obtain a record by its id.

We must remember to chain Error after First() method. This is easy to forget because it is not compulsory.

Remember that in the previous post where Find() can also accept a slice of users? First() method can also accept a slice. So be careful with what you are putting into it. The number of records being returned is not determined by the method we use, but by the type given to Find() or First().

func (r *repo) Get(ctx context.Context, userID int64) (*User, error) {
	var user User

	err := r.db.WithContext(ctx).
	  // First() also can accept a `var user []*User` which can return more than one record!
	  First(&user, userID).
	  Error  
	if err != nil {
		return nil, err
	}

	return &user, nil
}

The second argument of First() accepts a variadic interface. So unless you refer to gorm’s website for a documentation, you cannot infer what type it needs.

As our User model already has - json struct tag on password, we do not have to worry it leaking to client.

type User struct {
	ID         uint   `json:"id"`
	FirstName  string `json:"first_name"`
	MiddleName string `json:"middle_name"`
	LastName   string `json:"last_name"`
	Email      string `json:"email"`
	Password   string `json:"-"`
}

sqlboiler

You do not have to write much boilerplate to get a single item in sqlboiler. FindUser() argument types are laid out specifically and the name helps into inferring what you need to supply.

func FindUser(ctx context.Context, exec boil.ContextExecutor, iD int64, selectCols ...string) (*User, error) {}

This makes getting a record in sqlboiler a lot easier than gorm:

func (r *database) Get(ctx context.Context, userID int64) (*models.User, error) {
	return models.FindUser(ctx, r.db, userID)
}

When inserting a record, you do it from a User struct (user.Insert()). But to retrieve an item, you do it from models package,

Finally, you still need to copy the fields to a new struct if you want to control what the client sees.

&db.UserResponse{
		ID:         uint(u.ID),
		FirstName:  u.FirstName,
		MiddleName: u.MiddleName.String,
		LastName:   u.LastName,
		Email:      u.Email,
	}

ent

Discounting error handling, it also a one-liner for ent.

	u, err := r.db.User.Query().Where(user.ID(uint(userID))).First(ctx)
	if err != nil {
		if gen.IsNotFound(err) {
			return nil, errors.New("no record found")
		}
		return nil, err
	}

	return u, nil

No copying to a new struct is needed since the password field is already made sensitive.

 

Getting a specific resource is a lot simpler than creating and listing them. All of them have their own convenience method to scan database result to a Go struct.

 

Next > Golang Database Library ORM Example Update