Help us shape the future of Go! Take the 2026 Go Developer Survey

postgres

package
v0.1.0 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Oct 22, 2026 License: MIT Imports: 22 Imported by: 0

Documentation

Overview

Package postgres provides PostgreSQL test database support for testdb.

Choosing the Right API

This package provides two main functions: Setup() and New(). Here's how to choose:

Use Setup() when:

  • You're using pgx/pgxpool directly in your application
  • You don't need custom initialization logic
  • You want the simplest API (just get a pool and test)

Use New() when:

  • You're using an ORM (GORM, ent, SQLBoiler)
  • You're using database/sql interfaces (use SqlDbInitializer)
  • You're using sqlx for struct scanning
  • You have custom connection pooling requirements
  • You need to wrap the connection in your own type
  • You need custom tracing/logging/instrumentation

Basic Usage with Setup() 

import (
    "github.com/bashhack/testdb"
    "github.com/bashhack/testdb/postgres"
)

func TestUsers(t *testing.T) {
    pool := postgres.Setup(t,
        testdb.WithMigrations("./migrations"),
        testdb.WithMigrationTool(testdb.MigrationToolTern))

    _, err := pool.Exec(ctx, "INSERT INTO users (email) VALUES ($1)", "test@example.com")
    require.NoError(t, err)
}

Built-in Initializers

The package provides two built-in initializers:

PoolInitializer (default) - Creates *pgxpool.Pool for PostgreSQL-specific features: 

pool := postgres.Setup(t)  // Uses PoolInitializer
// or
db := postgres.New(t, &postgres.PoolInitializer{})
pool := db.Entity().(*pgxpool.Pool)

SqlDbInitializer - Creates *sql.DB for database/sql compatibility: 

db := postgres.New(t, &postgres.SqlDbInitializer{})
sqlDB := db.Entity().(*sql.DB)
// Use standard database/sql operations
sqlDB.QueryRow("SELECT * FROM users WHERE id = $1", 1)

Custom Initializer Examples

When your application uses an ORM like GORM, you need New() with a custom initializer: 

type GormInitializer struct{}

func (g *GormInitializer) InitializeTestDatabase(ctx context.Context, dsn string) (any, error) {
    return gorm.Open(postgres.Open(dsn), &gorm.Config{})
}

func TestWithGORM(t *testing.T) {
    db := postgres.New(t, &GormInitializer{})
    gormDB := db.Entity().(*gorm.DB)
    // Now you can call your application functions that expect *gorm.DB
}

When your application wraps connections in a custom type: 

type AppDB struct {
    Pool    *pgxpool.Pool
    Timeout time.Duration
}

type AppDBInitializer struct{}

func (a *AppDBInitializer) InitializeTestDatabase(ctx context.Context, dsn string) (any, error) {
    pool, err := pgxpool.New(ctx, dsn)
    if err != nil {
        return nil, err
    }
    return &AppDB{Pool: pool, Timeout: 30 * time.Second}, nil
}

func TestWithCustomType(t *testing.T) {
    db := postgres.New(t, &AppDBInitializer{})
    appDB := db.Entity().(*AppDB)
    // Now you can call your application functions that expect *AppDB
}
Example (BasicUsage)

Example_basicUsage demonstrates creating an isolated test database. 

t := newExampleT()
defer t.done()

// Create isolated test database
pool := postgres.Setup(t)

// Use the database - it's completely isolated
// Cleanup is automatic via t.Cleanup()
var result int
err := pool.QueryRow(context.Background(), "SELECT 1").Scan(&result)
if err != nil {
	fmt.Printf("Query failed: %v\n", err)
	return
}

fmt.Printf("Query result: %d\n", result)

Output:
Query result: 1
Example (Configuration)

Example_configuration demonstrates configuration options. 

t := newExampleT()
defer t.done()

pool := postgres.Setup(t,
	testdb.WithMigrator(postgres.SQLFiles("../testdata/postgres/schema")),
	testdb.WithDBPrefix("myapp_test"),
)

if err := pool.Ping(context.Background()); err != nil {
	fmt.Printf("Error: %v\n", err)
	return
}

fmt.Println("Database configured successfully")
fmt.Println("Custom prefix and migrations applied")

Output:
Database configured successfully
Custom prefix and migrations applied
Example (CustomMigrator)

Example_customMigrator shows that any function taking a context and a DSN can prepare the database: here, a few statements run directly. 

t := newExampleT()
defer t.done()

createSchema := func(ctx context.Context, dsn string) error {
	conn, err := pgxpool.New(ctx, dsn)
	if err != nil {
		return err
	}
	defer conn.Close()
	_, err = conn.Exec(ctx, "CREATE TABLE notes (id SERIAL PRIMARY KEY, body TEXT NOT NULL)")
	return err
}

pool := postgres.Setup(t,
	testdb.WithMigrator(testdb.MigratorFunc(createSchema)),
)

if _, err := pool.Exec(context.Background(),
	"INSERT INTO notes (body) VALUES ($1)", "hello"); err != nil {
	fmt.Printf("Failed to insert: %v\n", err)
	return
}

