Golang Database Library Orm Example - One to Many

In this post, we will look at getting a list of countries along with its addresses.

There are three ways of loading this kind of relationship:

1. Perform one sql query on both tables
2. Perform a left join from countries to addresses table,
3. Use array agg (for postgres) function or group concat for mysql/mariadb.

Option one is what most ORM does. They query the parent table, then get its IDs. With those IDs, they query child table relationships. The more relationships you require, the more sql queries you make. Each child relationships are then attached to the parent based on the foreign keys that has been defined. As a result, you get a struct where you can walk through from parent to child table.

Using joins can be an easy way of getting all data in a single query. You potentially return more data than option number one when a child record belongs to many parent table. Furthermore, the type of joins is important. If you have made an INNER JOIN between parent and child table, and that child table record is NULL, you may end up losing parent table record in your final result. Joining a number of tables with millions of records can result in huge memory usage. This is because in SQL order of execution, it first joins the tables in memory before selecting relevant columns. Even though you make a single SQL query, you need to loop through the results and scan child record to the correct parent record. In my opinion, tracking the relationships are hard and error-prone.

From: https://www.sisense.com/blog/sql-query-order-of-operations/

Finally, we have an option to use native functions to aggregate child record results to their related parent tables. There is array agg for postgres and group concat for mysql or mariadb. For this, I create a view called country address that aggregates addresses to its country.

CREATE VIEW country address as
select c.id, c.code, c.name,
	   (
		   select array to json(array agg(row to json(addresslist.*))) as array to json
		   from (
					select a.*
					from addresses a
					where c.id = a.country id
				) addresslist) as address
from countries AS c;

Running select * from country address; will give id, code, name, and address in a JSON format.

To get everything in a JSON format, you may query with select row to json(row) from (select * from country address) row;. However, you still need to parse this JSON response back to a Go struct.

{
  "id": 1,
  "code": "AU",
  "name": "Australia",
  "address": [
    {
      "id": 1,
      "line 1": "Sydney Opera House",
      "line 2": "Bennelong Point",
      "postcode": 2000,
      "city": "Sydney",
      "state": "NSW",
      "country id": 1
    }
  ]
},
{
  "id": 2,
  "code": "MY",
  "name": "Malaysia",
  "address": [
    {
      "id": 2,
      "line 1": "Petronas Twin Towers",
      "line 2": "",
      "postcode": 50088,
      "city": "Kuala Lumpur",
      "state": "Wilayah Persekutuan",
      "country id": 2
    }
  ]
}

In most of the below examples, I will only show one method in each library each to keep this post short.

Table of Contents

sqlx

The snippet below uses the third approach.

const GetWithAddresses2 = "select row to json(row) from (select * from country address) row"

func (r *database) Countries(ctx context.Context) ([]*CountryResponseWithAddress, error) {
	var resp []*CountryResponseWithAddress

	rows, err := r.db.QueryContext(ctx, GetWithAddresses2)
	if err != nil {
		return nil, fmt.Errorf(`{"message": "db error"}`)
	}
	defer rows.Close()

	for rows.Next() {
		var i CountryResponseWithAddress
		err = rows.Scan(&i)
		if err != nil {
			return nil, err
		}
		resp = append(resp, &i)
	}

	return resp, nil
}

GetWithAddresses2 query is a statement that queries the view that we have created. On top of that, we used row to json() function that tuns the whole response as a json format. Before we go ahead, let us take a step back and define our Go struct.

type CountryResponseWithAddress struct {
	Id   int    `json:"id,omitempty"`
	Code string `json:"code,omitempty"`
	Name string `json:"name,omitempty"`

	Addresses []*AddressForCountry `json:"address"`
}

To figure out how to form this struct, we need to know the response coming from the database.

{"id":1,"code":"AU","name":"Australia","addresses":[{"id":1,"line 1":"Sydney Opera House","line 2":"Bennelong Point","postcode":2000,"city":"Sydney","state":"NSW","country id":1}]}
{"id":2,"code":"MY","name":"Malaysia","addresses":[{"id":2,"line 1":"Petronas Twin Towers","line 2":"","postcode":50088,"city":"Kuala Lumpur","state":"Wilayah Persekutuan","country id":2}]}

The key (pun intended) here is to look at the JSON key, namely id, code, name, and addresses. That is how we decide to name the fields in the CountryResponseWithAddress struct. Go will automatically figure out which json key maps to a field.

Notice that the last field needed to be plural Addresses instead of singular address. We can override this by using a db struct tag - which uses reflection.

Address []*AddressForCountry `json:"address" db:"addresses"`

Because the whole response is in a JSON format, we need to tell the runtime on how to handle and parse the result to a Go struct.

To achieve this, we implement one of database/sql interface called Scanner. It contains one method called Scan(). So we create our own implementation of scanner interface with Scan(src any) error by unmarshalling into JSON.

// From standard library `database/sql` package
type Scanner interface {
  Scan(src any) error
}

