Compare · 004

Nullability through joins, COALESCE and aggregates: CockroachDB and SQLite

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

Compare CockroachDB withSQLite PostgreSQL TimescaleDB MySQL MariaDB DuckDB ClickHouse

Configuration

sgl.yamldiffers

CockroachDB
SQLite
1version: 1
1version: 1
2
2
3databases:
3databases:
4  - name: colony
4  - name: colony
5    dialect: postgres
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
11      driver: database/sql

Migrations

db/migrations/0001_gulls.up.sqldiffers

CockroachDB
SQLite
1CREATE TABLE gulls (
1CREATE TABLE gulls (
2    id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
2    id       INTEGER PRIMARY KEY,
3    ring     TEXT   NOT NULL UNIQUE,
3    ring     TEXT    NOT NULL UNIQUE,
4    nickname TEXT
4    nickname TEXT
5);
5);
6
6
7CREATE TABLE sightings (
7CREATE TABLE sightings (
8    id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
8    id      INTEGER PRIMARY KEY,
9    gull_id BIGINT NOT NULL REFERENCES gulls (id),
9    gull_id INTEGER NOT NULL REFERENCES gulls (id),
10    seen_on DATE   NOT NULL,
10    seen_on TEXT    NOT NULL,
11    place   TEXT   NOT NULL
11    place   TEXT    NOT NULL
12);
12);

db/migrations/0001_gulls.down.sqlidentical

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

Queries

RingGulldiffers

CockroachDB · 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 ($1, $2);
2INSERT INTO gulls (ring, nickname) VALUES (?, ?);

RecordSightingdiffers

CockroachDB · 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 ($1, $2, $3);
2INSERT INTO sightings (gull_id, seen_on, place) VALUES (?, ?, ?);

ListSightingsidentical

CockroachDB · 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;

SummariseGullsidentical

CockroachDB · 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, 'ring ' || 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

CockroachDB
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

CockroachDB
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

CockroachDB
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

CockroachDB
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

CockroachDB
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/postgres/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, keeping the DATE column's type.
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: mayDay(1), Place: "the pier"},
42		{GullID: 1, SeenOn: "2026-05-01", Place: "the pier"},
44		{GullID: 1, SeenOn: mayDay(3), Place: "the chip shop"},
43		{GullID: 1, SeenOn: "2026-05-03", Place: "the chip shop"},
45		{GullID: 2, SeenOn: mayDay(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// mayDay returns a day in May 2026, for the DATE column.
78//
79// Takes day (int) which is the day of the month.
80//
81// Returns time.Time which is midnight UTC on that day.
82func mayDay(day int) time.Time {
83	return time.Date(2026, time.May, day, 0, 0, 0, 0, time.UTC)
84}
85
86// 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.
87//
73//
88// Takes value (*string) which is a nullable field.
74// Takes value (*string) which is a nullable field.
89//
75//
90// Returns string which is the text to print.
76// Returns string which is the text to print.
91func orNull(value *string) string {
77func orNull(value *string) string {
92	if value == nil {
78	if value == nil {
93		return "NULL"
79		return "NULL"
94	}
80	}
95	return *value
81	return *value
96}
82}

Go type differences

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

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