Read replicas

On this page

The same on CockroachDB. CockroachDB runs this example unchanged: the SQL and the Go that sgl generates are identical to the PostgreSQL version. Only dialect: in sgl.yaml changes, and the program migrates with migrate_backend_cockroachdb.New().

Sending reads to a replica spreads the load and keeps the primary free for writes. The hard part is deciding, query by query, which is which. Get it wrong one way and a write reaches a replica that refuses it; get it wrong the other way and a read that must see a fresh write lands on a replica that has not caught up yet.

Seagull makes that decision when it analyses each query, and writes the answer into the generated method. You pass two connections to NewWithReplica, a writer and a reader, and every method uses the one its query needs. This is beyond what sqlc does: there you would build two sets of queries and choose between them at every call site.

The example opens a second pool on the same database with default_transaction_read_only turned on, which refuses writes the way a hot standby does. Pass -replica with a real replica's connection string to use one instead.

The queries

On PostgreSQL a SELECT is not always a read. It writes if it locks rows with FOR UPDATE, modifies data in a common table expression, or calls a function that is not IMMUTABLE or STABLE. Seagull checks all three, which is why the migration's functions declare their volatility:

db/migrations/0001_sightings.up.sql

CREATE TABLE sightings (
    id      BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    species TEXT   NOT NULL,
    place   TEXT   NOT NULL
);

CREATE FUNCTION sighting_label(species TEXT, place TEXT) RETURNS TEXT
LANGUAGE sql IMMUTABLE STRICT
AS $$ SELECT species || ' at ' || place $$;

CREATE FUNCTION log_sighting(new_species TEXT, new_place TEXT) RETURNS BIGINT
LANGUAGE plpgsql VOLATILE
AS $$
DECLARE
    new_id BIGINT;
BEGIN
    INSERT INTO sightings (species, place) VALUES (new_species, new_place) RETURNING id INTO new_id;
    RETURN new_id;
END;
$$;

sighting_label is IMMUTABLE, so ListSightings, which calls it, stays on the reader. It is also STRICT, which means a NULL comes out only when a NULL goes in, so label is a string rather than a *string.

db/queries/sightings.sql

-- sgl.query(ListSightings, many)
SELECT id, sighting_label(species, place) AS label FROM sightings ORDER BY id;

log_sighting is VOLATILE and inserts a row, so LogSighting goes to the writer, even though it is a SELECT. Function arguments have no column to take a name from, so the query names its parameters :species and :place.

db/queries/sightings.sql

-- sgl.query(LogSighting, one)
SELECT log_sighting(:species, :place) AS id;

ClaimSighting locks a row with FOR UPDATE, and ForgetPlace runs a DELETE inside its WITH. Both are writes, and both go to the writer:

db/queries/sightings.sql

-- sgl.query(ClaimSighting, one)
SELECT id, species FROM sightings WHERE id = $1 FOR UPDATE;

db/queries/sightings.sql

-- sgl.query(ForgetPlace, many)
WITH forgotten AS (DELETE FROM sightings WHERE place = $1 RETURNING id) SELECT id FROM forgotten ORDER BY id;

Reads that must see the latest write

A real replica lags behind its primary, by milliseconds or by minutes. A read that must see a write made a moment ago, such as a count shown straight after adding a row, belongs on the writer. readonly: false in the header overrides Seagull's detection and keeps CountSightings there; readonly: true overrides it the other way.

db/queries/sightings.sql

-- sgl.query(CountSightings, one, readonly: false)
SELECT COUNT(*) AS total FROM sightings;

What sgl generates

The Queries struct holds a reader and a writer, and the only difference between the methods is which one they call. New sets both to the same connection, so code that never uses a replica pays nothing for this. WithTx sets both to the transaction, so everything inside a transaction runs on the primary.

internal/store

type ListSightingsRow struct {
	ID    int64  `json:"id"`
	Label string `json:"label"`
}

