Compare · 025

Prepared statements: PostgreSQL and SQLite

Every file of the PostgreSQL and SQLite versions side by side, with the lines that differ marked.

Compare PostgreSQL withSQLiteCockroachDBTimescaleDB

Configuration

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

Migrations

db/migrations/0001_tide_times.up.sqldiffers
PostgreSQL
SQLite
1CREATE TABLE tide_times (
1CREATE TABLE tide_times (
2 id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
2 id INTEGER PRIMARY KEY,
3 port TEXT NOT NULL,
3 port TEXT NOT NULL,
4 day BIGINT NOT NULL,
4 day INTEGER 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
PostgreSQL
SQLite
1DROP TABLE tide_times;
1DROP TABLE tide_times;

Queries

AddTideTimediffers
PostgreSQL · db/queries/tide_times.sql
SQLite · 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 (?, ?, ?);
HighTidediffers
PostgreSQL · db/queries/tide_times.sql
SQLite · 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 = ? AND day = ?;

What sgl generates

AddTideTimeidentical
PostgreSQL
SQLite
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
PostgreSQL
SQLite
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.godiffers
PostgreSQL
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
8
9 "seagull.sh/seagull/examples/postgres/025_prepared_statements/internal/store"
9 "seagull.sh/seagull/examples/sqlite/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.
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.
46 prepared, err := store.Prepare(ctx, database)
44 prepared, err := store.Prepare(ctx, database)
47 if err != nil {
45 if err != nil {
48 return fmt.Errorf("preparing queries: %w", err)
46 return fmt.Errorf("preparing queries: %w", err)
49 }
47 }
50 defer func() { _ = prepared.Close() }()
48 defer func() { _ = prepared.Close() }()
51
49
52 // *store.PreparedDBTX is a DBTX: every method of these Queries runs its prepared
50 // *store.PreparedDBTX is a DBTX: every method of these Queries runs its prepared
53 // statement instead of sending the SQL text again.
51 // statement instead of sending the SQL text again.
54 queries := store.New(prepared)
52 queries := store.New(prepared)
55
53
56 // RunInTx needs the *sql.DB itself, which UnderlyingDB returns. PreparedDBTX also has a
54 // 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
55 // 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.
56 // prepared statements, each bound to the *sql.Tx with StmtContext.
59 err = queries.RunInTx(ctx, prepared.UnderlyingDB(), func(inTransaction *store.Queries) error {
57 err = queries.RunInTx(ctx, prepared.UnderlyingDB(), func(inTransaction *store.Queries) error {
60 return loadTideTimes(ctx, inTransaction)
58 return loadTideTimes(ctx, inTransaction)
61 })
59 })
62 if err != nil {
60 if err != nil {
63 return fmt.Errorf("loading tide times: %w", err)
61 return fmt.Errorf("loading tide times: %w", err)
64 }
62 }
65
63
66 fmt.Fprint(output, " ")
64 fmt.Fprint(output, " ")
67 for day := 1; day <= days; day++ {
65 for day := 1; day <= days; day++ {
68 fmt.Fprintf(output, " day %d", day)
66 fmt.Fprintf(output, " day %d", day)
69 }
67 }
70 fmt.Fprintln(output)
68 fmt.Fprintln(output)
71
69
72 // The same HighTide statement runs once per port and day.
70 // The same HighTide statement runs once per port and day.
73 for _, port := range ports {
71 for _, port := range ports {
74 fmt.Fprintf(output, "%-9s", port.name)
72 fmt.Fprintf(output, "%-9s", port.name)
75 for day := int64(1); day <= days; day++ {
73 for day := int64(1); day <= days; day++ {
76 tide, err := queries.HighTide(ctx, store.HighTideParams{Port: port.name, Day: day})
74 tide, err := queries.HighTide(ctx, store.HighTideParams{Port: port.name, Day: day})
77 if err != nil {
75 if err != nil {
78 return fmt.Errorf("looking up %s on day %d: %w", port.name, day, err)
76 return fmt.Errorf("looking up %s on day %d: %w", port.name, day, err)
79 }
77 }
80 fmt.Fprintf(output, " %s", tide.HighAt)
78 fmt.Fprintf(output, " %s", tide.HighAt)
81 }
79 }
82 fmt.Fprintln(output)
80 fmt.Fprintln(output)
83 }
81 }
84 return nil
82 return nil
85}
83}
86
84
87// loadTideTimes adds a high tide for every port on every day, each a little later than
85// loadTideTimes adds a high tide for every port on every day, each a little later than
88// the day before.
86// the day before.
89//
87//
90// Takes queries (*store.Queries) which add the rows.
88// Takes queries (*store.Queries) which add the rows.
91//
89//
92// Returns error when a row cannot be added.
90// Returns error when a row cannot be added.
93func loadTideTimes(ctx context.Context, queries *store.Queries) error {
91func loadTideTimes(ctx context.Context, queries *store.Queries) error {
94 for _, port := range ports {
92 for _, port := range ports {
95 for day := range days {
93 for day := range days {
96 minute := (port.first + day*tideDrift) % minutesPerDay
94 minute := (port.first + day*tideDrift) % minutesPerDay
97 err := queries.AddTideTime(ctx, store.AddTideTimeParams{
95 err := queries.AddTideTime(ctx, store.AddTideTimeParams{
98 Port: port.name,
96 Port: port.name,
99 Day: int64(day + 1),
97 Day: int64(day + 1),
100 HighAt: fmt.Sprintf("%02d:%02d", minute/60, minute%60),
98 HighAt: fmt.Sprintf("%02d:%02d", minute/60, minute%60),
101 })
99 })
102 if err != nil {
100 if err != nil {
103 return err
101 return err
104 }
102 }
105 }
103 }
106 }
104 }
107 return nil
105 return nil
108}
106}