Compare · 024

Read replicas: 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: sightings
4 - name: sightings
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
12 migrate:
13 dsn: ${env:DATABASE_URL}

Migrations

db/migrations/0001_sightings.up.sqldiffers
SQLite
PostgreSQL
1CREATE TABLE sightings (
1CREATE TABLE sightings (
2 id INTEGER PRIMARY KEY,
2 id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
3 species TEXT NOT NULL,
3 species TEXT NOT NULL,
4 place TEXT NOT NULL
4 place TEXT NOT NULL
5);
5);
6
7CREATE FUNCTION sighting_label(species TEXT, place TEXT) RETURNS TEXT
8LANGUAGE sql IMMUTABLE STRICT
9AS $$ SELECT species || ' at ' || place $$;
10
11CREATE FUNCTION log_sighting(new_species TEXT, new_place TEXT) RETURNS BIGINT
12LANGUAGE plpgsql VOLATILE
13AS $$
14DECLARE
15 new_id BIGINT;
16BEGIN
17 INSERT INTO sightings (species, place) VALUES (new_species, new_place) RETURNING id INTO new_id;
18 RETURN new_id;
19END;
20$$;
db/migrations/0001_sightings.down.sqldiffers
SQLite
PostgreSQL
1DROP FUNCTION log_sighting(TEXT, TEXT);
2DROP FUNCTION sighting_label(TEXT, TEXT);
1DROP TABLE sightings;
3DROP TABLE sightings;

Queries

RecordSightingdiffers
SQLite · db/queries/sightings.sql
PostgreSQL · db/queries/sightings.sql
1-- sgl.query(RecordSighting, one)
1-- sgl.query(RecordSighting, one)
2INSERT INTO sightings (species, place) VALUES (?, ?) RETURNING id;
2INSERT INTO sightings (species, place) VALUES ($1, $2) RETURNING id;
ListSightingsdiffers
SQLite · db/queries/sightings.sql
PostgreSQL · db/queries/sightings.sql
1-- sgl.query(ListSightings, many)
1-- sgl.query(ListSightings, many)
2SELECT id, species, place FROM sightings ORDER BY id;
2SELECT id, sighting_label(species, place) AS label FROM sightings ORDER BY id;
CountSightingsidentical
SQLite · db/queries/sightings.sql
PostgreSQL · db/queries/sightings.sql
1-- sgl.query(CountSightings, one, readonly: false)
1-- sgl.query(CountSightings, one, readonly: false)
2SELECT COUNT(*) AS total FROM sightings;
2SELECT COUNT(*) AS total FROM sightings;
LogSightingonly PostgreSQL
SQLite · not in this version
PostgreSQL · db/queries/sightings.sql
1-- sgl.query(LogSighting, one)
2SELECT log_sighting(:species, :place) AS id;
ClaimSightingonly PostgreSQL
SQLite · not in this version
PostgreSQL · db/queries/sightings.sql
1-- sgl.query(ClaimSighting, one)
2SELECT id, species FROM sightings WHERE id = $1 FOR UPDATE;
ForgetPlaceonly PostgreSQL
SQLite · not in this version
PostgreSQL · db/queries/sightings.sql
1-- sgl.query(ForgetPlace, many)
2WITH forgotten AS (DELETE FROM sightings WHERE place = $1 RETURNING id) SELECT id FROM forgotten ORDER BY id;

What sgl generates

