This is a way that I can ensure that I have a test database in postgres with ephemeral state

db_test_harness.go

package core

import (
	"database/sql"
	"errors"
	"fmt"
	"net"
	"net/url"
	"strings"

	"github.com/JoTech-Solutions/SmartSYNC/pkg/queries"
	"github.com/google/uuid"
)

type testDB struct {
	dbname  string
	creator *sql.DB
	*sql.DB
}

func (t testDB) Close() error {
	var errs [3]error
	errs[0] = t.DB.Close()
	_, errs[1] = t.creator.Exec(fmt.Sprintf(`DROP DATABASE "%s";`, t.dbname))
	errs[2] = t.creator.Close()
	return errors.Join(errs[:]...)
}

func dsn() *url.URL {
	return &url.URL{
		Scheme: "postgres",
		User:   url.UserPassword("postgres", "postgres"),
		Host:   net.JoinHostPort("10.233.1.2", "5432"),
		RawQuery: url.Values{
			"connect_timeout": []string{"10"},
			"sslmode":         []string{"disable"},
		}.Encode(),
	}
}
func dsnDatabase(dbname string) *url.URL {
	dsn := dsn()
	dsn.Path = dbname
	return dsn
}

func creatorDB(dbname string) (*sql.DB, error) {
	db, err := sql.Open("postgres", dsn().String())
	if err != nil {
		return nil, err
	}
	rows, err := db.Query(`SELECT datname FROM pg_database;`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	testDbExists := false
	{
		for rows.Next() {
			var name string
			if err := rows.Scan(&name); err != nil {
				return nil, err
			}
			if name == dbname {
				testDbExists = true
				break
			}
		}
		if err := rows.Err(); err != nil {
			return nil, err
		}
	}
	if !testDbExists {
		if _, err := db.Exec(fmt.Sprintf(`CREATE DATABASE "%s"`, dbname)); err != nil {
			return nil, err
		}
	}
	return db, err
}

func newTestDB() (*testDB, error) {
	prefix := "joplex_test"
	suf, _, _ := strings.Cut(uuid.New().String(), "-")
	dbname := strings.Join([]string{prefix, suf}, "_")
	// XXX: closing is not handled properly here or cleaning up of intermediate
	// failures, please stay on the happy path :).
	creator, err := sql.Open("postgres", dsn().String())
	if err != nil {
		return nil, err
	}
	if _, err := creator.Exec(fmt.Sprintf(`CREATE DATABASE "%s"`, dbname)); err != nil {
		return nil, errors.Join(creator.Close(), err)
	}
	db, err := sql.Open("postgres", dsnDatabase(dbname).String())
	if err != nil {
		return nil, err
	}
	if _, err = db.Exec(queries.CreateAllTables); err != nil {
		return nil, err
	}
	return &testDB{dbname: dbname, creator: creator, DB: db}, nil
}