Learning Golang - Database, Persistence, and Data Access

#Introduction

An application that only stores data in memory loses everything on restart. That's where persistence comes in. Episode 8 takes you through connecting a Go application to a relational database safely and efficiently.

We start with database/sql — Go's standard interface for SQL databases — then move to popular drivers like PostgreSQL, connection pooling, parameterized queries for protection against SQL injection, and transaction management for data consistency. Finally, you'll see a comparison of the code-generation approaches of sqlc, ent, and gorm.

#database/sql and Drivers

#Opening a Connection

Go doesn't ship a built-in database driver; database/sql is the interface, and drivers provide the implementation. The most popular examples for PostgreSQL are github.com/jackc/pgx and github.com/lib/pq.

go get github.com/jackc/pgx/v5/stdlib

package main
 
import (
	"database/sql"
	"fmt"
 
	_ "github.com/jackc/pgx/v5/stdlib"
)
 
func main() {
	dsn := "postgres://user:pass@localhost:5432/app"
	db, err := sql.Open("pgx", dsn)
	if err != nil {
		panic(err)
	}
	defer db.Close()
	fmt.Println("koneksi database siap")
}

The blank import _ "github.com/jackc/pgx/v5/stdlib" registers the driver without using its identifier. sql.Open doesn't contact the database directly; verification is done with db.Ping().

#Connection Pool

database/sql manages a connection pool internally. Tune SetMaxOpenConns, SetMaxIdleConns, and SetConnMaxLifetime to match the database capacity and the application's load patterns:

db.SetMaxOpenConns(25)
db.SetMaxIdleConns(5)
db.SetConnMaxLifetime(30 * time.Minute)

Too many open connections exhaust the database's resources; too few makes requests queue up. SetConnMaxLifetime prevents stale connections that could break behind proxies and load balancers.

#Query Parameterization

#Avoiding SQL Injection

The most important rule: never build queries with string concatenation. Use the placeholders $1, $2 on PostgreSQL and ? on MySQL. The driver escapes values safely.

var nama string
err := db.QueryRow(
	"SELECT nama FROM pengguna WHERE id = $1",
	id,
).Scan(&nama)
if err != nil {
	log.Fatal(err)
}
fmt.Println("nama:", nama)

QueryRow returns a single row and Scan maps the columns to variables. If the query produces no rows, the sql.ErrNoRows error is returned — check it and give an appropriate response. This example is part of a pattern you can quickly test with go run main.go once the database connection is available.

#SELECT with Multiple Rows

For many rows, use Query and iterate over Rows:

rows, err := db.Query(
	"SELECT id, nama FROM pengguna WHERE aktif = $1",
	true,
)
if err != nil {
	log.Fatal(err)
}
defer rows.Close()
 
for rows.Next() {
	var id int
	var nama string
	if err := rows.Scan(&id, &nama); err != nil {
		log.Fatal(err)
	}
	fmt.Println(id, nama)
}
if err := rows.Err(); err != nil {
	log.Fatal(err)
}

rows.Close() and rows.Err() must be called to release the connection and check for errors that occurred during iteration.

#Transaction Management

#BEGIN, COMMIT, and ROLLBACK

Operations involving multiple queries must run in a single transaction to stay consistent — for example, a transfer between accounts. db.BeginTx starts a transaction, tx.Commit locks in the results, and tx.Rollback cancels them.

tx, err := db.BeginTx(ctx, nil)
if err != nil {
	return err
}
defer tx.Rollback()
 
_, err = tx.Exec(
	"UPDATE rekening SET saldo = saldo - $1 WHERE id = $2",
	jumlah, dariRekening,
)
if err != nil {
	return err
}
_, err = tx.Exec(
	"UPDATE rekening SET saldo = saldo + $1 WHERE id = $2",
	jumlah, keRekening,
)
if err != nil {
	return err
}
 
return tx.Commit()

defer tx.Rollback() is safe to call even after Commit — after a commit, the rollback becomes a no-op. All queries using tx share the same transaction.

#ORMs and Code Generation

#sqlc: SQL as the Source of Truth

sqlc generates Go code from SQL files. You write queries as pure SQL, and sqlc produces reflection-free, type-safe functions. This approach keeps full control over queries.

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

#ent and gorm

ent is an entity framework with code generation, popular for complex and graph-like schemas. gorm is an ORM offering struct-to-table mapping with automatic migrations. The choice: use sqlc for queries that need control, ent for large domain models, and gorm for quick prototypes. Performance consistency and ease of debugging are the main considerations.

#Data Access Best Practices

Several patterns that keep applications healthy in production:

• Parameterized queries for all user input.
• Context-aware: database operations use context.Context so they can be cancelled when a request is cancelled.
• Repository pattern: separate queries from business logic so they are easy to test with mocks.
• Proper indexes: profile slow queries with EXPLAIN in PostgreSQL.
• Rate limit the database: avoid connection spikes during peak load.

This repository pattern is also what we'll use when we add caching and schema migrations in episode 9.

#Closing

Episode 8 connected Go applications to the database world: opening connections with database/sql and the pgx driver, configuring connection pools, writing parameterized queries safe from SQL injection, managing transactions with BeginTx, Commit, and Rollback, and choosing between sqlc, ent, and gorm.

Key takeaways:

• database/sql is the interface; drivers provide the implementation.
• Configure the connection pool so the database isn't overwhelmed.
• Always use $1 placeholders to prevent SQL injection.
• QueryRow for a single row, Query for many rows.
• Use transactions for consistent multi-query operations.
• sqlc for full control, ent or gorm for productivity.

In the next episode we will discuss state, caching, and schema migrations — in-memory caching techniques and Redis integration with go-redis, database schema management with golang-migrate, and state management strategies for Go services. Your application will be faster and better structured.