docker-compose.yml

version: "3.6"
services:
  testdb:
    image: postgres:15
    environment:
      POSTGRES_PASSWORD: password
    restart: unless-stopped
    volumes:
      # Uses a tmpfs volume to make tests extremely fast. The data in test
      # databases is not persisted across restarts, nor does it need to be.
      - type: tmpfs
        target: /var/lib/postgresql/data/
    command:
      - "postgres"
      - "-c"
      - "fsync=off"
      - "-c"
      - "shared_buffers=1024MB"
      - "-c"
      - "synchronous_commit=off"
      - "-c"
      - "full_page_writes=off"
      - "-c"
      - "log_statement=all"
      - "-c"
      - "max_connections=1000"
    ports:
      - "5433:5432"

main_test.go

package main_test

import (
  "testing"
  "database/sql"

  _ "github.com/lib/pq" // or any other postgres driver

  "github.com/peterldowns/testy/assert"
  "github.com/peterldowns/pgtestdb"
  "github.com/peterldowns/pgtestdb/migrators/goosemigrator"
)

// runs against its own, fast, isolated, postgres database
// parallel works great :)
func TestWithDatabase(t *testing.T) {
  t.Parallel()
  db := newDB(t)

  var message string
  err := db.QueryRow("select 'hello world'").Scan(&message)
  assert.Nil(t, err)
  assert.Equal(t, "hello world", message)
}

// creates a new database or fails the test.
func newDB(t *testing.T) *sql.DB {
  t.Helper()
  migrator := goosemigrator.New("migrations")
  return pgtestdb.New(t, pgtestdb.Config{
    DriverName: "postgres",
    Host:       "localhost",
    User:       "postgres",
    Password:   "password",
    Port:       "5433",
    Options:    "sslmode=disable",
  }, migrator)
}

This is an example of how to use peterldowns/pgtestdb to run a test that gets its own test database, fully migrated with pressly/goose.

Save these two files to a directory, then:

go mod init example
go mod tidy
docker compose up -d
go test .

If you want to actually run some migrations, create a folder migrations and add a migration file, like 0001_cats.sql. This example uses pressly/goose but there are plenty of other adaptors for the most common migration frameworks:

-- +goose Up
CREATE TABLE public.cats (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text
);

-- +goose Down
DROP TABLE public.cats;