Compare · 019

Types through CTEs, window functions and views: MariaDB and MySQL

MariaDB and MySQL run the same files for this example: the SQL and the Go are identical.

Compare MariaDB withSQLitePostgreSQLCockroachDBTimescaleDBMySQLDuckDBClickHouse

Configuration

sgl.yamlidentical
MariaDB
MySQL
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: colony
4 - name: colony
5 dialect: mysql
5 dialect: mysql
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

Migrations

db/migrations/0001_gulls.up.sqlidentical
MariaDB
MySQL
1CREATE TABLE gulls (
1CREATE TABLE gulls (
2 id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
2 id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
3 name VARCHAR(50) NOT NULL,
3 name VARCHAR(50) NOT NULL,
4 parent_id BIGINT,
4 parent_id BIGINT,
5 beach VARCHAR(50) NOT NULL,
5 beach VARCHAR(50) NOT NULL,
6 FOREIGN KEY (parent_id) REFERENCES gulls (id)
6 FOREIGN KEY (parent_id) REFERENCES gulls (id)
7);
7);
8
8
9CREATE TABLE thefts (
9CREATE TABLE thefts (
10 id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
10 id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
11 gull_id BIGINT NOT NULL,
11 gull_id BIGINT NOT NULL,
12 snack VARCHAR(50) NOT NULL,
12 snack VARCHAR(50) NOT NULL,
13 stolen_on DATE NOT NULL,
13 stolen_on DATE NOT NULL,
14 FOREIGN KEY (gull_id) REFERENCES gulls (id)
14 FOREIGN KEY (gull_id) REFERENCES gulls (id)
15);
15);
16
16
17CREATE VIEW beach_records AS
17CREATE VIEW beach_records AS
18SELECT
18SELECT
19 gulls.beach,
19 gulls.beach,
20 COUNT(DISTINCT gulls.id) AS gulls,
20 COUNT(DISTINCT gulls.id) AS gulls,
21 COUNT(thefts.id) AS thefts,
21 COUNT(thefts.id) AS thefts,
22 MAX(thefts.stolen_on) AS last_theft
22 MAX(thefts.stolen_on) AS last_theft
23FROM gulls
23FROM gulls
24LEFT JOIN thefts ON thefts.gull_id = gulls.id
24LEFT JOIN thefts ON thefts.gull_id = gulls.id
25GROUP BY gulls.beach;
25GROUP BY gulls.beach;
db/migrations/0001_gulls.down.sqlidentical
MariaDB
MySQL
1DROP VIEW beach_records;
1DROP VIEW beach_records;
2DROP TABLE thefts;
2DROP TABLE thefts;
3DROP TABLE gulls;
3DROP TABLE gulls;

Queries

