add package pgruntime

14 files changed, 880 insertions, 1 deletions

diff --git a/.gitignore b/.gitignore
index 00d893e..d18b035 100644
--- a/.gitignore
+++ b/.gitignore
@@ -1,2 +1,3 @@
 /build/
 !/build/.gitkeep
+/.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 2565fa6..ba63efd 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -7,7 +7,7 @@ SPDX-License-Identifier: Apache-2.0
 
 Changes:
 
-- Add packages errext and gsql.
+- Add packages errext, gsql and pgruntime.
 
 # v1.11.1 (2026-06-27)
 
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..c95866a 100644
--- a/REUSE.toml
+++ b/REUSE.toml
@@ -12,6 +12,7 @@ path = [
   "go.work",
   "benchmark/go.mod",
   "benchmark/go.sum",
+  "pgruntime/tool_template.sh",
 ]
 SPDX-FileCopyrightText = "Stefan Majewsky <majewsky@gmx.net>"
 SPDX-License-Identifier = "Apache-2.0"
diff --git a/pgruntime/behavior.go b/pgruntime/behavior.go
new file mode 100644
index 0000000..54766ed
--- /dev/null
+++ b/pgruntime/behavior.go
@@ -0,0 +1,130 @@
+// 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
+	var rowCount int64
+	err = queryRow(ctx, db, `SELECT COUNT(*) FROM schema_migrations`, nil, []any{&rowCount})
+	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 = %d, 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..082f970
--- /dev/null
+++ b/pgruntime/connector.go
@@ -0,0 +1,112 @@
+// SPDX-FileCopyrightText: 2026 Stefan Majewsky <majewsky@gmx.net>
+// SPDX-License-Identifier: Apache-2.0
+
+package pgruntime
+
+import (
+	"context"
+	"database/sql"
+	"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(ctx context.Context, dbURL string) (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, dbURL string) (*gsql.DB, error) {
+		db, err := sql.Open(driverName, dbURL)
+		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
+
+	u, err := target.IntoURL()
+	if err != nil {
+		return none, err
+	}
+	db, err := c(ctx, u.String())
+	if err != nil {
+		return none, err
+	}
+	err = behavior.applyTo(ctx, db)
+	if err != nil {
+		return none, errext.WithCleanup(err, "db.Close", db.GSQLClose())
+	}
+	return db, nil
+}
+
+// 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) (T, ConnectionTarget) {
+	ctx := t.Context()
+
+	// normalize t.Name() into an acceptable database name for PostgreSQL
+	//   - only alphanumerics and underscore -> replace all other symbols with _
+	//   - max 63 chars -> reject longer names
+	dbName := strings.ToLower(t.Name())
+	dbName = regexp.MustCompile(`[^a-z_]`).ReplaceAllString(dbName, "_")
+	if len(dbName) > 63 {
+		t.Fatalf("cannot use t.Name() = %q (normalized to %q) as a database name because it is longer than 63 chars", t.Name(), dbName)
+	}
+
+	// connect to "postgres" database for the DROP/CREATE DATABASE queries
+	target := ConnectionTarget{
+		HostName:          "127.0.0.1",
+		Port:              testdbPort,
+		UserName:          testdbUserName,
+		DatabaseName:      "postgres",
+		ConnectionOptions: "sslmode=disable",
+	}
+	err := c.prepareTestDatabase(ctx, target, dbName)
+	if err != nil {
+		t.Fatal(err.Error())
+	}
+
+	// connect to actual test database
+	target.DatabaseName = dbName
+	handle, err := c.Connect(ctx, target, behavior)
+	if err != nil {
+		t.Fatal(err.Error())
+	}
+	return handle, target
+}
+
+func (c Connector[T]) prepareTestDatabase(ctx context.Context, target ConnectionTarget, dbName string) error {
+	u, err := target.IntoURL()
+	if err != nil {
+		return err
+	}
+	db, err := c(ctx, u.String())
+	if err != nil {
+		return fmt.Errorf("%w (if this error is about the database server not running, check if your TestMain() calls pgruntime.WithTestDB())", err)
+	}
+	_, err = execQuery(ctx, db, "DROP DATABASE IF EXISTS "+quoteIdentifier(dbName), nil)
+	if err != nil {
+		return errext.WithCleanup(err, "db.Close", db.GSQLClose())
+	}
+	_, err = execQuery(ctx, db, "CREATE DATABASE "+quoteIdentifier(dbName), nil)
+	return errext.WithCleanup(err, "db.Close", db.GSQLClose())
+}
diff --git a/pgruntime/helpers.go b/pgruntime/helpers.go
new file mode 100644
index 0000000..11bc84e
--- /dev/null
+++ b/pgruntime/helpers.go
@@ -0,0 +1,39 @@
+// 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.
+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 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/main_test.go b/pgruntime/main_test.go
new file mode 100644
index 0000000..db6b0ac
--- /dev/null
+++ b/pgruntime/main_test.go
@@ -0,0 +1,14 @@
+// SPDX-FileCopyrightText: 2026 Stefan Majewsky <majewsky@gmx.net>
+// SPDX-License-Identifier: Apache-2.0
+
+package pgruntime_test
+
+import (
+	"testing"
+
+	"go.xyrillian.de/gg/pgruntime"
+)
+
+func TestMain(m *testing.M) {
+	pgruntime.WithTestDB(m, m.Run)
+}
diff --git a/pgruntime/pgruntime.go b/pgruntime/pgruntime.go
new file mode 100644
index 0000000..ed44076
--- /dev/null
+++ b/pgruntime/pgruntime.go
@@ -0,0 +1,32 @@
+// 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).
+//   - ConnectForTest now recreates databases instead of just wiping their contents.
+//
+// [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
+
+// TODO: before merging this branch, start work on go-gg-pgx to verify that we're not painting ourselves into a corner with the gsql.Handle interfaces
+// TODO: test coverage via separate module importing github.com/lib/pq
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..1c7035e
--- /dev/null
+++ b/pgruntime/testdb.go
@@ -0,0 +1,280 @@
+// 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"
+	"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)
+//	}
+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 {
+		fmt.Fprintln(os.Stderr, err.Error())
+		return 1
+	}
+}
+
+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) {
+	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)
+	}
+	return filepath.Join(rootPath, ".testdb"), nil
+}
+
+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..d0ddea1
--- /dev/null
+++ b/pgruntime/tool_template.sh
@@ -0,0 +1,13 @@
+#!/usr/bin/env bash
+set -euo pipefail
+
+stop_postgres() {
+	EXIT_CODE=$?
+	pg_ctl stop --wait --silent -D .testdb/datadir
+	exit "${EXIT_CODE}"
+}
+trap stop_postgres EXIT INT TERM
+
+rm -f -- .testdb/run/postgresql.log
+pg_ctl start --wait --silent -D .testdb/datadir -l .testdb/run/postgresql.log
+$COMMAND -U postgres -h 127.0.0.1 -p 54320 "$@"