~ / Blog / How I Work With PostgreSQL in Go

How I Work With PostgreSQL in Go

May 15, 2025

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(&amp;user.ID, &amp;user.CreatedAt, &amp;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 := &amp;models.User{}
err := r.dbpool.QueryRow(
    ctx,
    query,
    id,
).Scan(
    &amp;user.ID,
    &amp;user.FirstName,
    &amp;user.LastName,
    &amp;user.Email,
    &amp;user.Password,
    &amp;user.CreatedAt,
    &amp;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.