Compare · 019

Types through CTEs, window functions and views: SQLite and ClickHouse

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

Compare SQLite withPostgreSQLCockroachDBTimescaleDBMySQLMariaDBDuckDBClickHouse

Configuration

sgl.yamldiffers
SQLite
ClickHouse
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: colony
4 - name: colony
5 dialect: sqlite
5 dialect: clickhouse
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
ClickHouse
1CREATE TABLE gulls (
1CREATE TABLE gulls (
2 id INTEGER PRIMARY KEY,
2 id UInt64,
3 name TEXT NOT NULL,
3 name String,
4 parent_id INTEGER REFERENCES gulls (id),
4 parent_id Nullable(UInt64),
5 beach TEXT NOT NULL
5 beach String
6);
6) ENGINE = MergeTree() ORDER BY id;
7
7
8CREATE TABLE thefts (
8CREATE TABLE thefts (
9 id INTEGER PRIMARY KEY,
9 id UInt64,
10 gull_id INTEGER NOT NULL REFERENCES gulls (id),
10 gull_id UInt64,
11 snack TEXT NOT NULL,
11 snack String,
12 stolen_on TEXT NOT NULL
12 stolen_on Date
13);
13) ENGINE = MergeTree() ORDER BY id;
14
14
15CREATE VIEW beach_records AS
15CREATE VIEW beach_records AS
16SELECT
16SELECT
17 gulls.beach,
17 gulls.beach,
18 COUNT(DISTINCT gulls.id) AS gulls,
18 uniqExact(gulls.id) AS gulls,
19 COUNT(thefts.id) AS thefts,
19 count(thefts.id) AS thefts,
20 MAX(thefts.stolen_on) AS last_theft
20 max(thefts.stolen_on) AS last_theft
21FROM gulls
21FROM gulls
22LEFT JOIN thefts ON thefts.gull_id = gulls.id
22LEFT JOIN thefts ON thefts.gull_id = gulls.id
23GROUP BY gulls.beach;
23GROUP BY gulls.beach
24SETTINGS join_use_nulls = 1;
db/migrations/0001_gulls.down.sqlidentical
SQLite
ClickHouse
1DROP VIEW beach_records;
1DROP VIEW beach_records;
2DROP TABLE thefts;
2DROP TABLE thefts;
3DROP TABLE gulls;
3DROP TABLE gulls;

Queries

HatchGulldiffers
SQLite · db/queries/gulls.sql
ClickHouse · 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 (id, name, parent_id, beach)
3VALUES ({id:UInt64}, {name:String}, {parent_id:Nullable(UInt64)}, {beach:String});
RecordTheftdiffers
SQLite · db/queries/gulls.sql
ClickHouse · 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 (id, gull_id, snack, stolen_on)
3VALUES ({id:UInt64}, {gull_id:UInt64}, {snack:String}, {stolen_on:Date});
FamilyTreediffers
SQLite · db/queries/gulls.sql
ClickHouse · 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 = {root:UInt64}
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
15SETTINGS join_use_nulls = 1;
TheftLeaguediffers
SQLite · db/queries/gulls.sql
ClickHouse · 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 stolen
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, gulls.name, gulls.beach
7)
7)
8SELECT
8SELECT
9 name,
9 name,
10 beach,
10 beach,
11 thefts,
11 stolen,
12 RANK() OVER (PARTITION BY beach ORDER BY thefts DESC) AS beach_rank,
12 rank() OVER (PARTITION BY beach ORDER BY stolen DESC) AS beach_rank,
13 SUM(thefts) OVER (PARTITION BY beach) AS beach_thefts
13 sum(stolen) OVER (PARTITION BY beach) AS beach_thefts
14FROM totals
14FROM totals
15ORDER BY beach, beach_rank, name;
15ORDER BY beach, beach_rank, name
16SETTINGS join_use_nulls = 1;
BeachRecordsidentical
SQLite · db/queries/gulls.sql
ClickHouse · 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

HatchGulldiffers
SQLite
ClickHouse
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 ID uint64
4 Name string
5 Name string
5 ParentID *int64
6 ParentID *uint64
6 Beach string
7 Beach string
7}
8}
RecordTheftdiffers
SQLite
ClickHouse
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 ID uint64
5 GullID uint64
5 Snack string
6 Snack string
6 StolenOn string
7 StolenOn time.Time
7}
8}
FamilyTreediffers
SQLite
ClickHouse
1func (queries *Queries) FamilyTree(ctx context.Context, id int64) ([]FamilyTreeRow, error)
1func (queries *Queries) FamilyTree(ctx context.Context, root uint64) ([]FamilyTreeRow, error)
2
2
3type FamilyTreeRow struct {
3type FamilyTreeRow struct {
4 Name string `json:"name"`
4 Name string `json:"name"`
5 Generation int64 `json:"generation"`
5 Generation uint8 `json:"generation"`
6 Parent *string `json:"parent"`
6 Parent *string `json:"parent"`
7}
7}
TheftLeaguediffers
SQLite
ClickHouse
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 Stolen uint64 `json:"stolen"`
7 BeachRank int64 `json:"beach_rank"`
7 BeachRank uint64 `json:"beach_rank"`
8 BeachThefts *int64 `json:"beach_thefts"`
8 BeachThefts uint64 `json:"beach_thefts"`
9}
9}
BeachRecordsdiffers
SQLite
ClickHouse
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 uint64 `json:"gulls"`
6 Thefts int64 `json:"thefts"`
6 Thefts uint64 `json:"thefts"`
7 LastTheft *string `json:"last_theft"`
7 LastTheft *time.Time `json:"last_theft"`
8}
8}

The program

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

Go type differences

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

QueryFieldSQLiteClickHouse
HatchGullparameter parent_id*int64*uint64
RecordTheftparameter gull_idint64uint64
RecordTheftparameter stolen_onstringtime.Time
FamilyTreecolumn generationint64uint8
TheftLeaguecolumn beach_rankint64uint64
TheftLeaguecolumn beach_thefts*int64uint64
BeachRecordscolumn gullsint64uint64
BeachRecordscolumn theftsint64uint64
BeachRecordscolumn last_theft*string*time.Time