Learn Echo - Database & ORM Integration

#Introduction

Almost every REST API eventually ends up at data storage. This episode connects the architecture from episode 8 to a real database: PostgreSQL as the primary choice, alongside approaches with database/sql, GORM, and sqlc. You'll see the repository pattern truly at work.

Episode 9 covers PostgreSQL connections with pgx, pooling, schema migrations, and a complete CRUD implementation in Echo handlers.

#Setting Up PostgreSQL

#Running the Database with Docker

Start with a local PostgreSQL instance. The cleanest way is Docker:

docker run --name belajar-echo-pg \
  -e POSTGRES_USER=postgres \
  -e POSTGRES_PASSWORD=postgres \
  -e POSTGRES_DB=belajar_echo \
  -p 5432:5432 \
  -d postgres:16

Once the container is running, you have a belajar_echo database at localhost:5432. Note the credentials — they'll be used as DATABASE_URL in episode 10.

#Connections with pgx and database/sql

#Pooling with pgxpool

pgx is the most popular PostgreSQL driver for Go, and pgxpool provides a healthy connection pool for an HTTP server:

import (
	"context"
	"github.com/jackc/pgx/v5/pgxpool"
)
 
func NewDB(ctx context.Context, dsn string) (*pgxpool.Pool, error) {
	pool, err := pgxpool.New(ctx, dsn)
	if err != nil {
		return nil, err
	}
	if err := pool.Ping(ctx); err != nil {
		pool.Close()
		return nil, err
	}
	return pool, nil
}

pgxpool.New creates a pool ready to be used concurrently by many goroutines — exactly what an Echo server handling many requests at once needs.

#Queries with Parameters

Use parameterized queries to avoid SQL injection:

func (r *userRepo) FindByID(ctx context.Context, id int) (*model.User, error) {
	row := r.pool.QueryRow(ctx,
		"SELECT id, name, email FROM users WHERE id = $1", id)
	var u model.User
	if err := row.Scan(&u.ID, &u.Name, &u.Email); err != nil {
		return nil, err
	}
	return &u, nil
}

The $1 placeholder is filled with a variable's value, not concatenated into a string. This is a non-negotiable principle for every database query.

#Alternatives: GORM and sqlc

#GORM for Productivity

GORM is the most popular ORM in the Go ecosystem. It's a good fit when you want to avoid writing repetitive SQL:

go get gorm.io/gorm
go get gorm.io/driver/postgres

import (
	"gorm.io/driver/postgres"
	"gorm.io/gorm"
)
 
db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{})
db.AutoMigrate(&model.User{})

db.AutoMigrate creates or updates tables from struct definitions. It's convenient for development, but in production migrations should be managed by a dedicated tool.

#sqlc for Type-Safe SQL

sqlc takes the opposite route: you write SQL, and type-safe Go code is generated from .sql files:

go install github.com/sqlc-dev/sqlc/cmd/sqlc@latest
sqlc init

Each approach has trade-offs: database/sql is minimal and transparent, GORM is productive, sqlc is type-safe. Choose based on your team's needs; all of them work well behind the repository interface.

#Schema Migrations

#Managed Migrations with goose

For production, use a versioned migration tool. goose is a simple SQL-based choice:

go install github.com/pressly/goose/v3/cmd/goose@latest
goose -dir migrations postgres \
  "host=localhost user=postgres password=postgres dbname=belajar_echo" up

Migration files are stored in the migrations/ directory in version order. The goose up command applies all pending migrations.

#Complete CRUD in Echo Handlers

#Repository, Service, Handler

With the pattern from episode 8, CRUD is built in three layers. The repository holds queries, the service manages the flow, and the handler connects it to HTTP:

func (h *UserHandler) Create(c echo.Context) error {
	var input model.CreateUserInput
	if err := c.Bind(&input); err != nil {
		return echo.NewHTTPError(http.StatusBadRequest, err.Error())
	}
	user, err := h.svc.Create(c.Request().Context(), input)
	if err != nil {
		return err
	}
	return c.JSON(http.StatusCreated, user)
}

The handler stays thin: bind, call the service, return the response. All transaction and business-validation logic lives in the service, and all queries in the repository.

#Transactions at the Service Level

The service is also what manages transactions — for example, creating a user while recording an audit log:

tx, err := s.db.BeginTx(ctx, nil)
defer tx.Rollback()
if _, err := tx.Exec(ctx, "INSERT INTO users ..."); err != nil {
	return err
}
if _, err := tx.Exec(ctx, "INSERT INTO audit_logs ..."); err != nil {
	return err
}
return tx.Commit()

The BeginTx and Commit pattern ensures multiple operations become a single atomic unit.

#Closing

Episode 9 connects Echo to the world of data: a pgx connection pool that's safe for concurrency, parameterized queries for security, the GORM and sqlc choices according to your needs, managed migrations with goose, and a complete CRUD structured in the repository, service, and handler pattern.

Key takeaways:

• pgxpool provides connections safe for high concurrency.
• Always use parameterized queries to prevent SQL injection.
• GORM is productive for development; sqlc generates type-safe code.
• Production migrations are managed by a versioned tool like goose, not AutoMigrate.
• The handler stays thin: bind, call the service, return the response.
• Atomic transactions are managed at the service level.

In episode 10 next, we'll discuss configuration & environment — reading environment variables with os.Getenv, godotenv, and Viper, a centralized config struct, a custom HTTP server with ReadTimeout and WriteTimeout, and clean graceful shutdown when the server receives a stop signal.