fmt.Println("Custom migrator prepared the database")

Output:
Custom migrator prepared the database
Example (CustomPrefix)

Example_customPrefix demonstrates using a custom database name prefix. 

t := newExampleT()
defer t.done()

pool := postgres.Setup(t,
	testdb.WithDBPrefix("myapp"),
)

// Database name will be like "myapp_1699564231_a1b2c3d4"
var dbname string
err := pool.QueryRow(context.Background(), "SELECT current_database()").Scan(&dbname)
if err != nil {
	fmt.Printf("Error: %v\n", err)
	return
}

// Verify prefix is used
if len(dbname) < 6 || dbname[:6] != "myapp_" {
	fmt.Printf("Expected database name to start with 'myapp_', got: %s\n", dbname)
	return
}

fmt.Println("Custom prefix applied successfully")
fmt.Printf("Database name starts with: %s\n", strings.Split(dbname, "_")[0])

Output:
Custom prefix applied successfully
Database name starts with: myapp
Example (Gorm)

Example_gorm demonstrates using postgres.New() with GORM for custom database initialization. 

t := newExampleT()
defer t.done()

// Create test database with GORM initializer
// postgres.New() is used instead of postgres.Setup() when you need custom initialization
db := postgres.New(t, &GormExampleInitializer{})

// Get GORM DB instance
gormDB := db.Entity().(*gorm.DB)

// Define a model
type Article struct {
	ID      uint   `gorm:"primaryKey"`
	Title   string `gorm:"not null"`
	Content string
}

// Auto-migrate schema
if err := gormDB.AutoMigrate(&Article{}); err != nil {
	fmt.Printf("Error: %v\n", err)
	return
}

// Create a record
article := Article{Title: "Getting Started", Content: "Introduction to testdb"}
if err := gormDB.Create(&article).Error; err != nil {
	fmt.Printf("Error: %v\n", err)
	return
}

// Query the record
var found Article
if err := gormDB.First(&found, "title = ?", "Getting Started").Error; err != nil {
	fmt.Printf("Error: %v\n", err)
	return
}

fmt.Println("GORM setup complete")
fmt.Printf("Article created: %s\n", found.Title)

Output:
GORM setup complete
Article created: Getting Started
Example (HelperFunction)

Example_helperFunction demonstrates the helper function pattern for consistent test setup. 

setupDB := func(t testing.TB) *pgxpool.Pool {
	t.Helper()
	return postgres.Setup(t,
		testdb.WithMigrator(postgres.SQLFiles("../testdata/postgres/schema")),
		testdb.WithDBPrefix("myapp_test"),
	)
}

t := newExampleT()
defer t.done()
pool := setupDB(t)

if err := pool.Ping(context.Background()); err != nil {
	fmt.Printf("Error: %v\n", err)
	return
}

fmt.Println("Helper function setup complete")

Output:
Helper function setup complete
Example (MultipleOptions)

Example_multipleOptions demonstrates combining multiple configuration options. 

t := newExampleT()
defer t.done()

pool := postgres.Setup(t,
	testdb.WithDBPrefix("test"),
	testdb.WithMigrator(postgres.SQLFiles("../testdata/postgres/schema")),
)

// Verify database exists and migrations ran
var dbname string
err := pool.QueryRow(context.Background(), "SELECT current_database()").Scan(&dbname)
if err != nil {
	fmt.Printf("Error: %v\n", err)
	return
}

// Check migration ran
var exists bool
err = pool.QueryRow(context.Background(), `
		SELECT EXISTS (
			SELECT FROM information_schema.tables
			WHERE table_name = 'test_table'
		)
	`).Scan(&exists)

if err != nil {
	fmt.Printf("Error: %v\n", err)
	return
}

if !exists {
	fmt.Println("Expected test_table to exist")
	return
}

fmt.Println("Multiple options configured successfully")
fmt.Println("Database created with custom prefix")
fmt.Println("Migrations ran and tables exist")

Output:
Multiple options configured successfully
Database created with custom prefix
Migrations ran and tables exist
Example (MultipleTemplates)

Example_multipleTemplates demonstrates using multiple templates for different test scenarios. 

package main

import (
	"context"
	"fmt"
	"testing"

	"github.com/bashhack/testdb"
	"github.com/bashhack/testdb/postgres"
	"github.com/jackc/pgx/v5/pgxpool"
)

