Golang Database Library Orm Example - List
In this post, we will look at comparing listing of user resource. We limit the record to the first thirty records.
Basically in all of them we want to make the following query
SELECT * FROM users ORDER BY id LIMIT 30 OFFSET 0;
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, 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, errors.New("db scanning error")
}
users = append(users, &UserResponse{
ID: u.ID,
FirstName: u.FirstName,
MiddleName: u.MiddleName.String,
LastName: u.LastName,
Email: u.Email,
UpdatedAt: u.UpdatedAt.String(),
})
}
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. So instead of scanning to UserResponse
struct, you should scan into
a struct with a Password
field.
text[] data type
Since we work with postgres, we should also explore into one of its data type not present in mysql,
sql server, and oracle, array
. We basically want to select tags
of a specific user.
In the users
schema, we have a text[]
data type. It is interesting to see how we can select this
kind of data type.
create table if not exists users
(
...
tags text[] default '{}'::text[] not null
...
To make it simple, we want to select its tags based on user ID:
SELECT tags FROM users WHERE users.id = $1
The result coming from the database for the column tags
is now in binary instead of text (data
type is text[]
). So in the code, data coming from database is in bytes
(bytes
is an alias for
uint8
) but, we are scanning the result into a slice of string. So, we cannot simply scan using
&values
because it is a mismatched data type - *[]string
vs []uint8
. What we do instead is to
use pq.Array()
, which helps us into choosing the optimum data type for scanning.
func (r *repository) Array(ctx context.Context, userID int64) ([]string, error) {
selectQuery := "SELECT tags FROM users WHERE users.id = $1"
var values []string
err := r.db.QueryRowContext(ctx, selectQuery, userID).Scan(pq.Array(&values))
if err != nil {
return nil, err
}
return values, nil
}
You write more code, but you have more control and a better performance dealing with this sql query with binary data.
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 want to look at it!
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 will need to remember
to transform to a userResponse
struct.
-- name: ListUsers :many
SELECT (id, first_name, middle_name, last_name, email)
FROM users
LIMIT 30
OFFSET 0;
The sql query above is annotated with a comment that comprises a method name and the expected count,
one
or many
.
Anytime you want to select a few columns, you will need to create a new sql query. sqlc in not flexible when it comes to dynamic queries at runtime.
squirrel
As squirrel is a query builder, we build the query that we wanted by using a builder pattern.
rows, err := r.db.
Select("*").
From("users").
Limit(uint64(f.Base.Limit)).
Offset(uint64(f.Base.Offset)).
OrderBy("id").
QueryContext(ctx)
And since we now use methods instead of writing raw SQL, it suddenly becomes easier to create dynamic query as you will see in the dynamic list section.
Squirrel requires a finisher which will then return a sql rows and a possible error.
gorm
Listing user records looks deceptively simple. It looks like a normal query builder but there are many ways it can go wrong. Have a look at this:
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
}
At a glance, there isn’t anything obviously wrong with the code above. In fact, if you run the code, you will get a result with no error. However, there are subtle mistakes or quirks compared to the query builder we have seen so far. 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. - The
Find()
method accepts aninterface
, so it will happily accept anything whether a slice or a single item (or something totally different).
- But you can pass in a
var user User
err = r.db.WithContext(ctx).Find(&user).Limit(30).Error` <- passing `&user` returns one record.
-
You are doing an implied
select
operation, selecting all fields using*
. If you want to select just a few fields:- Gorm does not provide a constant for each of database field’s name.
- Instead of passing a struct, you pass a slice of string. Need to refer documentation because
method’s signature does not help - its signature is saying it accepts a query interface and
a list of arguments?!
Select(query interface{}, args ...interface{}) (tx *DB)
- You can also pass strings to
Select()
and it will give the same output even if it doesn’t conform to method signature and the IDE telling you are wrong.
// this
err := r.db.WithContext(ctx).Select([]string{"id", "first_name", "last_name"}).Find(&users).Limit(30).Error
// also works, look at below screenshot
err := r.db.WithContext(ctx).Select("id", "first_name", "last_name").Find(&users).Limit(30).Error
first argument is a query
and the rest are args
as expected, but it still returns the same result
- If you want to do a ‘WHERE’ clause, you can use a
Where()
method. But you can also put theUser
struct as a second parameter to theFind()
method!
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:"-"`
}
WithContext()
is optional.
In Go, we tend to use context
for cancellation, deadline and timeout. WithContext()
is optional
which means we can lose these features by forgetting to chain it.
- ‘Finisher’ method is optional, and the order is important.
Finisher methods like Find()
, First()
, and Save()
are optional when building a query.
err = r.db.
WithContext(ctx).
Find(&users). // <- called to early! Subsequent `Limit()` will be ignored, so it'll return all records in the database.
Limit(30).
Error
If you do not include them, Gorm will do nothing, and thus will not return either an error or any result.
The order when you call those finishers are also important. If you call it too early, Gorm will
ignore subsequent methods. For example, the examples I have shown so far has Limit()
called
after Find()
. That means, Gorm will ignore anything after Find()
finisher and return all records
in the database! If you scroll back to point number 2, you may have noticed that in the code
screenshot, it returns 32 records (everything I have) in the database instead of returning 10
records. Unlike other libraries, calling other methods after a finisher is invalid.
Using the API requires a bit of discovery and careful reading of its documentation since its method signature does not help.
In my opinion, thanks to many pitfalls Gorm has, 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) ([]*db.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 []*db.UserResponse
for _, user := range users {
userResponse = append(userResponse, &db.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 makes it more type-safe
compared 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, a fair bit of discovering is needed to know all qm
helpers - {{model}}Where
, and
{{model}}Columns
patterns. For example, in my opinion it is more intuitive to use
models.Users().Limit(30).All(ctx, r.db)
to limit instead of using a query modifier.
Password
field is automatically generated, and you cannot set it to private. So you will need to
remember to perform data transform object (DTO) before returning to client - as you should always do.
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) ([]*ent.User, error) {
return r.db.User.Query().
Order(ent.Asc(user.FieldID)).
Limit(30).
Offset(0).
All(ctx)
}
To use a ‘SELECT’ for a few fields, you use the generated string constant in the ent/user
package
that gives a {{ model.Field{{FieldName}} }}
pattern.
return r.db.User.Query().
Select(user.FieldFirstName).
Order(ent.Asc(user.FieldID)).
Limit(30).
Offset(0).
All(ctx)
To use a ‘WHERE’ clause, you also use the generated ent/user
package
return r.db.User.Query().
Where(user.ID(1)).
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. However, you cannot customise sqlc queries at runtime.
While the examples here are very simple - list 30 records of the users at most - real world use case are usually more complex. We will see how we eager load a relationship and in the dynamic list section, we will explore at parsing query param for sorting and pagination.
Before going to those sections, we will finish of WHERE IN(?)
and the rest of CRUD stuff.
Next > Golang Database Library ORM Example Where IN ($1, $2, …)