Compare · 004

Nullability through joins, COALESCE and aggregates: SQLite and MySQL

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

Compare SQLite withPostgreSQL CockroachDB TimescaleDB MySQL MariaDB DuckDB ClickHouse

Configuration

sgl.yamldiffers

SQLite
MySQL
1version: 1
1version: 1
2
2
3databases:
3databases:
4  - name: colony
4  - name: colony
5    dialect: sqlite
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.sqldiffers

SQLite
MySQL
1CREATE TABLE gulls (
1CREATE TABLE gulls (
2    id       INTEGER PRIMARY KEY,
2    id       BIGINT      NOT NULL AUTO_INCREMENT PRIMARY KEY,
3    ring     TEXT    NOT NULL UNIQUE,
3    ring     VARCHAR(10) NOT NULL UNIQUE,
4    nickname TEXT
4    nickname VARCHAR(50)
5);
5);
6
6
7CREATE TABLE sightings (
7CREATE TABLE sightings (
8    id      INTEGER PRIMARY KEY,
8    id      BIGINT       NOT NULL AUTO_INCREMENT PRIMARY KEY,
9    gull_id INTEGER NOT NULL REFERENCES gulls (id),
9    gull_id BIGINT       NOT NULL,
10    seen_on TEXT    NOT NULL,
10    seen_on DATE         NOT NULL,
11    place   TEXT    NOT NULL
11    place   VARCHAR(100) NOT NULL,
12    FOREIGN KEY (gull_id) REFERENCES gulls (id)
12);
13);

db/migrations/0001_gulls.down.sqlidentical

SQLite
MySQL
1DROP TABLE sightings;
1DROP TABLE sightings;
2DROP TABLE gulls;
2DROP TABLE gulls;

Queries

RingGullidentical

SQLite · db/queries/gulls.sql
MySQL · db/queries/gulls.sql
1-- sgl.query(RingGull, exec)
1-- sgl.query(RingGull, exec)
2INSERT INTO gulls (ring, nickname) VALUES (?, ?);
2INSERT INTO gulls (ring, nickname) VALUES (?, ?);

RecordSightingidentical

SQLite · db/queries/gulls.sql
MySQL · db/queries/gulls.sql
1-- sgl.query(RecordSighting, exec)
1-- sgl.query(RecordSighting, exec)
2INSERT INTO sightings (gull_id, seen_on, place) VALUES (?, ?, ?);
2INSERT INTO sightings (gull_id, seen_on, place) VALUES (?, ?, ?);

ListSightingsidentical

SQLite · db/queries/gulls.sql
MySQL · db/queries/gulls.sql
1-- sgl.query(ListSightings, many)
1-- sgl.query(ListSightings, many)
2SELECT gulls.ring, gulls.nickname, sightings.place
2SELECT gulls.ring, gulls.nickname, sightings.place
3FROM gulls
3FROM gulls
4LEFT JOIN sightings ON sightings.gull_id = gulls.id
4LEFT JOIN sightings ON sightings.gull_id = gulls.id
5ORDER BY gulls.id, sightings.seen_on;
5ORDER BY gulls.id, sightings.seen_on;

SummariseGullsdiffers

SQLite · db/queries/gulls.sql
MySQL · db/queries/gulls.sql
1-- sgl.query(SummariseGulls, many)
1-- sgl.query(SummariseGulls, many)
2SELECT
2SELECT
3    gulls.ring,
3    gulls.ring,
4    COALESCE(gulls.nickname, 'ring ' || gulls.ring) AS known_as,
4    COALESCE(gulls.nickname, gulls.ring) AS known_as,
5    COUNT(sightings.id) AS times_seen,
5    COUNT(sightings.id) AS times_seen,
6    MAX(sightings.seen_on) AS last_seen
6    MAX(sightings.seen_on) AS last_seen
7FROM gulls
7FROM gulls
8LEFT JOIN sightings ON sightings.gull_id = gulls.id
8LEFT JOIN sightings ON sightings.gull_id = gulls.id
9GROUP BY gulls.id
9GROUP BY gulls.id
10ORDER BY gulls.id;
10ORDER BY gulls.id;

What sgl generates

RingGullidentical

SQLite
MySQL
1func (queries *Queries) RingGull(ctx context.Context, params RingGullParams) error
1func (queries *Queries) RingGull(ctx context.Context, params RingGullParams) error
2
2
3type RingGullParams struct {
3type RingGullParams struct {
4	Ring     string
4	Ring     string
5	Nickname *string
5	Nickname *string
6}
6}

