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

 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, Get, 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,
	}, nil
}

This struct needs db struct tag annotated on each field for the scanning to work.

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

We can select all fields except password if you do not want to bother with copying the fields to UserResponse struct.

To make Json serialization work, you need to add a json struct tag to each field ofuserDB. However, mixing database struct and client response struct usually isn’t a good practice.

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

sqlc

func (r *database) Get(ctx context.Context, userID int64) (GetUserRow, error) {
	return r.db.GetUser(ctx, userID)
}

sqlc continues to impress with its API usage. Only three lines are needed for a simple listing of user records.

The sql query we made excludes the password field

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

If you require to play around the password field, then you will have to copy over the struct like sqlx above.

gorm

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

We 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().

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

	err := r.db.WithContext(ctx).First(&user, userID).Error // First() also can accept a `var user []*User`
	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.

Since 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 in 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 here, you do it from models package,

Finally, you still need to copy the fields to a new struct:

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

ent

It also a one-liner for ent. Unlike sqlboiler, in ent, everything is accessed starting from r.db.

func (r *database) Get(ctx context.Context, userID uint64) (*gen.User, error) {
	return r.db.User.Query().Where(user.ID(uint(userID))).First(ctx)
}

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