Go Practice #4 DB Integration — database/sql and Transactions

After #3 JSON I/O, this post turns to where data is actually persisted. DB integration.

Go’s standard is — the database/sql package. There’s no ORM-like abstraction in the standard library. You handle SQL directly — but the tool helps you handle it safely.

Getting started — driver registration #

import (
	"database/sql"
	_ "github.com/jackc/pgx/v5/stdlib"
)

func main() {
	db, err := sql.Open("pgx", "postgres://user:pass@localhost:5432/mydb")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	if err := db.Ping(); err != nil {
		log.Fatal(err)
	}
}

Two essentials:

• _ "github.com/.../stdlib" — register the driver via blank import (side effect)
• sql.Open doesn’t actually connect — connects on the first query or Ping

Common drivers:

• PostgreSQL — jackc/pgx
• MySQL — go-sql-driver/mysql
• SQLite — mattn/go-sqlite3 (cgo) or modernc.org/sqlite (pure Go)

Single-row query #

var name string
err := db.QueryRow("SELECT name FROM users WHERE id = $1", 42).Scan(&name)
if errors.Is(err, sql.ErrNoRows) {
	// not found
} else if err != nil {
	// other error
}

Key points:

• $1 (or ?, depending on driver) — placeholder. Values pass as separate arguments
• Scan puts results into variables
• sql.ErrNoRows is the standard “no rows” error

SQL injection defense — placeholders are standard. Don’t build SQL strings with fmt.Sprintf.

Multiple rows — Query + Rows #

rows, err := db.Query("SELECT id, name FROM users WHERE active = $1", true)
if err != nil {
	return err
}
defer rows.Close()

var users []User
for rows.Next() {
	var u User
	if err := rows.Scan(&u.ID, &u.Name); err != nil {
		return err
	}
	users = append(users, u)
}
if err := rows.Err(); err != nil {
	return err
}

Three things not to forget:

• defer rows.Close() — without it, connection leaks
• rows.Next() — one row at a time
• rows.Err() for the final check — confirm errors that happened during the loop

Insert / Update — Exec #

result, err := db.Exec(
	"INSERT INTO users (name, email) VALUES ($1, $2)",
	"Dokyung Lee", "x@y.z",
)
if err != nil {
	return err
}

id, _ := result.LastInsertId()      // MySQL etc.
affected, _ := result.RowsAffected()

LastInsertId depends on the DB. PostgreSQL uses a RETURNING clause instead.

var id int
err := db.QueryRow(
	"INSERT INTO users (name) VALUES ($1) RETURNING id",
	"Dokyung Lee",
).Scan(&id)

With Context #

Pass the context received in your handler through to DB calls.

func GetUser(ctx context.Context, db *sql.DB, id int) (User, error) {
	var u User
	err := db.QueryRowContext(ctx,
		"SELECT id, name FROM users WHERE id = $1", id,
	).Scan(&u.ID, &u.Name)
	return u, err
}

QueryContext, ExecContext, QueryRowContext — all have ctx-accepting versions. When the request is canceled, the query is canceled too (when the driver supports it — most major drivers do).

The pattern from Intermediate #5 context extends straight into the DB.

Prepared statement #

When the same query runs many times — let the DB cache its parsing/plan.

stmt, err := db.Prepare("SELECT name FROM users WHERE id = $1")
if err != nil {
	return err
}
defer stmt.Close()

for _, id := range ids {
	var name string
	stmt.QueryRow(id).Scan(&name)
	// ...
}

That said — Go’s database/sql automatically uses prepared even for single queries (depending on driver). Explicit Prepare is useful only for queries you call extremely often in a short window.

Transactions #

func transfer(ctx context.Context, db *sql.DB, from, to int, amount int) error {
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer tx.Rollback()    // auto rollback if commit didn't happen

	if _, err := tx.ExecContext(ctx,
		"UPDATE accounts SET balance = balance - $1 WHERE id = $2",
		amount, from,
	); err != nil {
		return err
	}

	if _, err := tx.ExecContext(ctx,
		"UPDATE accounts SET balance = balance + $1 WHERE id = $2",
		amount, to,
	); err != nil {
		return err
	}

	return tx.Commit()
}

Key pattern:

