How I Work With PostgreSQL in Go

In this article, I share my workflow for integrating PostgreSQL with my Go hobby project Vauban.

As I'm currently looking for a job as a backend developer, I've been keeping myself busy with side projects like this one.

I'll cover my database driver setup, migrations, repository pattern implementation, and seeding strategy.

I hope you'll find value in it.

1. Database Driver: pgx with Connection Pooling

I use jackc's pgx/v5 driver with connection pooling:

dbpool, err := pgxpool.New(context.Background(), env.GetVar("DATABASE_URL", "postgres://vauban:vauban@localhost/vauban?sslmode=disable"))
if err != nil {
    exit.WithErr(err)
}
defer dbpool.Close()

// Verify connection works
if err := dbpool.Ping(context.Background()); err != nil {
    exit.WithErr(err)
}

The connection pool handles lifecycle management, which helps when handling concurrent database access in web applications.

2. Migration Management with tern

For schema migrations, I use tern, also by jackc:

migration-up:
	@echo "⬆️  Running migrations..."
	TERN_MIGRATIONS="./database/migrations" tern -c ./tern.dev.conf migrate

migration-down:
	@echo "⬇️  Rolling back migrations..."
	TERN_MIGRATIONS="./database/migrations" tern -c ./tern.dev.conf migrate --destination 0

migration-fresh: migration-down migration-up
	@echo "🔄 Database freshed!"

Each migration contains both up and down SQL:

CREATE TABLE users(
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

---- create above / drop below ----

DROP TABLE users;

3. Development Environment

My Makefile creates a consistent development environment:

.PHONY: setup db-up db-down migration-up migration-down migration-fresh seed clean

setup:
	@echo "🔧 Installing tern for database migrations..."
	go install github.com/jackc/tern/v2@latest
	@echo "🔧 Installing Reflex for hot reload"
	go install github.com/cespare/reflex@latest
	@echo "📦 Installing dependencies..."
	go mod tidy

db-up:
	@echo "🐳 Starting database container..."
	docker compose -f ./compose.dev.yml up -d

db-down:
	@echo "🛑 Stopping database container..."
	docker compose -f ./compose.dev.yml down

css:
	npx @tailwindcss/cli -i ./views/styles.css -o ./public/assets/styles.css --watch

dev:
	reflex --start-service -- sh -c 'go run ./cmd/http'

clean: db-down
	@echo "🧹 Development environment has been cleaned up!"

This allows for quick setup of the environment, isolated Docker containers for the database, and hot reloading during development.

4. Repository Pattern

I organize database access through repositories:

type UsersRepository interface {
	// Store creates a user record in the database, mutating fields generated by the DBMS.
	Store(ctx context.Context, user *models.User) error

	// GetByID retrieves a user by their ID.
	// Returns ErrUserNotFound if the user doesn't exist.
	GetByID(ctx context.Context, id int64) (*models.User, error)

	// GetByEmail retrieves a user by their email address.
	// Returns ErrUserNotFound if the user doesn't exist.
	GetByEmail(ctx context.Context, email string) (*models.User, error)
}

// ErrUserNotFound is returned when a user with the specified ID is not found.
var ErrUserNotFound = errors.New("user not found")

type usersRepository struct {
	dbpool *pgxpool.Pool
}

func NewUsersRepository(dbpool *pgxpool.Pool) UsersRepository {
	return &usersRepository{
		dbpool: dbpool,
	}
}

The repository pattern separates database access logic from business logic and provides a clean, testable interface.

5. Writing SQL Queries

I write plain SQL within the repository methods:

func (r *usersRepository) Store(ctx context.Context, user *models.User) error {
	user.Email = r.normalizeEmail(user.Email)

	query := `
		INSERT INTO users (first_name, last_name, email, password)
		VALUES ($1, $2, $3, $4)
		RETURNING id, created_at, updated_at
	`
	err := r.dbpool.QueryRow(
		ctx,
		query,
		user.FirstName,
		user.LastName,
		user.Email,
		user.Password,
	).Scan(&user.ID, &user.CreatedAt, &user.UpdatedAt)
	if err != nil {
		return fmt.Errorf("failed to store user: %w", err)
	}
	return nil
}

func (r *usersRepository) GetByID(ctx context.Context, id int64) (*models.User, error) {
	query := `
		SELECT id, first_name, last_name, email, password, created_at, updated_at
		FROM users
		WHERE id = $1
	`

	user := &models.User{}
	err := r.dbpool.QueryRow(
		ctx,
		query,
		id,
	).Scan(
		&user.ID,
		&user.FirstName,
		&user.LastName,
		&user.Email,
		&user.Password,
		&user.CreatedAt,
		&user.UpdatedAt,
	)

	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return nil, ErrUserNotFound
		}
		return nil, fmt.Errorf("failed to get user by ID: %w", err)
	}

	return user, nil
}

I use parameterized queries to prevent SQL injection and wrap errors with context.

6. Database Seeding

For development and testing, I use seeders to populate the database:

// The seeder interface
type Seeder interface {
	Seed(ctx context.Context) error
}

// User seeder implementation
type UserSeeder struct {
	dbpool *pgxpool.Pool
}

func NewUserSeeder(dbpool *pgxpool.Pool) *UserSeeder {
	return &UserSeeder{
		dbpool: dbpool,
	}
}

// Seed creates the default set of users in the database.
func (s *UserSeeder) Seed(ctx context.Context) error {
	usersRepository := repositories.NewUsersRepository(s.dbpool)

	users := []*models.User{
		{Email: "socrates@athens.gr", Password: "i_know_that_i_know_nothing"},
		{Email: "plato@academy.gr", Password: "the_unexamined_life_is_not_worth_living"},
		// Additional users...
	}

	for _, user := range users {
		if err := usersRepository.Store(ctx, user); err != nil {
			return err
		}
	}

	return nil
}

This runs via a dedicated command, that I put in its own file:

// ./cmd/seed/main.go
func main() {
	ctx, cancel := context.WithTimeout(context.Background(), 10*time.Second)
	defer cancel()

	dbpool, err := pgxpool.New(ctx, env.GetVar("DATABASE_URL", "postgres://panache:panache@localhost/panache?sslmode=disable"))
	if err != nil {
		exit.WithErr(err)
	}
	defer dbpool.Close()

	seeders := []seeders.Seeder{
		seeders.NewUserSeeder(dbpool),
	}

	for _, seeder := range seeders {
		if err := seeder.Seed(ctx); err != nil {
			exit.WithErr(err)
		}
	}
}

And then, I add a new command in my Makefile:

seed:
	@echo "🌱 Seeding database..."
	@go run ./cmd/seed
	@echo "✨ Database successfully seeded!"

Conclusion

If you liked this article and want to see more of my work, check out my GitHub profile.

I'm currently looking for a developer position, so feel free to reach out if you think my approach to solving problems would be valuable to your team.