Setting up a test postgres instance wrapped by your store interface in a Go app.

testutil.go

func NewStoreWithTestDatabase(ctx context.Context) (*store.Store, func() error, error) {
	// uses a sensible default on windows (tcp/http) and linux/osx (socket)
	pool, err := dockertest.NewPool("")
	if err != nil {
		return nil, nil, errs.Wrap(err, "could not construct pool", nil)
	}

	// uses pool to try to connect to Docker
	err = pool.Client.Ping()
	if err != nil {
		return nil, nil, errs.Wrap(err, "could not connect to Docker", nil)
	}

	// pulls an image, creates a container based on it and runs it
	resource, err := pool.RunWithOptions(&dockertest.RunOptions{
		Repository: "postgres",
		Tag:        "14",
		Env: []string{
			"POSTGRES_PASSWORD=secret",
			"POSTGRES_USER=user_name",
			"POSTGRES_DB=dbname",
			"listen_addresses = '*'",
		},
	}, func(config *docker.HostConfig) {
		config.AutoRemove = false
		config.RestartPolicy = docker.RestartPolicy{Name: "no"}
	})
	if err != nil {
		return nil, nil, errs.Wrap(err, "could not start postgres resource", nil)
	}

	// Tell docker to kill the container in five minutes if we don't
	resource.Expire(300)

	conn := fmt.Sprintf("postgres://user_name:secret@%s/dbname?sslmode=disable", resource.GetHostPort("5432/tcp"))

	log.Printf("testutil/store: Connecting to test database %s", conn)

	var s *store.Store
	if err := pool.Retry(func() error {
		s, err = store.NewStore(ctx, conn)
		if err != nil {
			return err
		}

		return s.Ping()
	}); err != nil {
		return nil, nil, errs.Wrap(err, "could not connect to postgres", nil)
	}

	// Load schema into instance - use embed to expose the schema file from
	// your store package.
	_, err = s.DB().ExecContext(context.Background(), string(store.Schema))
	if err != nil {
		return nil, nil, errs.Wrap(err, "failed to load schema", nil)
	}

	closeFunc := func() error {
		if err := pool.Purge(resource); err != nil {
			return errs.Wrap(err, "failed to shutdown postgres", nil)
		}

		return nil
	}

	return s, closeFunc, nil
}

This uses https://github.com/ory/dockertest to run postgres in a Docker container that's then torn down on completion.

We use https://github.com/kyleconroy/sqlc for a store interface layer which lets us have a nice pure SQL "schema.sql" file which is embedded and exported using go:embed.

Run with go test -skip-purge to prevent database teardown on exit - container will still expire in 5 minutes, but you'll be able to connect and debug as you please until then.