// CountryResponseWithAddress is our own custom struct that reflects the result we want
type CountryResponseWithAddress struct {
	Id        int                  `json:"id"`
	Code      string               `json:"code"`
	Name      string               `json:"name"`
	Addresses []*AddressForCountry `json:"address"`
}

// Scan implements Scanner interface using our CountryResponseWithAddress struct
func (m *CountryResponseWithAddress) Scan(src any) error {
	val := src.([]byte) // []byte is an alias of []uint8
	return json.Unmarshal(val, &m)
}

This means, every time err = rows.Scan(&i) is called, it will go into this Scan(src any) error method of CountryResponseWithAddress instead of the default Rows struct from database/sql package.

We can try the second approach by using a struct like this

type countryWithAddress struct {
	CountryID        string         `db:"id"`
	CountryName      string         `db:"name"`
	CountryCode      string         `db:"code"`
	AddressID        uint           `db:"address id"`
	Line1            string         `db:"line 1"`
	Line2            sql.NullString `db:"line 2"`
	Postcode         sql.NullInt32  `db:"postcode"`
	City             sql.NullString `db:"city"`
	State            sql.NullString `db:"state"`
	AddressCountryID uint           `db:"country id"`
}

If no field name or alias conflicts, we should be able to attach the addresses to its country by looping through the records keeping track of the country ids.

And of course, we need to perform two queries for the first method. Then we need to loop over result from addresses and attach them to a country or countries by looking at the foreign keys.

sqlc

In sqlc, we do not have to worry about writing boilerplate because the code is auto generated from the sql query we already know.

-- name: CountriesWithAddressAggregate :many
select row to json(row) from (select * from country address) row;

So only three lines are needed to get the correct result.

func (r *database) Countries(ctx context.Context) ([]json.RawMessage, error) {
	return r.db.CountriesWithAddressAggregate(ctx)
}

However, sqlc uses json.RawMessage. If you need to play around with the returned database records, you need to unmarshall this JSON response to a Go struct, an example in which I show in the squirrel section below.

squirrel

The obvious way to do this is like in approach number 1. Query each table, and then attach relevant address (if exists) to a country.

But since squirrel is a query builder, we are more interested to find out how building the such query looks like. Let us take a closer look at the query.

select row to json(row) from (select * from country address) row;

First of we have a row to json postgres function that takes up row as its argument. The second part is we select everything from a view called country address and is aliased to become row. To build this query using squirrel, we have to build each of these to different parts:

s := r.db.Select("* from country address")
rows, err := r.db.
	Select("row to json(row)").
	FromSelect(s, "row").
	QueryContext(ctx)

First, we select the view and store in a variable (s) - this is the select * from country address part. Then we do a Select("row to json(row)"), and chain it to a FromSelect() method.

Once this is done, we can scan it to either simply a raw JSON message or a custom Go struct.

var items []json.RawMessage
for rows.Next() {
    var rowToJson json.RawMessage
    if err := rows.Scan(&rowToJson); err != nil {
        return nil, err
    }
    items = append(items, rowToJson)
}

Using a custom Go struct is much more elaborate. First you have to figure out the correct struct for this JSON payload:

type CustomGoStruct struct {
	Id      int    `json:"id"`
	Code    string `json:"code"`
	Name    string `json:"name"`
	Address []struct {
		Id        int    `json:"id"`
		Line1     string `json:"line 1"`
		Line2     string `json:"line 2"`
		Postcode  int    `json:"postcode"`
		City      string `json:"city"`
		State     string `json:"state"`
		CountryId int    `json:"country id"`
	} `json:"address"`
}

And then make sure to implement Scanner interface for this struct

func (m *CustomGoStruct) Scan(src interface{}) error {
	val := src.([]uint8)
	return json.Unmarshal(val, &m)
}

Finally, you can scan into this custom struct.

var scanned []*CustomGoStruct
for rows.Next() {
    var rowToJson CustomGoStruct
    if err := rows.Scan(&rowToJson); err != nil {
        return nil, err
    }
    scanned = append(scanned, &rowToJson)
}

gorm

To eager load one-to-many table, simply use Preload() method.

func (r *repo) Countries(ctx context.Context) ([]*Country, error) {
	var countries []*Country

	err := r.db.WithContext(ctx).
		Preload("Address").
		Limit(30).
		Find(&countries).
		Select("*").
		Error
    if err != nil {
		return nil, fmt.Errorf("error loading countries: %w", err)
	}

	return coutries, nil
}

So far, getting this to work is the hardest. The code above looks deceptively simple but to get to this point is not.

It is true that we simply call Preload() method to load child relationship, but before we dive further into the code, let us see how we declare our structs for our tables.

type Country struct {
	ID   int    `json:"id"`
	Code string `json:"code"`
	Name string `json:"name"`

	Address []Address `json:"address" gorm:"foreignkey:country id"`
}

The struct declares that we have three columns. To define the relationships, one cannot think of it like an sql schema. Here, it is telling that a ‘Country’ has many Address, and denoted by []Address slice. Thus, the Address field is a reference to the addresses table.