func (queries *Queries) ListSightings(ctx context.Context) ([]ListSightingsRow, error) {
	rows, err := queries.reader.QueryContext(ctx, listSightingsSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []ListSightingsRow
	if rows.Next() {
		var row ListSightingsRow
		scanArgs := []any{&row.ID, &row.Label}
		for more := true; more; more = rows.Next() {
			row = ListSightingsRow{}
			if err := rows.Scan(scanArgs...); err != nil {
				return nil, err
			}
			results = append(results, row)
		}
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

internal/store

type CountSightingsRow struct {
	Total int64 `json:"total"`
}

func (queries *Queries) CountSightings(ctx context.Context) (CountSightingsRow, error) {
	var row CountSightingsRow
	err := queries.writer.QueryRowContext(ctx, countSightingsSQL).Scan(&row.Total)
	if err != nil {
		return CountSightingsRow{}, err
	}
	return row, nil
}

Using it

The program wraps each connection in a connection type that prints its name and every statement before running it, so the output shows where each query went. At the end it builds queries on the reader alone and tries the writes there, to show what the routing protects you from.

example.go

package main

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"io"

	"seagull.sh/seagull/examples/postgres/024_read_replicas/internal/store"
)

// connection is a store.DBTX that prints its name and each statement before running it on
// the embedded database, so the output shows which connection served every query.
type connection struct {
	*sql.DB

	// output receives a line per statement.
	output io.Writer

	// name labels the connection in the output.
	name string
}

// ExecContext prints the statement, then runs it on the database.
//
// Takes query (string) which is the SQL to run.
// Takes arguments (...any) which are its bind values.
//
// Returns sql.Result which is the database's result.
// Returns error when the statement fails.
func (c connection) ExecContext(ctx context.Context, query string, arguments ...any) (sql.Result, error) {
	fmt.Fprintf(c.output, "%s  %s\n", c.name, query)
	return c.DB.ExecContext(ctx, query, arguments...)
}

// QueryContext prints the query, then runs it on the database.
//
// Takes query (string) which is the SQL to run.
// Takes arguments (...any) which are its bind values.
//
// Returns *sql.Rows which holds the result rows.
// Returns error when the query fails.
func (c connection) QueryContext(ctx context.Context, query string, arguments ...any) (*sql.Rows, error) {
	fmt.Fprintf(c.output, "%s  %s\n", c.name, query)
	return c.DB.QueryContext(ctx, query, arguments...)
}

// QueryRowContext prints the query, then runs it on the database.
//
// Takes query (string) which is the SQL to run.
// Takes arguments (...any) which are its bind values.
//
// Returns *sql.Row which holds the result row or the error.
func (c connection) QueryRowContext(ctx context.Context, query string, arguments ...any) *sql.Row {
	fmt.Fprintf(c.output, "%s  %s\n", c.name, query)
	return c.DB.QueryRowContext(ctx, query, arguments...)
}

// sqlStateError is a server error that carries a SQLSTATE code, as pgx's *pgconn.PgError
// does.
type sqlStateError interface {
	error

	// SQLState returns the five-character SQLSTATE code.
	SQLState() string
}

// run records sightings and reads them back through Queries built with NewWithReplica,
// printing which connection each statement went to. It then sends the queries that only
// look like reads straight to the reader, to show why Seagull routes them to the writer.
//
// Takes writer (*sql.DB) which is the migrated primary database.
// Takes reader (*sql.DB) which is a read-only connection to the same data.
// Takes output (io.Writer) which receives the routing and the results.
//
// Returns error when a query fails other than as the example expects.
func run(ctx context.Context, writer, reader *sql.DB, output io.Writer) error {
	// Seagull decides whether each query is read-only when it analyses it, and each
	// generated method uses the writer or the reader to match. On PostgreSQL a SELECT goes
	// to the writer if it locks rows (FOR UPDATE), modifies data in a CTE, or calls a
	// function whose data access is writing or unknown.
	queries := store.NewWithReplica(
		connection{DB: writer, output: output, name: "writer"},
		connection{DB: reader, output: output, name: "reader"},
	)

	herring, err := queries.RecordSighting(ctx, store.RecordSightingParams{Species: "herring gull", Place: "the pier"})
	if err != nil {
		return fmt.Errorf("recording a herring gull: %w", err)
	}
	if _, err := queries.RecordSighting(ctx, store.RecordSightingParams{Species: "kittiwake", Place: "the harbour wall"}); err != nil {
		return fmt.Errorf("recording a kittiwake: %w", err)
	}

	// LogSighting is a SELECT, but log_sighting inserts a row, so it goes to the writer. A
	// plpgsql function may return NULL, so ID is a pointer.
	logged, err := queries.LogSighting(ctx, store.LogSightingParams{Species: "puffin", Place: "the cliffs"})
	if err != nil {
		return fmt.Errorf("logging a puffin: %w", err)
	}
	if logged.ID == nil {
		return errors.New("log_sighting returned NULL")
	}
	fmt.Fprintf(output, "        logged sighting %d\n", *logged.ID)

	// ListSightings calls sighting_label, whose body only concatenates its arguments, so it
	// stays read-only and goes to the reader. With these non-NULL species and place columns,
	// the concatenation produces a non-NULL Label string.
	listed, err := queries.ListSightings(ctx)
	if err != nil {
		return fmt.Errorf("listing sightings: %w", err)
	}
	for _, sighting := range listed {
		fmt.Fprintf(output, "        %d %s\n", sighting.ID, sighting.Label)
	}

	// CountSightings is a SELECT, but its header says readonly: false, so it reads from the
	// writer. A real replica lags behind the primary; a read that must see a write made a
	// moment ago belongs on the writer.
	count, err := queries.CountSightings(ctx)
	if err != nil {
		return fmt.Errorf("counting sightings: %w", err)
	}
	fmt.Fprintf(output, "        %d sightings on the primary\n", count.Total)

	// FOR UPDATE locks the row, which only the primary can do.
	claimed, err := queries.ClaimSighting(ctx, herring.ID)
	if err != nil {
		return fmt.Errorf("claiming sighting %d: %w", herring.ID, err)
	}
	fmt.Fprintf(output, "        locked sighting %d, a %s\n", claimed.ID, claimed.Species)

	// The DELETE inside the CTE makes the whole statement a write.
	forgotten, err := queries.ForgetPlace(ctx, "the cliffs")
	if err != nil {
		return fmt.Errorf("forgetting the cliffs: %w", err)
	}
	fmt.Fprintf(output, "        forgot %d sighting at the cliffs\n", len(forgotten))

	return tryOnReader(ctx, store.New(reader), herring.ID, output)
}

// tryOnReader sends three queries that write or lock straight to the reader, which
// refuses each one, as a replica would, with SQLSTATE 25006, read_only_sql_transaction.
// pgx reports a server error as a *pgconn.PgError, and its SQLState method gives the
// code, which stays the same where the wording of the message does not.
//
// Takes onReader (*store.Queries) which runs everything on the reader.
// Takes sighting (int64) which is a sighting to try to lock.
// Takes output (io.Writer) which receives each refusal.
//
// Returns error when the reader does not refuse one of them.
func tryOnReader(ctx context.Context, onReader *store.Queries, sighting int64, output io.Writer) error {
	attempts := []struct {
		name string
		call func() error
	}{
		{name: "RecordSighting", call: func() error {
			_, err := onReader.RecordSighting(ctx, store.RecordSightingParams{Species: "gannet", Place: "the bay"})
			return err
		}},
		{name: "LogSighting", call: func() error {
			_, err := onReader.LogSighting(ctx, store.LogSightingParams{Species: "gannet", Place: "the bay"})
			return err
		}},
		{name: "ClaimSighting", call: func() error {
			_, err := onReader.ClaimSighting(ctx, sighting)
			return err
		}},
	}
	fmt.Fprintln(output, "on the reader:")
	for _, attempt := range attempts {
		err := attempt.call()
		if err == nil {
			return fmt.Errorf("%s on the reader: want a refusal from the server, got no error", attempt.name)
		}
		refusal, hasState := errors.AsType[sqlStateError](err)
		if !hasState {
			return fmt.Errorf("%s on the reader: want a refusal from the server, got %w", attempt.name, err)
		}
		fmt.Fprintf(output, "  %s refused: SQLSTATE %s\n", attempt.name, refusal.SQLState())
	}
	return nil
}

What it prints

Only ListSightings goes to the reader. The function call, the row lock and the writing CTE all go to the writer, and the last three lines show the reader refusing each of them with SQLSTATE 25006.

writer  INSERT INTO sightings (species, place) VALUES ($1, $2) RETURNING id;
writer  INSERT INTO sightings (species, place) VALUES ($1, $2) RETURNING id;
writer  SELECT log_sighting($1, $2) AS id;
        logged sighting 3
reader  SELECT id, sighting_label(species, place) AS label FROM sightings ORDER BY id;
        1 herring gull at the pier
        2 kittiwake at the harbour wall
        3 puffin at the cliffs
writer  SELECT COUNT(*) AS total FROM sightings;
        3 sightings on the primary
writer  SELECT id, species FROM sightings WHERE id = $1 FOR UPDATE;
        locked sighting 1, a herring gull
writer  WITH forgotten AS (DELETE FROM sightings WHERE place = $1 RETURNING id) SELECT id FROM forgotten ORDER BY id;
        forgot 1 sighting at the cliffs
on the reader:
  RecordSighting refused: SQLSTATE 25006
  LogSighting refused: SQLSTATE 25006
  ClaimSighting refused: SQLSTATE 25006

Try it

Change sighting_label from IMMUTABLE to VOLATILE and run sgl generate: ListSightings moves to queries.writer, because Seagull can no longer assume the function only reads. Functions in migrations goes further into how volatility decides this.

Run it locally

From the directory you cloned the Seagull repository into:

cd seagull/examples/postgres && go run ./024_read_replicas

Its test runs it too, and checks what it prints against output.txt:

cd seagull/examples/postgres && SGL_EXAMPLE_ENGINE=cockroachdb SGL_EXAMPLE_DSN="$DATABASE_URL" go test ./024_read_replicas

Needs a running CockroachDB server: set DATABASE_URL, or pass -dsn, to the address of an empty database.

The files

Every file of the example, as it is in the Seagull repository.

sgl.yaml

# Copyright 2026 PolitePixels Limited
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

# This project stands against fascism, authoritarianism, and all forms of
# oppression. We built this to empower people, not to enable those who would
# strip others of their rights and dignity.

version: 1

databases:
  - name: sightings
    dialect: postgres
    migrations: db/migrations
    queries: db/queries
    go:
      package: store
      out: internal/store
      driver: database/sql
    migrate:
      dsn: ${env:DATABASE_URL}

db/migrations/0001_sightings.up.sql

CREATE TABLE sightings (
    id      BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    species TEXT   NOT NULL,
    place   TEXT   NOT NULL
);

CREATE FUNCTION sighting_label(species TEXT, place TEXT) RETURNS TEXT
LANGUAGE sql IMMUTABLE STRICT
AS $$ SELECT species || ' at ' || place $$;

CREATE FUNCTION log_sighting(new_species TEXT, new_place TEXT) RETURNS BIGINT
LANGUAGE plpgsql VOLATILE
AS $$
DECLARE
    new_id BIGINT;
BEGIN
    INSERT INTO sightings (species, place) VALUES (new_species, new_place) RETURNING id INTO new_id;
    RETURN new_id;
END;
$$;

db/migrations/0001_sightings.down.sql

DROP FUNCTION log_sighting(TEXT, TEXT);
DROP FUNCTION sighting_label(TEXT, TEXT);
DROP TABLE sightings;

db/queries/sightings.sql

-- sgl.query(RecordSighting, one)
INSERT INTO sightings (species, place) VALUES ($1, $2) RETURNING id;

-- sgl.query(LogSighting, one)
SELECT log_sighting(:species, :place) AS id;

-- sgl.query(ListSightings, many)
SELECT id, sighting_label(species, place) AS label FROM sightings ORDER BY id;

-- sgl.query(CountSightings, one, readonly: false)
SELECT COUNT(*) AS total FROM sightings;

-- sgl.query(ClaimSighting, one)
SELECT id, species FROM sightings WHERE id = $1 FOR UPDATE;

-- sgl.query(ForgetPlace, many)
WITH forgotten AS (DELETE FROM sightings WHERE place = $1 RETURNING id) SELECT id FROM forgotten ORDER BY id;

example.go

package main

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"io"

	"seagull.sh/seagull/examples/postgres/024_read_replicas/internal/store"
)

// connection is a store.DBTX that prints its name and each statement before running it on
// the embedded database, so the output shows which connection served every query.
type connection struct {
	*sql.DB

	// output receives a line per statement.
	output io.Writer

	// name labels the connection in the output.
	name string
}

// ExecContext prints the statement, then runs it on the database.
//
// Takes query (string) which is the SQL to run.
// Takes arguments (...any) which are its bind values.
//
// Returns sql.Result which is the database's result.
// Returns error when the statement fails.
func (c connection) ExecContext(ctx context.Context, query string, arguments ...any) (sql.Result, error) {
	fmt.Fprintf(c.output, "%s  %s\n", c.name, query)
	return c.DB.ExecContext(ctx, query, arguments...)
}

// QueryContext prints the query, then runs it on the database.
//
// Takes query (string) which is the SQL to run.
// Takes arguments (...any) which are its bind values.
//
// Returns *sql.Rows which holds the result rows.
// Returns error when the query fails.
func (c connection) QueryContext(ctx context.Context, query string, arguments ...any) (*sql.Rows, error) {
	fmt.Fprintf(c.output, "%s  %s\n", c.name, query)
	return c.DB.QueryContext(ctx, query, arguments...)
}

// QueryRowContext prints the query, then runs it on the database.
//
// Takes query (string) which is the SQL to run.
// Takes arguments (...any) which are its bind values.
//
// Returns *sql.Row which holds the result row or the error.
func (c connection) QueryRowContext(ctx context.Context, query string, arguments ...any) *sql.Row {
	fmt.Fprintf(c.output, "%s  %s\n", c.name, query)
	return c.DB.QueryRowContext(ctx, query, arguments...)
}

// sqlStateError is a server error that carries a SQLSTATE code, as pgx's *pgconn.PgError
// does.
type sqlStateError interface {
	error

	// SQLState returns the five-character SQLSTATE code.
	SQLState() string
}

// run records sightings and reads them back through Queries built with NewWithReplica,
// printing which connection each statement went to. It then sends the queries that only
// look like reads straight to the reader, to show why Seagull routes them to the writer.
//
// Takes writer (*sql.DB) which is the migrated primary database.
// Takes reader (*sql.DB) which is a read-only connection to the same data.
// Takes output (io.Writer) which receives the routing and the results.
//
// Returns error when a query fails other than as the example expects.
func run(ctx context.Context, writer, reader *sql.DB, output io.Writer) error {
	// Seagull decides whether each query is read-only when it analyses it, and each
	// generated method uses the writer or the reader to match. On PostgreSQL a SELECT goes
	// to the writer if it locks rows (FOR UPDATE), modifies data in a CTE, or calls a
	// function whose data access is writing or unknown.
	queries := store.NewWithReplica(
		connection{DB: writer, output: output, name: "writer"},
		connection{DB: reader, output: output, name: "reader"},
	)

	herring, err := queries.RecordSighting(ctx, store.RecordSightingParams{Species: "herring gull", Place: "the pier"})
	if err != nil {
		return fmt.Errorf("recording a herring gull: %w", err)
	}
	if _, err := queries.RecordSighting(ctx, store.RecordSightingParams{Species: "kittiwake", Place: "the harbour wall"}); err != nil {
		return fmt.Errorf("recording a kittiwake: %w", err)
	}

	// LogSighting is a SELECT, but log_sighting inserts a row, so it goes to the writer. A
	// plpgsql function may return NULL, so ID is a pointer.
	logged, err := queries.LogSighting(ctx, store.LogSightingParams{Species: "puffin", Place: "the cliffs"})
	if err != nil {
		return fmt.Errorf("logging a puffin: %w", err)
	}
	if logged.ID == nil {
		return errors.New("log_sighting returned NULL")
	}
	fmt.Fprintf(output, "        logged sighting %d\n", *logged.ID)

	// ListSightings calls sighting_label, whose body only concatenates its arguments, so it
	// stays read-only and goes to the reader. With these non-NULL species and place columns,
	// the concatenation produces a non-NULL Label string.
	listed, err := queries.ListSightings(ctx)
	if err != nil {
		return fmt.Errorf("listing sightings: %w", err)
	}
	for _, sighting := range listed {
		fmt.Fprintf(output, "        %d %s\n", sighting.ID, sighting.Label)
	}

	// CountSightings is a SELECT, but its header says readonly: false, so it reads from the
	// writer. A real replica lags behind the primary; a read that must see a write made a
	// moment ago belongs on the writer.
	count, err := queries.CountSightings(ctx)
	if err != nil {
		return fmt.Errorf("counting sightings: %w", err)
	}
	fmt.Fprintf(output, "        %d sightings on the primary\n", count.Total)

	// FOR UPDATE locks the row, which only the primary can do.
	claimed, err := queries.ClaimSighting(ctx, herring.ID)
	if err != nil {
		return fmt.Errorf("claiming sighting %d: %w", herring.ID, err)
	}
	fmt.Fprintf(output, "        locked sighting %d, a %s\n", claimed.ID, claimed.Species)

	// The DELETE inside the CTE makes the whole statement a write.
	forgotten, err := queries.ForgetPlace(ctx, "the cliffs")
	if err != nil {
		return fmt.Errorf("forgetting the cliffs: %w", err)
	}
	fmt.Fprintf(output, "        forgot %d sighting at the cliffs\n", len(forgotten))

	return tryOnReader(ctx, store.New(reader), herring.ID, output)
}

// tryOnReader sends three queries that write or lock straight to the reader, which
// refuses each one, as a replica would, with SQLSTATE 25006, read_only_sql_transaction.
// pgx reports a server error as a *pgconn.PgError, and its SQLState method gives the
// code, which stays the same where the wording of the message does not.
//
// Takes onReader (*store.Queries) which runs everything on the reader.
// Takes sighting (int64) which is a sighting to try to lock.
// Takes output (io.Writer) which receives each refusal.
//
// Returns error when the reader does not refuse one of them.
func tryOnReader(ctx context.Context, onReader *store.Queries, sighting int64, output io.Writer) error {
	attempts := []struct {
		name string
		call func() error
	}{
		{name: "RecordSighting", call: func() error {
			_, err := onReader.RecordSighting(ctx, store.RecordSightingParams{Species: "gannet", Place: "the bay"})
			return err
		}},
		{name: "LogSighting", call: func() error {
			_, err := onReader.LogSighting(ctx, store.LogSightingParams{Species: "gannet", Place: "the bay"})
			return err
		}},
		{name: "ClaimSighting", call: func() error {
			_, err := onReader.ClaimSighting(ctx, sighting)
			return err
		}},
	}
	fmt.Fprintln(output, "on the reader:")
	for _, attempt := range attempts {
		err := attempt.call()
		if err == nil {
			return fmt.Errorf("%s on the reader: want a refusal from the server, got no error", attempt.name)
		}
		refusal, hasState := errors.AsType[sqlStateError](err)
		if !hasState {
			return fmt.Errorf("%s on the reader: want a refusal from the server, got %w", attempt.name, err)
		}
		fmt.Fprintf(output, "  %s refused: SQLSTATE %s\n", attempt.name, refusal.SQLState())
	}
	return nil
}

main.go

// Command 024_read_replicas applies its migrations to a PostgreSQL database, opens a
// second, read-only pool on the same database to stand in for a replica, then runs the
// example in example.go: writes go to the writer, reads to the reader.
//
// Run it with `go run . -dsn postgres://user:password@localhost:5432/example`, or set
// DATABASE_URL instead of passing -dsn. Give it an empty database of its own, as the
// migrations create its tables and the example adds rows each time it runs. Pass -replica
// with a real replica's connection string to read from it instead.
package main

import (
	"context"
	"database/sql"
	"embed"
	"errors"
	"flag"
	"fmt"
	"log"
	"os"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/stdlib"

	"seagull.sh/seagull/sdk/migrate"
	"seagull.sh/seagull/sdk/migrate/migrate_backend_postgres"
)

// migrations holds the migration files, compiled into the binary.
//
//go:embed db/migrations
var migrations embed.FS

func main() {
	dsn := flag.String("dsn", os.Getenv("DATABASE_URL"), "PostgreSQL connection string; defaults to $DATABASE_URL")
	replica := flag.String("replica", "", "read replica's connection string; the primary, read-only, when empty")
	flag.Parse()
	if err := execute(context.Background(), *dsn, *replica); err != nil {
		log.Fatal(err)
	}
}

// execute opens and migrates the database at dsn, opens a read-only pool on the replica,
// or on dsn when replica is empty, and runs the example against both.
//
// Takes dsn (string) which is the primary's connection string.
// Takes replica (string) which is the replica's connection string, or empty.
//
// Returns error when the databases cannot be prepared or the example fails.
func execute(ctx context.Context, dsn, replica string) error {
	if dsn == "" {
		return errors.New("set -dsn or DATABASE_URL to a PostgreSQL connection string")
	}
	writer, err := sql.Open("pgx", dsn)
	if err != nil {
		return err
	}
	defer func() { _ = writer.Close() }()

	migrator, err := migrate.NewMigrator(writer, migrate_backend_postgres.New(), migrations, "db/migrations")
	if err != nil {
		return err
	}
	if _, err := migrator.Up(ctx); err != nil {
		return fmt.Errorf("migrating: %w", err)
	}

	if replica == "" {
		replica = dsn
	}
	reader, err := openReader(replica)
	if err != nil {
		return err
	}
	defer func() { _ = reader.Close() }()
	return run(ctx, writer, reader, os.Stdout)
}

// openReader opens a pool whose sessions start with default_transaction_read_only on, so
// its transactions default to read-only and refuse writes to the sightings table.
//
// Takes dsn (string) which is the connection string, as a URL or keyword/value pairs.
//
// Returns *sql.DB which is the read-only pool.
// Returns error when the connection string cannot be parsed.
func openReader(dsn string) (*sql.DB, error) {
	config, err := pgx.ParseConfig(dsn)
	if err != nil {
		return nil, err
	}
	config.RuntimeParams["default_transaction_read_only"] = "on"
	return stdlib.OpenDB(*config), nil
}

internal/store/models.go

// Code generated by sgl. DO NOT EDIT.

package store

type Sightings struct {
	ID      int64  `json:"id"`
	Species string `json:"species"`
	Place   string `json:"place"`
}

internal/store/querier.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"context"
	"database/sql"
)

