SQLite extensions

On this page

On SQLite:

  • FTS5 and R*Tree tables are kept in step with the main table by a trigger in the migration
  • MATCH names an FTS5 column (places_search.description), and rank orders the results
  • R*Tree coordinates are never NULL, so the point parameters are plain float64 values
  • a generated column comes back through RETURNING, and writing to one is Q013

SQLite is small, but it carries more than tables. FTS5 adds full-text search, R*Tree a spatial index for finding what lies at a point or in a box, and the JSON functions let a query look inside JSON stored in a column. Generated columns compute a value from others and keep it up to date. Seagull reads all of these from the migration, so queries that use them are checked and typed like any other.

The example is a harbour guide. Each place has a description, some tags, and a box on a map, and the program finds places by words, by position and by tag.

The schema

db/migrations/0001_places.up.sql

CREATE TABLE places (
    id          INTEGER PRIMARY KEY,
    name        TEXT    NOT NULL,
    description TEXT    NOT NULL,
    tags        TEXT    NOT NULL DEFAULT '[]',
    west        REAL    NOT NULL,
    east        REAL    NOT NULL,
    south       REAL    NOT NULL,
    north       REAL    NOT NULL,
    tag_count   INTEGER NOT NULL GENERATED ALWAYS AS (json_array_length(tags)) VIRTUAL
);

CREATE VIRTUAL TABLE places_search USING fts5(name, description, content=places, content_rowid=id);

CREATE VIRTUAL TABLE places_area USING rtree(id, min_x, max_x, min_y, max_y);

CREATE TRIGGER places_indexed AFTER INSERT ON places BEGIN
    INSERT INTO places_search (rowid, name, description) VALUES (new.id, new.name, new.description);
    INSERT INTO places_area (id, min_x, max_x, min_y, max_y) VALUES (new.id, new.west, new.east, new.south, new.north);
END;

There are three tables and a trigger:

  • places holds the guide. tag_count is a generated column: SQLite works it out from tags with json_array_length, and refuses writes to it.
  • places_search is an FTS5 table over the name and description. content=places keeps the text in places itself, so the index holds only the words, keyed by places.id.
  • places_area is an R*Tree table of each place's box, also keyed by places.id.
  • The trigger copies each new place into both indexes. A schema that also updates or deletes places needs matching triggers for those.

A generated column

AddPlace writes every column except tag_count, and gets it back through RETURNING, already computed:

db/queries/places.sql

-- sgl.query(AddPlace, one)
INSERT INTO places (name, description, tags, west, east, south, north)
VALUES (?, ?, ?, ?, ?, ?, ?)
RETURNING id, tag_count;

A generated column cannot be written. If a query tries to, sgl vet reports Q013 before the database gets the chance to refuse it.

Full-text search: FTS5

MATCH takes a search in FTS5's own syntax, where gull* matches any word starting with "gull". Naming the column, places_search.description, limits the search to descriptions. rank orders the best match first, and snippet returns the matching part of the text with the matched words marked.

db/queries/places.sql

-- sgl.query(SearchDescriptions, many)
SELECT p.name, snippet(places_search, 1, '[', ']', '...', 6) AS excerpt
FROM places_search
JOIN places AS p ON p.id = places_search.rowid
WHERE places_search.description MATCH :terms
ORDER BY rank;

Seagull types snippet's result as nullable, so Excerpt is a *string.

internal/store

type SearchDescriptionsRow struct {
	Name    string  `json:"name"`
	Excerpt *string `json:"excerpt"`
}

