Golang Database Library Orm Example - Many to Many

In 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.

1. Get 30 records of users
2. For each user, get the IDs of the address from the pivot table
3. Get all records of addresses from the IDs retrieved in the previous step.
4. 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