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.

1 files changed, 92 insertions, 0 deletions

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