func main() {
	ctx := context.Background()

	// In TestMain: ensure one template per scenario
	us, err := postgres.EnsureTemplate(ctx, "us_region", nil)
	if err != nil {
		fmt.Printf("Error creating template: %v\n", err)
		return
	}
	defer func() {
		if err := us.Release(ctx); err != nil {
			fmt.Printf("Error releasing template: %v\n", err)
		}
	}()
	eu, err := postgres.EnsureTemplate(ctx, "eu_region", nil)
	if err != nil {
		fmt.Printf("Error creating template: %v\n", err)
		return
	}
	defer func() {
		if err := eu.Release(ctx); err != nil {
			fmt.Printf("Error releasing template: %v\n", err)
		}
	}()

	t := &testing.T{}

	// US checkout test uses US data
	dbUS := postgres.New(t, &postgres.PoolInitializer{},
		testdb.WithTemplate("us_region"))
	poolUS := dbUS.Entity().(*pgxpool.Pool)

	// EU VAT test uses EU data
	dbEU := postgres.New(t, &postgres.PoolInitializer{},
		testdb.WithTemplate("eu_region"))
	poolEU := dbEU.Entity().(*pgxpool.Pool)

	// Each test has isolated regional data
	var usDB, euDB string
	if err := poolUS.QueryRow(ctx, "SELECT current_database()").Scan(&usDB); err != nil {
		fmt.Printf("Error: %v\n", err)
		return
	}
	if err := poolEU.QueryRow(ctx, "SELECT current_database()").Scan(&euDB); err != nil {
		fmt.Printf("Error: %v\n", err)
		return
	}

	if usDB != euDB {
		fmt.Println("Each template has isolated database")
	}

	// Note: This example demonstrates the API but cannot be executed as a runnable
	// example because it uses testing.T.
}
Example (Parallel)

Example_parallel shows running tests in parallel with isolated databases. 

package main

import (
	"context"
	"fmt"
	"testing"

	"github.com/bashhack/testdb"
	"github.com/bashhack/testdb/postgres"
)

func main() {
	t := &testing.T{}

	t.Run("create user", func(t *testing.T) {
		t.Parallel()

		pool := postgres.Setup(t,
			testdb.WithMigrator(postgres.SQLFiles("../testdata/postgres/schema")))

		_, err := pool.Exec(context.Background(),
			"INSERT INTO users (email) VALUES ($1)", "user1@example.com")
		if err != nil {
			fmt.Printf("Error: %v\n", err)
			return
		}
		fmt.Println("User 1 created")
	})

	t.Run("delete user", func(t *testing.T) {
		t.Parallel()

		pool := postgres.Setup(t,
			testdb.WithMigrator(postgres.SQLFiles("../testdata/postgres/schema")))

		_, err := pool.Exec(context.Background(),
			"INSERT INTO users (email) VALUES ($1)", "user2@example.com")
		if err != nil {
			fmt.Printf("Error: %v\n", err)
			return
		}
		fmt.Println("User 2 created")
	})

	// Note: This example demonstrates the API but cannot be executed as a runnable
	// example because it uses t.Run() which requires a real testing.T instance.
}
Example (ParallelExecution)

Example_parallelExecution demonstrates running tests in parallel with isolated databases. 

package main

import (
	"context"
	"fmt"
	"testing"

	"github.com/bashhack/testdb/postgres"
)

func main() {
	t := &testing.T{}

	t.Run("test1", func(t *testing.T) {
		t.Parallel()

		pool := postgres.Setup(t)

		// Each test gets its own database
		var dbname string
		err := pool.QueryRow(context.Background(), "SELECT current_database()").Scan(&dbname)
		if err != nil {
			fmt.Printf("Error: %v\n", err)
			return
		}

		fmt.Printf("Test1 using database: %s\n", dbname)
	})

	t.Run("test2", func(t *testing.T) {
		t.Parallel()

		pool := postgres.Setup(t)

		// This database is different from test1
		var dbname string
		err := pool.QueryRow(context.Background(), "SELECT current_database()").Scan(&dbname)
		if err != nil {
			fmt.Printf("Error: %v\n", err)
			return
		}

		fmt.Printf("Test2 using database: %s\n", dbname)
	})

	t.Run("test3", func(t *testing.T) {
		t.Parallel()

		pool := postgres.Setup(t)

		// And this is yet another isolated database
		var dbname string
		err := pool.QueryRow(context.Background(), "SELECT current_database()").Scan(&dbname)
		if err != nil {
			fmt.Printf("Error: %v\n", err)
			return
		}

		fmt.Printf("Test3 using database: %s\n", dbname)
	})

	// Note: This example demonstrates the API but cannot be executed as a runnable
	// example because it uses t.Run() which requires a real testing.T instance.
}
Example (Setup)

Example_setup demonstrates basic usage of postgres.Setup for creating isolated test databases with automatic cleanup. 

t := newExampleT()
defer t.done()

// Create isolated test database with the schema applied
// Cleanup is automatic via t.Cleanup()
pool := postgres.Setup(t,
	testdb.WithMigrator(postgres.SQLFiles("../testdata/postgres/schema")))

_, err := pool.Exec(context.Background(),
	"INSERT INTO test_table (name) VALUES ($1)", "test")
if err != nil {
	fmt.Printf("Error: %v\n", err)
	return
}

fmt.Println("Database setup complete")
fmt.Println("Data inserted successfully")

Output:
Database setup complete
Data inserted successfully
Example (SetupVsNew)

Example_setupVsNew demonstrates when to use Setup() vs New(). 

