Golang Database Library Orm Example - One to Many
December 15, 2021In 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:
- Perform each sql query on both tables
- Perform a left join on
countries
andaddresses
table, - Use
array_agg
(for postgres) function orgroup_concat
for mysql/mariadb.
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.
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.