~ / Blog / 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.
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.
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;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.
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.
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.
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!"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.