type DBTX interface {
	ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
	QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
	QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
}

type dbtxWrapper interface {
	WrapDBTX(inner any) any
}

type Queries struct {
	reader  DBTX
	writer  DBTX
	wrapper dbtxWrapper
}

func New(db DBTX) *Queries {
	wrapper, _ := db.(dbtxWrapper)
	return &Queries{reader: db, writer: db, wrapper: wrapper}
}

func NewWithReplica(writer DBTX, reader DBTX) *Queries {
	wrapper, _ := writer.(dbtxWrapper)
	return &Queries{reader: reader, writer: writer, wrapper: wrapper}
}

func (queries *Queries) WithTx(transaction *sql.Tx) *Queries {
	if queries == nil {
		return &Queries{reader: transaction, writer: transaction, wrapper: nil}
	}
	db := queries.wrapDBTX(transaction)
	return &Queries{reader: db, writer: db, wrapper: queries.wrapper}
}

func (queries *Queries) wrapDBTX(db DBTX) DBTX {
	if queries.wrapper == nil {
		return db
	}
	wrapped, ok := queries.wrapper.WrapDBTX(db).(DBTX)
	if !ok {
		return db
	}
	return wrapped
}

func (queries *Queries) RunInTx(ctx context.Context, db *sql.DB, fn func(*Queries) error) error {
	transaction, err := db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer transaction.Rollback()
	if err = fn(queries.WithTx(transaction)); err != nil {
		return err
	}
	return transaction.Commit()
}

