Compare · 017

Range over rows: MySQL and CockroachDB

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

Compare MySQL withSQLitePostgreSQLCockroachDBTimescaleDBMariaDBDuckDB

Configuration

sgl.yamldiffers
MySQL
CockroachDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: tides
4 - name: tides
5 dialect: mysql
5 dialect: postgres
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
12 migrate:
13 dsn: ${env:DATABASE_URL}

Migrations

db/migrations/0001_readings.up.sqldiffers
MySQL
CockroachDB
1CREATE TABLE readings (
1CREATE TABLE readings (
2 id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
2 id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
3 taken_at CHAR(5) NOT NULL,
3 taken_at TEXT NOT NULL,
4 level_cm INT NOT NULL
4 level_cm BIGINT NOT NULL
5);
5);
db/migrations/0001_readings.down.sqlidentical
MySQL
CockroachDB
1DROP TABLE readings;
1DROP TABLE readings;

Queries

RecordReadingdiffers
MySQL · db/queries/readings.sql
CockroachDB · db/queries/readings.sql
1-- sgl.query(RecordReading, exec)
1-- sgl.query(RecordReading, exec)
2INSERT INTO readings (taken_at, level_cm) VALUES (?, ?);
2INSERT INTO readings (taken_at, level_cm) VALUES ($1, $2);
StreamReadingsidentical
MySQL · db/queries/readings.sql
CockroachDB · db/queries/readings.sql
1-- sgl.query(StreamReadings, stream)
1-- sgl.query(StreamReadings, stream)
2SELECT taken_at, level_cm FROM readings ORDER BY taken_at;
2SELECT taken_at, level_cm FROM readings ORDER BY taken_at;

What sgl generates

RecordReadingdiffers
MySQL
CockroachDB
1func (queries *Queries) RecordReading(ctx context.Context, params RecordReadingParams) error
1func (queries *Queries) RecordReading(ctx context.Context, params RecordReadingParams) error
2
2
3type RecordReadingParams struct {
3type RecordReadingParams struct {
4 TakenAt string
4 TakenAt string
5 LevelCm int32
5 LevelCm int64
6}
6}
StreamReadingsdiffers
MySQL
CockroachDB
1func (queries *Queries) StreamReadings(ctx context.Context) iter.Seq2[StreamReadingsRow, error]
1func (queries *Queries) StreamReadings(ctx context.Context) iter.Seq2[StreamReadingsRow, error]
2
2
3type StreamReadingsRow struct {
3type StreamReadingsRow struct {
4 TakenAt string `json:"taken_at"`
4 TakenAt string `json:"taken_at"`
5 LevelCm int32 `json:"level_cm"`
5 LevelCm int64 `json:"level_cm"`
6}
6}

The program

