Learn Chi - Database Integration & ORM

#Introduction

An HTTP service without a database is just a calculator. Episode 9 connects the chi router to PostgreSQL — the most common choice in the Go ecosystem — through several approaches: the direct pgx driver, database/sql, the GORM ORM, and the sqlc code generator.

Each approach has its trade-offs. This episode gives you a clear selection map, then builds one complete CRUD that uses the repository pattern from episode 8.

#Choosing a Driver and ORM

#Four Main Paths

• pgx: a fast, modern PostgreSQL driver that supports direct SQL queries with rich data types.
• database/sql: the standard library, abstract and neutral to any database.
• GORM: a full-featured ORM with auto-migration, hooks, and a query builder.
• sqlc: generates Go code from SQL files — type safety without runtime magic.

go get github.com/jackc/pgx/v5
go get gorm.io/gorm gorm.io/driver/postgres

go get github.com/jackc/pgx/v5 fetches the pgx driver; the second line fetches GORM along with its PostgreSQL driver.

#When to Use Which

Choose pgx or database/sql for full control over SQL and maximum performance. Choose GORM for high productivity with simple schemas. Choose sqlc for complex queries you want guaranteed type-safe and validated at build time.

#pgx and Connection Pooling

#Connection Pooling with pgxpool

Don't create a new connection per request — always use a pool:

ctx := context.Background()
pool, err := pgxpool.New(ctx, os.Getenv("DATABASE_URL"))
if err != nil {
    log.Fatal(err)
}
defer pool.Close()
 
pool.Config().MaxConns = 20
pool.Config().MaxConnIdleTime = 5 * time.Minute

pgxpool.New(ctx, os.Getenv("DATABASE_URL")) creates a connection pool from a postgres://user:pass@host:5432/db URL. The pool is shared across goroutines — thousands of requests can safely use a limited number of connections.

#Schema Migrations

#Migrating with golang-migrate

Manage schema changes as migration files:

migrate create -ext sql -dir migrations create_users
migrate -path migrations -database "$DATABASE_URL" up

migrate create -ext sql -dir migrations create_users creates empty up and down files, then migrate ... up applies pending migrations. Keep all migration files in git so the schema history stays documented.

The up file contains a schema like CREATE TABLE users with the columns id BIGSERIAL PRIMARY KEY, name, and email UNIQUE. The id column uses BIGSERIAL so the database fills it in automatically.

#The Repository Pattern

#Abstracting Queries

The repository packages data access in one place:

type UserRepo struct {
    pool *pgxpool.Pool
}
 
func (repo *UserRepo) FindByID(ctx context.Context, id int) (User, error) {
    var u User
    err := repo.pool.QueryRow(ctx,
        "SELECT id, name, email FROM users WHERE id = $1", id).
        Scan(&u.ID, &u.Name, &u.Email)
    return u, err
}

repo.pool.QueryRow(ctx, sql, id).Scan(...) runs a parameterized query — the $1 placeholder prevents SQL injection, not string concatenation.

#Insert with Return

Take return values directly from the database:

func (repo *UserRepo) Create(ctx context.Context, u User) (User, error) {
    err := repo.pool.QueryRow(ctx,
        "INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id",
        u.Name, u.Email).Scan(&u.ID)
    return u, err
}

RETURNING id makes INSERT return the generated column value — a single round-trip, without an extra query.

#CRUD Inside Handlers

#Assembling All Layers

Combine the repository, service, and handler into one flow:

type UserHandler struct {
    repo *repository.UserRepo
}
 
func (h *UserHandler) List(w http.ResponseWriter, req *http.Request) {
    users, err := h.repo.List(req.Context())
    if err != nil {
        http.Error(w, "gagal memuat users",
            http.StatusInternalServerError)
        return
    }
    writeJSON(w, http.StatusOK, users)
}
 
func (h *UserHandler) Create(w http.ResponseWriter, req *http.Request) {
    var u User
    if err := json.NewDecoder(req.Body).Decode(&u); err != nil {
        http.Error(w, "body tidak valid", http.StatusBadRequest)
        return
    }
    created, err := h.repo.Create(req.Context(), u)
    if err != nil {
        http.Error(w, "gagal membuat user",
            http.StatusInternalServerError)
        return
    }
    writeJSON(w, http.StatusCreated, created)
}

h.repo.List(req.Context()) forwards the request context all the way to the database query — when the client cancels the request, the query gets cancelled too. This is a pattern we'll deepen in episode 12.

#Registering the Routes

h := &handler.UserHandler{repo: userRepo}
 
r.Route("/users", func(users chi.Router) {
    users.Get("/", h.List)
    users.Post("/", h.Create)
    users.Get("/{id}", h.GetByID)
    users.Put("/{id}", h.Update)
    users.Delete("/{id}", h.Delete)
})

users.Delete("/{id}", h.Delete) closes the CRUD cycle on one tidy subrouter — exactly the subrouter pattern we built in episode 5.

#Conclusion

Key takeaways:

• Choose pgx, database/sql, GORM, or sqlc based on your query needs.
• Always use connection pooling, not a connection per request.
• Manage schemas with versioned migrations like golang-migrate.
• The repository packages queries; handlers don't write SQL.
• Parameterized queries with placeholders prevent SQL injection.
• The request context is forwarded all the way to the database for cancellation.

In the next episode 10 we discuss configuration: configuration and environment — environment variables with os.Getenv, godotenv and Viper, config structs injected into handlers, and graceful shutdown with signal.NotifyContext.