func (queries *Queries) SearchDescriptions(ctx context.Context, terms string) ([]SearchDescriptionsRow, error) {
	rows, err := queries.reader.QueryContext(ctx, searchDescriptionsSQL, terms)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []SearchDescriptionsRow
	for rows.Next() {
		var row SearchDescriptionsRow
		if err := rows.Scan(&row.Name, &row.Excerpt); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

What is here: R*Tree

An R*Tree table answers "which boxes contain this point" from its index, without looking at every row. PlacesAt asks it for the boxes around a point and joins back to places for the names.

db/queries/places.sql

-- sgl.query(PlacesAt, many)
SELECT p.name
FROM places_area AS area
JOIN places AS p ON p.id = area.id
WHERE area.min_x <= :x AND area.max_x >= :x AND area.min_y <= :y AND area.max_y >= :y
ORDER BY p.name;

An R*Tree table's columns are never NULL, and Seagull knows it: the coordinates are float64 and the id an int64, so the point compared with them comes through as two plain float64 parameters, X and Y.

Inside JSON: json_each

json_each turns a JSON array into rows, one per element. Joined to places, it lets a WHERE clause look inside each place's tags.

db/queries/places.sql

-- sgl.query(PlacesTagged, many)
-- :tag as sgl.param(tag, nullable: false)
SELECT p.name, p.tag_count
FROM places AS p, json_each(p.tags) AS tag
WHERE tag.value = :tag
ORDER BY p.name;

A JSON element can be any JSON value, null included, so a parameter compared with tag.value would be nullable too, and sgl.param again says the tag is not.

Using it

example.go

package main

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

	"seagull.sh/seagull/examples/sqlite/040_sqlite_extensions/internal/store"
)

// places is the harbour guide. Each place covers a box on a map measured in metres;
// R*Tree stores coordinates as 32-bit floats, which hold whole numbers like these
// exactly.
var places = []store.AddPlaceParams{
	{
		Name:        "The Old Pier",
		Description: "Wooden pier where the herring gulls gather at low tide",
		Tags:        `["gulls", "fishing"]`,
		West:        0,
		East:        20,
		South:       0,
		North:       6,
	},
	{
		Name:        "Harbour Wall",
		Description: "Stone breakwater with kittiwakes nesting on its seaward side",
		Tags:        `["birds", "walk"]`,
		West:        30,
		East:        90,
		South:       0,
		North:       4,
	},
	{
		Name:        "Chip Shop",
		Description: "Cod and chips to take away; guard them from the gulls",
		Tags:        `["food"]`,
		West:        10,
		East:        18,
		South:       20,
		North:       28,
	},
	{
		Name:        "Lifeboat Station",
		Description: "Open to visitors on Sunday afternoons",
		Tags:        `["walk", "history", "boats"]`,
		West:        60,
		East:        75,
		South:       15,
		North:       30,
	},
}

// run fills the harbour guide, then finds places three ways: by the words in their
// descriptions with FTS5, by a point on the map with R*Tree, and by tag with json_each.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives what each search found.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	// tag_count is a generated column: SQLite works it out from tags, and RETURNING hands it
	// back. A query that tried to write it would fail, and sgl vet reports Q013 before it
	// gets that far. The trigger in the migration copies each new place into the FTS5 and
	// R*Tree tables.
	for _, place := range places {
		added, err := queries.AddPlace(ctx, place)
		if err != nil {
			return fmt.Errorf("adding %s: %w", place.Name, err)
		}
		fmt.Fprintf(output, "added %d %-16s tag_count=%d\n", added.ID, place.Name, added.TagCount)
	}

	// FTS5 full-text search: MATCH takes a query in FTS5 syntax, gull* matches any word
	// starting gull, rank orders the best match first, and snippet marks the matching words.
	found, err := queries.SearchDescriptions(ctx, "gull*")
	if err != nil {
		return fmt.Errorf("searching descriptions: %w", err)
	}
	fmt.Fprintln(output, `descriptions matching "gull*":`)
	for _, place := range found {
		excerpt := ""
		if place.Excerpt != nil {
			excerpt = *place.Excerpt
		}
		fmt.Fprintf(output, "  %-12s %s\n", place.Name, excerpt)
	}

	// R*Tree: which boxes contain a point, answered from the spatial index.
	for _, point := range []store.PlacesAtParams{{X: 12, Y: 4}, {X: 70, Y: 20}} {
		here, err := queries.PlacesAt(ctx, point)
		if err != nil {
			return fmt.Errorf("finding what is at (%g, %g): %w", point.X, point.Y, err)
		}
		for _, place := range here {
			fmt.Fprintf(output, "at (%g, %g): %s\n", point.X, point.Y, place.Name)
		}
	}

	// json_each turns a JSON array into rows, one per element, so a WHERE clause can look
	// inside the tags column.
	tagged, err := queries.PlacesTagged(ctx, "walk")
	if err != nil {
		return fmt.Errorf("finding places tagged walk: %w", err)
	}
	for _, place := range tagged {
		fmt.Fprintf(output, "tagged walk: %-16s tag_count=%d\n", place.Name, place.TagCount)
	}
	return nil
}

