Sign in Get started

Go Web Frameworks: net/http, Chi, Gin, Echo and Fiber · Building Production APIs in Go

Database Access: database/sql, Repositories and Transactions

Talk to SQL databases with database/sql: connection pools, parameterised queries, scanning rows, transactions and context timeouts, behind a small repository interface. Compare GORM, sqlx and sqlc.

Create a free account to save this page, take notes and track your progress. Sign in

What you'll build

A BookRepo backed by a real SQL database (SQLite here, so it runs anywhere) with create, get-by-id, list and a transactional "borrow a book" operation that can't double-book.

The idea

  • database/sql is Go's standard interface to SQL databases; a driver (pgx for PostgreSQL, go-sql-driver/mysql, modernc.org/sqlite) plugs in underneath.
  • sql.Open returns a connection pool (*sql.DB), safe for concurrent use: create it once at startup, set SetMaxOpenConns and SetConnMaxLifetime, and share it.
  • Always use placeholders (? or $1), never string concatenation: that's what prevents SQL injection.
  • Pass a context (QueryContext, ExecContext) so slow queries are cancelled when the request is cancelled or times out.
  • sql.ErrNoRows means "not found": translate it to your own ErrNotFound.
  • Transactions (BeginTx, then Commit or Rollback) make multi-step changes all-or-nothing; a conditional UPDATE … WHERE available = 1 plus checking RowsAffected prevents races.

Libraries on top: sqlx (scanning into structs, named parameters), sqlc (generates type-safe Go from your SQL), GORM (a full ORM with migrations and associations). Hide whichever you choose behind a repository interface so handlers and tests don't care.

The code

A repository with database/sql, a pure-Go SQLite driver and a transaction
package main

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"time"

	_ "modernc.org/sqlite"
)

var ErrNotFound = errors.New("not found")
var ErrUnavailable = errors.New("already borrowed")

type Book struct {
	ID        int64
	Title     string
	Available bool
}

type BookRepo struct{ db *sql.DB }

func (r *BookRepo) Create(ctx context.Context, title string) (int64, error) {
	res, err := r.db.ExecContext(ctx, `INSERT INTO books (title, available) VALUES (?, 1)`, title)
	if err != nil {
		return 0, err
	}
	return res.LastInsertId()
}

func (r *BookRepo) Get(ctx context.Context, id int64) (Book, error) {
	var b Book
	err := r.db.QueryRowContext(ctx, `SELECT id, title, available FROM books WHERE id = ?`, id).
		Scan(&b.ID, &b.Title, &b.Available)
	if errors.Is(err, sql.ErrNoRows) {
		return Book{}, ErrNotFound
	}
	return b, err
}

// Borrow marks the book unavailable and records the loan, all or nothing.
func (r *BookRepo) Borrow(ctx context.Context, bookID int64, member string) error {
	tx, err := r.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer tx.Rollback() // no effect after a successful Commit

	res, err := tx.ExecContext(ctx, `UPDATE books SET available = 0 WHERE id = ? AND available = 1`, bookID)
	if err != nil {
		return err
	}
	if n, _ := res.RowsAffected(); n == 0 {
		return ErrUnavailable // someone else got it first (or it doesn't exist)
	}
	if _, err := tx.ExecContext(ctx, `INSERT INTO loans (book_id, member) VALUES (?, ?)`, bookID, member); err != nil {
		return err
	}
	return tx.Commit()
}

func main() {
	db, err := sql.Open("sqlite", ":memory:")
	if err != nil {
		panic(err)
	}
	db.SetMaxOpenConns(1) // one shared in-memory database for this demo
	defer db.Close()

	ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
	defer cancel()
	db.ExecContext(ctx, `CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT NOT NULL, available INTEGER NOT NULL)`)
	db.ExecContext(ctx, `CREATE TABLE loans (book_id INTEGER NOT NULL, member TEXT NOT NULL)`)

	repo := &BookRepo{db: db}
	id, _ := repo.Create(ctx, "The Go Programming Language")
	repo.Create(ctx, "Robert'); DROP TABLE books;--") // placeholders make this harmless text

	b, _ := repo.Get(ctx, id)
	fmt.Printf("book %d: %q available=%v\n", b.ID, b.Title, b.Available)
	fmt.Println("ana borrows:", repo.Borrow(ctx, id, "ana"))
	fmt.Println("ben borrows:", repo.Borrow(ctx, id, "ben"))
	_, err = repo.Get(ctx, 99)
	fmt.Println("get 99:", err)

	var count int
	db.QueryRowContext(ctx, `SELECT COUNT(*) FROM books`).Scan(&count)
	var loans int
	db.QueryRowContext(ctx, `SELECT COUNT(*) FROM loans`).Scan(&loans)
	fmt.Println("books table still has", count, "rows; loans:", loans)
}

Output

book 1: "The Go Programming Language" available=true
ana borrows: <nil>
ben borrows: already borrowed
get 99: not found
books table still has 2 rows; loans: 1

Run this on your computer — see “Get Started” for setup.

ana borrows: <nil> is Go printing a nil error, meaning success. Ben's attempt changes zero rows, so the transaction is rolled back and no second loan is written.

Common mistakes

  • Opening a new sql.DB per request. It's a pool; open once and share it.
  • Building SQL with fmt.Sprintf. That's SQL injection; always use placeholders.
  • Forgetting rows.Close() after QueryContext (use defer rows.Close() and check rows.Err()), which leaks connections.
  • Check-then-update races. "SELECT available, then UPDATE" lets two requests both succeed; use a conditional UPDATE or row locks.
  • No context timeouts, so a slow query holds a connection long after the client gave up.

Key points

  • database/sql + a driver; *sql.DB is a concurrent-safe pool created once.
  • Placeholders always; contexts on every query.
  • Transactions with defer tx.Rollback() and an explicit Commit.
  • sqlx, sqlc and GORM build on top; hide them behind a repository interface.

Further reading: Go: Accessing relational databases, Executing transactions, sqlc.

Practice questions

Why should a Go service create its *sql.DB once instead of per request?

Answer

*sql.DB is a connection pool that is safe for concurrent use. Creating it per request defeats pooling, opens too many connections and slows everything down. Open it at startup, configure pool limits, and share it.

How do you prevent two requests from borrowing the same item at once?

Answer

Make the change conditional and atomic, e.g. UPDATE books SET available = 0 WHERE id = ? AND available = 1 inside a transaction, and treat RowsAffected() == 0 as 'already taken' (or lock the row with SELECT ... FOR UPDATE). A separate SELECT-then-UPDATE races.

Open in the app to mark lessons complete, take notes and track your progress.

Was this page helpful?