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.

6 files changed, 230 insertions, 0 deletions

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