Compare · 025

Prepared statements: CockroachDB and PostgreSQL

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

Compare CockroachDB withSQLitePostgreSQLTimescaleDB

Configuration

sgl.yamlidentical
CockroachDB
PostgreSQL
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: tides
4 - name: tides
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
12 prepared: true
12 prepared: true
13 migrate:
13 migrate:
14 dsn: ${env:DATABASE_URL}
14 dsn: ${env:DATABASE_URL}

Migrations

db/migrations/0001_tide_times.up.sqlidentical
CockroachDB
PostgreSQL
1CREATE TABLE tide_times (
1CREATE TABLE tide_times (
2 id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
2 id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
3 port TEXT NOT NULL,
3 port TEXT NOT NULL,
4 day BIGINT NOT NULL,
4 day BIGINT NOT NULL,
5 high_at TEXT NOT NULL,
5 high_at TEXT NOT NULL,
6 UNIQUE (port, day)
6 UNIQUE (port, day)
7);
7);
db/migrations/0001_tide_times.down.sqlidentical
CockroachDB
PostgreSQL
1DROP TABLE tide_times;
1DROP TABLE tide_times;

Queries

AddTideTimeidentical
CockroachDB · db/queries/tide_times.sql
PostgreSQL · db/queries/tide_times.sql
1-- sgl.query(AddTideTime, exec)
1-- sgl.query(AddTideTime, exec)
2INSERT INTO tide_times (port, day, high_at) VALUES ($1, $2, $3);
2INSERT INTO tide_times (port, day, high_at) VALUES ($1, $2, $3);
HighTideidentical
CockroachDB · db/queries/tide_times.sql
PostgreSQL · db/queries/tide_times.sql
1-- sgl.query(HighTide, one)
1-- sgl.query(HighTide, one)
2SELECT high_at FROM tide_times WHERE port = $1 AND day = $2;
2SELECT high_at FROM tide_times WHERE port = $1 AND day = $2;

What sgl generates

AddTideTimeidentical
CockroachDB
PostgreSQL
1func (queries *Queries) AddTideTime(ctx context.Context, params AddTideTimeParams) error
1func (queries *Queries) AddTideTime(ctx context.Context, params AddTideTimeParams) error
2
2
3type AddTideTimeParams struct {
3type AddTideTimeParams struct {
4 Port string
4 Port string
5 Day int64
5 Day int64
6 HighAt string
6 HighAt string
7}
7}
HighTideidentical
CockroachDB
PostgreSQL
1func (queries *Queries) HighTide(ctx context.Context, params HighTideParams) (HighTideRow, error)
1func (queries *Queries) HighTide(ctx context.Context, params HighTideParams) (HighTideRow, error)
2
2
3type HighTideParams struct {
3type HighTideParams struct {
4 Port string
4 Port string
5 Day int64
5 Day int64
6}
6}
7
7
8type HighTideRow struct {
8type HighTideRow struct {
9 HighAt string `json:"high_at"`
9 HighAt string `json:"high_at"`
10}
10}

The program

