Golang Database Library Orm Example - Update

Comparison between popular go libraries and ORM for database access layer.

December 15, 2021

In this post we will see a simple update of a resource by using its ID. We will try to perform an UPDATE <TABLE> SET <COLUMN1>=<VALUE1> etc, if not, we simply retrieve the item first, set to new values, and then save the record.

We only show how to do a full PUT updating instead of a PATCH, where a patch means we only update certain fields only, leaving other columns untouched.

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

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
- Use WHERE IN(?)
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 a list of names, sorting, and pagination
Transaction - Handle transaction, rollback on error
SQL Injection - Try to list all users by using malicious query parameters
- and Conclusion

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

Our request struct looks like the following. We are making a PUT request instead of a PATCH. That means we have to include the changed value as well as all other fields in our update request to the database. If we only include one field, other fields will be zeroed out.

So, the client must use GET, then update what is necessary, and finally call a PUT endpoint.

An example request is as follows:

curl -X PUT http://localhost:3080/user/1 -d '
{
  "first_name": "John",
  "middle_name": "Middle",
  "last_name": "Doe",
  "email": "john-changed@example.com",
  "favourite_colour": "red"
}'

The struct to receive client request will look like this. We use json.NewDecoder(r.Body).Decode(&request) to parse PUT json payload to a Go struct. These json struct tag helps with determining with json key should map to a Go field.

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 by using a helper function.

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

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
}

sqlx

In sqlx, a simple ExecContext() method is used. It requires three 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;"

currUser, err := r.Get(ctx, userID)
if err != nil {
    return nil, err
}

currUser.FirstName = req.FirstName
currUser.MiddleName = req.MiddleName
currUser.LastName = req.LastName
currUser.Email = req.Email
currUser.FavouriteColour = req.FavouriteColour
		
_, 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)

Although the client did a GET prior to this PUT request, we still need to do another Get() because we need to retrieve hashed password. If we straight away assign new values, then password column will be empty since client never had this information! Also, we cannot trust the client sends complete data for this PUT request.

Finally, we call our own Get() method to return the updated record with the updated updated_at column value. 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 character. 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. Like sqlx, we need to retrieve the record before updating the values.

In many cases, we would want to perform a transaction over these three sql queries - something that which we will cover in the transaction section of this blog series.

func (r *database) Update(ctx context.Context, userID int64, req *db.UserUpdateRequest) (*GetUserRow, error) {
    currUser, err := r.Get(ctx, userID)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, errors.New("no record found")
		}
		return nil, err
	}

	currUser.FirstName = req.FirstName
	currUser.MiddleName = req.MiddleName
	currUser.LastName = req.LastName
	currUser.Email = req.Email
	currUser.FavouriteColour = req.FavouriteColour
	
	err := r.db.UpdateUser(ctx, UpdateUserParams{
		FirstName: req.FirstName,
		MiddleName: sql.NullString{
			String: req.MiddleName,
			Valid:  req.MiddleName != "",
		},
		LastName: req.LastName,
		Email:    req.Email,
		ID:       userID,
	})
	if err != nil {
		return nil, fmt.Errorf("error updating the user: %w", err)
	}

	u, err := r.db.GetUser(ctx, userID)
	if err != nil {
		return nil, errors.New("error getting a user")
	}

	return &u, nil
}

Other than the awkward sql.Nullstring{}, it is easier than sqlx.

Squirrel

Squirrel uses a builder pattern to set new values to a record.

_, err = r.db.Update("users").
	Set("first_name", currUser.FirstName).
	Set("middle_name", currUser.MiddleName).
	Set("last_name", currUser.LastName).
	Set("email", currUser.Email).
	Set("favourite_colour", currUser.FavouriteColour).
	Where(sq.Eq{"id": id}).
	ExecContext(ctx)

As long as you get the column names right, updating a record is pretty straightforward.

gorm

Like a typical ORM, there are two operations needed to update a record.

Get it
Update

func (r *repo) Update(ctx context.Context, userID int64, req *db.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 use 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 db.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:  req.MiddleName != "",
	}
	user.LastName = req.LastName
	user.Email = req.Email
	user.FavouriteColour = null.String{
		String: req.FavouriteColour,
		Valid:  req.FavouriteColour != "",
	}
	
    // Ignore number of affected rows with underscore
	_, 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 db.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:  req.MiddleName != "",
		},
		LastName:        req.LastName,
		Email:           req.Email,
		FavouriteColour: req.FavouriteColour,
	}

	_, 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 *db.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 sqlx, sqlc, and Gorm where it does three things, retrieve current record, update, and retrieve updated record.

We need to use sql.NullString in both sqlx and sqlboiler to deal against nullable columns.

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

Next > Golang Database Library ORM Example Delete