Next, take a look at Address struct:

type Address struct {
	ID       int    `json:"ID,omitempty"`
	Line1    string `json:"line 1,omitempty" gorm:"Column:line 1"`
	Line2    string `json:"line 2,omitempty" gorm:"Column:line 2"`
	Postcode int32  `json:"postcode,omitempty" gorm:"default:null"`
	City     string `json:"city,omitempty" gorm:"default:null"`
	State    string `json:"state,omitempty" gorm:"default:null"`

	CountryID int `json:"countryID,omitempty"`
}

This almost looks alright except that CountryID field should have been a foreign key to countries table. But just by looking at the struct above, there’s no indication that it is a foreign key. By Gorm convention, CountryID field will become country id column in this address table.

Let us take a second look at the following snippet:

err := r.db.WithContext(ctx).
  Preload("Address").
  Find(&countries).
  Select("*").
  Error

One must be careful with the Find() method with the type you are putting into it. We give it a slice of address pointers, ([]*Countries). If you do not, for example, giving it an Country type will return a single record!

Also, we made a mistake in the code above even though Go compiler returns no error. The Find() finisher is called too soon and ignore Select(). We are lucky because we were doing SELECT * anyway. But if we were doing SELECT id in the code above for example, it will be ignored.

var country Country

// country will be filled with the first record in the database.
err := r.db.WithContext(ctx).
  Preload("Address").
  Select("*").
  Find(&country).
  Error

There is no enforcement on the type that can be accepted inside Find(), not surprising because it accepts an empty interface. Gorm uses a lot of reflection to get to the type.

You can also omit the Select("*") builder, and it will work the same! It is forgiving but on the other hand loses explicitness when it comes to building a query.

sqlboiler

1-N in sqlboiler is more verbose than what you would expect from what an ORM would do. However, everything is typed - no magic strings anywhere which prevents any typo.

It starts with models.Countries(). In its argument, we use the generated package to load the relationship by its string, denoted by models.CountryRels.Addresses. The method All() ‘finisher’ executes the query.

func (r *database) Countries(ctx context.Context) ([]*db.CountryResponseWithAddress, error) {
    countries, err := models.Countries(
		qm.Load(models.CountryRels.Addresses),
		qm.Limit(30),
	).
		All(ctx, r.db)
	if err != nil {
		return nil, err
	}

	var all []*db.CountryResponseWithAddress
	for  , country := range countries {
		resp := &db.CountryResponseWithAddress{
			Id:        int(country.ID),
			Code:      country.Code,
			Name:      country.Name,
			Addresses: getAddress(country.R.Addresses),
		}
		all = append(all, resp)
	}

	return all, err
}

So why are we looping countries in the next few lines?

The reason is the generated model.Country struct relationship (or edge) has - as its struct tag. This means when we return the result to the client, the marshalled json would not contain any addresses.

type Country struct {
	ID   int64  `boil:"id" json:"id" toml:"id" yaml:"id"`
	Code string `boil:"code" json:"code" toml:"code" yaml:"code"`
	Name string `boil:"name" json:"name" toml:"name" yaml:"name"`

	R *countryR `boil:"-" json:"-" toml:"-" yaml:"-"` // this edge won't be returned to client
	                                                  // because json is tagged with -
	L countryL  `boil:"-" json:"-" toml:"-" yaml:"-"`
}

For this reason, we need to create a new struct, and copy them over if we want to send the child relationships to client.

ent

In ent, we use the With... method to eager load a relationship. Each model in a json response will contain an edges key no matter if there is any relationship(s) or not.

To define the relationship, or ’edges’ in ent’s lingo, you write an Edges() method on User struct.

// Edges of the User.
func (User) Edges() []ent.Edge {
	return []ent.Edge{
		edge.From("addresses", Address.Type).Ref("users"),
	}
}

It is compulsory to set a link back to user from an address:

// Edges of the Address.
func (Address) Edges() []ent.Edge {
	return []ent.Edge{
		edge.To("users", User.Type),
	}
}

All we need to do after the schema is defined is to run go generate ./... and we practically need one line is needed that says query all Country with its Address relationship.

func (r *database) Countries(ctx context.Context) ([]*gen.Country, error) {
	return r.db.Country.
        Query().
        Limit(30).
        WithAddresses().
        All(ctx)
}

In this post, we have seen each library and ORM vary wildly in the approach between them. Sqlx is quite normal with normal scanning. Sqlc on the other hand, removes all boilerplate present in sqlx. Squirrel documentation is contained inside its source code. You cannot be sure of Gorm’s method chaining order and its struct key. Sqlboiler is similar to squirrel in its ease of query building but its addresses child records are not serialized by default.

Ent however, shines over other libraries and ORMs. If you have a good sql expertise, sqlc is pretty awesome. Otherwise, ent’s eager loading API is a breeze to use.

Next > Golang Database Library ORM Example Many To Many