example.goidentical
CockroachDB
PostgreSQL
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/postgres/025_prepared_statements/internal/store"
9 "seagull.sh/seagull/examples/postgres/025_prepared_statements/internal/store"
10)
10)
11
11
12const (
12const (
13 // days is how many days of tide times the example loads for each port.
13 // days is how many days of tide times the example loads for each port.
14 days = 3
14 days = 3
15
15
16 // minutesPerDay wraps tide times past midnight.
16 // minutesPerDay wraps tide times past midnight.
17 minutesPerDay = 24 * 60
17 minutesPerDay = 24 * 60
18
18
19 // tideDrift is roughly how much later high tide comes each day, in minutes.
19 // tideDrift is roughly how much later high tide comes each day, in minutes.
20 tideDrift = 50
20 tideDrift = 50
21)
21)
22
22
23// ports lists the harbours and the minute of their first high tide.
23// ports lists the harbours and the minute of their first high tide.
24var ports = []struct {
24var ports = []struct {
25 name string
25 name string
26 first int
26 first int
27}{
27}{
28 {name: "Padstow", first: 5*60 + 12},
28 {name: "Padstow", first: 5*60 + 12},
29 {name: "Tenby", first: 6*60 + 40},
29 {name: "Tenby", first: 6*60 + 40},
30 {name: "Whitby", first: 11*60 + 3},
30 {name: "Whitby", first: 11*60 + 3},
31}
31}
32
32
33// run prepares every query once, loads a few days of tide times in a transaction, then
33// run prepares every query once, loads a few days of tide times in a transaction, then
34// looks up each port's high tide for each day through the prepared statements.
34// looks up each port's high tide for each day through the prepared statements.
35//
35//
36// Takes database (*sql.DB) which is a migrated database.
36// Takes database (*sql.DB) which is a migrated database.
37// Takes output (io.Writer) which receives the tide table.
37// Takes output (io.Writer) which receives the tide table.
38//
38//
39// Returns error when preparing or a query fails.
39// Returns error when preparing or a query fails.
40func run(ctx context.Context, database *sql.DB, output io.Writer) error {
40func run(ctx context.Context, database *sql.DB, output io.Writer) error {
41 // With go.prepared: true, sgl generates Prepare. It prepares every static query in the
41 // With go.prepared: true, sgl generates Prepare. It prepares every static query in the
42 // package up front, so a query that no longer matches the schema fails here, at
42 // package up front, so a query that no longer matches the schema fails here, at
43 // start-up, rather than on first use. Close releases the statements. pgx's driver
43 // start-up, rather than on first use. Close releases the statements. pgx's driver
44 // already keeps a prepared statement per connection for each query it runs, so on
44 // already keeps a prepared statement per connection for each query it runs, so on
45 // PostgreSQL that check at start-up is most of the gain.
45 // PostgreSQL that check at start-up is most of the gain.
46 prepared, err := store.Prepare(ctx, database)
46 prepared, err := store.Prepare(ctx, database)
47 if err != nil {
47 if err != nil {
48 return fmt.Errorf("preparing queries: %w", err)
48 return fmt.Errorf("preparing queries: %w", err)
49 }
49 }
50 defer func() { _ = prepared.Close() }()
50 defer func() { _ = prepared.Close() }()
51
51
52 // *store.PreparedDBTX is a DBTX: every method of these Queries runs its prepared
52 // *store.PreparedDBTX is a DBTX: every method of these Queries runs its prepared
53 // statement instead of sending the SQL text again.
53 // statement instead of sending the SQL text again.
54 queries := store.New(prepared)
54 queries := store.New(prepared)
55
55
56 // RunInTx needs the *sql.DB itself, which UnderlyingDB returns. PreparedDBTX also has a
56 // RunInTx needs the *sql.DB itself, which UnderlyingDB returns. PreparedDBTX also has a
57 // WrapDBTX method, so the Queries that RunInTx binds to the transaction keep using the
57 // WrapDBTX method, so the Queries that RunInTx binds to the transaction keep using the
58 // prepared statements, each bound to the *sql.Tx with StmtContext.
58 // prepared statements, each bound to the *sql.Tx with StmtContext.
59 err = queries.RunInTx(ctx, prepared.UnderlyingDB(), func(inTransaction *store.Queries) error {
59 err = queries.RunInTx(ctx, prepared.UnderlyingDB(), func(inTransaction *store.Queries) error {
60 return loadTideTimes(ctx, inTransaction)
60 return loadTideTimes(ctx, inTransaction)
61 })
61 })
62 if err != nil {
62 if err != nil {
63 return fmt.Errorf("loading tide times: %w", err)
63 return fmt.Errorf("loading tide times: %w", err)
64 }
64 }
65
65
66 fmt.Fprint(output, " ")
66 fmt.Fprint(output, " ")
67 for day := 1; day <= days; day++ {
67 for day := 1; day <= days; day++ {
68 fmt.Fprintf(output, " day %d", day)
68 fmt.Fprintf(output, " day %d", day)
69 }
69 }
70 fmt.Fprintln(output)
70 fmt.Fprintln(output)
71
71
72 // The same HighTide statement runs once per port and day.
72 // The same HighTide statement runs once per port and day.
73 for _, port := range ports {
73 for _, port := range ports {
74 fmt.Fprintf(output, "%-9s", port.name)
74 fmt.Fprintf(output, "%-9s", port.name)
75 for day := int64(1); day <= days; day++ {
75 for day := int64(1); day <= days; day++ {
76 tide, err := queries.HighTide(ctx, store.HighTideParams{Port: port.name, Day: day})
76 tide, err := queries.HighTide(ctx, store.HighTideParams{Port: port.name, Day: day})
77 if err != nil {
77 if err != nil {
78 return fmt.Errorf("looking up %s on day %d: %w", port.name, day, err)
78 return fmt.Errorf("looking up %s on day %d: %w", port.name, day, err)
79 }
79 }
80 fmt.Fprintf(output, " %s", tide.HighAt)
80 fmt.Fprintf(output, " %s", tide.HighAt)
81 }
81 }
82 fmt.Fprintln(output)
82 fmt.Fprintln(output)
83 }
83 }
84 return nil
84 return nil
85}
85}
86
86
87// loadTideTimes adds a high tide for every port on every day, each a little later than
87// loadTideTimes adds a high tide for every port on every day, each a little later than
88// the day before.
88// the day before.
89//
89//
90// Takes queries (*store.Queries) which add the rows.
90// Takes queries (*store.Queries) which add the rows.
91//
91//
92// Returns error when a row cannot be added.
92// Returns error when a row cannot be added.
93func loadTideTimes(ctx context.Context, queries *store.Queries) error {
93func loadTideTimes(ctx context.Context, queries *store.Queries) error {
94 for _, port := range ports {
94 for _, port := range ports {
95 for day := range days {
95 for day := range days {
96 minute := (port.first + day*tideDrift) % minutesPerDay
96 minute := (port.first + day*tideDrift) % minutesPerDay
97 err := queries.AddTideTime(ctx, store.AddTideTimeParams{
97 err := queries.AddTideTime(ctx, store.AddTideTimeParams{
98 Port: port.name,
98 Port: port.name,
99 Day: int64(day + 1),
99 Day: int64(day + 1),
100 HighAt: fmt.Sprintf("%02d:%02d", minute/60, minute%60),
100 HighAt: fmt.Sprintf("%02d:%02d", minute/60, minute%60),
101 })
101 })
102 if err != nil {
102 if err != nil {
103 return err
103 return err
104 }
104 }
105 }
105 }
106 }
106 }
107 return nil
107 return nil
108}
108}