RecordSightingidentical
SQLite
PostgreSQL
1func (queries *Queries) RecordSighting(ctx context.Context, params RecordSightingParams) (RecordSightingRow, error)
1func (queries *Queries) RecordSighting(ctx context.Context, params RecordSightingParams) (RecordSightingRow, error)
2
2
3type RecordSightingParams struct {
3type RecordSightingParams struct {
4 Species string
4 Species string
5 Place string
5 Place string
6}
6}
7
7
8type RecordSightingRow struct {
8type RecordSightingRow struct {
9 ID int64 `json:"id"`
9 ID int64 `json:"id"`
10}
10}
ListSightingsdiffers
SQLite
PostgreSQL
1func (queries *Queries) ListSightings(ctx context.Context) ([]ListSightingsRow, error)
1func (queries *Queries) ListSightings(ctx context.Context) ([]ListSightingsRow, error)
2
2
3type ListSightingsRow struct {
3type ListSightingsRow struct {
4 ID int64 `json:"id"`
4 ID int64 `json:"id"`
5 Species string `json:"species"`
5 Label string `json:"label"`
6 Place string `json:"place"`
7}
6}
CountSightingsidentical
SQLite
PostgreSQL
1func (queries *Queries) CountSightings(ctx context.Context) (CountSightingsRow, error)
1func (queries *Queries) CountSightings(ctx context.Context) (CountSightingsRow, error)
2
2
3type CountSightingsRow struct {
3type CountSightingsRow struct {
4 Total int64 `json:"total"`
4 Total int64 `json:"total"`
5}
5}
LogSightingonly PostgreSQL
SQLite · not in this version
PostgreSQL
1func (queries *Queries) LogSighting(ctx context.Context, params LogSightingParams) (LogSightingRow, error)
2
3type LogSightingParams struct {
4 Species string
5 Place string
6}
7
8type LogSightingRow struct {
9 ID *int64 `json:"id"`
10}
ClaimSightingonly PostgreSQL
SQLite · not in this version
PostgreSQL
1func (queries *Queries) ClaimSighting(ctx context.Context, id int64) (ClaimSightingRow, error)
2
3type ClaimSightingRow struct {
4 ID int64 `json:"id"`
5 Species string `json:"species"`
6}
ForgetPlaceonly PostgreSQL
SQLite · not in this version
PostgreSQL
1func (queries *Queries) ForgetPlace(ctx context.Context, place string) ([]ForgetPlaceRow, error)
2
3type ForgetPlaceRow struct {
4 ID int64 `json:"id"`
5}

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 "errors"
6 "errors"
7 "fmt"
7 "fmt"
8 "io"
8 "io"
9
9
10 "seagull.sh/seagull/examples/sqlite/024_read_replicas/internal/store"
10 "seagull.sh/seagull/examples/postgres/024_read_replicas/internal/store"
11)
11)
12
12
13// connection is a store.DBTX that prints its name and each statement before running it on
13// connection is a store.DBTX that prints its name and each statement before running it on
14// the embedded database, so the output shows which connection served every query.
14// the embedded database, so the output shows which connection served every query.
15type connection struct {
15type connection struct {
16 *sql.DB
16 *sql.DB
17
17
18 // output receives a line per statement.
18 // output receives a line per statement.
19 output io.Writer
19 output io.Writer
20
20
21 // name labels the connection in the output.
21 // name labels the connection in the output.
22 name string
22 name string
23}
23}
24
24
25// ExecContext prints the statement, then runs it on the database.
25// ExecContext prints the statement, then runs it on the database.
26//
26//
27// Takes query (string) which is the SQL to run.
27// Takes query (string) which is the SQL to run.
28// Takes arguments (...any) which are its bind values.
28// Takes arguments (...any) which are its bind values.
29//
29//
30// Returns sql.Result which is the database's result.
30// Returns sql.Result which is the database's result.
31// Returns error when the statement fails.
31// Returns error when the statement fails.
32func (c connection) ExecContext(ctx context.Context, query string, arguments ...any) (sql.Result, error) {
32func (c connection) ExecContext(ctx context.Context, query string, arguments ...any) (sql.Result, error) {
33 fmt.Fprintf(c.output, "%s %s\n", c.name, query)
33 fmt.Fprintf(c.output, "%s %s\n", c.name, query)
34 return c.DB.ExecContext(ctx, query, arguments...)
34 return c.DB.ExecContext(ctx, query, arguments...)
35}
35}
36
36
37// QueryContext prints the query, then runs it on the database.
37// QueryContext prints the query, then runs it on the database.
38//
38//
39// Takes query (string) which is the SQL to run.
39// Takes query (string) which is the SQL to run.
40// Takes arguments (...any) which are its bind values.
40// Takes arguments (...any) which are its bind values.
41//
41//
42// Returns *sql.Rows which holds the result rows.
42// Returns *sql.Rows which holds the result rows.
43// Returns error when the query fails.
43// Returns error when the query fails.
44func (c connection) QueryContext(ctx context.Context, query string, arguments ...any) (*sql.Rows, error) {
44func (c connection) QueryContext(ctx context.Context, query string, arguments ...any) (*sql.Rows, error) {
45 fmt.Fprintf(c.output, "%s %s\n", c.name, query)
45 fmt.Fprintf(c.output, "%s %s\n", c.name, query)
46 return c.DB.QueryContext(ctx, query, arguments...)
46 return c.DB.QueryContext(ctx, query, arguments...)
47}
47}
48
48
49// QueryRowContext prints the query, then runs it on the database.
49// QueryRowContext prints the query, then runs it on the database.
50//
50//
51// Takes query (string) which is the SQL to run.
51// Takes query (string) which is the SQL to run.
52// Takes arguments (...any) which are its bind values.
52// Takes arguments (...any) which are its bind values.
53//
53//
54// Returns *sql.Row which holds the result row or the error.
54// Returns *sql.Row which holds the result row or the error.
55func (c connection) QueryRowContext(ctx context.Context, query string, arguments ...any) *sql.Row {
55func (c connection) QueryRowContext(ctx context.Context, query string, arguments ...any) *sql.Row {
56 fmt.Fprintf(c.output, "%s %s\n", c.name, query)
56 fmt.Fprintf(c.output, "%s %s\n", c.name, query)
57 return c.DB.QueryRowContext(ctx, query, arguments...)
57 return c.DB.QueryRowContext(ctx, query, arguments...)
58}
58}
59
59
60// run records two sightings and reads them back through Queries built with
60// sqlStateError is a server error that carries a SQLSTATE code, as pgx's *pgconn.PgError
61// NewWithReplica, printing which connection each statement went to. It then shows that
61// does.
62// the reader refuses writes, which is why the routing matters.
62type sqlStateError interface {
63 error
64
65 // SQLState returns the five-character SQLSTATE code.
66 SQLState() string
67}
68
69// run records sightings and reads them back through Queries built with NewWithReplica,
70// printing which connection each statement went to. It then sends the queries that only
71// look like reads straight to the reader, to show why Seagull routes them to the writer.
63//
72//
64// Takes writer (*sql.DB) which is the migrated primary database.
73// Takes writer (*sql.DB) which is the migrated primary database.
65// Takes reader (*sql.DB) which is a read-only connection to the same data.
74// Takes reader (*sql.DB) which is a read-only connection to the same data.
66// Takes output (io.Writer) which receives the routing and the results.
75// Takes output (io.Writer) which receives the routing and the results.
67//
76//
68// Returns error when a query fails other than as the example expects.
77// Returns error when a query fails other than as the example expects.
69func run(ctx context.Context, writer, reader *sql.DB, output io.Writer) error {
78func run(ctx context.Context, writer, reader *sql.DB, output io.Writer) error {
70 // Seagull marks each query read-only or not when it analyses it, and each generated
79 // Seagull decides whether each query is read-only when it analyses it, and each
71 // method uses the writer or the reader to match: INSERT, UPDATE and DELETE go to the
80 // generated method uses the writer or the reader to match. On PostgreSQL a SELECT goes
72 // writer, SELECT to the reader.
81 // to the writer if it locks rows (FOR UPDATE), modifies data in a CTE, or calls a
82 // function whose data access is writing or unknown.
73 queries := store.NewWithReplica(
83 queries := store.NewWithReplica(
74 connection{DB: writer, output: output, name: "writer"},
84 connection{DB: writer, output: output, name: "writer"},
75 connection{DB: reader, output: output, name: "reader"},
85 connection{DB: reader, output: output, name: "reader"},
76 )
86 )
77
87
78 sightings := []store.RecordSightingParams{
88 herring, err := queries.RecordSighting(ctx, store.RecordSightingParams{Species: "herring gull", Place: "the pier"})
79 {Species: "herring gull", Place: "the pier"},
89 if err != nil {
80 {Species: "kittiwake", Place: "the harbour wall"},
90 return fmt.Errorf("recording a herring gull: %w", err)
81 }
91 }
82 for _, sighting := range sightings {
92 if _, err := queries.RecordSighting(ctx, store.RecordSightingParams{Species: "kittiwake", Place: "the harbour wall"}); err != nil {
83 if _, err := queries.RecordSighting(ctx, sighting); err != nil {
93 return fmt.Errorf("recording a kittiwake: %w", err)
84 return fmt.Errorf("recording a %s: %w", sighting.Species, err)
85 }
86 }
94 }
87
95
96 // LogSighting is a SELECT, but log_sighting inserts a row, so it goes to the writer. A
97 // plpgsql function may return NULL, so ID is a pointer.
98 logged, err := queries.LogSighting(ctx, store.LogSightingParams{Species: "puffin", Place: "the cliffs"})
99 if err != nil {
100 return fmt.Errorf("logging a puffin: %w", err)
101 }
102 if logged.ID == nil {
103 return errors.New("log_sighting returned NULL")
104 }
105 fmt.Fprintf(output, " logged sighting %d\n", *logged.ID)
106
107 // ListSightings calls sighting_label, whose body only concatenates its arguments, so it
108 // stays read-only and goes to the reader. With these non-NULL species and place columns,
109 // the concatenation produces a non-NULL Label string.
88 listed, err := queries.ListSightings(ctx)
110 listed, err := queries.ListSightings(ctx)
89 if err != nil {
111 if err != nil {
90 return fmt.Errorf("listing sightings: %w", err)
112 return fmt.Errorf("listing sightings: %w", err)
91 }
113 }
92 for _, sighting := range listed {
114 for _, sighting := range listed {
93 fmt.Fprintf(output, " %d %s at %s\n", sighting.ID, sighting.Species, sighting.Place)
115 fmt.Fprintf(output, " %d %s\n", sighting.ID, sighting.Label)
94 }
116 }
95
117
96 // CountSightings is a SELECT, but its header says readonly: false, so it reads from the
118 // CountSightings is a SELECT, but its header says readonly: false, so it reads from the
97 // writer. A real replica lags behind the primary; a read that must see a write made a
119 // writer. A real replica lags behind the primary; a read that must see a write made a
98 // moment ago belongs on the writer.
120 // moment ago belongs on the writer.
99 count, err := queries.CountSightings(ctx)
121 count, err := queries.CountSightings(ctx)
100 if err != nil {
122 if err != nil {
101 return fmt.Errorf("counting sightings: %w", err)
123 return fmt.Errorf("counting sightings: %w", err)
102 }
124 }
103 fmt.Fprintf(output, " %d sightings on the primary\n", count.Total)
125 fmt.Fprintf(output, " %d sightings on the primary\n", count.Total)
104
126
105 // The reader was opened with mode=ro, so a write sent to it fails.
127 // FOR UPDATE locks the row, which only the primary can do.
106 _, err = store.New(reader).RecordSighting(ctx, store.RecordSightingParams{Species: "puffin", Place: "the cliffs"})
128 claimed, err := queries.ClaimSighting(ctx, herring.ID)
107 if err == nil {
129 if err != nil {
108 return errors.New("the read-only connection accepted a write")
130 return fmt.Errorf("claiming sighting %d: %w", herring.ID, err)
109 }
131 }
110 fmt.Fprintf(output, "a write on the reader fails: %v\n", err)
132 fmt.Fprintf(output, " locked sighting %d, a %s\n", claimed.ID, claimed.Species)
133
134 // The DELETE inside the CTE makes the whole statement a write.
135 forgotten, err := queries.ForgetPlace(ctx, "the cliffs")
136 if err != nil {
137 return fmt.Errorf("forgetting the cliffs: %w", err)
138 }
139 fmt.Fprintf(output, " forgot %d sighting at the cliffs\n", len(forgotten))
140
141 return tryOnReader(ctx, store.New(reader), herring.ID, output)
142}
143
144// tryOnReader sends three queries that write or lock straight to the reader, which
145// refuses each one, as a replica would, with SQLSTATE 25006, read_only_sql_transaction.
146// pgx reports a server error as a *pgconn.PgError, and its SQLState method gives the
147// code, which stays the same where the wording of the message does not.
148//
149// Takes onReader (*store.Queries) which runs everything on the reader.
150// Takes sighting (int64) which is a sighting to try to lock.
151// Takes output (io.Writer) which receives each refusal.
152//
153// Returns error when the reader does not refuse one of them.
154func tryOnReader(ctx context.Context, onReader *store.Queries, sighting int64, output io.Writer) error {
155 attempts := []struct {
156 name string
157 call func() error
158 }{
159 {name: "RecordSighting", call: func() error {
160 _, err := onReader.RecordSighting(ctx, store.RecordSightingParams{Species: "gannet", Place: "the bay"})
161 return err
162 }},
163 {name: "LogSighting", call: func() error {
164 _, err := onReader.LogSighting(ctx, store.LogSightingParams{Species: "gannet", Place: "the bay"})
165 return err
166 }},
167 {name: "ClaimSighting", call: func() error {
168 _, err := onReader.ClaimSighting(ctx, sighting)
169 return err
170 }},
171 }
172 fmt.Fprintln(output, "on the reader:")
173 for _, attempt := range attempts {
174 err := attempt.call()
175 if err == nil {
176 return fmt.Errorf("%s on the reader: want a refusal from the server, got no error", attempt.name)
177 }
178 refusal, hasState := errors.AsType[sqlStateError](err)
179 if !hasState {
180 return fmt.Errorf("%s on the reader: want a refusal from the server, got %w", attempt.name, err)
181 }
182 fmt.Fprintf(output, " %s refused: SQLSTATE %s\n", attempt.name, refusal.SQLState())
183 }
111 return nil
184 return nil
112}
185}