Golang Database Library Orm Example - Update

December 15, 2021

In this post we will see a simple update of a resource by using its ID.

As a good practice, we scan user’s request to a custom UserUpdateRequest struct is. Then we hand over this struct to our database access layer.

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.

type UserUpdateRequest struct {
	FirstName  string `json:"first_name"`
	MiddleName string `json:"middle_name"`
	LastName   string `json:"last_name"`
	Email      string `json:"email"`
}

Note that there’s no ID field because we are going to parse it from the url request.

userID, err := param.Int64(r, "userID")

sqlx

In sqlx, a simple ExecContext() method is used. It requires 3 parameters, a context, the sql query and arguments.

Order of arguments is important. It needs to match with your sql query

Update = "UPDATE users set first_name=$1, middle_name=$2, last_name=$3, email=$4 WHERE id=$5;"
_, err := r.db.ExecContext(ctx, Update,
    req.FirstName,
    req.MiddleName,
    req.LastName,
    req.Email,
    userID,
)
if err != nil {
    return nil, err
}

return r.Get(ctx, userID)

A decision must be made if the endpoint method is PUT or PATCH. If we are using PUT, then client must supply a (almost) complete data before updating. So, client must retrieve use GET, then update what is necessary, then call PUT endpoint.

That means, the client must supply:

{
  "first_name": "John",
  "middle_name": "Middle",
  "last_name": "Doe",
  "email": "john-changed@example.com",
  "favourite_colour": "red"
}

We usually add validation logic in the handler layer.

if req.FirstName == "" || ... {
    respond.Error(w, http.StatusBadRequest, errors.New("required field(s) is/are empty"))
	return
}

Although the client does a GET, we still need to do another Get() because we need to retrieve hashed password. If we are straight away assigning new values, then password column will be empty since client never had this information!

currUser.FirstName = req.FirstName
currUser.MiddleName = req.MiddleName
currUser.LastName = req.LastName
currUser.Email = req.Email
currUser.FavouriteColour = req.FavouriteColour

Finally, we call our own Get() method to return the updated record. Three separate sql queries means this is a good candidate to perform a transaction on.

Named Query

Sqlx has a feature called named queries. To use it, we need to modify our sql query with colon sign. Good thing is this is still a valid postgres query.

const UpdateNamed = "UPDATE users set first_name=:first_name, middle_name=:middle_name, last_name=:last_name, email=:email WHERE id=:id;"

Then we create a new struct with db struct tag to tell which field belongs to which column.

type updateNamed struct {
    ID         int64  `db:"id"`
    FirstName  string `db:"first_name"`
    MiddleName string `db:"middle_name"`
    LastName   string `db:"last_name"`
    Email      string `db:"email"`
}

Finally, we can call namedExecContext() method without worrying about the order.

update := updateNamed{
    ID:         userID,
    FirstName:  req.FirstName,
    MiddleName: req.MiddleName,
    LastName:   req.LastName,
    Email:      req.Email,
}

return r.db.NamedExecContext(ctx, UpdateNamed, update)

Scanning is a pinch in expense of writing more boilerplate beforehand.

sqlc

Unlike sqlx, we do not have to worry about the order because we are filling in the update to the generated struct made by sqlc generate.

However, we would want to perform a transaction over these two sql queries.

func (r *database) Update(ctx context.Context, userID int64, req *sqlx2.UserUpdateRequest) (*GetUserRow, error) {
	err := r.db.UpdateUser(ctx, UpdateUserParams{
		FirstName: req.FirstName,
		MiddleName: sql.NullString{
			String: req.MiddleName,
			Valid:  true,
		},
		LastName: req.LastName,
		Email:    req.Email,
		ID:       userID,
	})
	if err != nil {
		return nil, fmt.Errorf("error updating the user")
	}

	u, err := r.db.GetUser(ctx, userID)
	if err != nil {
		return nil, err
	}

	return &u, nil
}

Note that GetUser() returns GetUserRow but we return a pointer to GetUserRow here.

gorm

Like a typical ORM, there are two operations needed to update a record. Gorm is no exception, thus there is more I/O load.

  1. Get it
  2. Update

Doing two different operations means this is a good candidate to perform a transaction. We will look at it in the last section of this series.