RecordSightingdiffers

SQLite
MySQL
1func (queries *Queries) RecordSighting(ctx context.Context, params RecordSightingParams) error
1func (queries *Queries) RecordSighting(ctx context.Context, params RecordSightingParams) error
2
2
3type RecordSightingParams struct {
3type RecordSightingParams struct {
4	GullID int64
4	GullID int64
5	SeenOn string
5	SeenOn time.Time
6	Place  string
6	Place  string
7}
7}

ListSightingsidentical

SQLite
MySQL
1func (queries *Queries) ListSightings(ctx context.Context) ([]ListSightingsRow, error)
1func (queries *Queries) ListSightings(ctx context.Context) ([]ListSightingsRow, error)
2
2
3type ListSightingsRow struct {
3type ListSightingsRow struct {
4	Ring     string  `json:"ring"`
4	Ring     string  `json:"ring"`
5	Nickname *string `json:"nickname"`
5	Nickname *string `json:"nickname"`
6	Place    *string `json:"place"`
6	Place    *string `json:"place"`
7}
7}

SummariseGullsdiffers

SQLite
MySQL
1func (queries *Queries) SummariseGulls(ctx context.Context) ([]SummariseGullsRow, error)
1func (queries *Queries) SummariseGulls(ctx context.Context) ([]SummariseGullsRow, error)
2
2
3type SummariseGullsRow struct {
3type SummariseGullsRow struct {
4	Ring      string  `json:"ring"`
4	Ring      string     `json:"ring"`
5	KnownAs   string  `json:"known_as"`
5	KnownAs   string     `json:"known_as"`
6	TimesSeen int64   `json:"times_seen"`
6	TimesSeen int64      `json:"times_seen"`
7	LastSeen  *string `json:"last_seen"`
7	LastSeen  *time.Time `json:"last_seen"`
8}
8}

The program

example.godiffers

