Golang Database Library Orm Example - Many to Many
January 2, 2022In this post, we will look at listing users with all their addresses. users
and addresses
table
are joined with a pivot table, user_addresses
, which holds the foreign key of each of users
and
address
table primary key.
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.
There are many ways to do this. The most straight away method (what most ORM does) is to perform a query on each of the related tables.
- Get 30 records of users
- For each user, get the IDs of the address from the pivot table
- Get all records of addresses from the IDs retrieved in the previous step.
- Begin attaching address to users.
ORMs often does this automatically for you. Writing sql manually means you have to do all the steps above.
The other way is to perform left joins on the tables, starting from users
, and ends with addresses
.
Left join is important because if a user do not have an address, that user record will not be returned.
While doing left joins, alias, especially on IDs must be set to prevent name conflict.
sqlx
If you look at the full source code, it is 2 pages long. So, I will only include snippets here.
Starting with sql queries, we try to limit number of users to 30.
-- #1
SELECT u.id, u.first_name, u.middle_name, u.last_name, u.email
FROM "users" u
LIMIT 30;
To find out the address of each user, we get that relationship from the pivot table. For an array
of user IDs, we can always do a SELECT DISTINCT u.id FROM users u LIMIT 30;
. But this is an
additional query while we can extract the IDs from query #1.
-- #2
SELECT DISTINCT ua.user_id AS user_id, ua.address_id AS address_id
FROM "addresses" a
LEFT JOIN "user_addresses" ua ON a.id = ua.address_id
WHERE ua.user_id IN (?);
We then get all address IDs, and we execute this query.
-- #3
SELECT a.*
FROM addresses a
WHERE a.id IN (?);
Notice that the two last queries uses ?
instead of $1
. It relates to sqlx’s ability (or inability)
to properly turn array values for IN
operator.
query, args, err := sqlx.In(UsersAddress, userIDs)
sqlc.In
accepts an array of interface but executing QueryContext()
expects int8
.
Only using ?
placeholder sqlx won’t complain about types.
Once we perform the first sql query, we can the result into a custom struct
([]*UserResponseWithAddressesSqlx
), one with address field.
type UserResponseWithAddressesSqlx struct {
ID uint `json:"id,omitempty"`
FirstName string `json:"first_name"`
MiddleName string `json:"middle_name,omitempty"`
LastName string `json:"last_name"`
Email string `json:"email"`
Address []AddressForCountry `json:"address"`
}
var all []*UserResponseWithAddressesSqlx
for users.Next() {
var u userDB
if err := users.Scan(&u.ID, &u.FirstName, &u.MiddleName, &u.LastName, &u.Email); err != nil {
return nil, fmt.Errorf("db scanning error")
}
all = append(all, &UserResponseWithAddressesSqlx{
ID: u.ID,
FirstName: u.FirstName,
MiddleName: u.MiddleName.String,
LastName: u.LastName,
Email: u.Email,
})
}
We retrieve all user IDs and get its associated addresses with the second sql query. We need to scan and hold the data with another struct so that wen use it to find the associations later.
type userAddress struct {
UserID int `db:"user_id"`
AddressID int `db:"address_id"`
}
Then get all address IDs and execute the third sql query. Scan the results into a new address
struct -
it needs the db
struct tag.
The final step is to loop through all array of structs and attach their associations
for _, u := range uas {
for _, user := range all {
if u.UserID == int(user.ID) {
for _, addr := range allAddresses {
if addr.ID == uint(u.AddressID) {
user.Address = append(user.Address, AddressForCountry{
ID: addr.ID,
Line1: addr.Line1,
Line2: addr.Line2.String,
Postcode: addr.Postcode.Int32,
City: addr.City.String,
State: addr.State.String,
})
}
}
}
}
}
Note that if a user record do not have any address, the value for address
key will be null
,
instead of an empty array.
{
"id": 3,
"first_name": "Jake",
"last_name": "Doe",
"email": "jake@example.com",
"address": null
}
So you need to remember to initialize the addresses for a user
all = append(all, &UserResponseWithAddressesSqlx{
ID: u.ID,
FirstName: u.FirstName,
MiddleName: u.MiddleName.String,
LastName: u.LastName,
Email: u.Email,
Address: []*AddressForCountry{}, // instead of leaving it empty
})
In my opinion, this is too much code for just a simple two tables plus a pivot table to deal with. Imagine having to eager-load 7 tables deep. That is a nightmare.
sqlc
There is less boilerplate in sqlc. No need to manually scan database results to a struct because they are all generated for you, but you still need to extract IDS from each. It only takes a page long to perform many-to-many compared to almost 2 pages long for sqlx.
You still need to manually attach address to a user like sqlx. Also, you need to remember to initialize addresses for a user like above instead of leaving it empty.
gorm
Performing many to many is very similar to doing a one-to-many. We supply a slice to User
pointers
and gorm will use reflection to infer what we want.
func (r *repo) ListM2M(ctx context.Context) ([]*User, error) {
var users []*User
err := r.db.WithContext(ctx).
Preload("Addresses").
Find(&users).
Select("*").
Limit(30).
Error
if err != nil {
return nil, fmt.Errorf("error loading countries: %w", err)
}
return users, nil
}
Unlike sqlx and sqlc, a user without addrress will be marshalled with an empty array
...
{
"id": 3,
"first_name": "Jake",
"middle_name": "",
"last_name": "Doe",
"email": "jake@example.com",
"address": []
}
]
The amount of code needed tobe written is also greatly reduced compared to both sqlx and sqlc.
The key is to use Preload()
method. What is not obvious is what magic string you need to supply to
it. The answer is the name of the field itself - it has to match. For example, User
struct has
Addresses
in its field, so we supply the string Addresses
inside Preload()
.
type User struct {
...
Addresses []Address `json:"address" gorm:"many2many:user_addresses;"`
}
We also need to tell Gorm the pivot table that is used to link between users
and addresses
table.
It is done by using a struct tag gorm:"many2many:user_addresses;"
.
Gorm has no code generation like sqlboiler and ent. Instead, it relies on structs (and struct tags) as its point of reference.
sqlboiler
Unfortunately I cannot find an easy way to eager load many-to-many relationships with sqlboiler. Current recommendation is to fall back to raw sql queries.
ent
Like one-to-many operation, ent shines at loading relationships.
func (r *database) ListM2M(ctx context.Context) ([]*gen.User, error) {
return r.db.User.Query().
Limit(30).
WithAddresses().
All(ctx)
}
We use the same pattern as what we did with one-to-many - {{ With{{Model}} }}
pattern.
If a user has no address, the edges
key will only contain an empty object.
[
{
"id": 3,
"first_name": "Jake",
"last_name": "Doe",
"email": "jake@example.com",
"edges": {}
},
{
"id": 1,
"first_name": "John",
"last_name": "Doe",
"email": "john@example.com",
"edges": {
"addresses": [
{
"id": 1,
...
Ent makes 3 separate sql queries, just like what we have done for sqlx, and sqlc.
In my opinion, the API is very straightforward and there isn’t a way to get it wrong. Everything
is statically typed with no interface{}
anywhere which makes it predictable.
In conclusion, using raw sql is too much work when it comes to loading many-to-many relationships.
Imagine if we want to load 7 tables deep, it’d be a nightmare. While using sqlc improves upon sqlx
by reducing much boilerplate, it still pales in comparison to using an ORM. You need to be careful
with struct tag with Gorm, and it was not obvious that you had to use a field name for Preload()
.
Ent does not have Gorm’s issues as everything is typed. You will need to learn to using With...
pattern though which isn’t a big deal.
Next > (coming!) Golang Database Library ORM Example - Dynamic List