From 81e32cec36291685b59102594c714d235726e214 Mon Sep 17 00:00:00 2001 From: Stefan Majewsky Date: Thu, 30 Jul 2026 19:57:29 +0200 Subject: add package pgruntime --- pgruntime/testdb.go | 280 ++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 280 insertions(+) create mode 100644 pgruntime/testdb.go (limited to 'pgruntime/testdb.go') 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 +// 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 + } +} -- cgit v1.3.1 From e26a214de3958e48e94cdac0e1ae0641e3221deb Mon Sep 17 00:00:00 2001 From: Stefan Majewsky Date: Fri, 31 Jul 2026 23:08:39 +0200 Subject: pgruntime: allow overriding .testdb path --- pgruntime/testdb.go | 31 ++++++++++++++++++++++++++++++- pgruntime/tool_template.sh | 7 ++++--- 2 files changed, 34 insertions(+), 4 deletions(-) (limited to 'pgruntime/testdb.go') diff --git a/pgruntime/testdb.go b/pgruntime/testdb.go index 1c7035e..261f88a 100644 --- a/pgruntime/testdb.go +++ b/pgruntime/testdb.go @@ -10,6 +10,7 @@ import ( "os" "os/exec" "path/filepath" + "runtime/debug" "strings" "testing" "time" @@ -46,6 +47,16 @@ var toolScriptTemplate []byte // 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. @@ -91,6 +102,7 @@ func withTestDB(m *testing.M, action func() int) (_ int, returnedError error) { } func findTestdbPath() (string, error) { + // find `$REPO_ROOT/.testdb` cwd, err := os.Getwd() if err == nil { cwd, err = filepath.Abs(cwd) @@ -106,7 +118,24 @@ func findTestdbPath() (string, error) { 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 + 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 + return overridePath, os.Symlink(overridePath, testdbPath) } func findModuleRootDir(dirPath string) (Option[string], error) { diff --git a/pgruntime/tool_template.sh b/pgruntime/tool_template.sh index d0ddea1..7e3827f 100644 --- a/pgruntime/tool_template.sh +++ b/pgruntime/tool_template.sh @@ -1,13 +1,14 @@ #!/usr/bin/env bash set -euo pipefail +cd "$(dirname "$0")" stop_postgres() { EXIT_CODE=$? - pg_ctl stop --wait --silent -D .testdb/datadir + pg_ctl stop --wait --silent -D 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 +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 "$@" -- cgit v1.3.1 From 6d72c85766a8355e1d660ff0c19b43e93faad4d8 Mon Sep 17 00:00:00 2001 From: Stefan Majewsky Date: Sat, 1 Aug 2026 14:49:15 +0200 Subject: 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. --- .gitignore | 5 +- CHANGELOG.md | 2 - Makefile | 2 +- REUSE.toml | 2 + go.work | 1 + pgruntime/connector.go | 10 ++-- pgruntime/main_test.go | 14 ------ pgruntime/pgruntime.go | 4 +- pgruntime/testdb.go | 10 ++-- testing/go.mod | 8 ++++ testing/go.sum | 4 ++ testing/pgruntime/connect_test.go | 59 ++++++++++++++++++++++++ testing/pgruntime/helpers_test.go | 47 +++++++++++++++++++ testing/pgruntime/main_test.go | 20 ++++++++ testing/pgruntime/migration_test.go | 92 +++++++++++++++++++++++++++++++++++++ 15 files changed, 252 insertions(+), 28 deletions(-) delete mode 100644 pgruntime/main_test.go create mode 100644 testing/go.mod create mode 100644 testing/go.sum create mode 100644 testing/pgruntime/connect_test.go create mode 100644 testing/pgruntime/helpers_test.go create mode 100644 testing/pgruntime/main_test.go create mode 100644 testing/pgruntime/migration_test.go (limited to 'pgruntime/testdb.go') 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 " 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/main_test.go b/pgruntime/main_test.go deleted file mode 100644 index db6b0ac..0000000 --- a/pgruntime/main_test.go +++ /dev/null @@ -1,14 +0,0 @@ -// SPDX-FileCopyrightText: 2026 Stefan Majewsky -// 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 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 +// 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 +// 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 +// 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 +// 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) + } +} -- cgit v1.3.1