func (r *repo) Update(ctx context.Context, userID int64, req *sqlx.UserUpdateRequest) (*User, error) {
	u := &User{}
	u.ID = uint(userID)
	r.db.First(&u)

	u.FirstName = req.FirstName
	u.MiddleName = req.MiddleName
	u.LastName = req.LastName
	u.Email = req.Email
	err := r.db.WithContext(ctx).Save(&u).Error
	if err != nil {
		return nil, err
	}
	
	return r.Get(ctx, userID)
}

Once we retrieve the record from database, we begin to update the field that we want.

The Save() method can be chained to Error to propagate the error up to our handler. It is easy to forget this because it is not compulsory and Go compiler won’t complain about it.

sqlboiler

Updating in sqlboiler, like gorm, behaves like common ORM; you have to find the record, and then update desired fields.

Note that we omit setting up a password, so that it will not be changed. Since middle_name is an optional field, you have to you null.String struct to fill in the new value. We ignore the first return value with underscore unless you want to know the number of rows being affected.

func (r *database) Update(ctx context.Context, id int64, req sqlx2.UserUpdateRequest) (*models.User, error) {
	user, err := r.Get(ctx, id)
	if err != nil {
		return nil, err
	}

	user.FirstName = req.FirstName
	user.MiddleName = null.String{
		String: req.MiddleName,
		Valid:  true,
	}
	user.LastName = req.LastName
	user.Email = req.Email

	_, err = user.Update(ctx, r.db, boil.Infer())
	if err != nil {
		return nil, err
	}

	return user, nil
}

Important: You need to find the record before updating. If you straightaway call Update() and not setting a password, that password field will be deleted from the database!

// don't do this!
func (r *database) Update(ctx context.Context, id int64, req sqlx2.UserUpdateRequest) (*models.User, error) {
	boil.DebugMode = true
	defer func() {
		boil.DebugMode = false
	}()
	user := &models.User{
		ID:        id,
		FirstName: req.FirstName,
		MiddleName: null.String{
			String: req.MiddleName,
			Valid:  true,
		},
		LastName: req.LastName,
		Email:    req.Email,
	}

	_, err := user.Update(ctx, r.db, boil.Infer())
	if err != nil {
		return nil, err
	}

	return user, nil
}

sqlboiler will attempt to update password field too, which we do not want.

UPDATE "users" SET "first_name"=$1,"middle_name"=$2,"last_name"=$3,"email"=$4,"password"=$5 WHERE "id"=$6
[John { true} Doe john-changed@example.com  13]

ent

Ent uses builder pattern to set the fields.

func (r *database) Update(ctx context.Context, userID int64, req *sqlx.UserUpdateRequest) (*gen.User, error) {
	return r.db.User.UpdateOneID(uint(userID)).
		SetFirstName(req.FirstName).
		SetNillableMiddleName(&req.MiddleName).
		SetLastName(req.LastName).
		SetEmail(req.Email).
		Save(ctx)
}

Like sqlboiler, it is able to return the updated record as well.

However, it does two queries as seen by the Debug() builder method. The good thing is ent automatically does them in a transaction.

2021/12/15 10:30:55 driver.Tx(a6715265-a078-4ed8-aee1-5582eda00e95): started
2021/12/15 10:30:55 Tx(a6715265-a078-4ed8-aee1-5582eda00e95).Exec: query=UPDATE "users" SET "first_name" = $1, "middle_name" = $2, "last_name" = $3, "email" = $4 WHERE "id" = $5 args=[John  Doe john-changed@example.com 1]
2021/12/15 10:30:55 Tx(a6715265-a078-4ed8-aee1-5582eda00e95).Query: query=SELECT "id", "first_name", "middle_name", "last_name", "email", "password" FROM "users" WHERE "id" = $1 args=[1]
2021/12/15 10:30:55 Tx(a6715265-a078-4ed8-aee1-5582eda00e95): committed

Out of all these, only ent does automatic transaction when updating and retrieving the record.

The most expensive operation is done by Gorm where it does three things, retrieve current record, update, and retrieve updated record.

The best implementation is ent where it does only two queries for update and query in a transaction.

Gorm and sqlboiler, which behave like many ORMs, need the record to be retrieved before updating, otherwise you will lose data.

Next > Golang Database Library ORM Example Delete