Learn Gin - Database & ORM Integration

#Introduction

An API that only stores data in memory won't last long. This episode 9 connects the architecture you built in episode 8 to a real database: PostgreSQL. You'll learn to choose a driver, create connections with healthy connection pooling, manage schemas through migrations, and implement the repository pattern with full CRUD.

Why is this topic important? The database is the source of truth for almost every business application. Connections managed carelessly waste resources, unmigrated schemas leave teams out of sync, and queries embedded in handlers make testing difficult. The repository layer addresses all three.

#Choosing a Database Driver

#Three Main Options

Go has several ways to connect to PostgreSQL, each with trade-offs:

• database/sql + lib/pq: the standard approach with a simple DSN, good for raw queries.
• pgx: the high-performance driver most recommended today, available as pgxpool.
• GORM: a feature-rich ORM with AutoMigrate, relations, and hooks — the most productive for CRUD.

For projects needing speed and control, pgx is the primary choice; for productivity, GORM excels. You can combine both: GORM can even run on top of database/sql.

#Connections and Connection Pooling

#Building a Pool with pgxpool

Open a connection from the environment, then configure the pool size:

func ConnectPool(ctx context.Context, dsn string) (*pgxpool.Pool, error) {
    cfg, err := pgxpool.ParseConfig(dsn)
    if err != nil {
        return nil, err
    }
    cfg.MaxConns = 10
    cfg.MinConns = 2
    cfg.MaxConnLifetime = 30 * time.Minute
    cfg.MaxConnIdleTime = 5 * time.Minute
 
    pool, err := pgxpool.NewWithConfig(ctx, cfg)
    if err != nil {
        return nil, err
    }
    if err := pool.Ping(ctx); err != nil {
        pool.Close()
        return nil, err
    }
    return pool, nil
}

The setting cfg.MaxConns = 10 limits concurrent connections so you don't exhaust database resources. MaxConnLifetime prevents stale connections, and pool.Ping(ctx) verifies the connection before use. Call pool.Close() when the application stops to clean up resources.

#Database Migrations

#Creating and Running Migrations

Migrations keep the schema stored as versioned files. Install the golang-migrate CLI, create a migration file, then run it:

go install -tags 'postgres' github.com/golang-migrate/migrate/v4/cmd/migrate@latest
 
migrate create -ext sql -dir migrations -seq create_users
ls migrations

export DATABASE_URL="postgres://arman:rahasia@localhost:5432/belajargin?sslmode=disable"
 
migrate -path migrations -database "$DATABASE_URL" up
migrate -path migrations -database "$DATABASE_URL" version

The file 000001_create_users.up.sql contains the SQL statements to create the table, and its .down.sql counterpart reverses them. The command migrate -path migrations -database "$DATABASE_URL" up applies all pending migrations. In production, run migrations in the pipeline, not inside a handler.

#CRUD with GORM

#Models and AutoMigrate

GORM maps structs to tables. Open a connection and create the schema automatically:

type User struct {
    ID        uint      `gorm:"primaryKey"`
    Name      string    `gorm:"size:100;not null"`
    Email     string    `gorm:"uniqueIndex"`
    CreatedAt time.Time
}
 
func OpenGORM(dsn string) (*gorm.DB, error) {
    db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{})
    if err != nil {
        return nil, err
    }
    if err := db.AutoMigrate(&User{}); err != nil {
        return nil, err
    }
    return db, nil
}

db.AutoMigrate(&User{}) creates the users table with its indexes if it doesn't exist. For schemas that evolve over time, still use versioned migrations; AutoMigrate is only a convenience for early development.

#A Repository with Full CRUD

Implement the repository using GORM:

type UserRepo struct {
    db *gorm.DB
}
 
func (r *UserRepo) Create(ctx context.Context, u *User) error {
    return r.db.WithContext(ctx).Create(u).Error
}
 
func (r *UserRepo) FindByID(ctx context.Context, id uint) (*User, error) {
    var u User
    err := r.db.WithContext(ctx).First(&u, id).Error
    return &u, err
}
 
func (r *UserRepo) List(ctx context.Context) ([]User, error) {
    var users []User
    err := r.db.WithContext(ctx).Find(&users).Error
    return users, err
}
 
func (r *UserRepo) Update(ctx context.Context, u *User) error {
    return r.db.WithContext(ctx).Save(u).Error
}
 
func (r *UserRepo) Delete(ctx context.Context, id uint) error {
    return r.db.WithContext(ctx).Delete(&User{}, id).Error
}

All methods accept ctx and pass it through r.db.WithContext(ctx). This is important: when the client cancels the request, the query is cancelled too. The WithContext(ctx) pattern links the database lifecycle to the request lifecycle.

#A Handler Using the Repository

The handler at the top layer just calls the injected repository:

func (h *UserHandler) ListUsers(c *gin.Context) {
    users, err := h.repo.List(c.Request.Context())
    if err != nil {
        c.JSON(500, gin.H{"error": err.Error()})
        return
    }
    c.JSON(200, gin.H{"users": users})
}

h.repo.List(c.Request.Context()) keeps the handler clean of SQL. Note that this follows the layering pattern from episode 8 — the repository is used directly in the handler for a short example, but in a real project put it behind a service.

#Closing

Key takeaways:

• pgx with pgxpool for fast connections and healthy connection pooling.
• Versioned migrations with golang-migrate keep schemas in sync.
• GORM maps structs to tables with AutoMigrate and a query builder.
• Always pass ctx so queries are cancelled when the request is cancelled.
• The repository pattern hides SQL from handlers.
• Tune MaxConns, MaxConnLifetime, and MaxConnIdleTime according to load.

In the next episode, episode 10, we'll dissect configuration & environment — reading environment variables with os.Getenv, godotenv, and Viper, structuring a config struct injected into handlers, and implementing graceful shutdown with http.Server and signal.NotifyContext.