Compare · 002

Choosing a command: one, many, exec, execrows, execresult: TimescaleDB and CockroachDB

TimescaleDB and CockroachDB run the same files for this example: the SQL and the Go are identical.

Compare TimescaleDB withSQLitePostgreSQLCockroachDBMySQLMariaDB

Configuration

sgl.yamlidentical
TimescaleDB
CockroachDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: harbour
4 - name: harbour
5 dialect: postgres
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
11 driver: database/sql

Migrations

db/migrations/0001_boats.up.sqlidentical
TimescaleDB
CockroachDB
1CREATE TABLE boats (
1CREATE TABLE boats (
2 id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
2 id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
3 name TEXT NOT NULL,
3 name TEXT NOT NULL,
4 berth BIGINT NOT NULL,
4 berth BIGINT NOT NULL,
5 at_sea BOOLEAN NOT NULL DEFAULT false
5 at_sea BOOLEAN NOT NULL DEFAULT false
6);
6);
db/migrations/0001_boats.down.sqlidentical
TimescaleDB
CockroachDB
1DROP TABLE boats;
1DROP TABLE boats;

Queries

MoorBoatidentical
TimescaleDB · db/queries/boats.sql
CockroachDB · db/queries/boats.sql
1-- sgl.query(MoorBoat, one)
1-- sgl.query(MoorBoat, one)
2INSERT INTO boats (name, berth) VALUES ($1, $2) RETURNING id;
2INSERT INTO boats (name, berth) VALUES ($1, $2) RETURNING id;
MoveBerthidentical
TimescaleDB · db/queries/boats.sql
CockroachDB · db/queries/boats.sql
1-- sgl.query(MoveBerth, execresult)
1-- sgl.query(MoveBerth, execresult)
2UPDATE boats SET berth = $1 WHERE id = $2;
2UPDATE boats SET berth = $1 WHERE id = $2;
GetBoatidentical
TimescaleDB · db/queries/boats.sql
CockroachDB · db/queries/boats.sql
1-- sgl.query(GetBoat, one)
1-- sgl.query(GetBoat, one)
2SELECT id, name, berth, at_sea FROM boats WHERE id = $1;
2SELECT id, name, berth, at_sea FROM boats WHERE id = $1;
ListBoatsidentical
TimescaleDB · db/queries/boats.sql
CockroachDB · db/queries/boats.sql
1-- sgl.query(ListBoats, many)
1-- sgl.query(ListBoats, many)
2SELECT id, name, berth, at_sea FROM boats ORDER BY berth;
2SELECT id, name, berth, at_sea FROM boats ORDER BY berth;
PutToSeaidentical
TimescaleDB · db/queries/boats.sql
CockroachDB · db/queries/boats.sql
1-- sgl.query(PutToSea, execrows)
1-- sgl.query(PutToSea, execrows)
2UPDATE boats SET at_sea = true WHERE NOT at_sea;
2UPDATE boats SET at_sea = true WHERE NOT at_sea;
ScrapBoatidentical
TimescaleDB · db/queries/boats.sql
CockroachDB · db/queries/boats.sql
1-- sgl.query(ScrapBoat, exec)
1-- sgl.query(ScrapBoat, exec)
2DELETE FROM boats WHERE id = $1;
2DELETE FROM boats WHERE id = $1;

What sgl generates

MoorBoatidentical
TimescaleDB
CockroachDB
1func (queries *Queries) MoorBoat(ctx context.Context, params MoorBoatParams) (MoorBoatRow, error)
1func (queries *Queries) MoorBoat(ctx context.Context, params MoorBoatParams) (MoorBoatRow, error)
2
2
3type MoorBoatParams struct {
3type MoorBoatParams struct {
4 Name string
4 Name string
5 Berth int64
5 Berth int64
6}
6}
7
7
8type MoorBoatRow struct {
8type MoorBoatRow struct {
9 ID int64 `json:"id"`
9 ID int64 `json:"id"`
10}
10}
MoveBerthidentical
TimescaleDB
CockroachDB
1func (queries *Queries) MoveBerth(ctx context.Context, params MoveBerthParams) (sql.Result, error)
1func (queries *Queries) MoveBerth(ctx context.Context, params MoveBerthParams) (sql.Result, error)
2
2
3type MoveBerthParams struct {
3type MoveBerthParams struct {
4 Berth int64
4 Berth int64
5 ID int64
5 ID int64
6}
6}
GetBoatidentical
TimescaleDB
CockroachDB
1func (queries *Queries) GetBoat(ctx context.Context, id int64) (GetBoatRow, error)
1func (queries *Queries) GetBoat(ctx context.Context, id int64) (GetBoatRow, error)
2
2
3type GetBoatRow struct {
3type GetBoatRow struct {
4 ID int64 `json:"id"`
4 ID int64 `json:"id"`
5 Name string `json:"name"`
5 Name string `json:"name"`
6 Berth int64 `json:"berth"`
6 Berth int64 `json:"berth"`
7 AtSea bool `json:"at_sea"`
7 AtSea bool `json:"at_sea"`
8}
8}
ListBoatsidentical
TimescaleDB
CockroachDB
1func (queries *Queries) ListBoats(ctx context.Context) ([]ListBoatsRow, error)
1func (queries *Queries) ListBoats(ctx context.Context) ([]ListBoatsRow, error)
2
2
3type ListBoatsRow struct {
3type ListBoatsRow struct {
4 ID int64 `json:"id"`
4 ID int64 `json:"id"`
5 Name string `json:"name"`
5 Name string `json:"name"`
6 Berth int64 `json:"berth"`
6 Berth int64 `json:"berth"`
7 AtSea bool `json:"at_sea"`
7 AtSea bool `json:"at_sea"`
8}
8}
PutToSeaidentical
TimescaleDB
CockroachDB
1func (queries *Queries) PutToSea(ctx context.Context) (int64, error)
1func (queries *Queries) PutToSea(ctx context.Context) (int64, error)
ScrapBoatidentical
TimescaleDB
CockroachDB
1func (queries *Queries) ScrapBoat(ctx context.Context, id int64) error
1func (queries *Queries) ScrapBoat(ctx context.Context, id int64) error