• defer tx.Rollback() — auto-rollback if the function ends without a commit. Calling after commit is harmless (ErrTxDone ignored).
• All queries via — tx.ExecContext / tx.QueryContext

Transaction isolation level #

tx, err := db.BeginTx(ctx, &sql.TxOptions{
	Isolation: sql.LevelSerializable,
	ReadOnly:  false,
})

The default is whatever the DB defaults to. For places where isolation matters (money transfer, inventory deduction, etc.), set it explicitly.

Handling NULL #

var u struct {
	ID    int
	Email sql.NullString    // can be NULL
}
err := db.QueryRow(...).Scan(&u.ID, &u.Email)

if u.Email.Valid {
	fmt.Println(u.Email.String)
}

When you must distinguish DB NULL from Go’s zero value — use sql.NullString, sql.NullInt64, sql.NullTime. Same motivation as the pointer pattern in JSON.

Or — a pointer (*string) works, but sql.Null* is more common for explicitness.

Connection pool tuning #

*sql.DB itself is a connection pool. Share a single instance across the app.

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

Typical recommendations:

• MaxOpenConns — what the DB can handle, usually 10–50
• MaxIdleConns — usually equal to MaxOpen
• ConnMaxLifetime — 5–30 minutes (refresh behind a load balancer)
• ConnMaxIdleTime — 1–10 minutes

Common pitfalls #

1) Forgetting rows.Close() — connection leak #

rows, _ := db.Query(...)
// returning early in the loop → rows isn't closed → connection occupied
for rows.Next() {
	if some_condition {
		return    // ✗ rows.Close not called
	}
}

Solution — always defer rows.Close() on the first line.

2) Type mismatch in Scan #

var n int
db.QueryRow("SELECT amount FROM x").Scan(&n)
// if amount is NUMERIC — possible drift, possible error

DB column type and Go type — match precisely. When ambiguous, scan as string and convert.

3) Plain db calls inside a transaction #

tx, _ := db.BeginTx(ctx, nil)
db.Exec(...)    // ✗ uses a plain connection, not tx
tx.Commit()

Inside a transaction — always go through tx.*. Plain db.* runs on a different connection, outside the transaction.

Higher-level tools #

database/sql alone is fine in some places — but you’ll often see these:

sqlx #

type User struct {
	ID   int    `db:"id"`
	Name string `db:"name"`
}

var users []User
err := sqlx.SelectContext(ctx, db, &users, "SELECT id, name FROM users")

On top of standard database/sql — adds struct auto-mapping and Get / Select helpers. The lowest-friction add-on.

sqlc #

-- name: GetUser :one
SELECT id, name FROM users WHERE id = $1;

sqlc generate

→ A Go function corresponding to that SQL is auto-generated. You write SQL directly with type safety. A code-generation case from Advanced #7.

Popular in modern Go backends — write SQL directly while reducing boilerplate.

GORM #

type User struct {
	gorm.Model
	Name  string
	Email string
}

db.Where("email = ?", "x@y.z").First(&user)

Like ORMs in other languages — method chaining, automatic association handling. Convenient — but hidden SQL makes performance issues hard to track, and lots of magical behavior is friction. The Go community is divided on it.

Which tool to pick? #

Subjective: standard → sqlx → sqlc is the natural progression. GORM is familiar to those coming from other contexts — but somewhat distant from Go’s explicit design, a tradeoff.

Migrations #

Schema-change management isn’t in Go’s standard library. Usually:

• golang-migrate/migrate — most common
• pressly/goose — write migrations as Go code

migrate -database "postgres://..." -path migrations up

Apply migrations/0001_init.up.sql / 0001_init.down.sql files in order.

Wrap-up #

What we covered:

• database/sql — standard, drivers imported separately
• Query / QueryRow / Exec — method set
• Use placeholders — SQL injection defense
• Always use Context versions — propagate request cancellation
• Transactions — BeginTx + defer Rollback + final Commit
• NULL — sql.Null* or pointers
• Pool tuning — MaxOpenConns, ConnMaxLifetime, etc.
• Higher-level tools — sqlx (light), sqlc (code gen), GORM (ORM)
• Migrations — golang-migrate, goose

In the next post (#5 Middleware) — Go’s middleware pattern. How chains of http.Handler adapters cleanly handle auth, logging, and panic recovery.