Compare · 015

Nested structs with sgl.embed: MySQL and CockroachDB

Every file of the MySQL and CockroachDB versions side by side, with the lines that differ marked. 2 parameter or column types differ in Go.

Compare MySQL withSQLite PostgreSQL CockroachDB TimescaleDB MariaDB DuckDB

Configuration

sgl.yamldiffers

MySQL
CockroachDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4  - name: fleet
4  - name: fleet
5    dialect: mysql
5    dialect: postgres
6    migrations: db/migrations
6    migrations: db/migrations
7    queries: db/queries
7    queries: db/queries
8    go:
8    go:
9      package: store
9      package: store
10      out: internal/store
10      out: internal/store
11      driver: database/sql

Migrations

db/migrations/0001_fleet.up.sqldiffers

MySQL
CockroachDB
1CREATE TABLE harbours (
1CREATE TABLE harbours (
2    id     BIGINT      NOT NULL AUTO_INCREMENT PRIMARY KEY,
2    id     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
3    name   VARCHAR(50) NOT NULL,
3    name   TEXT   NOT NULL,
4    county VARCHAR(50) NOT NULL
4    county TEXT   NOT NULL
5);
5);
6
6
7CREATE TABLE skippers (
7CREATE TABLE skippers (
8    id      BIGINT      NOT NULL AUTO_INCREMENT PRIMARY KEY,
8    id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
9    name    VARCHAR(50) NOT NULL,
9    name    TEXT   NOT NULL,
10    licence VARCHAR(20) NOT NULL
10    licence TEXT   NOT NULL
11);
11);
12
12
13CREATE TABLE boats (
13CREATE TABLE boats (
14    id         BIGINT      NOT NULL AUTO_INCREMENT PRIMARY KEY,
14    id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
15    name       VARCHAR(50) NOT NULL,
15    name       TEXT   NOT NULL,
16    harbour_id BIGINT      NOT NULL,
16    harbour_id BIGINT NOT NULL REFERENCES harbours (id),
17    skipper_id BIGINT,
17    skipper_id BIGINT REFERENCES skippers (id)
18    FOREIGN KEY (harbour_id) REFERENCES harbours (id),
19    FOREIGN KEY (skipper_id) REFERENCES skippers (id)
20);
18);

db/migrations/0001_fleet.down.sqlidentical

MySQL
CockroachDB
1DROP TABLE boats;
1DROP TABLE boats;
2DROP TABLE skippers;
2DROP TABLE skippers;
3DROP TABLE harbours;
3DROP TABLE harbours;

Queries

AddHarbourdiffers

MySQL · db/queries/boats.sql
CockroachDB · db/queries/boats.sql
1-- sgl.query(AddHarbour, exec)
1-- sgl.query(AddHarbour, exec)
2INSERT INTO harbours (name, county) VALUES (?, ?);
2INSERT INTO harbours (name, county) VALUES ($1, $2);

AddSkipperdiffers

MySQL · db/queries/boats.sql
CockroachDB · db/queries/boats.sql
1-- sgl.query(AddSkipper, exec)
1-- sgl.query(AddSkipper, exec)
2INSERT INTO skippers (name, licence) VALUES (?, ?);
2INSERT INTO skippers (name, licence) VALUES ($1, $2);

AddBoatdiffers

MySQL · db/queries/boats.sql
CockroachDB · db/queries/boats.sql
1-- sgl.query(AddBoat, exec)
1-- sgl.query(AddBoat, exec)
2INSERT INTO boats (name, harbour_id, skipper_id) VALUES (?, ?, ?);
2INSERT INTO boats (name, harbour_id, skipper_id) VALUES ($1, $2, $3);

GetBoatdiffers

MySQL · db/queries/boats.sql
CockroachDB · db/queries/boats.sql
1-- sgl.query(GetBoat, one)
1-- sgl.query(GetBoat, one)
2-- sgl.embed(harbours)
2-- sgl.embed(harbours)
3SELECT boats.id, boats.name, harbours.id, harbours.name, harbours.county
3SELECT boats.id, boats.name, harbours.id, harbours.name, harbours.county
4FROM boats
4FROM boats
5INNER JOIN harbours ON harbours.id = boats.harbour_id
5INNER JOIN harbours ON harbours.id = boats.harbour_id
6WHERE boats.id = ?;
6WHERE boats.id = $1;

ListCrewsidentical