internal/store/sightings.sql.go

// Code generated by sgl. DO NOT EDIT.

package store

import "context"

const claimSightingSQL = `SELECT id, species FROM sightings WHERE id = $1 FOR UPDATE;`

type ClaimSightingRow struct {
	ID      int64  `json:"id"`
	Species string `json:"species"`
}

func (queries *Queries) ClaimSighting(ctx context.Context, id int64) (ClaimSightingRow, error) {
	var row ClaimSightingRow
	err := queries.writer.QueryRowContext(ctx, claimSightingSQL, id).Scan(&row.ID, &row.Species)
	if err != nil {
		return ClaimSightingRow{}, err
	}
	return row, nil
}

const countSightingsSQL = `SELECT COUNT(*) AS total FROM sightings;`

type CountSightingsRow struct {
	Total int64 `json:"total"`
}

func (queries *Queries) CountSightings(ctx context.Context) (CountSightingsRow, error) {
	var row CountSightingsRow
	err := queries.writer.QueryRowContext(ctx, countSightingsSQL).Scan(&row.Total)
	if err != nil {
		return CountSightingsRow{}, err
	}
	return row, nil
}

const forgetPlaceSQL = `WITH forgotten AS (DELETE FROM sightings WHERE place = $1 RETURNING id) SELECT id FROM forgotten ORDER BY id;`

