Merge branch 'pgruntime'

21 files changed, 1232 insertions, 1 deletions

diff --git a/.gitignore b/.gitignore
index 00d893e..9027518 100644
--- a/.gitignore
+++ b/.gitignore
@@ -1,2 +1,6 @@
 /build/
 !/build/.gitkeep
+
+# ignore /testing/.testdb both as a symlink (if $PGRUNTIME_TESTDB_PATH is set) and as a directory (otherwise)
+/testing/.testdb
+/testing/.testdb/
diff --git a/.golangci.yaml b/.golangci.yaml
index 9767b3a..9d3fb3d 100644
--- a/.golangci.yaml
+++ b/.golangci.yaml
@@ -71,6 +71,7 @@ linters:
       go-version-pattern: 1\.\d+(\.0)?$
     gosec:
       excludes:
+        - G204 # gosec likes to scream about the fact that we are passing paths derived from $PWD into command invocations
         - G306 # gosec does not understand how umask works
     nolintlint:
       require-specific: true
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 1959eb8..11dda13 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -7,6 +7,7 @@ SPDX-License-Identifier: Apache-2.0
 
 Changes:
 
+- Add package pgruntime.
 - Add WithinTransaction method to `*gsql.DB` and `*gsql.Conn` methods.
 
 # v1.12.0 (2026-07-31)
diff --git a/Makefile b/Makefile
index 9079e24..a141d5f 100644
--- a/Makefile
+++ b/Makefile
@@ -13,7 +13,7 @@ static-check: FORCE
 	@if ! reuse lint -q; then reuse lint; fi
 
 GO_COVERPKGS := $(shell go list ./... | tr '\n' , | sed 's/,$$//')
-GO_TESTPKGS := $(shell go list -f '{{if or .TestGoFiles .XTestGoFiles}}{{.ImportPath}}{{end}}' ./...)
+GO_TESTPKGS := $(shell go list -f '{{if or .TestGoFiles .XTestGoFiles}}{{.ImportPath}}{{end}}' ./... ./testing/...)
 
 build/cover.out: FORCE
 	@printf "\e[1;36m>> go test\e[0m\n"