What it prints

Each place comes back with the tag count SQLite computed. "gull*" finds "gulls" in two descriptions, each excerpt with the match in brackets, and the two points on the map each fall inside one place's box.

added 1 The Old Pier     tag_count=2
added 2 Harbour Wall     tag_count=2
added 3 Chip Shop        tag_count=1
added 4 Lifeboat Station tag_count=3
descriptions matching "gull*":
  The Old Pier Wooden pier where the herring [gulls]...
  Chip Shop    ...away; guard them from the [gulls]
at (12, 4): The Old Pier
at (70, 20): Lifeboat Station
tagged walk: Harbour Wall     tag_count=2
tagged walk: Lifeboat Station tag_count=3

Try it

Delete the two sgl.param lines from PlacesAt and run sgl generate: PlacesAtParams gets X *float64 and Y *float64, and every caller has to pass pointers for a value that is never missing. Then add tag_count to the column list of AddPlace, with a value for it, and run sgl vet to see Q013.

Run it locally

From the directory you cloned the Seagull repository into:

cd seagull/examples/sqlite && go run ./040_sqlite_extensions

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

cd seagull/examples/sqlite && go test ./040_sqlite_extensions

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

db/migrations/0001_places.up.sql

CREATE TABLE places (
    id          INTEGER PRIMARY KEY,
    name        TEXT    NOT NULL,
    description TEXT    NOT NULL,
    tags        TEXT    NOT NULL DEFAULT '[]',
    west        REAL    NOT NULL,
    east        REAL    NOT NULL,
    south       REAL    NOT NULL,
    north       REAL    NOT NULL,
    tag_count   INTEGER NOT NULL GENERATED ALWAYS AS (json_array_length(tags)) VIRTUAL
);

CREATE VIRTUAL TABLE places_search USING fts5(name, description, content=places, content_rowid=id);

CREATE VIRTUAL TABLE places_area USING rtree(id, min_x, max_x, min_y, max_y);

CREATE TRIGGER places_indexed AFTER INSERT ON places BEGIN
    INSERT INTO places_search (rowid, name, description) VALUES (new.id, new.name, new.description);
    INSERT INTO places_area (id, min_x, max_x, min_y, max_y) VALUES (new.id, new.west, new.east, new.south, new.north);
END;

db/migrations/0001_places.down.sql

DROP TRIGGER places_indexed;
DROP TABLE places_area;
DROP TABLE places_search;
DROP TABLE places;

db/queries/places.sql

-- sgl.query(AddPlace, one)
INSERT INTO places (name, description, tags, west, east, south, north)
VALUES (?, ?, ?, ?, ?, ?, ?)
RETURNING id, tag_count;

-- sgl.query(SearchDescriptions, many)
SELECT p.name, snippet(places_search, 1, '[', ']', '...', 6) AS excerpt
FROM places_search
JOIN places AS p ON p.id = places_search.rowid
WHERE places_search.description MATCH :terms
ORDER BY rank;

-- sgl.query(PlacesAt, many)
SELECT p.name
FROM places_area AS area
JOIN places AS p ON p.id = area.id
WHERE area.min_x <= :x AND area.max_x >= :x AND area.min_y <= :y AND area.max_y >= :y
ORDER BY p.name;

-- sgl.query(PlacesTagged, many)
-- :tag as sgl.param(tag, nullable: false)
SELECT p.name, p.tag_count
FROM places AS p, json_each(p.tags) AS tag
WHERE tag.value = :tag
ORDER BY p.name;

example.go

package main

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

	"seagull.sh/seagull/examples/sqlite/040_sqlite_extensions/internal/store"
)

