Golang Database Library Orm Example - Get

December 14, 2021

a highly stylized photo of baby elephants, playing in the cities, with trees, and building, blurred background, trending on arts 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 {
        if errors.Is(err, sql.ErrNoRows) {
            return &db.UserResponse{}, &db.Err{Msg: message.ErrRecordNotFound.Error(), Status: http.StatusNotFound}
        }
        log.Println(err)
        return &db.UserResponse{}, &db.Err{Msg: message.ErrInternalError.Error(), Status: http.StatusInternalServerError}	
	} 

	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 at the moment. 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"`
}

Note that usage of that db struct tag will cause reflection to be used. There is a way to avoid it by matching the field names with database column names. For example First Name to match against first name database column. Title Case naming style is not a convention in Go though.

One last thing is the way we handle error. It is possible that the client requests for a user ID that do not exist, or have been removed from the database. So we want to return appropriate message and correct HTTP status. We can use error.Is() function to compare the err value against built in error provided in the sql package.

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}
		}
		log.Println(err)
	    return &db.UserResponse{}, &db.Err{Msg: message.ErrInternalError.Error(), Status: http.StatusInternalServerError}
	}

	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
}

squirrel

Like in WHERE IN, squirrel also uses sq.Eq{} struct for a WHERE sql clause.

rows := r.db.
	Select("*").
	From("users").
	Where(sq.Eq{"id": userID}).
	QueryRowContext(ctx)

Select() and From() method on the other hand are ok because their method signatures clearly states what type they require.

We then finish off with a finisher QueryRowContext() that returns a RowScanner interface. That means, we have to manually scan and take care of the column orders.

var u db.UserDB
err := rows.Scan(&u.ID, &u.FirstName, &u.MiddleName, &u.LastName, &u.Email, &u.Password, &u.FavouriteColour, &u.UpdatedAt)
if err != nil {
    if errors.Is(err, sql.ErrNoRows) {
        return &db.UserResponse{}, &db.Err{Msg: message.ErrRecordNotFound.Error(), Status: http.StatusNotFound}
    }
    log.Println(err)
    return &db.UserResponse{}, &db.Err{Msg: message.ErrInternalError.Error(), Status: http.StatusInternalServerError}
}

gorm

Gorm is relatively simpler 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 {
        if errors.Is(err, gorm.ErrRecordNotFound) {
            return &User{}, &db.Err{Msg: message.ErrRecordNotFound.Error(), Status: http.StatusNotFound}
        }
        log.Println(err)
        return &User{}, &db.Err{Msg: message.ErrInternalError.Error(), Status: http.StatusInternalServerError}
    }

	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) {
	user, err := models.FindUser(ctx, r.db, userID)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, errors.New("no record found")
		}
		log.Println(err)
		return &models.User{}, &db.Err{Msg: message.ErrInternalError.Error(), Status: http.StatusInternalServerError}
	}

	return user, nil
}

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 &gen.User{}, &db.Err{Msg: message.ErrRecordNotFound.Error(), Status: http.StatusNotFound}
        }
        log.Println(err)
        return &gen.User{}, &db.Err{Msg: message.ErrInternalError.Error(), Status: http.StatusInternalServerError}
    }

	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