HatchGullidentical
MariaDB · db/queries/gulls.sql
MySQL · db/queries/gulls.sql
1-- sgl.query(HatchGull, exec)
1-- sgl.query(HatchGull, exec)
2INSERT INTO gulls (name, parent_id, beach) VALUES (?, ?, ?);
2INSERT INTO gulls (name, parent_id, beach) VALUES (?, ?, ?);
RecordTheftidentical
MariaDB · db/queries/gulls.sql
MySQL · db/queries/gulls.sql
1-- sgl.query(RecordTheft, exec)
1-- sgl.query(RecordTheft, exec)
2INSERT INTO thefts (gull_id, snack, stolen_on) VALUES (?, ?, ?);
2INSERT INTO thefts (gull_id, snack, stolen_on) VALUES (?, ?, ?);
FamilyTreeidentical
MariaDB · db/queries/gulls.sql
MySQL · db/queries/gulls.sql
1-- sgl.query(FamilyTree, many)
1-- sgl.query(FamilyTree, many)
2WITH RECURSIVE family AS (
2WITH RECURSIVE family AS (
3 SELECT id, name, parent_id, 0 AS generation
3 SELECT id, name, parent_id, 0 AS generation
4 FROM gulls
4 FROM gulls
5 WHERE id = ?
5 WHERE id = ?
6 UNION ALL
6 UNION ALL
7 SELECT gulls.id, gulls.name, gulls.parent_id, family.generation + 1
7 SELECT gulls.id, gulls.name, gulls.parent_id, family.generation + 1
8 FROM gulls
8 FROM gulls
9 JOIN family ON gulls.parent_id = family.id
9 JOIN family ON gulls.parent_id = family.id
10)
10)
11SELECT family.name, family.generation, parents.name AS parent
11SELECT family.name, family.generation, parents.name AS parent
12FROM family
12FROM family
13LEFT JOIN gulls AS parents ON parents.id = family.parent_id
13LEFT JOIN gulls AS parents ON parents.id = family.parent_id
14ORDER BY family.generation, family.id;
14ORDER BY family.generation, family.id;
TheftLeagueidentical
MariaDB · db/queries/gulls.sql
MySQL · db/queries/gulls.sql
1-- sgl.query(TheftLeague, many)
1-- sgl.query(TheftLeague, many)
2WITH totals AS (
2WITH totals AS (
3 SELECT gulls.name, gulls.beach, COUNT(thefts.id) AS thefts
3 SELECT gulls.name, gulls.beach, COUNT(thefts.id) AS thefts
4 FROM gulls
4 FROM gulls
5 LEFT JOIN thefts ON thefts.gull_id = gulls.id
5 LEFT JOIN thefts ON thefts.gull_id = gulls.id
6 GROUP BY gulls.id
6 GROUP BY gulls.id
7)
7)
8SELECT
8SELECT
9 name,
9 name,
10 beach,
10 beach,
11 thefts,
11 thefts,
12 RANK() OVER (PARTITION BY beach ORDER BY thefts DESC) AS beach_rank,
12 RANK() OVER (PARTITION BY beach ORDER BY thefts DESC) AS beach_rank,
13 SUM(thefts) OVER (PARTITION BY beach) AS beach_thefts
13 SUM(thefts) OVER (PARTITION BY beach) AS beach_thefts
14FROM totals
14FROM totals
15ORDER BY beach, beach_rank, name;
15ORDER BY beach, beach_rank, name;
BeachRecordsidentical
MariaDB · db/queries/gulls.sql
MySQL · db/queries/gulls.sql
1-- sgl.query(BeachRecords, many)
1-- sgl.query(BeachRecords, many)
2SELECT beach, gulls, thefts, last_theft FROM beach_records ORDER BY beach;
2SELECT beach, gulls, thefts, last_theft FROM beach_records ORDER BY beach;

What sgl generates

