Golang Database Library Orm Example - Get
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:
- Introduction
- Create - Create a single record
- List
- 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
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.