Golang Database Library Orm Example - List
December 14, 2021In this post, we will look at comparing listing of user resource. We limit the record count to 30 and the offset is from 0. We will look at pagination in section 4 of the series, (coming!)Dynamic list filter from query parameter
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, listing an array of json looks similar to how sqlx creates a record. We use QueryxContext()
method that allows us to use StructScan()
that makes scanning easier - no longer do you need to
type out all the field names.
const List = "SELECT * FROM users LIMIT 30 OFFSET 0;"
func (r *database) List(ctx context.Context) (users []*UserResponse, err error) {
rows, err := r.db.QueryxContext(ctx, List)
if err != nil {
return nil, fmt.Errorf("error retrieving user records")
}
for rows.Next() {
var u userDB
err = rows.StructScan(&u)
if err != nil {
return nil, fmt.Errorf("db scanning error")
}
users = append(users, &UserResponse{
ID: u.ID,
FirstName: u.FirstName,
MiddleName: u.MiddleName.String,
LastName: u.LastName,
Email: u.Email,
})
}
return users, nil
}
We collect the records as an array of UserResponse
. This is because we do not want to expose the
password to the client. This is true for returning users' details, but sometimes you want to play
around with that protected field and only converting to UserResponse
at handler layer, just before
returning that data to client. Otherwise, you can always omit selecting the password
field
from the sql query:
const List = "SELECT (id, first_name, middle_name, last_name, email) FROM users LIMIT 30 OFFSET 0;"
sqlc
func (r *database) List(ctx context.Context) ([]ListUsersRow, error) {
return r.db.ListUsers(ctx)
}
That’s it. Only 3 lines - or 1 line however you may look at!
Since in the query we omit selecting the password, this prevents that field from leaking. So I
cheated a bit. If you require to play around with the password
value, you still need to copy
to userResponse
struct.
-- name: ListUsers :many
SELECT (id, first_name, middle_name, last_name, email)
FROM users
LIMIT 30
OFFSET 0;
gorm
Listing user records looks deceptively simple. It looks like a normal query builder but there are many ways to go wrong.
func (r *repo) List(ctx context.Context) ([]*User, error) {
var users []*User
err = r.db.WithContext(ctx).Find(&users).Limit(30).Error
if err != nil {
return nil, fmt.Errorf(`{"message": "db scanning error"}`)
}
return users, nil
}
For an ORM, listing a resource doesn’t feel like one. You need to
Tell the model you are asking for a list with
[]*User
- But you can pass in a
*User
instead, and it will return 1 record. err = r.db.WithContext(ctx).Find(&user).Limit(30).Error
<- passing&user
returns one record.
- But you can pass in a
You are doing a select ‘*’ operation, selecting all fields using
*
. If you want to select just a few fields:err := r.db.WithContext(ctx).Select([]string{"id", "first_name", "last_name"}).Find(&users).Limit(30).Error
Gorm does not provide a constant for each of database field’s name.
If you want to do a ‘WHERE’ clause, put the
User
struct as a second parameter to theFind()
method, notWhere()
which is more intuitive!err = r.db.WithContext(ctx).Find(&users, User{FirstName: "John"}).Limit(30).Error
Returning an error is optional.
On the upside, you do not have to worry about leaking password to client because in the User
model, we set the json struct tag to -
. Thus, no copying to a new struct is needed.
type User struct {
ID uint
FirstName string
MiddleName string
LastName string
Email string
Password string `json:"-"`
}
Using the API requires a bit of discovery and careful reading of its documentation. Neither sqlx nor sqlc require typing any magic string for such a simple operation like this.
In my opinion, it is certainly the hardest to use so far.
sqlboiler
In sqlboiler, listing a resource is done with All()
method. To limit the records, you have to use
the query modifier (qm
) package.
import "github.com/volatiletech/sqlboiler/v4/queries/qm"
func (r *database) List(ctx context.Context) ([]*sqlx2.UserResponse, error) {
users, err := models.Users(qm.Limit(30)).All(ctx, r.db)
if err != nil {
return nil, fmt.Errorf("error getting users")
}
var userResponse []*sqlx2.UserResponse
for _, user := range users {
userResponse = append(userResponse, &sqlx2.UserResponse{
ID: uint(user.ID),
FirstName: user.FirstName,
MiddleName: user.MiddleName.String,
LastName: user.LastName,
Email: user.Email,
})
}
return userResponse, nil
}
If you want to use a ‘WHERE’ clause, you supply a UserWhere
struct
users, err := models.Users(
qm.Limit(30),
models.UserWhere.FirstName.EQ("John"),
).
All(ctx, r.db)
If you want to select for a few fields, you provide the generated string constant from the models
package using {{model}}Columns
pattern.
users, err := models.Users(
qm.Limit(30),
qm.Select(models.UserColumns.ID, models.UserColumns.LastName),
).
All(ctx, r.db)
sqlboiler is a lot nicer than gorm for sure. You avoid any magic string which this a more type-safe
alternative to gorm. It will complain if you do not provide a context. You cannot ignore the error
return unless you explicit does so with underscore, _
.
But discovering what to use requires you to know all qm
helpers,
{{model}}Where
, and {{model}}Columns
patterns.
Password
field is automatically generated, and you cannot set it to private. So you will need to
copy to the new UserResponse
struct.
ent
In ent, it is similar to sqlc, but we do not have to worry about copying the fields to a new struct
without password because that field is already protected (or in ent’s lingo, sensitive
) from json
marshalling.
Limiting the number of records is done with Limit()
builder method instead of using another
package like sqlboiler.
func (r *database) List(ctx context.Context) ([]*gen.User, error) {
return r.db.User.Query().Limit(30).All(ctx)
}
To use a ‘SELECT’ for a few fields, you use the provided string constant in the gen/user
package
that gives a {{ model.Field{{FieldName}} }}
pattern.
return r.db.User.Query().
Select(user.FieldFirstName).
Limit(30).
All(ctx)
To use a ‘WHERE’ clause, you also use the provided gen/user
package
return r.db.User.Query().
Where(
user.ID(1),
).
Limit(30).
All(ctx)
The advantage with ent, like gorm, is we can continue using that sensitive field but, we are assured it will not be leaked to client. However, the API is a lot more intuitive and looks more like an ORM in other language.
In conclusion, sqlc and ent were the easiest to use. sqlc is pretty much a one-liner once you have written the correct sql query. ent gives the best API for query building. In both, we do not have to worry about leaking the password field to the client.
While the examples here are very simple - list 30 records of the users at most - real world use case are usually more complex. In the second section, we will see how we eager load a relationship and in the third section, we will explore at parsing query param for sorting and pagination.