HatchGullidentical
MariaDB
MySQL
1func (queries *Queries) HatchGull(ctx context.Context, params HatchGullParams) error
1func (queries *Queries) HatchGull(ctx context.Context, params HatchGullParams) error
2
2
3type HatchGullParams struct {
3type HatchGullParams struct {
4 Name string
4 Name string
5 ParentID *int64
5 ParentID *int64
6 Beach string
6 Beach string
7}
7}
RecordTheftidentical
MariaDB
MySQL
1func (queries *Queries) RecordTheft(ctx context.Context, params RecordTheftParams) error
1func (queries *Queries) RecordTheft(ctx context.Context, params RecordTheftParams) error
2
2
3type RecordTheftParams struct {
3type RecordTheftParams struct {
4 GullID int64
4 GullID int64
5 Snack string
5 Snack string
6 StolenOn time.Time
6 StolenOn time.Time
7}
7}
FamilyTreeidentical
MariaDB
MySQL
1func (queries *Queries) FamilyTree(ctx context.Context, id int64) ([]FamilyTreeRow, error)
1func (queries *Queries) FamilyTree(ctx context.Context, id int64) ([]FamilyTreeRow, error)
2
2
3type FamilyTreeRow struct {
3type FamilyTreeRow struct {
4 Name string `json:"name"`
4 Name string `json:"name"`
5 Generation int32 `json:"generation"`
5 Generation int32 `json:"generation"`
6 Parent *string `json:"parent"`
6 Parent *string `json:"parent"`
7}
7}
TheftLeagueidentical
MariaDB
MySQL
1func (queries *Queries) TheftLeague(ctx context.Context) ([]TheftLeagueRow, error)
1func (queries *Queries) TheftLeague(ctx context.Context) ([]TheftLeagueRow, error)
2
2
3type TheftLeagueRow struct {
3type TheftLeagueRow struct {
4 Name string `json:"name"`
4 Name string `json:"name"`
5 Beach string `json:"beach"`
5 Beach string `json:"beach"`
6 Thefts int64 `json:"thefts"`
6 Thefts int64 `json:"thefts"`
7 BeachRank int64 `json:"beach_rank"`
7 BeachRank int64 `json:"beach_rank"`
8 BeachThefts *apd.Decimal `json:"beach_thefts"`
8 BeachThefts *apd.Decimal `json:"beach_thefts"`
9}
9}
BeachRecordsidentical
MariaDB
MySQL
1func (queries *Queries) BeachRecords(ctx context.Context) ([]BeachRecordsRow, error)
1func (queries *Queries) BeachRecords(ctx context.Context) ([]BeachRecordsRow, error)
2
2
3type BeachRecordsRow struct {
3type BeachRecordsRow struct {
4 Beach string `json:"beach"`
4 Beach string `json:"beach"`
5 Gulls int64 `json:"gulls"`
5 Gulls int64 `json:"gulls"`
6 Thefts int64 `json:"thefts"`
6 Thefts int64 `json:"thefts"`
7 LastTheft *time.Time `json:"last_theft"`
7 LastTheft *time.Time `json:"last_theft"`
8}
8}

The program

