add testing/pgruntime

Testing pgruntime has quickly turned out to be a nightmare, because so
many parts of the test logic are not easily reproducible (e.g.
`stopDBIfLingering`). I decided to not waste a bunch of time testing
code that is relatively compact and easy to verify by careful
examination, and that is unlikely to change a lot in the future.

14 files changed, 238 insertions, 14 deletions

diff --git a/.gitignore b/.gitignore
index d18b035..9027518 100644
--- a/.gitignore
+++ b/.gitignore
@@ -1,3 +1,6 @@
 /build/
 !/build/.gitkeep
-/.testdb/
+
+# 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/CHANGELOG.md b/CHANGELOG.md
index 34962eb..92f062d 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -8,8 +8,6 @@ SPDX-License-Identifier: Apache-2.0
 Changes:
 
 - Add package pgruntime.
-- TODO: add SelectOneValue, SelectSeveralValues, etc. to gsql
-- TODO: add WithinTransaction to gsql.DB, gsql.Conn (and same on gg-pgx)
 
 # 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/REUSE.toml b/REUSE.toml
index c95866a..ccab73e 100644
--- a/REUSE.toml
+++ b/REUSE.toml
@@ -13,6 +13,8 @@ path = [
   "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/connector.go b/pgruntime/connector.go
index 92780f6..5276a26 100644
--- a/pgruntime/connector.go
+++ b/pgruntime/connector.go
@@ -164,10 +164,12 @@ func resetTestDatabase(ctx context.Context, db gsql.Handle, params testSetupPara
 	}
 
 	// truncate all tables at once
-	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)
+	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/pgruntime.go b/pgruntime/pgruntime.go
index 17a6c94..0283eaa 100644
--- a/pgruntime/pgruntime.go
+++ b/pgruntime/pgruntime.go
@@ -20,12 +20,10 @@
 // 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.
+//   - 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
-
-// TODO: test coverage via separate module importing github.com/lib/pq
diff --git a/pgruntime/testdb.go b/pgruntime/testdb.go
index 261f88a..808784c 100644
--- a/pgruntime/testdb.go
+++ b/pgruntime/testdb.go
@@ -69,8 +69,7 @@ func WithTestDB(m *testing.M, action func() int) int {
 	if err == nil {
 		return result
 	} else {
-		fmt.Fprintln(os.Stderr, err.Error())
-		return 1
+		panic(err.Error())
 	}
 }
 
@@ -135,7 +134,12 @@ func findTestdbPath() (string, error) {
 
 	// 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
-	return overridePath, os.Symlink(overridePath, testdbPath)
+	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) {
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/pgruntime/main_test.go b/testing/pgruntime/main_test.go
index db6b0ac..3f23871 100644
--- a/pgruntime/main_test.go
+++ b/testing/pgruntime/main_test.go
@@ -6,9 +6,15 @@ 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)
+	}
+}