// places is the harbour guide. Each place covers a box on a map measured in metres;
// R*Tree stores coordinates as 32-bit floats, which hold whole numbers like these
// exactly.
var places = []store.AddPlaceParams{
	{
		Name:        "The Old Pier",
		Description: "Wooden pier where the herring gulls gather at low tide",
		Tags:        `["gulls", "fishing"]`,
		West:        0,
		East:        20,
		South:       0,
		North:       6,
	},
	{
		Name:        "Harbour Wall",
		Description: "Stone breakwater with kittiwakes nesting on its seaward side",
		Tags:        `["birds", "walk"]`,
		West:        30,
		East:        90,
		South:       0,
		North:       4,
	},
	{
		Name:        "Chip Shop",
		Description: "Cod and chips to take away; guard them from the gulls",
		Tags:        `["food"]`,
		West:        10,
		East:        18,
		South:       20,
		North:       28,
	},
	{
		Name:        "Lifeboat Station",
		Description: "Open to visitors on Sunday afternoons",
		Tags:        `["walk", "history", "boats"]`,
		West:        60,
		East:        75,
		South:       15,
		North:       30,
	},
}

// run fills the harbour guide, then finds places three ways: by the words in their
// descriptions with FTS5, by a point on the map with R*Tree, and by tag with json_each.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives what each search found.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	// tag_count is a generated column: SQLite works it out from tags, and RETURNING hands it
	// back. A query that tried to write it would fail, and sgl vet reports Q013 before it
	// gets that far. The trigger in the migration copies each new place into the FTS5 and
	// R*Tree tables.
	for _, place := range places {
		added, err := queries.AddPlace(ctx, place)
		if err != nil {
			return fmt.Errorf("adding %s: %w", place.Name, err)
		}
		fmt.Fprintf(output, "added %d %-16s tag_count=%d\n", added.ID, place.Name, added.TagCount)
	}

	// FTS5 full-text search: MATCH takes a query in FTS5 syntax, gull* matches any word
	// starting gull, rank orders the best match first, and snippet marks the matching words.
	found, err := queries.SearchDescriptions(ctx, "gull*")
	if err != nil {
		return fmt.Errorf("searching descriptions: %w", err)
	}
	fmt.Fprintln(output, `descriptions matching "gull*":`)
	for _, place := range found {
		excerpt := ""
		if place.Excerpt != nil {
			excerpt = *place.Excerpt
		}
		fmt.Fprintf(output, "  %-12s %s\n", place.Name, excerpt)
	}

	// R*Tree: which boxes contain a point, answered from the spatial index.
	for _, point := range []store.PlacesAtParams{{X: 12, Y: 4}, {X: 70, Y: 20}} {
		here, err := queries.PlacesAt(ctx, point)
		if err != nil {
			return fmt.Errorf("finding what is at (%g, %g): %w", point.X, point.Y, err)
		}
		for _, place := range here {
			fmt.Fprintf(output, "at (%g, %g): %s\n", point.X, point.Y, place.Name)
		}
	}

	// json_each turns a JSON array into rows, one per element, so a WHERE clause can look
	// inside the tags column.
	tagged, err := queries.PlacesTagged(ctx, "walk")
	if err != nil {
		return fmt.Errorf("finding places tagged walk: %w", err)
	}
	for _, place := range tagged {
		fmt.Fprintf(output, "tagged walk: %-16s tag_count=%d\n", place.Name, place.TagCount)
	}
	return nil
}

main.go

// Command 040_sqlite_extensions applies its migrations to a SQLite database, then runs
// the example in example.go: a harbour guide is searched with FTS5, R*Tree and json_each,
// and a generated column counts each place's tags.
//
// Run it with `go run .`, or `go run . -db harbour.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,
// and runs the example against it.
//
// 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")
	}
	database, err := sql.Open("sqlite", "file:"+path)
	if err != nil {
		return err
	}
	defer func() { _ = database.Close() }()

	migrator, err := migrate.NewMigrator(database, 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)
	}
	return run(ctx, database, os.Stdout)
}

internal/store/models.go

// Code generated by sgl. DO NOT EDIT.

package store

type Places struct {
	ID          int64   `json:"id"`
	Name        string  `json:"name"`
	Description string  `json:"description"`
	Tags        string  `json:"tags"`
	West        float64 `json:"west"`
	East        float64 `json:"east"`
	South       float64 `json:"south"`
	North       float64 `json:"north"`
	TagCount    int64   `json:"tag_count"`
}