The program

example.goidentical
TimescaleDB
CockroachDB
1package main
1package main
2
2
3import (
3import (
4 "context"
4 "context"
5 "database/sql"
5 "database/sql"
6 "errors"
6 "errors"
7 "fmt"
7 "fmt"
8 "io"
8 "io"
9
9
10 "seagull.sh/seagull/examples/postgres/002_commands/internal/store"
10 "seagull.sh/seagull/examples/postgres/002_commands/internal/store"
11)
11)
12
12
13// run moors three boats, moves one, looks one up, scraps one, puts the rest to sea and
13// run moors three boats, moves one, looks one up, scraps one, puts the rest to sea and
14// lists the harbour. Each query in db/queries/boats.sql names a command, and the command
14// lists the harbour. Each query in db/queries/boats.sql names a command, and the command
15// decides what its generated method returns:
15// decides what its generated method returns:
16//
16//
17// - one returns a single row, or sql.ErrNoRows when nothing matches; on an INSERT with
17// - one returns a single row, or sql.ErrNoRows when nothing matches; on an INSERT with
18// RETURNING it hands back the new row's ID;
18// RETURNING it hands back the new row's ID;
19// - execresult returns sql.Result. pgx's driver fills in RowsAffected, but its
19// - execresult returns sql.Result. pgx's driver fills in RowsAffected, but its
20// LastInsertId always fails, as PostgreSQL has no last insert ID: use RETURNING;
20// LastInsertId always fails, as PostgreSQL has no last insert ID: use RETURNING;
21// - many returns a slice of rows;
21// - many returns a slice of rows;
22// - execrows returns how many rows the statement changed;
22// - execrows returns how many rows the statement changed;
23// - exec returns only an error.
23// - exec returns only an error.
24//
24//
25// The command has to fit the statement: exec on a SELECT, or one on an INSERT without
25// The command has to fit the statement: exec on a SELECT, or one on an INSERT without
26// RETURNING, makes sgl vet report Q009.
26// RETURNING, makes sgl vet report Q009.
27//
27//
28// Takes database (*sql.DB) which is a migrated database.
28// Takes database (*sql.DB) which is a migrated database.
29// Takes output (io.Writer) which receives what each call returned.
29// Takes output (io.Writer) which receives what each call returned.
30//
30//
31// Returns error when a query fails.
31// Returns error when a query fails.
32func run(ctx context.Context, database *sql.DB, output io.Writer) error {
32func run(ctx context.Context, database *sql.DB, output io.Writer) error {
33 queries := store.New(database)
33 queries := store.New(database)
34
34
35 arrivals := []store.MoorBoatParams{
35 arrivals := []store.MoorBoatParams{
36 {Name: "Kittiwake", Berth: 1},
36 {Name: "Kittiwake", Berth: 1},
37 {Name: "Salty Chip", Berth: 2},
37 {Name: "Salty Chip", Berth: 2},
38 {Name: "Gull Wing", Berth: 5},
38 {Name: "Gull Wing", Berth: 5},
39 }
39 }
40 for _, arrival := range arrivals {
40 for _, arrival := range arrivals {
41 moored, err := queries.MoorBoat(ctx, arrival)
41 moored, err := queries.MoorBoat(ctx, arrival)
42 if err != nil {
42 if err != nil {
43 return fmt.Errorf("mooring %s: %w", arrival.Name, err)
43 return fmt.Errorf("mooring %s: %w", arrival.Name, err)
44 }
44 }
45 fmt.Fprintf(output, "one moored %s as boat %d\n", arrival.Name, moored.ID)
45 fmt.Fprintf(output, "one moored %s as boat %d\n", arrival.Name, moored.ID)
46 }
46 }
47
47
48 result, err := queries.MoveBerth(ctx, store.MoveBerthParams{Berth: 4, ID: 2})
48 result, err := queries.MoveBerth(ctx, store.MoveBerthParams{Berth: 4, ID: 2})
49 if err != nil {
49 if err != nil {
50 return fmt.Errorf("moving boat 2: %w", err)
50 return fmt.Errorf("moving boat 2: %w", err)
51 }
51 }
52 moved, err := result.RowsAffected()
52 moved, err := result.RowsAffected()
53 if err != nil {
53 if err != nil {
54 return fmt.Errorf("reading the rows moved: %w", err)
54 return fmt.Errorf("reading the rows moved: %w", err)
55 }
55 }
56 fmt.Fprintf(output, "execresult moved boat 2 to berth 4, %d row affected\n", moved)
56 fmt.Fprintf(output, "execresult moved boat 2 to berth 4, %d row affected\n", moved)
57 if _, err := result.LastInsertId(); err != nil {
57 if _, err := result.LastInsertId(); err != nil {
58 fmt.Fprintf(output, "execresult LastInsertId fails: %v\n", err)
58 fmt.Fprintf(output, "execresult LastInsertId fails: %v\n", err)
59 }
59 }
60
60
61 boat, err := queries.GetBoat(ctx, 2)
61 boat, err := queries.GetBoat(ctx, 2)
62 if err != nil {
62 if err != nil {
63 return fmt.Errorf("getting boat 2: %w", err)
63 return fmt.Errorf("getting boat 2: %w", err)
64 }
64 }
65 fmt.Fprintf(output, "one boat 2 is %s, at berth %d\n", boat.Name, boat.Berth)
65 fmt.Fprintf(output, "one boat 2 is %s, at berth %d\n", boat.Name, boat.Berth)
66
66
67 if err := queries.ScrapBoat(ctx, 3); err != nil {
67 if err := queries.ScrapBoat(ctx, 3); err != nil {
68 return fmt.Errorf("scrapping boat 3: %w", err)
68 return fmt.Errorf("scrapping boat 3: %w", err)
69 }
69 }
70 fmt.Fprintln(output, "exec scrapped boat 3")
70 fmt.Fprintln(output, "exec scrapped boat 3")
71
71
72 _, err = queries.GetBoat(ctx, 3)
72 _, err = queries.GetBoat(ctx, 3)
73 if err == nil {
73 if err == nil {
74 return errors.New("getting scrapped boat 3: got a boat, want sql.ErrNoRows")
74 return errors.New("getting scrapped boat 3: got a boat, want sql.ErrNoRows")
75 }
75 }
76 if !errors.Is(err, sql.ErrNoRows) {
76 if !errors.Is(err, sql.ErrNoRows) {
77 return fmt.Errorf("getting scrapped boat 3: got %w, want sql.ErrNoRows", err)
77 return fmt.Errorf("getting scrapped boat 3: got %w, want sql.ErrNoRows", err)
78 }
78 }
79 fmt.Fprintln(output, "one boat 3 is gone, so GetBoat returns sql.ErrNoRows")
79 fmt.Fprintln(output, "one boat 3 is gone, so GetBoat returns sql.ErrNoRows")
80
80
81 // PutToSea only changes boats still in harbour, so a second run changes none.
81 // PutToSea only changes boats still in harbour, so a second run changes none.
82 for range 2 {
82 for range 2 {
83 sailed, err := queries.PutToSea(ctx)
83 sailed, err := queries.PutToSea(ctx)
84 if err != nil {
84 if err != nil {
85 return fmt.Errorf("putting boats to sea: %w", err)
85 return fmt.Errorf("putting boats to sea: %w", err)
86 }
86 }
87 fmt.Fprintf(output, "execrows %d boats put to sea\n", sailed)
87 fmt.Fprintf(output, "execrows %d boats put to sea\n", sailed)
88 }
88 }
89
89
90 boats, err := queries.ListBoats(ctx)
90 boats, err := queries.ListBoats(ctx)
91 if err != nil {
91 if err != nil {
92 return fmt.Errorf("listing boats: %w", err)
92 return fmt.Errorf("listing boats: %w", err)
93 }
93 }
94 for _, boat := range boats {
94 for _, boat := range boats {
95 fmt.Fprintf(output, "many berth %d %-10s at sea: %t\n", boat.Berth, boat.Name, boat.AtSea)
95 fmt.Fprintf(output, "many berth %d %-10s at sea: %t\n", boat.Berth, boat.Name, boat.AtSea)
96 }
96 }
97 return nil
97 return nil
98}
98}