MySQL · db/queries/boats.sql
CockroachDB · db/queries/boats.sql
1-- sgl.query(ListCrews, many)
1-- sgl.query(ListCrews, many)
2-- sgl.embed(skippers)
2-- sgl.embed(skippers)
3SELECT boats.id, boats.name, skippers.id, skippers.name, skippers.licence
3SELECT boats.id, boats.name, skippers.id, skippers.name, skippers.licence
4FROM boats
4FROM boats
5LEFT JOIN skippers ON skippers.id = boats.skipper_id
5LEFT JOIN skippers ON skippers.id = boats.skipper_id
6ORDER BY boats.id;
6ORDER BY boats.id;

What sgl generates

AddHarbouridentical

MySQL
CockroachDB
1func (queries *Queries) AddHarbour(ctx context.Context, params AddHarbourParams) error
1func (queries *Queries) AddHarbour(ctx context.Context, params AddHarbourParams) error
2
2
3type AddHarbourParams struct {
3type AddHarbourParams struct {
4	Name   string
4	Name   string
5	County string
5	County string
6}
6}

AddSkipperidentical

MySQL
CockroachDB
1func (queries *Queries) AddSkipper(ctx context.Context, params AddSkipperParams) error
1func (queries *Queries) AddSkipper(ctx context.Context, params AddSkipperParams) error
2
2
3type AddSkipperParams struct {
3type AddSkipperParams struct {
4	Name    string
4	Name    string
5	Licence string
5	Licence string
6}
6}

AddBoatidentical

MySQL
CockroachDB
1func (queries *Queries) AddBoat(ctx context.Context, params AddBoatParams) error
1func (queries *Queries) AddBoat(ctx context.Context, params AddBoatParams) error
2
2
3type AddBoatParams struct {
3type AddBoatParams struct {
4	Name      string
4	Name      string
5	HarbourID int64
5	HarbourID int64
6	SkipperID *int64
6	SkipperID *int64
7}
7}

GetBoatidentical

MySQL
CockroachDB
1func (queries *Queries) GetBoat(ctx context.Context, id int64) (GetBoatRow, error)
1func (queries *Queries) GetBoat(ctx context.Context, id int64) (GetBoatRow, error)
2
2
3type GetBoatRow struct {
3type GetBoatRow struct {
4	ID       int64  `json:"id"`
4	ID       int64  `json:"id"`
5	Name     string `json:"name"`
5	Name     string `json:"name"`
6	Harbours GetBoatHarbours
6	Harbours GetBoatHarbours
7}
7}
8
8
9type GetBoatHarbours struct {
9type GetBoatHarbours struct {
10	ID     int64  `json:"id"`
10	ID     int64  `json:"id"`
11	Name   string `json:"name"`
11	Name   string `json:"name"`
12	County string `json:"county"`
12	County string `json:"county"`
13}
13}

ListCrewsidentical

MySQL
CockroachDB
1func (queries *Queries) ListCrews(ctx context.Context) ([]ListCrewsRow, error)
1func (queries *Queries) ListCrews(ctx context.Context) ([]ListCrewsRow, error)
2
2
3type ListCrewsRow struct {
3type ListCrewsRow struct {
4	ID       int64  `json:"id"`
4	ID       int64  `json:"id"`
5	Name     string `json:"name"`
5	Name     string `json:"name"`
6	Skippers *ListCrewsSkippers
6	Skippers *ListCrewsSkippers
7}
7}
8
8
9type ListCrewsSkippers struct {
9type ListCrewsSkippers struct {
10	ID      *int64  `json:"id"`
10	ID      *int64  `json:"id"`
11	Name    *string `json:"name"`
11	Name    *string `json:"name"`
12	Licence *string `json:"licence"`
12	Licence *string `json:"licence"`
13}
13}

The program

example.godiffers

