diff options
| -rw-r--r-- | .gitignore | 1 | ||||
| -rw-r--r-- | .golangci.yaml | 1 | ||||
| -rw-r--r-- | CHANGELOG.md | 6 | ||||
| -rw-r--r-- | README.md | 1 | ||||
| -rw-r--r-- | REUSE.toml | 1 | ||||
| -rw-r--r-- | pgruntime/behavior.go | 130 | ||||
| -rw-r--r-- | pgruntime/connector.go | 141 | ||||
| -rw-r--r-- | pgruntime/helpers.go | 39 | ||||
| -rw-r--r-- | pgruntime/main_test.go | 14 | ||||
| -rw-r--r-- | pgruntime/pgruntime.go | 32 | ||||
| -rw-r--r-- | pgruntime/target.go | 144 | ||||
| -rw-r--r-- | pgruntime/target_test.go | 111 | ||||
| -rw-r--r-- | pgruntime/testdb.go | 280 | ||||
| -rw-r--r-- | pgruntime/tool_template.sh | 13 |
14 files changed, 914 insertions, 0 deletions
@@ -1,2 +1,3 @@ /build/ !/build/.gitkeep +/.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 af15823..92f062d 100644 --- a/CHANGELOG.md +++ b/CHANGELOG.md @@ -3,6 +3,12 @@ SPDX-FileCopyrightText: 2026 Stefan Majewsky <majewsky@gmx.net> SPDX-License-Identifier: Apache-2.0 --> +# v1.13.0 (TBD) + +Changes: + +- Add package pgruntime. + # v1.12.0 (2026-07-31) Changes: @@ -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,7 @@ path = [ "go.work", "benchmark/go.mod", "benchmark/go.sum", + "pgruntime/tool_template.sh", ] SPDX-FileCopyrightText = "Stefan Majewsky <majewsky@gmx.net>" SPDX-License-Identifier = "Apache-2.0" diff --git a/pgruntime/behavior.go b/pgruntime/behavior.go new file mode 100644 index 0000000..9fb8662 --- /dev/null +++ b/pgruntime/behavior.go @@ -0,0 +1,130 @@ +// 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 + var rowCount int64 + err = queryRow(ctx, db, `SELECT COUNT(*) FROM schema_migrations`, nil, []any{&rowCount}) + 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..60e0de2 --- /dev/null +++ b/pgruntime/connector.go @@ -0,0 +1,141 @@ +// SPDX-FileCopyrightText: 2026 Stefan Majewsky <majewsky@gmx.net> +// SPDX-License-Identifier: Apache-2.0 + +package pgruntime + +import ( + "context" + "database/sql" + "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(ctx context.Context, dbURL string) (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, dbURL string) (*gsql.DB, error) { + db, err := sql.Open(driverName, dbURL) + 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 + + u, err := target.IntoURL() + if err != nil { + return none, err + } + db, err := c(ctx, u.String()) + 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 -> reject longer names + dbName := strings.ToLower(params.DatabaseName) + dbName = regexp.MustCompile(`[^a-z_]`).ReplaceAllString(dbName, "_") + if len(dbName) > 63 { + t.Fatalf("cannot use t.Name() = %q (normalized to %q) as a database name because it is longer than 63 chars", params.DatabaseName, dbName) + } + + // connect to "postgres" database for the DROP/CREATE DATABASE queries + // TODO: DROP/CREATE DATABASE turns out to be very slow (in a real-world scenario: 100ms per test instead of ~10ms to wipe just the DB contents and reset sequences) + target := ConnectionTarget{ + HostName: "127.0.0.1", + Port: testdbPort, + UserName: testdbUserName, + DatabaseName: "postgres", + ConnectionOptions: "sslmode=disable", + } + err := c.prepareTestDatabase(ctx, target, dbName) + if err != nil { + t.Fatal(err.Error()) + } + + // connect to actual test database + target.DatabaseName = dbName + handle, err := c.Connect(ctx, target, behavior) + if err != nil { + t.Fatal(err.Error()) + } + return handle, target +} + +func (c Connector[T]) prepareTestDatabase(ctx context.Context, target ConnectionTarget, dbName string) error { + u, err := target.IntoURL() + if err != nil { + return err + } + db, err := c(ctx, u.String()) + if err != nil { + return fmt.Errorf("%w (if this error is about the database server not running, check if your TestMain() calls pgruntime.WithTestDB())", err) + } + _, err = execQuery(ctx, db, "DROP DATABASE IF EXISTS "+quoteIdentifier(dbName), nil) + if err != nil { + return errext.WithCleanup(fmt.Errorf("during DROP DATABASE: %w", err), "db.Close", db.GSQLClose(ctx)) + } + _, err = execQuery(ctx, db, "CREATE DATABASE "+quoteIdentifier(dbName), nil) + if err != nil { + return errext.WithCleanup(fmt.Errorf("during CREATE DATABASE: %w", err), "db.Close", db.GSQLClose(ctx)) + } + return errext.WithCleanup(nil, "db.Close", db.GSQLClose(ctx)) +} diff --git a/pgruntime/helpers.go b/pgruntime/helpers.go new file mode 100644 index 0000000..11bc84e --- /dev/null +++ b/pgruntime/helpers.go @@ -0,0 +1,39 @@ +// 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. +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 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/main_test.go b/pgruntime/main_test.go new file mode 100644 index 0000000..db6b0ac --- /dev/null +++ b/pgruntime/main_test.go @@ -0,0 +1,14 @@ +// SPDX-FileCopyrightText: 2026 Stefan Majewsky <majewsky@gmx.net> +// 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 new file mode 100644 index 0000000..ed44076 --- /dev/null +++ b/pgruntime/pgruntime.go @@ -0,0 +1,32 @@ +// 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). +// - ConnectForTest now recreates databases instead of just wiping their contents. +// +// [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: before merging this branch, start work on go-gg-pgx to verify that we're not painting ourselves into a corner with the gsql.Handle interfaces +// TODO: test coverage via separate module importing github.com/lib/pq 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..1c7035e --- /dev/null +++ b/pgruntime/testdb.go @@ -0,0 +1,280 @@ +// 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" + "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 + } +} diff --git a/pgruntime/tool_template.sh b/pgruntime/tool_template.sh new file mode 100644 index 0000000..d0ddea1 --- /dev/null +++ b/pgruntime/tool_template.sh @@ -0,0 +1,13 @@ +#!/usr/bin/env bash +set -euo pipefail + +stop_postgres() { + EXIT_CODE=$? + pg_ctl stop --wait --silent -D .testdb/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 +$COMMAND -U postgres -h 127.0.0.1 -p 54320 "$@" |