t := newExampleT()
defer t.done()

// Use Setup() when working with pgx/pgxpool directly
// Returns *pgxpool.Pool - simplest API
pool := postgres.Setup(t)

var result int
err := pool.QueryRow(context.Background(), "SELECT 1").Scan(&result)
if err != nil {
	fmt.Printf("Error: %v\n", err)
	return
}
fmt.Printf("Setup() returns pool, result: %d\n", result)

// Use New() when you need custom initialization (GORM, sqlx, etc.)
// Returns *testdb.TestDatabase with more flexibility
db := postgres.New(t, &postgres.PoolInitializer{})
pool2 := db.Entity().(*pgxpool.Pool)

var result2 int
err = pool2.QueryRow(context.Background(), "SELECT 2").Scan(&result2)
if err != nil {
	fmt.Printf("Error: %v\n", err)
	return
}
fmt.Printf("New() returns TestDatabase, result: %d\n", result2)
fmt.Printf("Database name starts with: %s\n", db.Name()[:5]) // Show prefix only

Output:
Setup() returns pool, result: 1
New() returns TestDatabase, result: 2
Database name starts with: test_
Example (SqlDB)

Example_sqlDB demonstrates using postgres.New() with SqlDbInitializer for database/sql compatibility.

Use SqlDbInitializer when:

  • Your application uses database/sql interfaces
  • You need compatibility with existing sql.DB-based code
  • You're working with libraries that expect *sql.DB

For PostgreSQL-specific features, use PoolInitializer or Setup() instead. 

t := newExampleT()
defer t.done()

// Create test database with sql.DB
db := postgres.New(t, &postgres.SqlDbInitializer{})

// Get the *sql.DB instance
sqlDB := db.Entity().(*sql.DB)

// Create table
_, err := sqlDB.Exec(`
		CREATE TABLE users (
			id SERIAL PRIMARY KEY,
			name TEXT NOT NULL,
			email TEXT UNIQUE NOT NULL
		)
	`)
if err != nil {
	fmt.Printf("Error: %v\n", err)
	return
}

// Insert data
_, err = sqlDB.Exec(
	`INSERT INTO users (name, email) VALUES ($1, $2)`,
	"Alice", "alice@example.com",
)
if err != nil {
	fmt.Printf("Error: %v\n", err)
	return
}

// Query data
var name string
err = sqlDB.QueryRow(
	`SELECT name FROM users WHERE email = $1`,
	"alice@example.com",
).Scan(&name)
if err != nil {
	fmt.Printf("Error: %v\n", err)
	return
}

fmt.Println("sql.DB setup complete")
fmt.Printf("Found user: %s\n", name)

// Cleanup happens automatically via t.Cleanup()

Output:
sql.DB setup complete
Found user: Alice
Example (SqlFiles)

Example_sqlFiles applies a directory of plain .sql files to each test database, no migration tool required. For goose, tern or golang-migrate, use the adapter modules under github.com/bashhack/testdb/migrate. 

t := newExampleT()
defer t.done()

pool := postgres.Setup(t,
	testdb.WithMigrator(postgres.SQLFiles("../testdata/postgres/schema")),
)

// Verify the schema is in place
var exists bool
err := pool.QueryRow(context.Background(), `
		SELECT EXISTS (
			SELECT FROM information_schema.tables
			WHERE table_name = 'test_table'
		)
	`).Scan(&exists)

if err != nil {
	fmt.Printf("Failed to check table: %v\n", err)
	return
}

if !exists {
	fmt.Println("Expected test_table to exist after migrations")
	return
}

// Insert data to verify table works
_, err = pool.Exec(context.Background(),
	"INSERT INTO test_table (name) VALUES ($1)", "test")
if err != nil {
	fmt.Printf("Failed to insert: %v\n", err)
	return
}

fmt.Println("Schema applied from SQL files")
fmt.Println("Table exists and data inserted")

Output:
Schema applied from SQL files
Table exists and data inserted
Example (TemplateComposite)

Example_templateComposite demonstrates combining migrations with fixtures. 

package main

import (
	"context"
	"fmt"
	"testing"

	"github.com/bashhack/testdb"
	"github.com/bashhack/testdb/postgres"
	"github.com/jackc/pgx/v5/pgxpool"
)