type PlacesArea struct {
	ID   int64   `json:"id"`
	MinX float64 `json:"min_x"`
	MaxX float64 `json:"max_x"`
	MinY float64 `json:"min_y"`
	MaxY float64 `json:"max_y"`
}

type PlacesSearch struct {
	Name        *string `json:"name"`
	Description *string `json:"description"`
}

internal/store/places.sql.go

// Code generated by sgl. DO NOT EDIT.

package store

import "context"

const addPlaceSQL = `INSERT INTO places (name, description, tags, west, east, south, north)
VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)
RETURNING id, tag_count;`

type AddPlaceParams struct {
	Name        string
	Description string
	Tags        string
	West        float64
	East        float64
	South       float64
	North       float64
}

type AddPlaceRow struct {
	ID       int64 `json:"id"`
	TagCount int64 `json:"tag_count"`
}

func (queries *Queries) AddPlace(ctx context.Context, params AddPlaceParams) (AddPlaceRow, error) {
	var row AddPlaceRow
	err := queries.writer.QueryRowContext(ctx, addPlaceSQL, params.Name, params.Description, params.Tags, params.West, params.East, params.South, params.North).Scan(&row.ID, &row.TagCount)
	if err != nil {
		return AddPlaceRow{}, err
	}
	return row, nil
}

const placesAtSQL = `SELECT p.name
FROM places_area AS area
JOIN places AS p ON p.id = area.id
WHERE area.min_x <= ?1 AND area.max_x >= ?1 AND area.min_y <= ?2 AND area.max_y >= ?2
ORDER BY p.name;`

type PlacesAtParams struct {
	X float64
	Y float64
}

type PlacesAtRow struct {
	Name string `json:"name"`
}

func (queries *Queries) PlacesAt(ctx context.Context, params PlacesAtParams) ([]PlacesAtRow, error) {
	rows, err := queries.reader.QueryContext(ctx, placesAtSQL, params.X, params.Y)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []PlacesAtRow
	if rows.Next() {
		var row PlacesAtRow
		scanArgs := []any{&row.Name}
		for more := true; more; more = rows.Next() {
			row = PlacesAtRow{}
			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 placesTaggedSQL = `SELECT p.name, p.tag_count
FROM places AS p, json_each(p.tags) AS tag
WHERE tag.value = ?1
ORDER BY p.name;`

type PlacesTaggedRow struct {
	Name     string `json:"name"`
	TagCount int64  `json:"tag_count"`
}

func (queries *Queries) PlacesTagged(ctx context.Context, tag string) ([]PlacesTaggedRow, error) {
	rows, err := queries.reader.QueryContext(ctx, placesTaggedSQL, tag)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []PlacesTaggedRow
	if rows.Next() {
		var row PlacesTaggedRow
		scanArgs := []any{&row.Name, &row.TagCount}
		for more := true; more; more = rows.Next() {
			row = PlacesTaggedRow{}
			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 searchDescriptionsSQL = `SELECT p.name, snippet(places_search, 1, '[', ']', '...', 6) AS excerpt
FROM places_search
JOIN places AS p ON p.id = places_search.rowid
WHERE places_search.description MATCH ?1
ORDER BY rank;`

type SearchDescriptionsRow struct {
	Name    string  `json:"name"`
	Excerpt *string `json:"excerpt"`
}

func (queries *Queries) SearchDescriptions(ctx context.Context, terms string) ([]SearchDescriptionsRow, error) {
	rows, err := queries.reader.QueryContext(ctx, searchDescriptionsSQL, terms)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []SearchDescriptionsRow
	for rows.Next() {
		var row SearchDescriptionsRow
		if err := rows.Scan(&row.Name, &row.Excerpt); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

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()
}

output.txt

added 1 The Old Pier     tag_count=2
added 2 Harbour Wall     tag_count=2
added 3 Chip Shop        tag_count=1
added 4 Lifeboat Station tag_count=3
descriptions matching "gull*":
  The Old Pier Wooden pier where the herring [gulls]...
  Chip Shop    ...away; guard them from the [gulls]
at (12, 4): The Old Pier
at (70, 20): Lifeboat Station
tagged walk: Harbour Wall     tag_count=2
tagged walk: Lifeboat Station tag_count=3