Read replicas

On SQLite:

• SQLite has no replicas of its own, so a second connection opened with mode=ro plays the reader
• SELECT is read-only and INSERT, UPDATE and DELETE are writes; SQLite has no writing CTEs or volatile functions to detect
• readonly: false on a SELECT keeps a read-after-write on the writer

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.

SQLite has no replicas, so the example opens a second connection to the same file with mode=ro to play the part. SQLite refuses every write on that connection, as a replica would.

The queries

On SQLite the rule is simple: INSERT, UPDATE and DELETE write, and SELECT reads. RecordSighting is an INSERT, so it goes to the writer even though it returns a row; ListSightings is a SELECT, so it goes to the reader.

db/queries/sightings.sql

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

db/queries/sightings.sql

-- sgl.query(ListSightings, many)
SELECT id, species, place FROM sightings 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"`
	Species string `json:"species"`
	Place   string `json:"place"`
}

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.Species, &row.Place}
		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/sqlite/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...)
}

// run records two sightings and reads them back through Queries built with
// NewWithReplica, printing which connection each statement went to. It then shows that
// the reader refuses writes, which is why the routing matters.
//
// 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 marks each query read-only or not when it analyses it, and each generated
	// method uses the writer or the reader to match: INSERT, UPDATE and DELETE go to the
	// writer, SELECT to the reader.
	queries := store.NewWithReplica(
		connection{DB: writer, output: output, name: "writer"},
		connection{DB: reader, output: output, name: "reader"},
	)

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

	listed, err := queries.ListSightings(ctx)
	if err != nil {
		return fmt.Errorf("listing sightings: %w", err)
	}
	for _, sighting := range listed {
		fmt.Fprintf(output, "        %d %s at %s\n", sighting.ID, sighting.Species, sighting.Place)
	}

	// 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)

	// The reader was opened with mode=ro, so a write sent to it fails.
	_, err = store.New(reader).RecordSighting(ctx, store.RecordSightingParams{Species: "puffin", Place: "the cliffs"})
	if err == nil {
		return errors.New("the read-only connection accepted a write")
	}
	fmt.Fprintf(output, "a write on the reader fails: %v\n", err)
	return nil
}

What it prints

The inserts go to the writer, the list to the reader, and the count to the writer because of readonly: false. The last line is SQLite refusing a write on the read-only connection.

writer  INSERT INTO sightings (species, place) VALUES (?1, ?2) RETURNING id;
writer  INSERT INTO sightings (species, place) VALUES (?1, ?2) RETURNING id;
reader  SELECT id, species, place FROM sightings ORDER BY id;
        1 herring gull at the pier
        2 kittiwake at the harbour wall
writer  SELECT COUNT(*) AS total FROM sightings;
        2 sightings on the primary
a write on the reader fails: attempt to write a readonly database (8)

Try it

Remove readonly: false from CountSightings and run sgl generate: the method switches to queries.reader. With a real replica, the count could then miss the sightings just recorded.

Across engines

CountSightings has the same Go types on every engine that has this example.

CockroachDB and TimescaleDB run the PostgreSQL version unchanged.

Compare the whole example side by side with PostgreSQL.

Run it locally

From the directory you cloned the Seagull repository into:

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

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

cd seagull/examples/sqlite && go test ./024_read_replicas

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: sqlite
    migrations: db/migrations
    queries: db/queries
    go:
      package: store
      out: internal/store

db/migrations/0001_sightings.up.sql

CREATE TABLE sightings (
    id      INTEGER PRIMARY KEY,
    species TEXT    NOT NULL,
    place   TEXT    NOT NULL
);

db/migrations/0001_sightings.down.sql

DROP TABLE sightings;

db/queries/sightings.sql

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

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

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

example.go

package main

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

	"seagull.sh/seagull/examples/sqlite/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...)
}

// run records two sightings and reads them back through Queries built with
// NewWithReplica, printing which connection each statement went to. It then shows that
// the reader refuses writes, which is why the routing matters.
//
// 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 marks each query read-only or not when it analyses it, and each generated
	// method uses the writer or the reader to match: INSERT, UPDATE and DELETE go to the
	// writer, SELECT to the reader.
	queries := store.NewWithReplica(
		connection{DB: writer, output: output, name: "writer"},
		connection{DB: reader, output: output, name: "reader"},
	)

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

	listed, err := queries.ListSightings(ctx)
	if err != nil {
		return fmt.Errorf("listing sightings: %w", err)
	}
	for _, sighting := range listed {
		fmt.Fprintf(output, "        %d %s at %s\n", sighting.ID, sighting.Species, sighting.Place)
	}

	// 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)

	// The reader was opened with mode=ro, so a write sent to it fails.
	_, err = store.New(reader).RecordSighting(ctx, store.RecordSightingParams{Species: "puffin", Place: "the cliffs"})
	if err == nil {
		return errors.New("the read-only connection accepted a write")
	}
	fmt.Fprintf(output, "a write on the reader fails: %v\n", err)
	return nil
}

main.go

// Command 024_read_replicas applies its migrations to a SQLite database, opens a second,
// read-only connection to the same file 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 .`, or `go run . -db sightings.db` to keep the database.
package main

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

	_ "modernc.org/sqlite"

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

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

func main() {
	path := flag.String("db", "", "SQLite database file; a temporary one when empty")
	flag.Parse()
	if err := execute(context.Background(), *path); err != nil {
		log.Fatal(err)
	}
}

// execute opens and migrates the database at path, or a temporary one when path is empty,
// opens a read-only connection to it, and runs the example against both.
//
// Takes path (string) which is the database file, or empty.
//
// Returns error when the database cannot be prepared or the example fails.
func execute(ctx context.Context, path string) error {
	if path == "" {
		directory, err := os.MkdirTemp("", "seagull-example-")
		if err != nil {
			return err
		}
		defer func() { _ = os.RemoveAll(directory) }()
		path = filepath.Join(directory, "example.db")
	}
	writer, err := sql.Open("sqlite", "file:"+path)
	if err != nil {
		return err
	}
	defer func() { _ = writer.Close() }()

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

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

// openReader opens a read-only connection to the SQLite file at path. mode=ro makes
// SQLite refuse every write on it, as a replica would.
//
// Takes path (string) which is the database file.
//
// Returns *sql.DB which is the read-only connection.
// Returns error when the connection cannot be opened.
func openReader(path string) (*sql.DB, error) {
	return sql.Open("sqlite", "file:"+path+"?mode=ro")
}

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 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 listSightingsSQL = `SELECT id, species, place FROM sightings ORDER BY id;`

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

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.Species, &row.Place}
		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 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;
reader  SELECT id, species, place FROM sightings ORDER BY id;
        1 herring gull at the pier
        2 kittiwake at the harbour wall
writer  SELECT COUNT(*) AS total FROM sightings;
        2 sightings on the primary
a write on the reader fails: attempt to write a readonly database (8)