func main() {
	ctx := context.Background()

	// In TestMain: ensure a template with both migrations and fixtures
	setupFunc := func(ctx context.Context, dsn string) error {
		// First apply the schema (or run your migration tool's adapter)
		if err := postgres.SQLFiles("../testdata/postgres/schema")(ctx, dsn); err != nil {
			return err
		}
		// Then load fixture data
		// return postgres.LoadSQLFile("fixtures/products.sql")(ctx, dsn)
		return nil
	}

	tpl, err := postgres.EnsureTemplate(ctx, "ecommerce_full", setupFunc)
	if err != nil {
		fmt.Printf("Error creating template: %v\n", err)
		return
	}
	defer func() {
		if err := tpl.Release(ctx); err != nil {
			fmt.Printf("Error releasing template: %v\n", err)
		}
	}()

	// In your test: Get fully populated database instantly
	t := &testing.T{}
	db := postgres.New(t, &postgres.PoolInitializer{},
		testdb.WithTemplate("ecommerce_full"))

	pool := db.Entity().(*pgxpool.Pool)

	var exists bool
	err = pool.QueryRow(context.Background(), `
		SELECT EXISTS (
			SELECT FROM information_schema.tables
			WHERE table_name = 'products'
		)
	`).Scan(&exists)

	if err != nil {
		fmt.Printf("Error: %v\n", err)
		return
	}

	if exists {
		fmt.Println("Schema and data ready")
	}

	// Note: This example demonstrates the API but cannot be executed as a runnable
	// example because it uses testing.T.
}
Example (TemplateCustomSetup)

Example_templateCustomSetup demonstrates using a custom setup function for programmatic data generation. 

package main

import (
	"context"
	"fmt"
	"testing"

	"github.com/bashhack/testdb"
	"github.com/bashhack/testdb/postgres"
	"github.com/jackc/pgx/v5/pgxpool"
)

func main() {
	ctx := context.Background()

	// In TestMain: ensure a template with custom Go setup logic
	setupFunc := func(ctx context.Context, dsn string) error {
		pool, err := pgxpool.New(ctx, dsn)
		if err != nil {
			return err
		}
		defer pool.Close()

		// Create schema
		_, err = pool.Exec(ctx, `
			CREATE TABLE test_data (
				id SERIAL PRIMARY KEY,
				value INTEGER NOT NULL
			)
		`)
		if err != nil {
			return err
		}

		// Programmatically generate dataset
		for i := range 100 {
			_, err = pool.Exec(ctx,
				"INSERT INTO test_data (value) VALUES ($1)", i)
			if err != nil {
				return err
			}
		}

		return nil
	}

	tpl, err := postgres.EnsureTemplate(ctx, "large_dataset", setupFunc)
	if err != nil {
		fmt.Printf("Error creating template: %v\n", err)
		return
	}
	defer func() {
		if err := tpl.Release(ctx); err != nil {
			fmt.Printf("Error releasing template: %v\n", err)
		}
	}()

	// In your test: Dataset already generated
	t := &testing.T{}
	db := postgres.New(t, &postgres.PoolInitializer{},
		testdb.WithTemplate("large_dataset"))

	pool := db.Entity().(*pgxpool.Pool)

	var count int
	err = pool.QueryRow(context.Background(),
		"SELECT COUNT(*) FROM test_data").Scan(&count)

	if err != nil {
		fmt.Printf("Error: %v\n", err)
		return
	}

	if count > 0 {
		fmt.Println("Dataset loaded from template")
	}

	// Note: This example demonstrates the API but cannot be executed as a runnable
	// example because it uses testing.T.
}
Example (TemplateWithMigrations)

Example_templateWithMigrations demonstrates using templates to avoid running migrations for every test. 

package main

import (
	"context"
	"fmt"
	"testing"

	"github.com/bashhack/testdb"
	"github.com/bashhack/testdb/postgres"
	"github.com/jackc/pgx/v5/pgxpool"
)

func main() {
	ctx := context.Background()

	// In TestMain: ensure the template once, with the schema applied.
	// A migration-tool adapter's Migrate method works here too, for
	// example goose.Dir("./migrations").Migrate.
	tpl, err := postgres.EnsureTemplate(ctx, "with_schema",
		postgres.SQLFiles("../testdata/postgres/schema"))
	if err != nil {
		fmt.Printf("Error creating template: %v\n", err)
		return
	}
	defer func() {
		if err := tpl.Release(ctx); err != nil {
			fmt.Printf("Error releasing template: %v\n", err)
		}
	}()

	// In your test: Clone from template (fast!)
	t := &testing.T{}
	db := postgres.New(t, &postgres.PoolInitializer{},
		testdb.WithTemplate("with_schema"))

	pool := db.Entity().(*pgxpool.Pool)

	// Schema already applied from template - start testing immediately
	var exists bool
	err = pool.QueryRow(context.Background(), `
		SELECT EXISTS (
			SELECT FROM information_schema.tables
			WHERE table_name = 'products'
		)
	`).Scan(&exists)

	if err != nil {
		fmt.Printf("Error: %v\n", err)
		return
	}

	if exists {
		fmt.Println("Products table exists from migrations")
	}

	// Note: This example demonstrates the API but cannot be executed as a runnable
	// example because it uses testing.T.
}

Index

Examples

Constants

View Source 
const (
	// AdminDSNEnv is the environment variable this provider checks first for
	// its admin connection string, before the shared TEST_DATABASE_URL and
	// DATABASE_URL. Set it when a project tests against more than one
	// database server.
	AdminDSNEnv = "TEST_POSTGRES_URL"

	// DefaultAdminDSN is used when no override or environment variable is set.
	DefaultAdminDSN = "postgres://postgres:postgres@localhost:5432/postgres?sslmode=disable"
)