type ForgetPlaceRow struct {
	ID int64 `json:"id"`
}

func (queries *Queries) ForgetPlace(ctx context.Context, place string) ([]ForgetPlaceRow, error) {
	rows, err := queries.writer.QueryContext(ctx, forgetPlaceSQL, place)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []ForgetPlaceRow
	if rows.Next() {
		var row ForgetPlaceRow
		scanArgs := []any{&row.ID}
		for more := true; more; more = rows.Next() {
			row = ForgetPlaceRow{}
			if err := rows.Scan(scanArgs...); err != nil {
				return nil, err
			}
			results = append(results, row)
		}
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

const listSightingsSQL = `SELECT id, sighting_label(species, place) AS label FROM sightings ORDER BY id;`

type ListSightingsRow struct {
	ID    int64  `json:"id"`
	Label string `json:"label"`
}

func (queries *Queries) ListSightings(ctx context.Context) ([]ListSightingsRow, error) {
	rows, err := queries.reader.QueryContext(ctx, listSightingsSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []ListSightingsRow
	if rows.Next() {
		var row ListSightingsRow
		scanArgs := []any{&row.ID, &row.Label}
		for more := true; more; more = rows.Next() {
			row = ListSightingsRow{}
			if err := rows.Scan(scanArgs...); err != nil {
				return nil, err
			}
			results = append(results, row)
		}
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

const logSightingSQL = `SELECT log_sighting($1, $2) AS id;`

type LogSightingParams struct {
	Species string
	Place   string
}

type LogSightingRow struct {
	ID *int64 `json:"id"`
}

func (queries *Queries) LogSighting(ctx context.Context, params LogSightingParams) (LogSightingRow, error) {
	var row LogSightingRow
	err := queries.writer.QueryRowContext(ctx, logSightingSQL, params.Species, params.Place).Scan(&row.ID)
	if err != nil {
		return LogSightingRow{}, err
	}
	return row, nil
}

const recordSightingSQL = `INSERT INTO sightings (species, place) VALUES ($1, $2) RETURNING id;`

type RecordSightingParams struct {
	Species string
	Place   string
}

type RecordSightingRow struct {
	ID int64 `json:"id"`
}

func (queries *Queries) RecordSighting(ctx context.Context, params RecordSightingParams) (RecordSightingRow, error) {
	var row RecordSightingRow
	err := queries.writer.QueryRowContext(ctx, recordSightingSQL, params.Species, params.Place).Scan(&row.ID)
	if err != nil {
		return RecordSightingRow{}, err
	}
	return row, nil
}

output.txt

writer  INSERT INTO sightings (species, place) VALUES ($1, $2) RETURNING id;
writer  INSERT INTO sightings (species, place) VALUES ($1, $2) RETURNING id;
writer  SELECT log_sighting($1, $2) AS id;
        logged sighting 3
reader  SELECT id, sighting_label(species, place) AS label FROM sightings ORDER BY id;
        1 herring gull at the pier
        2 kittiwake at the harbour wall
        3 puffin at the cliffs
writer  SELECT COUNT(*) AS total FROM sightings;
        3 sightings on the primary
writer  SELECT id, species FROM sightings WHERE id = $1 FOR UPDATE;
        locked sighting 1, a herring gull
writer  WITH forgotten AS (DELETE FROM sightings WHERE place = $1 RETURNING id) SELECT id FROM forgotten ORDER BY id;
        forgot 1 sighting at the cliffs
on the reader:
  RecordSighting refused: SQLSTATE 25006
  LogSighting refused: SQLSTATE 25006
  ClaimSighting refused: SQLSTATE 25006

Also on other engines

SQLiteNewWithReplica sends each query to the writer or the reader by Seagull's read-only detection; a mode=ro connection to the same file stands in for the replica.PostgreSQLNewWithReplica sends each query to the writer or the reader by Seagull's read-only detection, which on PostgreSQL also catches FOR UPDATE, writing CTEs and calls of functions that change data; a pool with default_transaction_read_only plays the replica.TimescaleDBNewWithReplica sends each query to the writer or the reader by Seagull's read-only detection, which on PostgreSQL also catches FOR UPDATE, writing CTEs and calls of functions that change data; a pool with default_transaction_read_only plays the replica.