Compare · 004

Nullability through joins, COALESCE and aggregates: MariaDB and SQLite

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

Compare MariaDB withSQLite PostgreSQL CockroachDB TimescaleDB MySQL DuckDB ClickHouse

Configuration

sgl.yamldiffers

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

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

db/migrations/0001_gulls.down.sqlidentical

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

Queries

RingGullidentical

MariaDB · db/queries/gulls.sql
SQLite · 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

MariaDB · db/queries/gulls.sql
SQLite · 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

MariaDB · db/queries/gulls.sql
SQLite · 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

MariaDB · db/queries/gulls.sql
SQLite · 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, gulls.ring) AS known_as,
4    COALESCE(gulls.nickname, 'ring ' || 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

MariaDB
SQLite
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

MariaDB
SQLite
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 time.Time
5	SeenOn string
6	Place  string
6	Place  string
7}
7}

ListSightingsidentical

MariaDB
SQLite
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

MariaDB
SQLite
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  *time.Time `json:"last_seen"`
7	LastSeen  *string `json:"last_seen"`
8}
8}

The program

example.godiffers

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

Go type differences

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

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