SQLite
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
9
9	"seagull.sh/seagull/examples/sqlite/004_nullability/internal/store"
10	"seagull.sh/seagull/examples/mysql/004_nullability/internal/store"
10)
11)
11
12
12// run rings three gulls, records where two of them were seen, and lists them twice.
13// run rings three gulls, records where two of them were seen, and lists them twice.
13// Seagull decides each field's nullability from the SQL, and a field that can be NULL is
14// Seagull decides each field's nullability from the SQL, and a field that can be NULL is
14// a pointer:
15// a pointer:
15//
16//
16//   - gulls.nickname has no NOT NULL, so Nickname is *string, as a parameter and a
17//   - gulls.nickname has no NOT NULL, so Nickname is *string, as a parameter and a
17//     column;
18//     column;
18//   - sightings.place is NOT NULL, but the LEFT JOIN gives a gull with no sightings a
19//   - sightings.place is NOT NULL, but the LEFT JOIN gives a gull with no sightings a
19//     NULL place, so Place is *string;
20//     NULL place, so Place is *string;
20//   - COALESCE with a non-NULL last argument is never NULL, so KnownAs is string;
21//   - COALESCE with a non-NULL last argument is never NULL, so KnownAs is string;
21//   - COUNT is never NULL, but MAX over no rows is, so TimesSeen is int64 and LastSeen is
22//   - COUNT is never NULL, but MAX over no rows is, so TimesSeen is int64 and LastSeen is
22//     *string.
23//     *time.Time, the type of the DATE column it reads.
23//
24//
24// Takes database (*sql.DB) which is a migrated database.
25// Takes database (*sql.DB) which is a migrated database.
25// Takes output (io.Writer) which receives both listings.
26// Takes output (io.Writer) which receives both listings.
26//
27//
27// Returns error when a query fails.
28// Returns error when a query fails.
28func run(ctx context.Context, database *sql.DB, output io.Writer) error {
29func run(ctx context.Context, database *sql.DB, output io.Writer) error {
29	queries := store.New(database)
30	queries := store.New(database)
30
31
31	gulls := []store.RingGullParams{
32	gulls := []store.RingGullParams{
32		{Ring: "GB1001", Nickname: new("Steven")},
33		{Ring: "GB1001", Nickname: new("Steven")},
33		{Ring: "GB1002", Nickname: nil},
34		{Ring: "GB1002", Nickname: nil},
34		{Ring: "GB1003", Nickname: new("Gwen")},
35		{Ring: "GB1003", Nickname: new("Gwen")},
35	}
36	}
36	for _, gull := range gulls {
37	for _, gull := range gulls {
37		if err := queries.RingGull(ctx, gull); err != nil {
38		if err := queries.RingGull(ctx, gull); err != nil {
38			return fmt.Errorf("ringing %s: %w", gull.Ring, err)
39			return fmt.Errorf("ringing %s: %w", gull.Ring, err)
39		}
40		}
40	}
41	}
41	sightings := []store.RecordSightingParams{
42	sightings := []store.RecordSightingParams{
42		{GullID: 1, SeenOn: "2026-05-01", Place: "the pier"},
43		{GullID: 1, SeenOn: day(2026, time.May, 1), Place: "the pier"},
43		{GullID: 1, SeenOn: "2026-05-03", Place: "the chip shop"},
44		{GullID: 1, SeenOn: day(2026, time.May, 3), Place: "the chip shop"},
44		{GullID: 2, SeenOn: "2026-05-02", Place: "the harbour wall"},
45		{GullID: 2, SeenOn: day(2026, time.May, 2), Place: "the harbour wall"},
45	}
46	}
46	for _, sighting := range sightings {
47	for _, sighting := range sightings {
47		if err := queries.RecordSighting(ctx, sighting); err != nil {
48		if err := queries.RecordSighting(ctx, sighting); err != nil {
48			return fmt.Errorf("recording a sighting: %w", err)
49			return fmt.Errorf("recording a sighting: %w", err)
49		}
50		}
50	}
51	}
51
52
52	rows, err := queries.ListSightings(ctx)
53	rows, err := queries.ListSightings(ctx)
53	if err != nil {
54	if err != nil {
54		return fmt.Errorf("listing sightings: %w", err)
55		return fmt.Errorf("listing sightings: %w", err)
55	}
56	}
56	fmt.Fprintln(output, "ring    nickname  place")
57	fmt.Fprintln(output, "ring    nickname  place")
57	for _, row := range rows {
58	for _, row := range rows {
58		fmt.Fprintf(output, "%-7s %-9s %s\n", row.Ring, orNull(row.Nickname), orNull(row.Place))
59		fmt.Fprintf(output, "%-7s %-9s %s\n", row.Ring, orNull(row.Nickname), orNull(row.Place))
59	}
60	}
60
61
61	summaries, err := queries.SummariseGulls(ctx)
62	summaries, err := queries.SummariseGulls(ctx)
62	if err != nil {
63	if err != nil {
63		return fmt.Errorf("summarising gulls: %w", err)
64		return fmt.Errorf("summarising gulls: %w", err)
64	}
65	}
65	fmt.Fprintln(output, "\nring    known as     times seen  last seen")
66	fmt.Fprintln(output, "\nring    known as     times seen  last seen")
66	for _, summary := range summaries {
67	for _, summary := range summaries {
67		fmt.Fprintf(output, "%-7s %-12s %-11d %s\n", summary.Ring, summary.KnownAs, summary.TimesSeen, orNull(summary.LastSeen))
68		lastSeen := "NULL"
69		if summary.LastSeen != nil {
70			lastSeen = summary.LastSeen.Format(time.DateOnly)
71		}
72		fmt.Fprintf(output, "%-7s %-12s %-11d %s\n", summary.Ring, summary.KnownAs, summary.TimesSeen, lastSeen)
68	}
73	}
69	return nil
74	return nil
70}
75}
71
76
77// day returns midnight UTC on a date, the value a DATE column holds.
78//
79// Takes year (int) which is the year.
80// Takes month (time.Month) which is the month.
81// Takes date (int) which is the day of the month.
82//
83// Returns time.Time which is the start of that day.
84func day(year int, month time.Month, date int) time.Time {
85	return time.Date(year, month, date, 0, 0, 0, 0, time.UTC)
86}
87
72// orNull returns the text a nullable field holds, or NULL when the field is nil.
88// orNull returns the text a nullable field holds, or NULL when the field is nil.
73//
89//
74// Takes value (*string) which is a nullable field.
90// Takes value (*string) which is a nullable field.
75//
91//
76// Returns string which is the text to print.
92// Returns string which is the text to print.
77func orNull(value *string) string {
93func orNull(value *string) string {
78	if value == nil {
94	if value == nil {
79		return "NULL"
95		return "NULL"
80	}
96	}
81	return *value
97	return *value
82}
98}

Go type differences

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

Query	Field	SQLite	MySQL
RecordSighting	parameter `seen_on`	`string`	`time.Time`
SummariseGulls	column `last_seen`	`*string`	`*time.Time`