Compare · 025

Prepared statements: SQLite and PostgreSQL

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

Compare SQLite withPostgreSQLCockroachDBTimescaleDB

Configuration

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

Migrations

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

Queries

AddTideTimediffers
SQLite · 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 (?, ?, ?);
2INSERT INTO tide_times (port, day, high_at) VALUES ($1, $2, $3);
HighTidediffers
SQLite · 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 = ? AND day = ?;
2SELECT high_at FROM tide_times WHERE port = $1 AND day = $2;

What sgl generates

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