diff --git a/README.md b/README.md
index e4f404f..2a2ad6e 100644
--- a/README.md
+++ b/README.md
@@ -19,6 +19,7 @@ My personal extension of the standard library.
 ### Addons for database/sql
 
 - [gsql](./gsql/): abstraction layer for database libraries, supporting both database/sql drivers and non-standard drivers like [pgx](https://github.com/jackc/pgx)
+- [pgruntime](./pgruntime/): connection handling for PostgreSQL databases, including optional support for database migrations and self-contained test DB instances
 
 ### Addons for errors
 
diff --git a/REUSE.toml b/REUSE.toml
index 0f8b58a..ccab73e 100644
--- a/REUSE.toml
+++ b/REUSE.toml
@@ -12,6 +12,9 @@ path = [
   "go.work",
   "benchmark/go.mod",
   "benchmark/go.sum",
+  "pgruntime/tool_template.sh",
+  "testing/go.mod",
+  "testing/go.sum",
 ]
 SPDX-FileCopyrightText = "Stefan Majewsky <majewsky@gmx.net>"
 SPDX-License-Identifier = "Apache-2.0"
diff --git a/go.work b/go.work
index c163e32..1d7d7f0 100644
--- a/go.work
+++ b/go.work
@@ -3,4 +3,5 @@ go 1.26
 use (
 	.
 	./benchmark
+	./testing
 )
diff --git a/pgruntime/behavior.go b/pgruntime/behavior.go
new file mode 100644
index 0000000..3ffff1e
--- /dev/null
+++ b/pgruntime/behavior.go
@@ -0,0 +1,129 @@
+// SPDX-FileCopyrightText: 2026 Stefan Majewsky <majewsky@gmx.net>
+// SPDX-License-Identifier: Apache-2.0
+
+package pgruntime
+
+import (
+	"context"
+	"fmt"
+	"slices"
+
+	"go.xyrillian.de/gg/gsql"
+)
+
+// ConnectionBehavior contains configuration for [Connector.Connect] and [Connector.ConnectForTest].
+//
+// If Migrations is not nil, pgruntime will perform very basic handling for schema migrations.
+// Migrations must be given with the version number as key, and one or several DDL queries needed to reach that version from the previous version.
+// For example:
+//
+//	behavior.Migrations = map[int]string{
+//		1: `
+//			CREATE TABLE assets (
+//				id   BIGSERIAL PRIMARY KEY,
+//				name TEXT
+//			);
+//		`,
+//		2: `
+//			UPDATE assets SET name = 'unknown' WHERE name IS NULL;
+//			ALTER TABLE assets ALTER COLUMN name SET NOT NULL;
+//		`,
+//	}
+//
+// Versions need not start at 1 and need not be contiguous (so e.g. UNIX timestamps can be used as schema versions).
+// Schema migrations will be executed as follows:
+//
+//   - The table "schema_migrations" will be created with the same schema as used by [golang-migrate] if it does not exists (see [MigrationsSchema]).
+//     It will only ever contain one record, initially with version 0.
+//   - If Migrations contains entries with a version number larger than the one recorded in the database,
+//     pgruntime picks the first such migration by ascending version number, executes the DDL query, and increases the version number in "schema_migrations" accordingly.
+//
+// [golang-migrate]: https://pkg.go.dev/github.com/golang-migrate/migrate
+type ConnectionBehavior struct {
+	Migrations map[int64]string // or nil to skip schema_migrations
+}
+
+// MigrationsSchema defines the structure of the "schema_migrations" table used by pgruntime's schema migration handling.
+// It is the same table schema as used by [golang-migrate]'s postgres driver, to enable seamless migration from that library to pgruntime.
+// The "dirty" column is not used by pgruntime, and will always be set to FALSE for compatibility with golang-migrate.
+//
+// [golang-migrate]: https://pkg.go.dev/github.com/golang-migrate/migrate
+const MigrationsSchema = `CREATE TABLE IF NOT EXISTS schema_migrations (version BIGINT NOT NULL PRIMARY KEY, dirty BOOLEAN NOT NULL)`
+
+func (b ConnectionBehavior) applyTo(ctx context.Context, db gsql.ConnectionHandle) error {
+	if len(b.Migrations) > 0 {
+		err := applyMigrations(ctx, db, b.Migrations)
+		if err != nil {
+			return err
+		}
+	}
+	return nil
+}
+
+func applyMigrations(ctx context.Context, db gsql.ConnectionHandle, migrations map[int64]string) error {
+	// apply schema_migrations table schema
+	_, err := execQuery(ctx, db, MigrationsSchema, nil)
+	if err != nil {
+		return fmt.Errorf("could not apply schema_migrations table schema: %w", err)
+	}
+
+	// read schema_migrations table
+	rowCount, err := selectOneValue[int64](ctx, db, `SELECT COUNT(*) FROM schema_migrations`)
+	if err != nil {
+		return fmt.Errorf("could not check row count for schema_migrations: %w", err)
+	}
+	var (
+		currentVersion int64
+		dirty          bool
+	)
+	switch rowCount {
+	case 0:
+		currentVersion = 0
+		_, err = execQuery(ctx, db, `INSERT INTO schema_migrations (version, dirty) VALUES (0, FALSE)`, nil)
+		if err != nil {
+			return fmt.Errorf("could not initialize schema_migrations record: %w", err)
+		}
+	case 1:
+		err = queryRow(ctx, db, `SELECT version, dirty FROM schema_migrations`, nil, []any{&currentVersion, &dirty})
+		if err != nil {
+			return fmt.Errorf("could not read schema_migrations record: %w", err)
+		}
+	default:
+		if err != nil {
+			return fmt.Errorf("expected 1 record in schema_migrations table, but found %d records", rowCount)
+		}
+	}
+	if dirty {
+		// NOTE: defense in depth: this can never occur when only using pgruntime, but may occur when migrating from golang-migrate
+		return fmt.Errorf("schema_migrations is marked as dirty (version = %d)", currentVersion)
+	}
+
+	// find migrations to apply
+	var pendingVersions []int64
+	for version := range migrations {
+		if version > currentVersion {
+			pendingVersions = append(pendingVersions, version)
+		}
+	}
+	slices.Sort(pendingVersions)
+
+	// apply migrations
+	for _, version := range pendingVersions {
+		err := db.GSQLTransact(ctx, func(tx gsql.Handle) error {
+			_, err := execQuery(ctx, db, migrations[version], nil)
+			if err != nil {
+				return fmt.Errorf("could not execute schema migration: %w", err)
+			}
+			_, err = execQuery(ctx, db, `UPDATE schema_migrations SET version = $1, dirty = FALSE`, []any{version})
+			if err != nil {
+				return fmt.Errorf("could not update schema_migrations record: %w", err)
+			}
+			return nil
+		})
+		if err != nil {
+			return fmt.Errorf("while migrating to schema version %d: %w", version, err)
+		}
+	}
+
+	return nil
+}
diff --git a/pgruntime/connector.go b/pgruntime/connector.go
new file mode 100644
index 0000000..5276a26
--- /dev/null
+++ b/pgruntime/connector.go
@@ -0,0 +1,176 @@
+// SPDX-FileCopyrightText: 2026 Stefan Majewsky <majewsky@gmx.net>
+// SPDX-License-Identifier: Apache-2.0
+
+package pgruntime
+
+import (
+	"context"
+	"crypto/sha256"
+	"database/sql"
+	"encoding/base32"
+	"fmt"
+	"regexp"
+	"strings"
+
+	"go.xyrillian.de/gg/assert"
+	"go.xyrillian.de/gg/errext"
+	"go.xyrillian.de/gg/gsql"
+)
+
+// Connector describes how to connect to a PostgreSQL database given a [libpq-style connection URI].
+// This type acts as a dependency injection surface, abstracting the different ways in which different database drivers and libraries perform connection.
+//
+// [libpq-style connection URI]: https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING-URIS
+type Connector[T gsql.ConnectionHandle] func(context.Context, ConnectionTarget) (T, error)
+
+// StdConnector returns a [Connector] for database/sql drivers.
+// When used with [lib/pq], the driver name must be "postgres".
+func StdConnector(driverName string) Connector[*gsql.DB] {
+	return func(ctx context.Context, target ConnectionTarget) (*gsql.DB, error) {
+		u, err := target.IntoURL()
+		if err != nil {
+			return nil, err
+		}
+		db, err := sql.Open(driverName, u.String())
+		if err != nil {
+			return nil, err
+		}
+		return gsql.NewDB(db), nil
+	}
+}
+
+// Connect connects to a PostgreSQL database.
+func (c Connector[T]) Connect(ctx context.Context, target ConnectionTarget, behavior ConnectionBehavior) (T, error) {
+	var none T // shorthand for error return paths
+
+	db, err := c(ctx, target)
+	if err != nil {
+		return none, err
+	}
+	err = behavior.applyTo(ctx, db)
+	if err != nil {
+		return none, errext.WithCleanup(err, "db.Close", db.GSQLClose(ctx))
+	}
+	return db, nil
+}
+
+// TestSetupOption is an optional behavior that can be given to [Connector.ConnectForTest].
+type TestSetupOption func(*testSetupParams)
+
+type testSetupParams struct {
+	DatabaseName string
+}
+
+// OverrideDatabaseName is a [TestSetupOption] that picks a different database name than the default of t.Name().
+//
+// This is only necessary if a single test needs to use multiple database connections at the same time,
+// e.g. to simulate two separate deployments of the application next to each other.
+func OverrideDatabaseName(dbName string) TestSetupOption {
+	return func(params *testSetupParams) {
+		params.DatabaseName = dbName
+	}
+}
+
+// ConnectForTest connects to the test database server managed by [WithTestDB].
+//
+// Each test will run in its own separate database (whose name is the same as t.Name()),
+// so it is safe to mark tests as t.Parallel() to run multiple tests within the same package concurrently.
+// ConnectForTest will always drop and recreate the database to ensure deterministic test behavior.
+//
+// Some tests require setting up multiple separate connections to the same database.
+// The second return argument can be be used with [Connector.Connect] to obtain additional connections as needed.
+func (c Connector[T]) ConnectForTest(t assert.TestingTB, behavior ConnectionBehavior, opts ...TestSetupOption) (T, ConnectionTarget) {
+	t.Helper()
+	ctx := t.Context()
+
+	params := testSetupParams{
+		DatabaseName: t.Name(),
+	}
+	for _, opt := range opts {
+		opt(&params)
+	}
+
+	// normalize t.Name() into an acceptable database name for PostgreSQL
+	//   - only alphanumerics and underscore -> replace all other symbols with _
+	//   - max 63 chars -> if overflown, truncate and append a short digest to hopefully make it unique
+	dbName := strings.ToLower(params.DatabaseName)
+	dbName = regexp.MustCompile(`[^a-z_]`).ReplaceAllString(dbName, "_")
+	if len(dbName) > 63 {
+		digest := sha256.Sum256([]byte(params.DatabaseName))
+		encoded := base32.HexEncoding.EncodeToString(digest[:])
+		dbName = dbName[0:53] + "__" + strings.ToLower(encoded[0:8])
+	}
+
+	// connect to "postgres" database for the CREATE DATABASE query (if necessary)
+	target := ConnectionTarget{
+		HostName:          "127.0.0.1",
+		Port:              testdbPort,
+		UserName:          testdbUserName,
+		DatabaseName:      "postgres",
+		ConnectionOptions: "sslmode=disable",
+	}
+	adminDB, err := c(ctx, target)
+	if err != nil {
+		t.Fatal(err.Error() + " (if this error is about the database server not running, check if your TestMain() calls pgruntime.WithTestDB())")
+	}
+	err = createDatabaseIfMissing(ctx, adminDB, dbName)
+	err = errext.WithCleanup(err, "db.Close", adminDB.GSQLClose(ctx))
+	if err != nil {
+		t.Fatal(err.Error())
+	}
+
+	// connect to actual test database
+	target.DatabaseName = dbName
+	testDB, err := c.Connect(ctx, target, behavior)
+	if err != nil {
+		t.Fatal(err.Error())
+	}
+	err = resetTestDatabase(ctx, testDB, params, behavior)
+	if err != nil {
+		err = errext.WithCleanup(err, "db.Close", testDB.GSQLClose(ctx))
+		t.Fatal(err.Error())
+	}
+	return testDB, target
+}
+
+func createDatabaseIfMissing(ctx context.Context, db gsql.Handle, dbName string) error {
+	// check if database exists
+	exists, err := selectOneValue[bool](ctx, db, `SELECT COUNT(*) > 0 FROM pg_catalog.pg_database WHERE datname = $1`, dbName)
+	if err != nil {
+		return fmt.Errorf("while reading from pg_catalog.pg_database: %w", err)
+	}
+
+	// create database if necessary
+	if !exists {
+		_, err = execQuery(ctx, db, "CREATE DATABASE "+quoteIdentifier(dbName), nil)
+		if err != nil {
+			return fmt.Errorf("during CREATE DATABASE: %w", err)
+		}
+	}
+
+	return nil
+}
+
+func resetTestDatabase(ctx context.Context, db gsql.Handle, params testSetupParams, behavior ConnectionBehavior) error {
+	// enumerate all tables that need to be truncated (all tables that are not managed by pgruntime)
+	condition := `table_schema = 'public' AND table_type = 'BASE TABLE'`
+	if len(behavior.Migrations) > 0 {
+		condition += ` AND table_name != 'schema_migrations'`
+	}
+	query := fmt.Sprintf(`SELECT quote_ident(table_name) FROM information_schema.tables WHERE %s ORDER BY table_name`, condition)
+	quotedTableNames, err := selectSeveralValues[string](ctx, db, query)
+	if err != nil {
+		return fmt.Errorf("while listing tables to truncate: %w", err)
+	}
+
+	// truncate all tables at once
+	if len(quotedTableNames) > 0 {
+		query = fmt.Sprintf(`TRUNCATE %s RESTART IDENTITY CASCADE`, strings.Join(quotedTableNames, ", "))
+		_, err = execQuery(ctx, db, query, nil)
+		if err != nil {
+			return fmt.Errorf("during %s: %w", query, err)
+		}
+	}
+
+	return nil
+}
diff --git a/pgruntime/helpers.go b/pgruntime/helpers.go
new file mode 100644
index 0000000..1019e6e
--- /dev/null
+++ b/pgruntime/helpers.go
@@ -0,0 +1,74 @@
+// SPDX-FileCopyrightText: 2026 Stefan Majewsky <majewsky@gmx.net>
+// SPDX-License-Identifier: Apache-2.0
+
+package pgruntime
+
+import (
+	"context"
+	"database/sql"
+	"strings"
+
+	"go.xyrillian.de/gg/errext"
+	"go.xyrillian.de/gg/gsql"
+)
+
+// Convenience function for executing a one-off SQL query returning no rows.
+// TODO: move to gsql
+func execQuery(ctx context.Context, db gsql.Handle, query string, args []any) (sql.Result, error) {
+	stmt, err := db.GSQLPrepare(ctx, query, false)
+	if err != nil {
+		return nil, err
+	}
+	result, err := stmt.Exec(ctx, args)
+	return result, errext.WithCleanup(err, "stmt.Close", stmt.Close())
+}
+
+// Convenience function for executing a one-off SQL query returning one row.
+func queryRow(ctx context.Context, db gsql.Handle, query string, args, slots []any) error {
+	stmt, err := db.GSQLPrepare(ctx, query, false)
+	if err != nil {
+		return err
+	}
+	err = stmt.QueryRow(ctx, args, slots)
+	return errext.WithCleanup(err, "stmt.Close", stmt.Close())
+}
+
+// Convenience function for executing a one-off SQL query returning one value.
+// TODO: move to gsql
+func selectOneValue[T any](ctx context.Context, db gsql.Handle, query string, args ...any) (T, error) {
+	stmt, err := db.GSQLPrepare(ctx, query, false)
+	if err != nil {
+		var none T
+		return none, err
+	}
+
+	var result T
+	err = stmt.QueryRow(ctx, args, []any{&result})
+	return result, errext.WithCleanup(err, "stmt.Close", stmt.Close())
+}
+
+// Convenience function for executing a one-off SQL query returning several single-column rows.
+// TODO: move to gsql (and also add ForeachValue with a callback instead of a slice return, maybe even ForeachPair and ForeachTriple)
+func selectSeveralValues[T any](ctx context.Context, db gsql.Handle, query string, args ...any) ([]T, error) {
+	rows, err := db.GSQLQuery(ctx, query, args)
+	if err != nil {
+		return nil, err
+	}
+	var result []T
+	for rows.Next() {
+		// TODO: this should share growRecordSlice() from Oblast to optimize allocations
+		var value T
+		err := rows.Scan(&value)
+		if err != nil {
+			return nil, errext.WithCleanup(err, "rows.Close", rows.Close())
+		}
+		result = append(result, value)
+	}
+	return result, errext.WithCleanup(nil, "rows.Err", rows.Err())
+}
+
+// Convenience function for preparing an identifier that needs to be inserted into a query verbatim
+// (e.g. a database name for CREATE DATABASE).
+func quoteIdentifier(name string) string {
+	return `"` + strings.ReplaceAll(name, `"`, `""`) + `"`
+}
diff --git a/pgruntime/pgruntime.go b/pgruntime/pgruntime.go
new file mode 100644
index 0000000..0283eaa
--- /dev/null
+++ b/pgruntime/pgruntime.go
@@ -0,0 +1,29 @@
+// SPDX-FileCopyrightText: 2026 Stefan Majewsky <majewsky@gmx.net>
+// SPDX-License-Identifier: Apache-2.0
+
+// Package pgruntime provides connection handling for PostgreSQL databases,
+// including optional support for database migrations and self-contained test DB instances.
+// It can be used with any PostgreSQL data driver, including [lib/pq] and [pgx].
+//
+// # Basic usage
+//
+// In productive scenarios, build a [ConnectionTarget] instance from configuration values or command-line arguments, and call [Connector.Connect].
+// In test scenarios, call [Connector.ConnectForTest].
+//
+// Both situations require a [Connector] instance:
+// When using an std-compatible driver like [lib/pq], use [StdConnector].
+// Otherwise, a custom [Connector] implementation must be supplied.
+// For [pgx], the connectors from [gg-pgx] may be used.
+//
+// # Legacy
+//
+// This is a clean-room reimplementation of one half of [easypg] with several interface improvements and cleanups, most notably:
+//   - The hard dependency on lib/pq has been removed.
+//   - Support for creating databases on first use has been removed (except in ConnectForTest).
+//   - The reset logic in ConnectForTest is completely reworked and massively simplified.
+//
+// [easypg]: https://pkg.go.dev/github.com/sapcc/go-bits/easypg
+// [lib/pq]: https://pkg.go.dev/github.com/lib/pq
+// [pgx]: https://pkg.go.dev/github.com/jackc/pgx/v5
+// [gg-pgx]: https://git.xyrillian.de/go-gg-pgx/
+package pgruntime
diff --git a/pgruntime/target.go b/pgruntime/target.go
new file mode 100644
index 0000000..a03c8c5
--- /dev/null
+++ b/pgruntime/target.go
@@ -0,0 +1,144 @@
+// SPDX-FileCopyrightText: 2026 Stefan Majewsky <majewsky@gmx.net>
+// SPDX-License-Identifier: Apache-2.0
+
+package pgruntime
+
+import (
+	"fmt"
+	"maps"
+	"net"
+	"net/url"
+	"strings"
+)
+
+// ConnectionTarget describes the location of and credentials and other connection options for the database server that [Connector.Connect] connects to.
+//
+//   - The HostName, UserName and DatabaseName fields are required. All other fields are optional.
+//   - The ConnectionOptions field is intended for options coming in via config.
+//     Its value must be formatted in the syntax accepted by [url.ParseQuery].
+//   - The ExtraConnectionOptions field is intended for options coming in via code.
+//   - Both ConnectionOptions fields accept all query parameters that are allowed in [libpq-style connection URIs].
+//
+// This type is intended to be retrieved from environment variables, for example:
+//
+//	ct := pgruntime.ConnectionTarget{
+//		HostName:          cmp.Or(os.Getenv("FOOBAR_DB_HOSTNAME"), "localhost"),
+//		Port:              cmp.Or(os.Getenv("FOOBAR_DB_PORT"), "5432"),
+//		UserName:          cmp.Or(os.Getenv("FOOBAR_DB_USERNAME"), "postgres"),
+//		Password:          os.Getenv("FOOBAR_DB_PASSWORD"),
+//		ConnectionOptions: os.Getenv("FOOBAR_DB_CONNECTION_OPTIONS"),
+//		DatabaseName:      cmp.Or(os.Getenv("FOOBAR_DB_NAME"), "foobar"),
+//	}))
+//
+// [libpq-style connection URIs]: https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING-URIS
+type ConnectionTarget struct {
+	HostName               string
+	Port                   string
+	UserName               string
+	Password               string // default: <no password>
+	DatabaseName           string
+	ConnectionOptions      string
+	ExtraConnectionOptions url.Values
+}
+
+const (
+	connectionURISchemeShort = "postgres"
+	connectionURISchemeLong  = "postgresql"
+)
+
+// ParseConnectionTargetFromURL parses a [libpq-style connection URI] into a [ConnectionTarget] instance.
+// If there are connection options, they will be placed in the ConnectionOptions field, not in ExtraConnectionOptions.
+//
+// The special libpq syntax with multiple host:port pairs is not supported by this function and will result in an error.
+//
+// [libpq-style connection URI]: https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING-URIS
+func ParseConnectionTargetFromURL(u *url.URL) (ConnectionTarget, error) {
+	var (
+		result ConnectionTarget
+		none   ConnectionTarget // shorthand for error return paths
+	)
+
+	if u.Scheme != connectionURISchemeShort && u.Scheme != connectionURISchemeLong {
+		return none, fmt.Errorf(`in ParseConnectionTargetFromURL: expected scheme %q or %q, but got %q`, connectionURISchemeShort, connectionURISchemeLong, u.Scheme)
+	}
+	if u.Opaque != "" {
+		return none, fmt.Errorf(`in ParseConnectionTargetFromURL: expected authority component, but got rootless path %q`, u.Opaque)
+	}
+	if u.Fragment != "" {
+		return none, fmt.Errorf(`in ParseConnectionTargetFromURL: unexpected fragment %q`, u.Fragment)
+	}
+
+	var err error
+	if strings.Contains(u.Host, ":") {
+		result.HostName, result.Port, err = net.SplitHostPort(u.Host)
+		if err != nil {
+			return none, fmt.Errorf(`in ParseConnectionTargetFromURL: malformed host %q`, u.Host)
+		}
+	} else {
+		result.HostName = u.Host
+	}
+	result.UserName = u.User.Username()
+	result.Password, _ = u.User.Password()
+
+	result.DatabaseName = strings.TrimPrefix(u.Path, "/")
+	if strings.Contains(result.DatabaseName, "/") {
+		return none, fmt.Errorf(`in ParseConnectionTargetFromURL: malformed database name %q`, result.DatabaseName)
+	}
+
+	if u.RawQuery != "" {
+		_, err := url.ParseQuery(u.RawQuery)
+		if err != nil {
+			return none, fmt.Errorf(`in ParseConnectionTargetFromURL: malformed connection options: %w`, err)
+		}
+		result.ConnectionOptions = u.RawQuery
+	}
+
+	return result, nil
+}
+
+// IntoURL formats the ConnectionTarget as a libpq-style connection URI.
+func (t ConnectionTarget) IntoURL() (*url.URL, error) {
+	rawQuery, err := mergeConnectionOptions(t.ConnectionOptions, t.ExtraConnectionOptions)
+	if err != nil {
+		return nil, fmt.Errorf("in ConnectionTarget.IntoURL: malformed connection options: %w", err)
+	}
+	return &url.URL{
+		Scheme:   connectionURISchemeShort, // could also use `connectionURISchemeLong`, but go-bits/easypg made this choice
+		User:     t.buildUserInfo(),
+		Host:     t.buildHostPort(),
+		Path:     "/" + t.DatabaseName,
+		RawQuery: rawQuery,
+	}, nil
+}
+
+func (t ConnectionTarget) buildUserInfo() *url.Userinfo {
+	if t.Password == "" {
+		return url.User(t.UserName)
+	} else {
+		return url.UserPassword(t.UserName, t.Password)
+	}
+}
+
+func (t ConnectionTarget) buildHostPort() string {
+	if t.Port == "" {
+		return t.HostName
+	} else {
+		return net.JoinHostPort(t.HostName, t.Port)
+	}
+}
+
+func mergeConnectionOptions(opts string, extraOpts url.Values) (string, error) {
+	if len(extraOpts) == 0 {
+		return opts, nil
+	}
+	if opts == "" {
+		return extraOpts.Encode(), nil
+	}
+
+	values, err := url.ParseQuery(opts)
+	if err != nil {
+		return "", err
+	}
+	maps.Copy(values, extraOpts)
+	return values.Encode(), nil
+}
diff --git a/pgruntime/target_test.go b/pgruntime/target_test.go
new file mode 100644
index 0000000..d62721c
--- /dev/null
+++ b/pgruntime/target_test.go
@@ -0,0 +1,111 @@
+// SPDX-FileCopyrightText: 2026 Stefan Majewsky <majewsky@gmx.net>
+// SPDX-License-Identifier: Apache-2.0
+
+package pgruntime_test
+
+import (
+	"net/url"
+	"strings"
+	"testing"
+
+	"go.xyrillian.de/gg/assert"
+	"go.xyrillian.de/gg/pgruntime"
+)
+
+func TestParseConnectionTargetSuccess(t *testing.T) {
+	testCases := map[string]pgruntime.ConnectionTarget{
+		// minimal case: just the required fields are set
+		`postgresql://alice@localhost/bookstore`: {
+			HostName:     "localhost",
+			UserName:     "alice",
+			DatabaseName: "bookstore",
+		},
+		// maximal case: all required fields are set
+		`postgres://alice:swordfish@db.example.com:5432/bookstore?sslmode=prefer&application_name=frontdesk`: {
+			HostName:          "db.example.com",
+			Port:              "5432",
+			UserName:          "alice",
+			Password:          "swordfish",
+			DatabaseName:      "bookstore",
+			ConnectionOptions: "sslmode=prefer&application_name=frontdesk",
+		},
+	}
+
+	for input, expected := range testCases {
+		t.Run(input, func(t *testing.T) {
+			u, err := url.Parse(input)
+			if !assert.ErrEqual(t, err, nil) {
+				t.FailNow()
+			}
+			parsed, err := pgruntime.ParseConnectionTargetFromURL(u)
+			if !assert.ErrEqual(t, err, nil) {
+				t.FailNow()
+			}
+			assert.Equal(t, parsed, expected)
+			serialized, err := parsed.IntoURL()
+			if !assert.ErrEqual(t, err, nil) {
+				t.FailNow()
+			}
+			// IntoURL() always uses postgres://, so this is not always an exact roundtrip
+			assert.Equal(t, serialized.String(), strings.ReplaceAll(input, "postgresql:", "postgres:"))
+		})
+	}
+}
+
+func TestParseConnectionTargetFailure(t *testing.T) {
+	testCases := map[string]string{
+		`https://alice@db.example.com/foo?sslmode=prefer`:            `expected scheme "postgres" or "postgresql", but got "https"`,
+		`postgres:foo?sslmode=prefer`:                                `expected authority component, but got rootless path "foo"`,
+		`postgres://alice@db.example.com/foo#sslmode=prefer`:         `unexpected fragment "sslmode=prefer"`,
+		`postgres://alice@db.example.com::5432/foo?sslmode=prefer`:   `malformed host "db.example.com::5432"`,
+		`postgres://alice@db.example.com/foo/bar?sslmode=prefer`:     `malformed database name "foo/bar"`,
+		`postgres://alice@db.example.com/foo?sslmode=prefer;foo=bar`: `malformed connection options: invalid semicolon separator in query`,
+	}
+	for input, expected := range testCases {
+		t.Run(input, func(t *testing.T) {
+			u, err := url.Parse(input)
+			if !assert.ErrEqual(t, err, nil) {
+				t.FailNow()
+			}
+			_, err = pgruntime.ParseConnectionTargetFromURL(u)
+			assert.ErrEqual(t, err, "in ParseConnectionTargetFromURL: "+expected)
+		})
+	}
+}
+
+func TestSerializeConnectionOptions(t *testing.T) {
+	ct := pgruntime.ConnectionTarget{
+		HostName:     "localhost",
+		UserName:     "alice",
+		DatabaseName: "bookstore",
+	}
+
+	// cannot merge ConnectionOptions if the string part is malformed
+	ct.ConnectionOptions = `sslmode=prefer;foo=bar`
+	ct.ExtraConnectionOptions = url.Values{"application_name": {"frontdesk"}}
+	_, err := ct.IntoURL()
+	assert.ErrEqual(t, err, `in ConnectionTarget.IntoURL: malformed connection options: invalid semicolon separator in query`)
+
+	// successful trivial merges
+	ct.ConnectionOptions = `sslmode=prefer`
+	ct.ExtraConnectionOptions = nil
+	u, err := ct.IntoURL()
+	if assert.ErrEqual(t, err, nil) {
+		assert.Equal(t, u.RawQuery, `sslmode=prefer`)
+	}
+
+	ct.ConnectionOptions = ""
+	ct.ExtraConnectionOptions = url.Values{"application_name": {"frontdesk"}}
+	u, err = ct.IntoURL()
+	if assert.ErrEqual(t, err, nil) {
+		assert.Equal(t, u.RawQuery, `application_name=frontdesk`)
+	}
+
+	// successful complex merge
+	ct.ConnectionOptions = `sslmode=prefer`
+	ct.ExtraConnectionOptions = url.Values{"application_name": {"frontdesk"}}
+	u, err = ct.IntoURL()
+	if assert.ErrEqual(t, err, nil) {
+		assert.Equal(t, u.RawQuery, `application_name=frontdesk&sslmode=prefer`)
+	}
+}
diff --git a/pgruntime/testdb.go b/pgruntime/testdb.go
new file mode 100644
index 0000000..808784c
--- /dev/null
+++ b/pgruntime/testdb.go
@@ -0,0 +1,313 @@
+// SPDX-FileCopyrightText: 2026 Stefan Majewsky <majewsky@gmx.net>
+// SPDX-License-Identifier: Apache-2.0
+
+package pgruntime
+
+import (
+	"bytes"
+	_ "embed"
+	"fmt"
+	"os"
+	"os/exec"
+	"path/filepath"
+	"runtime/debug"
+	"strings"
+	"testing"
+	"time"
+
+	. "go.xyrillian.de/gg/option"
+)
+
+const (
+	// ConnectionTarget attributes for test DB
+	testdbUserName = "postgres"
+	testdbPort     = "54320"
+
+	// paths below $MODULE_ROOT/.testdb
+	testdbDatadirLocation = "datadir"
+	testdbVersionLocation = "datadir/PG_VERSION"
+	testdbRuntimeLocation = "run"
+	testdbLogfileLocation = "run/postgresql.log"
+	testdbPidfileLocation = "run/pid"
+)
+
+//go:embed tool_template.sh
+var toolScriptTemplate []byte
+
+// WithTestDB spawns a PostgreSQL database for the duration of a `go test` run.
+// Its data directory, configuration and logs are stored in the ".testdb" directory below the module root dir (next to the "go.mod" file).
+//   - To inspect it manually, use one of the helper scripts in the ".testdb" directory, e.g. ".testdb/psql.sh".
+//   - It is currently not supported to run tests for multiple packages concurrently. Run "go test" with "-p 1" when running tests for multiple packages.
+//   - The "/.testdb" directory should be added to your repository's .gitignore rules.
+//
+// This function takes a [testing.M] because it is supposed to be called from TestMain().
+// This ensures that its cleanup phase shuts down the database server after all tests have been executed.
+// Add a TestMain() like this to each package that calls [Connector.ConnectForTest]:
+//
+//	func TestMain(m *testing.M) {
+//		pgruntime.WithTestDB(m, m.Run)
+//	}
+//
+// If the environment variable PGRUNTIME_TESTDB_PATH is set, this path will be used instead of ".testdb" below the module root dir.
+// This can be used e.g. to place the testdb on a tmpfs to avoid slow disk IO.
+// Doing so can reduce test execution times in realistic scenarios by as much as two thirds.
+// For clarity, a symlink will be created at the usual ".testdb" location, pointing to the actual path.
+//
+// If the value of $PGRUNTIME_TESTDB_PATH contains the string "%MODULE%" (e.g. "/tmp/testdb/%MODULE%"),
+// this string will be replaced by the name of the main module.
+// Using this placeholder, you can set this variable in your shell rc file once,
+// but the test databases of different applications will still be neatly separated.
+func WithTestDB(m *testing.M, action func() int) int {
+	// NOTE: Lifting the "-p 1" restriction is tough because tests for multiple packages are
+	//       compiled into one test binary per package, and thus execute in separate processes.
+	//       We would need some way for all these binaries to coordinate on only shutting down
+	//       the server once everyone is finished with their tests. The obvious choices for
+	//       coordination mechanisms (IPC or file locking) are platform-specific and would
+	//       require introducing dependencies (ugh) or adding platform-specific code (ugh).
+
+	result, err := withTestDB(m, action)
+	if err == nil {
+		return result
+	} else {
+		panic(err.Error())
+	}
+}
+
+func withTestDB(m *testing.M, action func() int) (_ int, returnedError error) {
+	testdbPath, err := findTestdbPath()
+	if err != nil {
+		return 0, err
+	}
+	err = stopDBIfLingering(testdbPath)
+	if err != nil {
+		return 0, err
+	}
+	err = wipeDBIfMajorUpgrade(testdbPath)
+	if err != nil {
+		return 0, err
+	}
+	err = initDBIfNecessary(testdbPath)
+	if err != nil {
+		return 0, err
+	}
+	err = startDB(testdbPath)
+	if err != nil {
+		return 0, err
+	}
+	defer func() {
+		returnedError = stopDB(testdbPath)
+	}()
+	return action(), nil
+}
+
+func findTestdbPath() (string, error) {
+	// find `$REPO_ROOT/.testdb`
+	cwd, err := os.Getwd()
+	if err == nil {
+		cwd, err = filepath.Abs(cwd)
+	}
+	if err != nil {
+		return "", fmt.Errorf("could not find working directory: %w", err)
+	}
+	result, err := findModuleRootDir(cwd)
+	if err != nil {
+		return "", fmt.Errorf("could not find module root directory: %w", err)
+	}
+	rootPath, ok := result.Unpack()
+	if !ok {
+		return "", fmt.Errorf("neither the working directory %q nor any of its parents contain a go.mod file", cwd)
+	}
+	testdbPath := filepath.Join(rootPath, ".testdb")
+
+	// find override path, if any
+	overridePath := os.Getenv("PGRUNTIME_TESTDB_PATH")
+	if overridePath == "" {
+		return testdbPath, nil
+	}
+	if strings.Contains(overridePath, "%MODULE%") {
+		info, ok := debug.ReadBuildInfo()
+		if !ok {
+			panic("$PGRUNTIME_TESTDB_PATH contains %MODULE% placeholder, but binary was not built with module support")
+		}
+		overridePath = strings.ReplaceAll(overridePath, "%MODULE%", info.Main.Path)
+	}
+
+	// if there is an override path, report the override path (so that initDBIfNecessary can create it),
+	// but put a symlink at the standard path for convenient access
+	err = os.Symlink(overridePath, testdbPath)
+	if os.IsExist(err) {
+		// do not complain if the symlink already exists
+		err = nil
+	}
+	return overridePath, err
+}
+
+func findModuleRootDir(dirPath string) (Option[string], error) {
+	_, err := os.Stat(filepath.Join(dirPath, "go.mod"))
+	switch {
+	case err == nil:
+		return Some(dirPath), nil
+	case os.IsNotExist(err):
+		parentPath := filepath.Dir(dirPath)
+		if parentPath == dirPath {
+			return None[string](), nil
+		} else {
+			return findModuleRootDir(parentPath)
+		}
+	default:
+		return None[string](), err
+	}
+}
+
+func wipeDBIfMajorUpgrade(testdbPath string) error {
+	// check datadir/PG_VERSION for test database
+	buf, err := os.ReadFile(filepath.Join(testdbPath, testdbVersionLocation))
+	switch {
+	case err == nil:
+		// continue below
+	case os.IsNotExist(err):
+		// DB not initialized yet -> nothing to do
+		return nil
+	default:
+		return err
+	}
+	serverVersion := strings.TrimSpace(string(buf))
+
+	// check installed PostgreSQL version via `psql --version`
+	cmd := exec.Command("psql", "--version")
+	cmd.Stderr = os.Stderr
+	buf, err = cmd.Output()
+	if err != nil {
+		return fmt.Errorf("could not run `psql --version`: %w", err)
+	}
+
+	// output from `psql --version` should look like e.g. "psql (PostgreSQL) 18.4" -> we want just the version number part
+	clientOutput := strings.TrimSpace(string(buf))
+	fields := strings.Fields(clientOutput)
+	if len(fields) != 3 || fields[0] != "psql" || fields[1] != "(PostgreSQL)" {
+		return fmt.Errorf("unexpected output from `psql --version`: %q", clientOutput)
+	}
+	clientVersion := fields[2]
+
+	// wipe DB on version mismatch (`serverVersion` will be a major version like "18", and `clientVersion` a minor version like "18.4")
+	if strings.HasPrefix(clientVersion, serverVersion) {
+		return nil
+	}
+	return os.RemoveAll(testdbPath) // wipe everything, including the .testdb folder, to make initDBIfNecessary() work
+}
+
+func initDBIfNecessary(testdbPath string) error {
+	// check if already initialized
+	fi, err := os.Stat(testdbPath)
+	switch {
+	case err == nil:
+		if fi.IsDir() {
+			return nil
+		} else {
+			return fmt.Errorf("unexpected type of filesystem entry on %q (expected a directory)", testdbPath)
+		}
+	case os.IsNotExist(err):
+		// need to run initdb, continue below
+	default:
+		return err
+	}
+
+	// create scaffold
+	var (
+		datadirPath = filepath.Join(testdbPath, testdbDatadirLocation)
+		runtimePath = filepath.Join(testdbPath, testdbRuntimeLocation)
+	)
+	err = os.MkdirAll(datadirPath, 0777)
+	if err != nil {
+		return err
+	}
+	err = os.MkdirAll(runtimePath, 0777)
+	if err != nil {
+		return err
+	}
+	for _, toolName := range []string{"pgcli", "pg_dump", "psql"} {
+		buf := bytes.ReplaceAll(toolScriptTemplate, []byte("$COMMAND"), []byte(toolName))
+		err := os.WriteFile(filepath.Join(testdbPath, toolName+".sh"), buf, 0777)
+		if err != nil {
+			return err
+		}
+	}
+
+	// run initdb
+	cmd := exec.Command("initdb",
+		"--pgdata="+datadirPath,
+		"--no-locale",
+		"--auth=trust", "--username="+testdbUserName,
+		"--set", "external_pid_file="+filepath.Join(testdbPath, testdbPidfileLocation),
+		"--set", "max_connections=250",
+		"--set", "port="+testdbPort,
+		"--set", "unix_socket_directories="+runtimePath,
+	)
+	cmd.Dir = testdbPath
+	cmd.Stdout = os.Stdout
+	cmd.Stderr = os.Stderr
+	err = cmd.Run()
+	if err != nil {
+		return fmt.Errorf("could not run `initdb`: %w", err)
+	}
+	return nil
+}
+
+func startDB(testdbPath string) error {
+	// truncate logfile
+	logfilePath := filepath.Join(testdbPath, testdbLogfileLocation)
+	err := os.Remove(logfilePath)
+	if err != nil && !os.IsNotExist(err) {
+		return err
+	}
+
+	// run `pg_ctl start`
+	cmd := exec.Command("pg_ctl",
+		"start", "--wait", "--silent",
+		"-D", filepath.Join(testdbPath, testdbDatadirLocation),
+		"-l", logfilePath,
+	)
+	cmd.Dir = testdbPath
+	cmd.Stdout = os.Stdout
+	cmd.Stderr = os.Stderr
+	err = cmd.Run()
+	if err != nil {
+		return fmt.Errorf("could not run `pg_ctl start`: %w", err)
+	}
+	return nil
+}
+
+func stopDB(testdbPath string) error {
+	cmd := exec.Command("pg_ctl",
+		"stop", "--wait", "--silent",
+		"-D", filepath.Join(testdbPath, testdbDatadirLocation),
+	)
+	cmd.Dir = testdbPath
+	cmd.Stdout = os.Stdout
+	cmd.Stderr = os.Stderr
+	err := cmd.Run()
+	if err != nil {
+		return fmt.Errorf("could not run `pg_ctl stop`: %w", err)
+	}
+	return nil
+}
+
+func stopDBIfLingering(testdbPath string) error {
+	_, err := os.Stat(filepath.Join(testdbPath, testdbPidfileLocation))
+	switch {
+	case err == nil:
+		// looks like a previous instance of this DB is still running -> restart it
+		// (we do not just reuse an instance started by someone else because they might terminate it at any point;
+		// better to make them fail loudly right now)
+		err = stopDB(testdbPath)
+		if err != nil {
+			return err
+		}
+		time.Sleep(time.Second / 2) // give them some time to blow up before starting the DB back up
+		return nil
+	case os.IsNotExist(err):
+		return nil // nothing to do, DB is not running
+	default:
+		return err
+	}
+}
diff --git a/pgruntime/tool_template.sh b/pgruntime/tool_template.sh
new file mode 100644
index 0000000..7e3827f
--- /dev/null
+++ b/pgruntime/tool_template.sh
@@ -0,0 +1,14 @@
+#!/usr/bin/env bash
+set -euo pipefail
+cd "$(dirname "$0")"
+
+stop_postgres() {
+	EXIT_CODE=$?
+	pg_ctl stop --wait --silent -D datadir
+	exit "${EXIT_CODE}"
+}
+trap stop_postgres EXIT INT TERM
+
+rm -f -- run/postgresql.log
+pg_ctl start --wait --silent -D datadir -l run/postgresql.log
+$COMMAND -U postgres -h 127.0.0.1 -p 54320 "$@"
diff --git a/testing/go.mod b/testing/go.mod
new file mode 100644
index 0000000..daf64dc
--- /dev/null
+++ b/testing/go.mod
@@ -0,0 +1,8 @@
+module go.xyrillian.de/gg/testing
+
+go 1.26
+
+require (
+	github.com/lib/pq v1.12.3
+	go.xyrillian.de/gg v1.12.1-0.20260731210839-e26a214de395
+)
diff --git a/testing/go.sum b/testing/go.sum
new file mode 100644
index 0000000..df1974e
--- /dev/null
+++ b/testing/go.sum
@@ -0,0 +1,4 @@
+github.com/lib/pq v1.12.3 h1:tTWxr2YLKwIvK90ZXEw8GP7UFHtcbTtty8zsI+YjrfQ=
+github.com/lib/pq v1.12.3/go.mod h1:/p+8NSbOcwzAEI7wiMXFlgydTwcgTr3OSKMsD2BitpA=
+go.xyrillian.de/gg v1.12.1-0.20260731210839-e26a214de395 h1:nA6DhnjgGw1sg+Piv4xZupuoD0dM+x4Fyd91Gi7XgOo=
+go.xyrillian.de/gg v1.12.1-0.20260731210839-e26a214de395/go.mod h1:DoO4fQSWIrBRlNlCjVyrYM0kAEBt/Jg2GkMH+cGRZ0k=
diff --git a/testing/pgruntime/connect_test.go b/testing/pgruntime/connect_test.go
new file mode 100644
index 0000000..1073791
--- /dev/null
+++ b/testing/pgruntime/connect_test.go
@@ -0,0 +1,59 @@
+// SPDX-FileCopyrightText: 2026 Stefan Majewsky <majewsky@gmx.net>
+// SPDX-License-Identifier: Apache-2.0
+
+package pgruntime_test
+
+import (
+	"testing"
+
+	"go.xyrillian.de/gg/assert"
+)
+
+func TestMultipleConnectionsToSameDB(t *testing.T) {
+	ctx := t.Context()
+
+	// connect once, create a fresh table and a record
+	db1, target := connector.ConnectForTest(t, defaultBehavior)
+	for _, query := range []string{
+		`DROP TABLE IF EXISTS objects`,
+		`CREATE TABLE objects (id BIGSERIAL PRIMARY KEY, name TEXT NOT NULL)`,
+		`INSERT INTO objects (name) VALUES ('foo')`, // -> id = 1
+		`INSERT INTO objects (name) VALUES ('bar')`, // -> id = 2
+	} {
+		_, err := execQuery(ctx, db1, query, nil)
+		if err != nil {
+			t.Fatalf("in query %q: %s", query, err.Error())
+		}
+	}
+
+	// a second connection made with Connect() should see that data
+	db2, err := connector.Connect(ctx, target, defaultBehavior)
+	if err != nil {
+		t.Fatal(err.Error())
+	}
+	barID, err := selectOneValue[int64](ctx, db2, `SELECT id FROM objects WHERE name = $1`, "bar")
+	if assert.ErrEqual(t, err, nil) {
+		assert.Equal(t, barID, 2)
+	}
+
+	// another connection with ConnectForTest() should truncate all tables and reset all sequences
+	db3, target2 := connector.ConnectForTest(t, defaultBehavior)
+	assert.Equal(t, target, target2)
+	var count int64
+	err = queryRow(ctx, db3, `SELECT COUNT(*) FROM objects`, nil, []any{&count})
+	if assert.ErrEqual(t, err, nil) {
+		assert.Equal(t, count, 0)
+	}
+	nextID, err := selectOneValue[int64](ctx, db3, `INSERT INTO objects (name) VALUES ($1) RETURNING id`, "qux")
+	if assert.ErrEqual(t, err, nil) {
+		assert.Equal(t, nextID, 1) // sequence was reset and starts at 1 again
+	}
+}
+
+func TestOverlongDatabaseName(t *testing.T) {
+	t.Run("that is so very long and ridiculous oh my god how is it still going wtf", func(t *testing.T) {
+		_, target := connector.ConnectForTest(t, defaultBehavior)
+		const expectedPrefix = "testoverlongdatabasename_that_is_so_very_long_and_rid__"
+		assert.Equal(t, target.DatabaseName, expectedPrefix+target.DatabaseName[len(expectedPrefix):63])
+	})
+}
diff --git a/testing/pgruntime/helpers_test.go b/testing/pgruntime/helpers_test.go
new file mode 100644
index 0000000..ca4ffd2
--- /dev/null
+++ b/testing/pgruntime/helpers_test.go
@@ -0,0 +1,47 @@
+// SPDX-FileCopyrightText: 2026 Stefan Majewsky <majewsky@gmx.net>
+// SPDX-License-Identifier: Apache-2.0
+
+package pgruntime_test
+
+import (
+	"context"
+	"database/sql"
+
+	"go.xyrillian.de/gg/errext"
+	"go.xyrillian.de/gg/gsql"
+)
+
+// Convenience function for executing a one-off SQL query returning no rows.
+// TODO: move to gsql
+func execQuery(ctx context.Context, db gsql.Handle, query string, args []any) (sql.Result, error) {
+	stmt, err := db.GSQLPrepare(ctx, query, false)
+	if err != nil {
+		return nil, err
+	}
+	result, err := stmt.Exec(ctx, args)
+	return result, errext.WithCleanup(err, "stmt.Close", stmt.Close())
+}
+
+// Convenience function for executing a one-off SQL query returning one row.
+func queryRow(ctx context.Context, db gsql.Handle, query string, args, slots []any) error {
+	stmt, err := db.GSQLPrepare(ctx, query, false)
+	if err != nil {
+		return err
+	}
+	err = stmt.QueryRow(ctx, args, slots)
+	return errext.WithCleanup(err, "stmt.Close", stmt.Close())
+}
+
+// Convenience function for executing a one-off SQL query returning one value.
+// TODO: move to gsql
+func selectOneValue[T any](ctx context.Context, db gsql.Handle, query string, args ...any) (T, error) {
+	stmt, err := db.GSQLPrepare(ctx, query, false)
+	if err != nil {
+		var none T
+		return none, err
+	}
+
+	var result T
+	err = stmt.QueryRow(ctx, args, []any{&result})
+	return result, errext.WithCleanup(err, "stmt.Close", stmt.Close())
+}
diff --git a/testing/pgruntime/main_test.go b/testing/pgruntime/main_test.go
new file mode 100644
index 0000000..3f23871
--- /dev/null
+++ b/testing/pgruntime/main_test.go
@@ -0,0 +1,20 @@
+// SPDX-FileCopyrightText: 2026 Stefan Majewsky <majewsky@gmx.net>
+// SPDX-License-Identifier: Apache-2.0
+
+package pgruntime_test
+
+import (
+	"testing"
+
+	_ "github.com/lib/pq"
+	"go.xyrillian.de/gg/pgruntime"
+)
+
+var (
+	defaultBehavior = pgruntime.ConnectionBehavior{}
+	connector       = pgruntime.StdConnector("postgres")
+)
+
+func TestMain(m *testing.M) {
+	pgruntime.WithTestDB(m, m.Run)
+}
diff --git a/testing/pgruntime/migration_test.go b/testing/pgruntime/migration_test.go
new file mode 100644
index 0000000..4dfff4d
--- /dev/null
+++ b/testing/pgruntime/migration_test.go
@@ -0,0 +1,92 @@
+// SPDX-FileCopyrightText: 2026 Stefan Majewsky <majewsky@gmx.net>
+// SPDX-License-Identifier: Apache-2.0
+
+package pgruntime_test
+
+import (
+	"testing"
+
+	"go.xyrillian.de/gg/assert"
+	"go.xyrillian.de/gg/pgruntime"
+)
+
+func TestMigrations(t *testing.T) {
+	ctx := t.Context()
+
+	// reset the test DB to empty if it exists
+	db, _ := connector.ConnectForTest(t, defaultBehavior)
+	for _, tableName := range []string{"comments", "posts", "schema_migrations"} {
+		_, err := execQuery(ctx, db, `DROP TABLE IF EXISTS `+tableName, nil)
+		if err != nil {
+			t.Fatal(err.Error())
+		}
+	}
+	err := db.Close()
+	if err != nil {
+		t.Fatal(err.Error())
+	}
+
+	// start with an initial baseline
+	b := pgruntime.ConnectionBehavior{
+		Migrations: map[int64]string{
+			42: `
+				CREATE TABLE posts (
+					id       BIGSERIAL  PRIMARY KEY,
+					message  TEXT       NOT NULL
+				);
+				CREATE TABLE comments (
+					id       BIGSERIAL  PRIMARY KEY,
+					post_id  BIGINT     REFERENCES posts ON DELETE CASCADE,
+					message  TEXT       NOT NULL
+				);
+			`,
+		},
+	}
+	db, target := connector.ConnectForTest(t, b)
+
+	// check that the schema is applied by inserting some basic records
+	postID, err := selectOneValue[int64](ctx, db, `INSERT INTO posts (message) VALUES ($1) RETURNING id`, "Hello World!")
+	if err != nil {
+		t.Fatal(err.Error())
+	}
+	_, err = execQuery(ctx, db, `INSERT INTO comments (post_id, message) VALUES ($1, $2)`, []any{postID, "Hi there."})
+	if err != nil {
+		t.Fatal(err.Error())
+	}
+	err = db.Close()
+	if err != nil {
+		t.Fatal(err.Error())
+	}
+
+	// apply another migration
+	b.Migrations[50] = `
+		UPDATE comments c SET message = c.message || ' in response to: ' || p.message FROM posts p WHERE p.id = c.post_id;
+	`
+	db, err = connector.Connect(ctx, target, b)
+	if err != nil {
+		t.Fatal(err.Error())
+	}
+
+	// check that the data was modified appropriately
+	message, err := selectOneValue[string](ctx, db, `SELECT message FROM comments`)
+	if assert.ErrEqual(t, err, nil) {
+		assert.Equal(t, message, "Hi there. in response to: Hello World!")
+	}
+
+	// try to apply a broken migration
+	b.Migrations[51] = `
+		TRUNCATE commands; -- should be "comments"
+	`
+	_, err = connector.Connect(ctx, target, b)
+	assert.ErrEqual(t, err, `while migrating to schema version 51: could not execute schema migration: pq: relation "commands" does not exist (42P01)`)
+
+	// check that the migration was not applied
+	version, err := selectOneValue[int64](ctx, db, `SELECT version FROM schema_migrations`)
+	if assert.ErrEqual(t, err, nil) {
+		assert.Equal(t, version, 50)
+	}
+	commentCount, err := selectOneValue[int64](ctx, db, `SELECT COUNT(*) FROM comments`)
+	if assert.ErrEqual(t, err, nil) {
+		assert.Equal(t, commentCount, 1)
+	}
+}