Databases with database/sql

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

func openDB(ctx context.Context, dsn string) (*sql.DB, error) {
	db, err := sql.Open("pgx", dsn)   // checks arguments only; no connection yet!
	if err != nil {
		return nil, err
	}
	db.SetMaxOpenConns(25)
	db.SetMaxIdleConns(25)
	db.SetConnMaxLifetime(5 * time.Minute)

	ctx, cancel := context.WithTimeout(ctx, 5*time.Second)
	defer cancel()
	if err := db.PingContext(ctx); err != nil {  // now it really connects
		db.Close()
		return nil, fmt.Errorf("connect to db: %w", err)
	}
	return db, nil
}

func (s *PGStore) Get(ctx context.Context, id int64) (Expense, error) {
	var e Expense
	err := s.db.QueryRowContext(ctx,
		"SELECT id, title, amount_paise, category, created_at FROM expenses WHERE id = $1", id,
	).Scan(&e.ID, &e.Title, &e.Paise, &e.Category, &e.Created)
	if errors.Is(err, sql.ErrNoRows) {
		return Expense{}, ErrNotFound
	}
	if err != nil {
		return Expense{}, fmt.Errorf("get expense %d: %w", id, err)
	}
	return e, nil
}

func (s *PGStore) List(ctx context.Context, limit, offset int) ([]Expense, error) {
	rows, err := s.db.QueryContext(ctx,
		"SELECT id, title, amount_paise, category, created_at FROM expenses ORDER BY id LIMIT $1 OFFSET $2",
		limit, offset)
	if err != nil {
		return nil, err
	}
	defer rows.Close()        // returns the connection to the pool

	list := []Expense{}
	for rows.Next() {
		var e Expense
		if err := rows.Scan(&e.ID, &e.Title, &e.Paise, &e.Category, &e.Created); err != nil {
			return nil, err
		}
		list = append(list, e)
	}
	return list, rows.Err()   // errors that stopped the loop early
}

// PostgreSQL: get the new id with RETURNING
err := s.db.QueryRowContext(ctx,
	"INSERT INTO expenses (title, amount_paise, category) VALUES ($1, $2, $3) RETURNING id, created_at",
	e.Title, e.Paise, e.Category,
).Scan(&e.ID, &e.Created)

// No rows needed back: ExecContext
res, err := s.db.ExecContext(ctx, "DELETE FROM expenses WHERE id = $1", id)
if err != nil {
	return err
}
n, _ := res.RowsAffected()
if n == 0 {
	return ErrNotFound
}

// NEVER do this:
q := fmt.Sprintf("SELECT * FROM users WHERE email = '%s'", email)
// email = "x' OR '1'='1" turns the query into "... WHERE email = 'x' OR '1'='1'"

// ALWAYS do this:
row := db.QueryRowContext(ctx, "SELECT id, name FROM users WHERE email = $1", email)

func transfer(ctx context.Context, db *sql.DB, from, to, paise int64) error {
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer tx.Rollback() // does nothing if Commit already succeeded

	if _, err := tx.ExecContext(ctx, "UPDATE wallets SET balance = balance - $1 WHERE id = $2 AND balance >= $1", paise, from); err != nil {
		return err
	}
	if _, err := tx.ExecContext(ctx, "UPDATE wallets SET balance = balance + $1 WHERE id = $2", paise, to); err != nil {
		return err
	}
	return tx.Commit()
}