Golang Database Library Orm Example - One to Many

December 15, 2021

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:

Perform one sql query on both tables
Perform a left join from countries to addresses table,
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 for 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. In this blog, 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 each of below examples, I will only show one method each to keep the length of this post short.

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
- Use WHERE IN(?)
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

The runnable code is available in the repository https://github.com/gmhafiz/golang-database-library-orm-example

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 which are 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 Addresses instead of address. We can override this by using a db struct tag - which uses reflection.

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

As 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 of database/sql.

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. 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)
}

The sql query is the same as sqlx.

-- name: CountriesWithAddressAggregate :many
select row_to_json(row) from (select * from country_address) row;

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.

gorm

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 (yes, harder than sqlx!). 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. 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!

var country Country

// country will be filled with the first record in the database.
err := r.db.WithContext(ctx).
  Preload("Address").
  Find(&country).
  Select("*").
  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 model.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 the Json response will contain an edges key no matter if there is any relationship(s) or not.

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

Practically one line is needed that says query all Country with its Address relationship.

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"),
	}
}

You may wish 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),
	}
}

In conclusion, we see how ent shines over other libraries and ORMs. If you have a good sql expertise, sqlc is pretty awesome. Otherwise, we set the edges once in ent and eager loading API is a breeze to use.

Next > Golang Database Library ORM Example Many To Many

Golang Database Library Orm Example - One to Many

Table of Contents

sqlx

sqlc

gorm

sqlboiler

ent