MySQL
CockroachDB
1package main
1package main
2
2
3import (
3import (
4	"context"
4	"context"
5	"database/sql"
5	"database/sql"
6	"fmt"
6	"fmt"
7	"io"
7	"io"
8
8
9	"seagull.sh/seagull/examples/mysql/015_embed/internal/store"
9	"seagull.sh/seagull/examples/postgres/015_embed/internal/store"
10)
10)
11
11
12// run adds a small fleet and reads it back with joined tables as nested structs.
12// run adds a small fleet and reads it back with joined tables as nested structs.
13// sgl.embed(table) gathers the columns the query selects from that table into a field
13// sgl.embed(table) gathers the columns the query selects from that table into a field
14// named after the table, here Harbours and Skippers, with its own struct type named after
14// named after the table, here Harbours and Skippers, with its own struct type named after
15// the query and the table, such as GetBoatHarbours.
15// the query and the table, such as GetBoatHarbours.
16//
16//
17// GetBoat uses an INNER JOIN, so every boat has a harbour and Harbours is a value.
17// GetBoat uses an INNER JOIN, so every boat has a harbour and Harbours is a value.
18// ListCrews uses a LEFT JOIN, so a boat may have no skipper: Skippers is a pointer that
18// ListCrews uses a LEFT JOIN, so a boat may have no skipper: Skippers is a pointer that
19// is nil when the joined row is missing, and its fields are pointers too, as each column
19// is nil when the joined row is missing, and its fields are pointers too, as each column
20// of a LEFT JOIN can be NULL.
20// of a LEFT JOIN can be NULL.
21//
21//
22// Takes database (*sql.DB) which is a migrated database.
22// Takes database (*sql.DB) which is a migrated database.
23// Takes output (io.Writer) which receives the boat and the crew list.
23// Takes output (io.Writer) which receives the boat and the crew list.
24//
24//
25// Returns error when a query fails.
25// Returns error when a query fails.
26func run(ctx context.Context, database *sql.DB, output io.Writer) error {
26func run(ctx context.Context, database *sql.DB, output io.Writer) error {
27	queries := store.New(database)
27	queries := store.New(database)
28
28
29	if err := queries.AddHarbour(ctx, store.AddHarbourParams{Name: "Newlyn", County: "Cornwall"}); err != nil {
29	if err := queries.AddHarbour(ctx, store.AddHarbourParams{Name: "Newlyn", County: "Cornwall"}); err != nil {
30		return fmt.Errorf("adding a harbour: %w", err)
30		return fmt.Errorf("adding a harbour: %w", err)
31	}
31	}
32	if err := queries.AddSkipper(ctx, store.AddSkipperParams{Name: "Morwenna Penrose", Licence: "MCA-1182"}); err != nil {
32	if err := queries.AddSkipper(ctx, store.AddSkipperParams{Name: "Morwenna Penrose", Licence: "MCA-1182"}); err != nil {
33		return fmt.Errorf("adding a skipper: %w", err)
33		return fmt.Errorf("adding a skipper: %w", err)
34	}
34	}
35	boats := []store.AddBoatParams{
35	boats := []store.AddBoatParams{
36		{Name: "Kittiwake", HarbourID: 1, SkipperID: new(int64(1))},
36		{Name: "Kittiwake", HarbourID: 1, SkipperID: new(int64(1))},
37		{Name: "Salty Chip", HarbourID: 1, SkipperID: nil},
37		{Name: "Salty Chip", HarbourID: 1, SkipperID: nil},
38	}
38	}
39	for _, boat := range boats {
39	for _, boat := range boats {
40		if err := queries.AddBoat(ctx, boat); err != nil {
40		if err := queries.AddBoat(ctx, boat); err != nil {
41			return fmt.Errorf("adding %s: %w", boat.Name, err)
41			return fmt.Errorf("adding %s: %w", boat.Name, err)
42		}
42		}
43	}
43	}
44
44
45	boat, err := queries.GetBoat(ctx, 2)
45	boat, err := queries.GetBoat(ctx, 2)
46	if err != nil {
46	if err != nil {
47		return fmt.Errorf("getting boat 2: %w", err)
47		return fmt.Errorf("getting boat 2: %w", err)
48	}
48	}
49	fmt.Fprintf(output, "%s is moored at %s, %s\n", boat.Name, boat.Harbours.Name, boat.Harbours.County)
49	fmt.Fprintf(output, "%s is moored at %s, %s\n", boat.Name, boat.Harbours.Name, boat.Harbours.County)
50
50
51	crews, err := queries.ListCrews(ctx)
51	crews, err := queries.ListCrews(ctx)
52	if err != nil {
52	if err != nil {
53		return fmt.Errorf("listing crews: %w", err)
53		return fmt.Errorf("listing crews: %w", err)
54	}
54	}
55	for _, crew := range crews {
55	for _, crew := range crews {
56		if crew.Skippers == nil {
56		if crew.Skippers == nil {
57			fmt.Fprintf(output, "%s has no skipper\n", crew.Name)
57			fmt.Fprintf(output, "%s has no skipper\n", crew.Name)
58			continue
58			continue
59		}
59		}
60		fmt.Fprintf(output, "%s is skippered by %s, licence %s\n", crew.Name, *crew.Skippers.Name, *crew.Skippers.Licence)
60		fmt.Fprintf(output, "%s is skippered by %s, licence %s\n", crew.Name, *crew.Skippers.Name, *crew.Skippers.Licence)
61	}
61	}
62	return nil
62	return nil
63}
63}

Go type differences

The same SQL types map to different Go types, because each database reports them differently.

Query	Field	MySQL	CockroachDB
ListCrews	column `id`	`*int64`	`int64`
ListCrews	column `name`	`*string`	`string`