Variables

This section is empty.

Functions

func HashFiles 

func HashFiles(paths ...string) (string, error)

HashFiles returns a fingerprint of the given files and directories, from their relative paths and contents. Directories are walked recursively in sorted order, so the result is stable. Use it with WithFingerprint: 

fp, err := postgres.HashFiles("migrations", "fixtures/us.sql")
...
tpl, err := postgres.EnsureTemplate(ctx, "us_region", setup,
    postgres.WithFingerprint(fp), postgres.WithKeepTemplate())

func New 

func New(t testing.TB, initializer testdb.DBInitializer, opts ...testdb.Option) *testdb.TestDatabase

New creates a PostgreSQL test database with a custom initializer.

When to use New():

  • You're using GORM, sqlx, ent, or another ORM/database wrapper
  • You have custom initialization logic (connection pooling, logging, tracing)
  • You need access to the TestDatabase (for DSN, config, or manual migration control)
  • You want to type-assert to your own database type

When to use Setup() instead:

  • You're using pgx/pgxpool directly (simpler API)
  • You don't need custom initialization

When to use testdb.New() instead:

  • You need manual cleanup control (not automatic via t.Cleanup)
  • You're implementing a custom database provider

The function:

  1. Creates an isolated test database
  2. Runs migrations if configured
  3. Returns a *testdb.TestDatabase with custom entity
  4. Registers cleanup via t.Cleanup() - no manual cleanup needed

IMPORTANT: Do NOT call db.Close() or manually close the entity. The function automatically registers cleanup via t.Cleanup() that will:

  1. Close the entity (pool, GORM db, sqlx db, etc.) if it implements io.Closer
  2. Drop the test database
  3. Clean up provider resources

Calls t.Fatal() on any error.

Example with GORM: 

type GormInitializer struct{}

func (g *GormInitializer) InitializeTestDatabase(ctx context.Context, dsn string) (any, error) {
    return gorm.Open(postgres.Open(dsn), &gorm.Config{})
}

func TestAdvanced(t *testing.T) {
    db := postgres.New(t, &GormInitializer{},
        testdb.WithMigrator(goose.Dir("./migrations")))
    gormDB := db.Entity().(*gorm.DB)
    // Use gormDB for testing - NO defer needed, cleanup is automatic!
}

Example with sqlx: 

type SqlxInitializer struct{}

func (s *SqlxInitializer) InitializeTestDatabase(ctx context.Context, dsn string) (any, error) {
    return sqlx.Connect("postgres", dsn)
}

func TestSqlx(t *testing.T) {
    db := postgres.New(t, &SqlxInitializer{})
    sqlxDB := db.Entity().(*sqlx.DB)
    // Use sqlxDB for testing - NO defer needed, cleanup is automatic!
}

func Setup 

func Setup(t testing.TB, opts ...testdb.Option) *pgxpool.Pool

Setup creates a PostgreSQL test database and returns a ready-to-use connection pool.

When to use Setup():

  • You're using pgx/pgxpool directly (recommended for most PostgreSQL applications)
  • You want the simplest API - just get a pool and start testing
  • You don't need custom initialization logic

When to use New() instead:

  • You need a custom initializer (GORM, sqlx, ent, or custom wrapper)
  • You need access to the TestDatabase for DSN or configuration
  • You need to run migrations manually (not during setup)

When to use testdb.New() instead:

  • You need fine-grained control over cleanup timing
  • You're implementing a custom database provider

The function:

  1. Creates an isolated test database
  2. Runs migrations if configured
  3. Returns a *pgxpool.Pool ready for testing
  4. Registers cleanup via t.Cleanup() - no manual cleanup needed

IMPORTANT: Do NOT call pool.Close() or defer any cleanup. The function automatically registers cleanup that will run after your test.

Calls t.Fatal() on any error.

Example: 

func TestUsers(t *testing.T) {
    pool := postgres.Setup(t,
        testdb.WithMigrator(goose.Dir("./migrations")))
    // Use pool for testing - NO defer pool.Close() needed!
    // Cleanup happens automatically via t.Cleanup()
}

func TemplateExists 

func TemplateExists(ctx context.Context, name string, opts ...TemplateOption) (bool, error)

TemplateExists reports whether a database with the given name exists. This is a debugging aid; EnsureTemplate handles existence itself.

Types

type PoolInitializer 

type PoolInitializer struct {
	// ConfigModifier allows customization of the pool configuration after
	// the DSN is parsed but before the pool is created.
	// If nil, sensible defaults for testing are applied.
	ConfigModifier func(*pgxpool.Config)
}

PoolInitializer is the default initializer for PostgreSQL connections. It creates a pgxpool.Pool with sensible defaults for testing.

func (*PoolInitializer) InitializeTestDatabase 

func (pi *PoolInitializer) InitializeTestDatabase(ctx context.Context, dsn string) (any, error)

InitializeTestDatabase creates a pgxpool.Pool for the test database.

type PostgresProvider 

