Golang Database Library Orm Example - Get
December 14, 2021In 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:
- Introduction
- Create - Create a single record
- List - Returns the first 30 records of a resource
- 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 active users, sort by field, and pagination
- (coming!) Transaction - Handle transaction, rollback on error
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.