diff options
| -rw-r--r-- | .gitignore | 4 | ||||
| -rw-r--r-- | .golangci.yaml | 1 | ||||
| -rw-r--r-- | CHANGELOG.md | 1 | ||||
| -rw-r--r-- | Makefile | 2 | ||||
| -rw-r--r-- | README.md | 1 | ||||
| -rw-r--r-- | REUSE.toml | 3 | ||||
| -rw-r--r-- | go.work | 1 | ||||
| -rw-r--r-- | pgruntime/behavior.go | 129 | ||||
| -rw-r--r-- | pgruntime/connector.go | 176 | ||||
| -rw-r--r-- | pgruntime/helpers.go | 74 | ||||
| -rw-r--r-- | pgruntime/pgruntime.go | 29 | ||||
| -rw-r--r-- | pgruntime/target.go | 144 | ||||
| -rw-r--r-- | pgruntime/target_test.go | 111 | ||||
| -rw-r--r-- | pgruntime/testdb.go | 313 | ||||
| -rw-r--r-- | pgruntime/tool_template.sh | 14 | ||||
| -rw-r--r-- | testing/go.mod | 8 | ||||
| -rw-r--r-- | testing/go.sum | 4 | ||||
| -rw-r--r-- | testing/pgruntime/connect_test.go | 59 | ||||
| -rw-r--r-- | testing/pgruntime/helpers_test.go | 47 | ||||
| -rw-r--r-- | testing/pgruntime/main_test.go | 20 | ||||
| -rw-r--r-- | testing/pgruntime/migration_test.go | 92 |
21 files changed, 1232 insertions, 1 deletions
@@ -1,2 +1,6 @@ /build/ !/build/.gitkeep + +# 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/.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 1959eb8..11dda13 100644 --- a/CHANGELOG.md +++ b/CHANGELOG.md @@ -7,6 +7,7 @@ SPDX-License-Identifier: Apache-2.0 Changes: +- Add package pgruntime. - Add WithinTransaction method to `*gsql.DB` and `*gsql.Conn` methods. # v1.12.0 (2026-07-31) @@ -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" @@ -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 @@ -12,6 +12,9 @@ path = [ "go.work", "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" @@ -3,4 +3,5 @@ go 1.26 use ( . ./benchmark + ./testing ) diff --git a/pgruntime/behavior.go b/pgruntime/behavior.go new file mode 100644 index 0000000..3ffff1e --- /dev/null +++ b/pgruntime/behavior.go @@ -0,0 +1,129 @@ +// 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 + rowCount, err := selectOneValue[int64](ctx, db, `SELECT COUNT(*) FROM schema_migrations`) + 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{¤tVersion, &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 = $1, 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..5276a26 --- /dev/null +++ b/pgruntime/connector.go @@ -0,0 +1,176 @@ +// SPDX-FileCopyrightText: 2026 Stefan Majewsky <majewsky@gmx.net> +// SPDX-License-Identifier: Apache-2.0 + +package pgruntime + +import ( + "context" + "crypto/sha256" + "database/sql" + "encoding/base32" + "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(context.Context, ConnectionTarget) (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, target ConnectionTarget) (*gsql.DB, error) { + u, err := target.IntoURL() + if err != nil { + return nil, err + } + db, err := sql.Open(driverName, u.String()) + 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 + + db, err := c(ctx, target) + if err != nil { + return none, err + } + err = behavior.applyTo(ctx, db) + if err != nil { + return none, errext.WithCleanup(err, "db.Close", db.GSQLClose(ctx)) + } + return db, nil +} + +// TestSetupOption is an optional behavior that can be given to [Connector.ConnectForTest]. +type TestSetupOption func(*testSetupParams) + +type testSetupParams struct { + DatabaseName string +} + +// OverrideDatabaseName is a [TestSetupOption] that picks a different database name than the default of t.Name(). +// +// This is only necessary if a single test needs to use multiple database connections at the same time, +// e.g. to simulate two separate deployments of the application next to each other. +func OverrideDatabaseName(dbName string) TestSetupOption { + return func(params *testSetupParams) { + params.DatabaseName = dbName + } +} + +// 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, opts ...TestSetupOption) (T, ConnectionTarget) { + t.Helper() + ctx := t.Context() + + params := testSetupParams{ + DatabaseName: t.Name(), + } + for _, opt := range opts { + opt(¶ms) + } + + // normalize t.Name() into an acceptable database name for PostgreSQL + // - only alphanumerics and underscore -> replace all other symbols with _ + // - max 63 chars -> if overflown, truncate and append a short digest to hopefully make it unique + dbName := strings.ToLower(params.DatabaseName) + dbName = regexp.MustCompile(`[^a-z_]`).ReplaceAllString(dbName, "_") + if len(dbName) > 63 { + digest := sha256.Sum256([]byte(params.DatabaseName)) + encoded := base32.HexEncoding.EncodeToString(digest[:]) + dbName = dbName[0:53] + "__" + strings.ToLower(encoded[0:8]) + } + + // connect to "postgres" database for the CREATE DATABASE query (if necessary) + target := ConnectionTarget{ + HostName: "127.0.0.1", + Port: testdbPort, + UserName: testdbUserName, + DatabaseName: "postgres", + ConnectionOptions: "sslmode=disable", + } + adminDB, err := c(ctx, target) + if err != nil { + t.Fatal(err.Error() + " (if this error is about the database server not running, check if your TestMain() calls pgruntime.WithTestDB())") + } + err = createDatabaseIfMissing(ctx, adminDB, dbName) + err = errext.WithCleanup(err, "db.Close", adminDB.GSQLClose(ctx)) + if err != nil { + t.Fatal(err.Error()) + } + + // connect to actual test database + target.DatabaseName = dbName + testDB, err := c.Connect(ctx, target, behavior) + if err != nil { + t.Fatal(err.Error()) + } + err = resetTestDatabase(ctx, testDB, params, behavior) + if err != nil { + err = errext.WithCleanup(err, "db.Close", testDB.GSQLClose(ctx)) + t.Fatal(err.Error()) + } + return testDB, target +} + +func createDatabaseIfMissing(ctx context.Context, db gsql.Handle, dbName string) error { + // check if database exists + exists, err := selectOneValue[bool](ctx, db, `SELECT COUNT(*) > 0 FROM pg_catalog.pg_database WHERE datname = $1`, dbName) + if err != nil { + return fmt.Errorf("while reading from pg_catalog.pg_database: %w", err) + } + + // create database if necessary + if !exists { + _, err = execQuery(ctx, db, "CREATE DATABASE "+quoteIdentifier(dbName), nil) + if err != nil { + return fmt.Errorf("during CREATE DATABASE: %w", err) + } + } + + return nil +} + +func resetTestDatabase(ctx context.Context, db gsql.Handle, params testSetupParams, behavior ConnectionBehavior) error { + // enumerate all tables that need to be truncated (all tables that are not managed by pgruntime) + condition := `table_schema = 'public' AND table_type = 'BASE TABLE'` + if len(behavior.Migrations) > 0 { + condition += ` AND table_name != 'schema_migrations'` + } + query := fmt.Sprintf(`SELECT quote_ident(table_name) FROM information_schema.tables WHERE %s ORDER BY table_name`, condition) + quotedTableNames, err := selectSeveralValues[string](ctx, db, query) + if err != nil { + return fmt.Errorf("while listing tables to truncate: %w", err) + } + + // truncate all tables at once + 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/helpers.go b/pgruntime/helpers.go new file mode 100644 index 0000000..1019e6e --- /dev/null +++ b/pgruntime/helpers.go @@ -0,0 +1,74 @@ +// 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. +// 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()) +} + +// Convenience function for executing a one-off SQL query returning several single-column rows. +// TODO: move to gsql (and also add ForeachValue with a callback instead of a slice return, maybe even ForeachPair and ForeachTriple) +func selectSeveralValues[T any](ctx context.Context, db gsql.Handle, query string, args ...any) ([]T, error) { + rows, err := db.GSQLQuery(ctx, query, args) + if err != nil { + return nil, err + } + var result []T + for rows.Next() { + // TODO: this should share growRecordSlice() from Oblast to optimize allocations + var value T + err := rows.Scan(&value) + if err != nil { + return nil, errext.WithCleanup(err, "rows.Close", rows.Close()) + } + result = append(result, value) + } + return result, errext.WithCleanup(nil, "rows.Err", rows.Err()) +} + +// 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/pgruntime.go b/pgruntime/pgruntime.go new file mode 100644 index 0000000..0283eaa --- /dev/null +++ b/pgruntime/pgruntime.go @@ -0,0 +1,29 @@ +// 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). +// - 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 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..808784c --- /dev/null +++ b/pgruntime/testdb.go @@ -0,0 +1,313 @@ +// 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" + "runtime/debug" + "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) +// } +// +// 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. + // 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 { + panic(err.Error()) + } +} + +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) { + // find `$REPO_ROOT/.testdb` + 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) + } + 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 + 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) { + _, 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..7e3827f --- /dev/null +++ b/pgruntime/tool_template.sh @@ -0,0 +1,14 @@ +#!/usr/bin/env bash +set -euo pipefail +cd "$(dirname "$0")" + +stop_postgres() { + EXIT_CODE=$? + pg_ctl stop --wait --silent -D datadir + exit "${EXIT_CODE}" +} +trap stop_postgres EXIT INT TERM + +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 "$@" 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) + } +} |