Example of the integration test suite for testcontainers/sqlc/goose/postgresql

testcontainers_sqlc_goose_integration_test.go

// Put it as integration_test.go or suite_test.go in tests/integration pkg

package integration_test

import (
	"context"
	"database/sql"
	"testing"
	"time"

	"github.com/jackc/pgx/v5/pgxpool"
	"github.com/pressly/goose/v3"
	"github.com/stretchr/testify/suite"
	"github.com/testcontainers/testcontainers-go"
	"github.com/testcontainers/testcontainers-go/modules/postgres"
	"github.com/testcontainers/testcontainers-go/wait"

	"YOUR_PROJECT_HERE/internal/db/sqlc" 
)

type IntegrationSuite struct {
	suite.Suite
	pgContainer *postgres.PostgresContainer
	db          *sql.DB 
	pool        *pgxpool.Pool
	queries     *sqlc.Queries 
}

func TestIntegrationSuite(t *testing.T) {
	suite.Run(t, new(IntegrationSuite))
}

func (s *IntegrationSuite) SetupSuite() {
	ctx := context.Background()
  
	pgC, err := postgres.RunContainer(ctx,
		testcontainers.WithImage("postgres:16-alpine"),
		postgres.WithDatabase("testdb"),
		postgres.WithUsername("testuser"),
		postgres.WithPassword("testpass"),
		testcontainers.WithWaitStrategy(
			wait.ForLog("database system is ready to accept connections").
				WithOccurrence(2).
				WithStartupTimeout(30*time.Second),
		),
	)
	s.Require().NoError(err)
	s.pgContainer = pgC

	connStr, err := pgC.ConnectionString(ctx, "sslmode=disable")
	s.Require().NoError(err)

	// goose require sql.DB
	db, err := sql.Open("postgres", connStr)
	s.Require().NoError(err)
	s.db = db

	// Assume migrations located here "migrations/" (0001_create_table.up.sql, etc)
	err = goose.SetDialect("postgres")
	s.Require().NoError(err)
	err = goose.Up(db, "migrations")
	s.Require().NoError(err)

	poolConfig, err := pgxpool.ParseConfig(connStr)
	s.Require().NoError(err)
	poolConfig.MaxConns = 10 
	s.pool, err = pgxpool.NewWithConfig(ctx, poolConfig)
	s.Require().NoError(err)

	s.queries = sqlc.New(s.pool) 
}

func (s *IntegrationSuite) TearDownSuite() {
	ctx := context.Background()

	if s.pool != nil {
		s.pool.Close()
	}
  
	if s.db != nil {
		s.db.Close()
	}

	if s.pgContainer != nil {
		err := s.pgContainer.Terminate(ctx)
		s.Require().NoError(err)
	}
}

// Example of test
func (s *IntegrationSuite) TestCreateUser() {
	ctx := context.Background()
  
	user, err := s.queries.CreateUser(ctx, sqlc.CreateUserParams{
		Name:  "Test User",
		Email: "test@example.com",
	})
	s.Require().NoError(err)
	s.NotZero(user.ID) 
  
	gotUser, err := s.queries.GetUser(ctx, user.ID)
	s.Require().NoError(err)
	s.Equal("Test User", gotUser.Name)
}