type PostgresProvider struct {
	// contains filtered or unexported fields
}

PostgresProvider implements testdb.Provider and testdb.TemplateProvider for PostgreSQL. It handles database creation, deletion, template cloning and connection management.

func (*PostgresProvider) BuildDSN 

func (p *PostgresProvider) BuildDSN(dbName string) (string, error)

BuildDSN constructs a PostgreSQL connection string (DSN) for the specified database. It uses the cached admin config to avoid re-parsing on every call.

func (*PostgresProvider) Cleanup 

func (p *PostgresProvider) Cleanup(ctx context.Context) error

Cleanup performs the necessary cleanup of the provider's resources. This includes closing the admin database connection.

func (*PostgresProvider) CreateDatabase 

func (p *PostgresProvider) CreateDatabase(ctx context.Context, name string) error

CreateDatabase creates a new PostgreSQL database with the given name.

func (*PostgresProvider) CreateDatabaseFromTemplate 

func (p *PostgresProvider) CreateDatabaseFromTemplate(ctx context.Context, name, templateName string) (string, error)

CreateDatabaseFromTemplate creates a new database by cloning a template. This is significantly faster than CreateDatabase for databases with large schemas or pre-seeded data (~3-5ms vs ~12-15ms).

PostgreSQL's template cloning works at the filesystem level, making it extremely fast regardless of the database size.

The template database must already exist; postgres.EnsureTemplate creates it and keeps it alive while this process uses it.

func (*PostgresProvider) DropDatabase 

func (p *PostgresProvider) DropDatabase(ctx context.Context, name string) error

DropDatabase drops a PostgreSQL database if it exists. Retries on SQLSTATE 55006 to handle the race where pg_terminate_backend() has sent termination signals but connections haven't fully closed yet. This is especially important under high concurrency when multiple databases are being dropped simultaneously.

func (*PostgresProvider) Initialize 

func (p *PostgresProvider) Initialize(ctx context.Context, cfg testdb.Config) error

Initialize sets up the PostgreSQL provider with admin credentials. It establishes a connection to the admin database which will be used for creating and managing test databases.

func (*PostgresProvider) InspectCommand 

func (p *PostgresProvider) InspectCommand(dsn string) string

InspectCommand returns a psql command line that opens the database, for the kept-database log (see testdb.WithKeepOnFailure).

func (*PostgresProvider) ResolvedAdminDSN 

func (p *PostgresProvider) ResolvedAdminDSN() string

ResolvedAdminDSN returns the resolved admin DSN being used by this provider. This is the actual DSN after resolving user overrides, environment variables, and defaults. Useful for migrations and other operations that need the admin connection string.

func (*PostgresProvider) TemplateExists 

func (p *PostgresProvider) TemplateExists(ctx context.Context, name string) (bool, error)

TemplateExists checks if a template database exists. This is useful for debugging and conditional template creation.

func (*PostgresProvider) TerminateConnections 

func (p *PostgresProvider) TerminateConnections(ctx context.Context, name string) error

TerminateConnections forcefully terminates all connections to the specified database. This is necessary before dropping a database, as active connections will prevent deletion.

This implementation uses a two-step approach to handle the race condition between pool.Close() and pg_stat_activity updates: 1. DISALLOW new connections (prevents races) 2. TERMINATE existing connections

type SetupFunc 

type SetupFunc = testdb.MigratorFunc

SetupFunc populates a template database. It receives a DSN for the freshly created, empty database and loads whatever the template should contain: schema, fixtures, seed data. See SQLFiles, LoadSQLFile and Compose for ready-made setup functions; a migration-tool adapter's Migrate method (for example goose.Dir("./migrations").Migrate) is one as well.

SetupFunc is testdb.MigratorFunc, so anything that prepares a template also works per test through testdb.WithMigrator.

func Compose 

func Compose(setupFuncs ...SetupFunc) SetupFunc

Compose runs several setup functions in order, stopping at the first error. Nil functions are skipped. 

postgres.EnsureTemplate(ctx, "full",
    postgres.Compose(
        goose.Dir("./migrations").Migrate,
        postgres.LoadSQLFile("fixtures/seed.sql"),
    ))

func LoadSQLFile 

func LoadSQLFile(path string) SetupFunc

LoadSQLFile returns a setup function that runs one SQL file against the database, for SQL dumps and fixture data. It also works per test: 

postgres.EnsureTemplate(ctx, "fixtures",
    postgres.LoadSQLFile("fixtures/seed_data.sql"))
postgres.Setup(t, testdb.WithMigrator(postgres.LoadSQLFile("schema.sql")))

func SQLFiles 

func SQLFiles(dir string) SetupFunc

SQLFiles returns a setup function that runs every .sql file in dir, in lexical order, against the database: the simplest way to apply a schema without a migration tool. Name files so they sort the way they should run (001_users.sql, 002_orders.sql). The directory is read when the function runs, not when it is created. 

postgres.Setup(t, testdb.WithMigrator(postgres.SQLFiles("./schema")))