example.goidentical
MariaDB
MySQL
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 "time"
8 "time"
9
9
10 "seagull.sh/seagull/examples/mysql/019_ctes_and_windows/internal/store"
10 "seagull.sh/seagull/examples/mysql/019_ctes_and_windows/internal/store"
11)
11)
12
12
13// run hatches a colony of gulls, records their snack thefts and reads them back three
13// run hatches a colony of gulls, records their snack thefts and reads them back three
14// ways. Seagull follows each column's type and nullability through the SQL:
14// ways. Seagull follows each column's type and nullability through the SQL:
15//
15//
16// - FamilyTree's recursive CTE starts from one gull and joins its chicks generation by
16// - FamilyTree's recursive CTE starts from one gull and joins its chicks generation by
17// generation; generation is an integer from 0 AS generation and generation + 1, and
17// generation; generation is an integer from 0 AS generation and generation + 1, and
18// parent is *string because the LEFT JOIN finds no parent for the first gull;
18// parent is *string because the LEFT JOIN finds no parent for the first gull;
19// - TheftLeague counts thefts in a CTE, then ranks gulls on each beach with RANK() and
19// - TheftLeague counts thefts in a CTE, then ranks gulls on each beach with RANK() and
20// totals the beach with SUM() OVER; RANK is never NULL, while SUM is nullable, as SQL
20// totals the beach with SUM() OVER; RANK is never NULL, while SUM is nullable, as SQL
21// sums no rows to NULL, and it is a DECIMAL, as MySQL sums integers exactly into a
21// sums no rows to NULL, and it is a DECIMAL, as MySQL sums integers exactly into a
22// DECIMAL, so BeachThefts is *apd.Decimal;
22// DECIMAL, so BeachThefts is *apd.Decimal;
23// - BeachRecords reads the beach_records view from the migration as if it were a table,
23// - BeachRecords reads the beach_records view from the migration as if it were a table,
24// and its columns keep the types the view's own SELECT gives them.
24// and its columns keep the types the view's own SELECT gives them.
25//
25//
26// Takes database (*sql.DB) which is a migrated database.
26// Takes database (*sql.DB) which is a migrated database.
27// Takes output (io.Writer) which receives the three listings.
27// Takes output (io.Writer) which receives the three listings.
28//
28//
29// Returns error when a query fails.
29// Returns error when a query fails.
30func run(ctx context.Context, database *sql.DB, output io.Writer) error {
30func run(ctx context.Context, database *sql.DB, output io.Writer) error {
31 queries := store.New(database)
31 queries := store.New(database)
32
32
33 gulls := []store.HatchGullParams{
33 gulls := []store.HatchGullParams{
34 {Name: "Steven", ParentID: nil, Beach: "Porthmeor"},
34 {Name: "Steven", ParentID: nil, Beach: "Porthmeor"},
35 {Name: "Gwen", ParentID: new(int64(1)), Beach: "Porthmeor"},
35 {Name: "Gwen", ParentID: new(int64(1)), Beach: "Porthmeor"},
36 {Name: "Jago", ParentID: new(int64(1)), Beach: "Porthminster"},
36 {Name: "Jago", ParentID: new(int64(1)), Beach: "Porthminster"},
37 {Name: "Kerensa", ParentID: new(int64(2)), Beach: "Porthmeor"},
37 {Name: "Kerensa", ParentID: new(int64(2)), Beach: "Porthmeor"},
38 {Name: "Tristan", ParentID: nil, Beach: "Porthminster"},
38 {Name: "Tristan", ParentID: nil, Beach: "Porthminster"},
39 {Name: "Morwenna", ParentID: nil, Beach: "Gwithian"},
39 {Name: "Morwenna", ParentID: nil, Beach: "Gwithian"},
40 }
40 }
41 for _, gull := range gulls {
41 for _, gull := range gulls {
42 if err := queries.HatchGull(ctx, gull); err != nil {
42 if err := queries.HatchGull(ctx, gull); err != nil {
43 return fmt.Errorf("hatching %s: %w", gull.Name, err)
43 return fmt.Errorf("hatching %s: %w", gull.Name, err)
44 }
44 }
45 }
45 }
46 thefts := []store.RecordTheftParams{
46 thefts := []store.RecordTheftParams{
47 {GullID: 1, Snack: "chips", StolenOn: day(1)},
47 {GullID: 1, Snack: "chips", StolenOn: day(1)},
48 {GullID: 1, Snack: "pasty", StolenOn: day(3)},
48 {GullID: 1, Snack: "pasty", StolenOn: day(3)},
49 {GullID: 1, Snack: "ice cream", StolenOn: day(4)},
49 {GullID: 1, Snack: "ice cream", StolenOn: day(4)},
50 {GullID: 2, Snack: "chips", StolenOn: day(2)},
50 {GullID: 2, Snack: "chips", StolenOn: day(2)},
51 {GullID: 3, Snack: "pasty", StolenOn: day(2)},
51 {GullID: 3, Snack: "pasty", StolenOn: day(2)},
52 {GullID: 3, Snack: "chips", StolenOn: day(5)},
52 {GullID: 3, Snack: "chips", StolenOn: day(5)},
53 {GullID: 5, Snack: "sandwich", StolenOn: day(3)},
53 {GullID: 5, Snack: "sandwich", StolenOn: day(3)},
54 }
54 }
55 for _, theft := range thefts {
55 for _, theft := range thefts {
56 if err := queries.RecordTheft(ctx, theft); err != nil {
56 if err := queries.RecordTheft(ctx, theft); err != nil {
57 return fmt.Errorf("recording a theft: %w", err)
57 return fmt.Errorf("recording a theft: %w", err)
58 }
58 }
59 }
59 }
60
60
61 family, err := queries.FamilyTree(ctx, 1)
61 family, err := queries.FamilyTree(ctx, 1)
62 if err != nil {
62 if err != nil {
63 return fmt.Errorf("tracing Steven's family: %w", err)
63 return fmt.Errorf("tracing Steven's family: %w", err)
64 }
64 }
65 fmt.Fprintln(output, "Steven's family:")
65 fmt.Fprintln(output, "Steven's family:")
66 for _, member := range family {
66 for _, member := range family {
67 fmt.Fprintf(output, " generation %d %-8s parent %s\n", member.Generation, member.Name, orNull(member.Parent))
67 fmt.Fprintf(output, " generation %d %-8s parent %s\n", member.Generation, member.Name, orNull(member.Parent))
68 }
68 }
69
69
70 league, err := queries.TheftLeague(ctx)
70 league, err := queries.TheftLeague(ctx)
71 if err != nil {
71 if err != nil {
72 return fmt.Errorf("ranking thieves: %w", err)
72 return fmt.Errorf("ranking thieves: %w", err)
73 }
73 }
74 fmt.Fprintln(output, "theft league:")
74 fmt.Fprintln(output, "theft league:")
75 for _, entry := range league {
75 for _, entry := range league {
76 fmt.Fprintf(output, " %-13s #%d %-8s %d of %s\n", entry.Beach, entry.BeachRank, entry.Name, entry.Thefts, orNull(entry.BeachThefts))
76 fmt.Fprintf(output, " %-13s #%d %-8s %d of %s\n", entry.Beach, entry.BeachRank, entry.Name, entry.Thefts, orNull(entry.BeachThefts))
77 }
77 }
78
78
79 records, err := queries.BeachRecords(ctx)
79 records, err := queries.BeachRecords(ctx)
80 if err != nil {
80 if err != nil {
81 return fmt.Errorf("reading beach records: %w", err)
81 return fmt.Errorf("reading beach records: %w", err)
82 }
82 }
83 fmt.Fprintln(output, "beach records:")
83 fmt.Fprintln(output, "beach records:")
84 for _, record := range records {
84 for _, record := range records {
85 fmt.Fprintf(output, " %-13s gulls %d, thefts %d, last theft %s\n", record.Beach, record.Gulls, record.Thefts, orNull(record.LastTheft))
85 fmt.Fprintf(output, " %-13s gulls %d, thefts %d, last theft %s\n", record.Beach, record.Gulls, record.Thefts, orNull(record.LastTheft))
86 }
86 }
87 return nil
87 return nil
88}
88}
89
89
90// day returns midnight UTC on a day in July 2026, the value a DATE column holds.
90// day returns midnight UTC on a day in July 2026, the value a DATE column holds.
91//
91//
92// Takes date (int) which is the day of the month.
92// Takes date (int) which is the day of the month.
93//
93//
94// Returns time.Time which is the start of that day.
94// Returns time.Time which is the start of that day.
95func day(date int) time.Time {
95func day(date int) time.Time {
96 return time.Date(2026, time.July, date, 0, 0, 0, 0, time.UTC)
96 return time.Date(2026, time.July, date, 0, 0, 0, 0, time.UTC)
97}
97}
98
98
99// orNull formats a nullable field, or returns NULL when the field is nil. A date prints
99// orNull formats a nullable field, or returns NULL when the field is nil. A date prints
100// as YYYY-MM-DD, and a decimal through its String method, which apd.Decimal declares on
100// as YYYY-MM-DD, and a decimal through its String method, which apd.Decimal declares on
101// its pointer.
101// its pointer.
102//
102//
103// Takes value (*T) which is a nullable field.
103// Takes value (*T) which is a nullable field.
104//
104//
105// Returns string which is the text to print.
105// Returns string which is the text to print.
106func orNull[T any](value *T) string {
106func orNull[T any](value *T) string {
107 if value == nil {
107 if value == nil {
108 return "NULL"
108 return "NULL"
109 }
109 }
110 switch field := any(value).(type) {
110 switch field := any(value).(type) {
111 case *time.Time:
111 case *time.Time:
112 return field.Format(time.DateOnly)
112 return field.Format(time.DateOnly)
113 case fmt.Stringer:
113 case fmt.Stringer:
114 return field.String()
114 return field.String()
115 default:
115 default:
116 return fmt.Sprint(*value)
116 return fmt.Sprint(*value)
117 }
117 }
118}
118}