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

There are three ways of loading this kind of relationship:

  1. Perform each sql query on both tables
  2. Perform a left join on countries and addresses table,
  3. Use array_agg (for postgres) function or group_concat for mysql/mariadb.

Table of Contents

This is part of a series of blog posts including:

 

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

The first approach is what many ORM uses. The number of queries being made correlates to how many tables you are querying for.

The second approach is by making a left join on countries and addresses table. Give an alias where appropriate and try to loop through and scan the result keeping in mind of both primary key of addresses and foreign key of countries.

SELECT 
    c.id AS country_id, 
    c.name, c.code, 
    a.id AS address_id, 
    a.line_1, a.line_2, 
    a.postcode, a.city, 
    a.state 
FROM countries c 
LEFT JOIN addresses a on c.id = a.country_id 
ORDER BY c.id

While there is only one query is made, the scanning and tracking the keys are hard and error prone, just like the first approach.

The third approach is a lot faster since only one query is made. The related data on foreign key are converted to json and then attached to the main table. However, we need to create a view beforehand, and you have to make sure that alias that you use in the view correlates with your db struct tag. You also must know the correct sql syntax to perform data aggregation.

sqlx

The snippet below uses the second approach.


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
}

const GetWithAddresses2 = "select row_to_json(row) from (select * from country_address) row"

GetWithAddresses2 query is a statement that queries a view that we create during initial creation.

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;

One caveat is we want the aggregated address to scan alongside other fields into our Addresses field of CountryResponseWithAddress struct. We do this because result from database is a json response. Thus, we need to manually unmarshal the json response from database into our struct.

type CountryResponseWithAddress struct {
	Id        int                 `json:"id"`
	Code      string              `json:"code"`
	Name      string              `json:"name"`
	Addresses []AddressForCountry `json:"address"`
}

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

This means, every time err = rows.Scan(&i) is called, it will go into this Scan() 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.

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;

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.

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"`
}

What is going on here? Our countries table should only have 3 columns, not 4. To define the relationships however, one cannot think of it like an sql schema. Here, it is telling that a ‘Country’ has many Address', and denoted by []Address slice.

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:"countries" json:"countryID,omitempty"`
}

This almost looks alright except that CountryID field should have been a foreign key to countries table. By convention, CountryID field will become country_id column in this address table.

Let us take a second look at this line

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!

sqlboiler

1-N in sqlboiler is more verbose than what you would expect and 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 a library to load the relationship by its string, denoted by models.CountryRels.Addresses. All() ‘finisher’ executes the query and automatically attaches addresses to its country.

func (r *database) Countries(ctx context.Context) ([]*sqlx.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 []*sqlx.CountryResponseWithAddress
	for _, country := range countries {
		resp := &sqlx.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.
	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