example.godiffers
MySQL
CockroachDB
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
8
9 "seagull.sh/seagull/examples/mysql/017_streaming/internal/store"
9 "seagull.sh/seagull/examples/postgres/017_streaming/internal/store"
10)
10)
11
11
12// floodWarningCm is the tide level, in centimetres, that raises a flood warning.
12// floodWarningCm is the tide level, in centimetres, that raises a flood warning.
13const floodWarningCm = 450
13const floodWarningCm = 450
14
14
15// run records a morning of tide gauge readings and reads them back twice with
15// run records a morning of tide gauge readings and reads them back twice with
16// StreamReadings. The stream command returns a range-over-func iterator instead of a
16// StreamReadings. The stream command returns a range-over-func iterator instead of a
17// slice, so each row is scanned only when the loop asks for it and a large result is
17// slice, so each row is scanned only when the loop asks for it and a large result is
18// never held in memory at once. Every step yields a row and an error; return on the
18// never held in memory at once. Every step yields a row and an error; return on the
19// error, as the loop goes on otherwise.
19// error, as the loop goes on otherwise.
20//
20//
21// Breaking out of the loop stops the query: the iterator closes its rows, which hands the
21// Breaking out of the loop stops the query: the iterator closes its rows, which hands the
22// connection back to the pool.
22// connection back to the pool.
23//
23//
24// Takes database (*sql.DB) which is a migrated database.
24// Takes database (*sql.DB) which is a migrated database.
25// Takes output (io.Writer) which receives the summary and the warning.
25// Takes output (io.Writer) which receives the summary and the warning.
26//
26//
27// Returns error when a query fails.
27// Returns error when a query fails.
28func run(ctx context.Context, database *sql.DB, output io.Writer) error {
28func run(ctx context.Context, database *sql.DB, output io.Writer) error {
29 queries := store.New(database)
29 queries := store.New(database)
30
30
31 readings := []store.RecordReadingParams{
31 readings := []store.RecordReadingParams{
32 {TakenAt: "06:00", LevelCm: 210},
32 {TakenAt: "06:00", LevelCm: 210},
33 {TakenAt: "07:00", LevelCm: 280},
33 {TakenAt: "07:00", LevelCm: 280},
34 {TakenAt: "08:00", LevelCm: 350},
34 {TakenAt: "08:00", LevelCm: 350},
35 {TakenAt: "09:00", LevelCm: 420},
35 {TakenAt: "09:00", LevelCm: 420},
36 {TakenAt: "10:00", LevelCm: 470},
36 {TakenAt: "10:00", LevelCm: 470},
37 {TakenAt: "11:00", LevelCm: 495},
37 {TakenAt: "11:00", LevelCm: 495},
38 {TakenAt: "12:00", LevelCm: 460},
38 {TakenAt: "12:00", LevelCm: 460},
39 {TakenAt: "13:00", LevelCm: 390},
39 {TakenAt: "13:00", LevelCm: 390},
40 }
40 }
41 for _, reading := range readings {
41 for _, reading := range readings {
42 if err := queries.RecordReading(ctx, reading); err != nil {
42 if err := queries.RecordReading(ctx, reading); err != nil {
43 return fmt.Errorf("recording the %s reading: %w", reading.TakenAt, err)
43 return fmt.Errorf("recording the %s reading: %w", reading.TakenAt, err)
44 }
44 }
45 }
45 }
46
46
47 count := 0
47 count := 0
48 var highest store.StreamReadingsRow
48 var highest store.StreamReadingsRow
49 for reading, err := range queries.StreamReadings(ctx) {
49 for reading, err := range queries.StreamReadings(ctx) {
50 if err != nil {
50 if err != nil {
51 return fmt.Errorf("streaming readings: %w", err)
51 return fmt.Errorf("streaming readings: %w", err)
52 }
52 }
53 count++
53 count++
54 if reading.LevelCm > highest.LevelCm {
54 if reading.LevelCm > highest.LevelCm {
55 highest = reading
55 highest = reading
56 }
56 }
57 }
57 }
58 fmt.Fprintf(output, "%d readings, high water %dcm at %s\n", count, highest.LevelCm, highest.TakenAt)
58 fmt.Fprintf(output, "%d readings, high water %dcm at %s\n", count, highest.LevelCm, highest.TakenAt)
59
59
60 read := 0
60 read := 0
61 for reading, err := range queries.StreamReadings(ctx) {
61 for reading, err := range queries.StreamReadings(ctx) {
62 if err != nil {
62 if err != nil {
63 return fmt.Errorf("streaming readings: %w", err)
63 return fmt.Errorf("streaming readings: %w", err)
64 }
64 }
65 read++
65 read++
66 if reading.LevelCm >= floodWarningCm {
66 if reading.LevelCm >= floodWarningCm {
67 fmt.Fprintf(output, "flood warning at %s, %dcm, after reading %d of %d rows\n", reading.TakenAt, reading.LevelCm, read, count)
67 fmt.Fprintf(output, "flood warning at %s, %dcm, after reading %d of %d rows\n", reading.TakenAt, reading.LevelCm, read, count)
68 break
68 break
69 }
69 }
70 }
70 }
71 return nil
71 return nil
72}
72}

Go type differences

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

QueryFieldMySQLCockroachDB
RecordReadingparameter level_cmint32int64
StreamReadingscolumn level_cmint32int64