type SqlDbInitializer 

type SqlDbInitializer struct{}

SqlDbInitializer creates a standard *sql.DB connection using pgx's database/sql driver.

This uses pgx/v5/stdlib, which implements the database/sql driver interface. You get the standard library's *sql.DB type with pgx's PostgreSQL implementation underneath, providing better PostgreSQL support than lib/pq while maintaining database/sql compatibility.

Use SqlDbInitializer when:

  • Your application code uses database/sql interfaces (*sql.DB, *sql.Tx, *sql.Rows)
  • You're working with libraries that expect *sql.DB (some ORMs, query builders)
  • You need compatibility with existing sql.DB-based code
  • You want standard database/sql semantics (connection pooling, transactions, prepared statements)

For PostgreSQL-specific features (arrays, JSON types, COPY, LISTEN/NOTIFY), use PoolInitializer instead which provides *pgxpool.Pool with full pgx capabilities.

Example: 

db := postgres.New(t, &postgres.SqlDbInitializer{})
sqlDB := db.Entity().(*sql.DB)

// Use standard database/sql operations
_, err := sqlDB.Exec("INSERT INTO users (name) VALUES ($1)", "Alice")
var name string
err = sqlDB.QueryRow("SELECT name FROM users WHERE id = $1", 1).Scan(&name)

func (*SqlDbInitializer) InitializeTestDatabase 

func (si *SqlDbInitializer) InitializeTestDatabase(ctx context.Context, dsn string) (any, error)

InitializeTestDatabase creates a *sql.DB using the "pgx" driver (pgx/v5/stdlib). The connection is verified via Ping before being returned.

Returns an error if the connection cannot be established or verified. On error, the database connection is automatically closed.

type Template 

type Template struct {
	// contains filtered or unexported fields
}

Template is this process's handle on a template database obtained from EnsureTemplate. It keeps one connection to the admin database open for as long as it lives; that connection holds the lock which tells other processes the template is in use. Call Release when the process is done.

func EnsureTemplate 

func EnsureTemplate(ctx context.Context, name string, setup SetupFunc, opts ...TemplateOption) (*Template, error)

EnsureTemplate makes sure the named template database exists and marks it as in use by this process, returning a handle to Release when done. Tests then clone it with testdb.WithTemplate(name).

It is safe to call from several test packages or CI jobs at once against the same server. The first process to arrive builds the template with setup; the others wait for it and reuse it; the last one to Release drops it. A process that crashes drops out of the count automatically, because its lock dies with its connection. Templates are built under a temporary name and renamed into place, so a half-built template is never visible to another process.

The usual place to call it is TestMain: 

func TestMain(m *testing.M) {
    ctx := context.Background()
    tpl, err := postgres.EnsureTemplate(ctx, "us_region",
        postgres.LoadSQLFile("fixtures/us_region.sql"))
    if err != nil {
        log.Fatal(err)
    }
    code := m.Run()
    if err := tpl.Release(ctx); err != nil {
        log.Print(err)
    }
    os.Exit(code)
}

A ctx with a deadline bounds the whole operation, including waiting for another process's build to finish.

func (*Template) Name 

func (t *Template) Name() string

Name returns the template database's name, for use with testdb.WithTemplate.

func (*Template) Option 

func (t *Template) Option() testdb.Option

Option returns the testdb.Option that makes a test database a clone of this template, so tests can write postgres.Setup(t, tpl.Option()).

func (*Template) Release 

func (t *Template) Release(ctx context.Context) error

Release ends this process's use of the template. If no other process is still using it, and WithKeepTemplate was not given, the template is dropped. It is safe to call more than once.

type TemplateOption 

type TemplateOption func(*templateConfig)

TemplateOption configures EnsureTemplate.

func WithFingerprint 

func WithFingerprint(fingerprint string) TemplateOption

WithFingerprint identifies the template's content, typically a hash of the files it is built from (see HashFiles). EnsureTemplate reuses an existing template whose fingerprint matches and rebuilds one whose fingerprint differs, so templates can safely outlive a test run (see WithKeepTemplate). Without a fingerprint an existing template is always reused as it is.

func WithKeepTemplate 

func WithKeepTemplate() TemplateOption

WithKeepTemplate leaves the template in place when the last process releases it, so the next run can reuse it instead of rebuilding. Pair it with WithFingerprint, otherwise a kept template is never refreshed.

func WithTemplateAdminDSN 

func WithTemplateAdminDSN(dsn string) TemplateOption

WithTemplateAdminDSN overrides the admin connection string used to create and manage the template. By default it is discovered the same way as for test databases (TEST_DATABASE_URL, then DATABASE_URL, then the local default). Every process sharing a template must use the same admin database, because that is where the coordinating locks live.

Source Files

View all Source files

Directories

Path Synopsis
internal
concurrencytest
Package concurrencytest is a test package that postgres's multi-process template tests compile and run as separate processes.
 Package concurrencytest is a test package that postgres's multi-process template tests compile and run as separate processes.

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.