Databases with database/sql & PostgreSQL

Almost every back-end service stores data in a database, and PostgreSQL is one of the most common choices for Go services. Go's standard library has a package for this, database/sql, which gives every SQL database the same API. A separate driver package does the talking to one particular database.

In this lesson you will start PostgreSQL in Docker, connect from Go through the pgx driver, insert and query rows safely, handle missing rows and NULL values, and group changes into a transaction so a bookshop never sells a book it does not have.

How database/sql works

You create one *sql.DB when the program starts and share it everywhere. It is safe for concurrent use; each query borrows a connection from the pool and returns it afterwards.

Step by step: connect to PostgreSQL

1. Start a local PostgreSQL 18 server in Docker (see the Docker Tutorial if Docker is new to you). The password is only for your laptop:

docker run --name shopdb -e POSTGRES_PASSWORD=devpass -e POSTGRES_DB=shop -p 5432:5432 -d postgres:18

2. Open psql inside the container with docker exec -it shopdb psql -U postgres -d shop and create two tables for a bookshop in Kochi:

CREATE TABLE books (
    id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title  text    NOT NULL,
    author text    NOT NULL,
    price  integer NOT NULL CHECK (price > 0),
    stock  integer NOT NULL DEFAULT 0,
    isbn   text
);

CREATE TABLE sales (
    id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    book_id bigint  NOT NULL REFERENCES books (id),
    qty     integer NOT NULL,
    sold_at timestamptz NOT NULL DEFAULT now()
);

3. In your Go module, add the driver with go get github.com/jackc/pgx/v5.
4. Put the connection string in an environment variable instead of the code: export DATABASE_URL="postgres://postgres:devpass@localhost:5432/shop?sslmode=disable" (in PowerShell: $env:DATABASE_URL = "...").
5. Open the pool, set its limits and check the connection with a ping:

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

	_ "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)
	if err != nil {
		return nil, err
	}
	db.SetMaxOpenConns(10)                 // never more than 10 connections
	db.SetMaxIdleConns(10)                 // keep them ready between requests
	db.SetConnMaxIdleTime(5 * time.Minute) // close ones unused for 5 minutes

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

The blank import _ runs the driver package's setup code, which registers "pgx"; you never call it directly. sql.Open does not connect; it only checks its arguments. PingContext makes a real connection, so a wrong password fails at start-up instead of on the first customer's request.

Writing and reading rows

Every query takes values through placeholders. PostgreSQL numbers them $1, $2, and so on, and the driver sends the values separately from the SQL text:

type Book struct {
	ID     int64
	Title  string
	Author string
	Price  int
	Stock  int
	ISBN   sql.Null[string] // the column may be NULL
}

var ErrNotFound = errors.New("book not found")

func AddBook(ctx context.Context, db *sql.DB, b Book) (int64, error) {
	var id int64
	err := db.QueryRowContext(ctx,
		`INSERT INTO books (title, author, price, stock) VALUES ($1, $2, $3, $4) RETURNING id`,
		b.Title, b.Author, b.Price, b.Stock,
	).Scan(&id)
	return id, err
}

func GetBook(ctx context.Context, db *sql.DB, id int64) (Book, error) {
	var b Book
	err := db.QueryRowContext(ctx,
		`SELECT id, title, author, price, stock, isbn FROM books WHERE id = $1`, id,
	).Scan(&b.ID, &b.Title, &b.Author, &b.Price, &b.Stock, &b.ISBN)
	if errors.Is(err, sql.ErrNoRows) {
		return Book{}, ErrNotFound
	}
	return b, err
}

func ListBooks(ctx context.Context, db *sql.DB, maxPrice int) ([]Book, error) {
	rows, err := db.QueryContext(ctx,
		`SELECT id, title, author, price, stock, isbn FROM books WHERE price <= $1 ORDER BY id`,
		maxPrice)
	if err != nil {
		return nil, err
	}
	defer rows.Close() // returns the connection to the pool

	var books []Book
	for rows.Next() {
		var b Book
		if err := rows.Scan(&b.ID, &b.Title, &b.Author, &b.Price, &b.Stock, &b.ISBN); err != nil {
			return nil, err
		}
		books = append(books, b)
	}
	return books, rows.Err() // an error may end the loop early
}

QueryRowContext is for exactly one row; Scan copies the columns into your variables in order. PostgreSQL's RETURNING id hands back the new ID in the same statement (the LastInsertId method does not work with PostgreSQL). A missing row is not a crash: Scan returns sql.ErrNoRows, which GetBook turns into its own ErrNotFound, so callers do not depend on database/sql details. QueryContext returns many rows: loop with rows.Next(), always defer rows.Close(), and check rows.Err() at the end.

A main that adds two books, asks for a missing one and lists the rest, run on fresh tables:

for _, b := range []Book{
	{Title: "Malgudi Days", Author: "R. K. Narayan", Price: 299, Stock: 5},
	{Title: "The God of Small Things", Author: "Arundhati Roy", Price: 399, Stock: 3},
} {
	id, err := AddBook(ctx, db, b)
	if err != nil {
		log.Fatal(err)
	}
	fmt.Println("added book", id)
}
if _, err := GetBook(ctx, db, 99); err != nil {
	fmt.Println("get 99:", err)
}
books, err := ListBooks(ctx, db, 500)
if err != nil {
	log.Fatal(err)
}
for _, b := range books {
	isbn := "no ISBN"
	if b.ISBN.Valid {
		isbn = b.ISBN.V
	}
	fmt.Printf("%d. %s by %s, ₹%d (%s)\n", b.ID, b.Title, b.Author, b.Price, isbn)
}

added book 1
added book 2
get 99: book not found
1. Malgudi Days by R. K. Narayan, ₹299 (no ISBN)
2. The God of Small Things by Arundhati Roy, ₹399 (no ISBN)

Scanning a NULL into a plain string fails with an error, so nullable columns use sql.Null[string] (Go 1.22), whose Valid field says whether there was a value and V holds it. A pointer such as *string also works.

Transactions

Selling a book changes two tables: stock goes down and a sale is recorded. If the program crashed between the two statements, the data would disagree. A transaction makes the steps all-or-nothing:

var ErrOutOfStock = errors.New("not enough stock")

func SellBook(ctx context.Context, db *sql.DB, bookID int64, qty int) error {
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer tx.Rollback() // does nothing once Commit has succeeded

	res, err := tx.ExecContext(ctx,
		`UPDATE books SET stock = stock - $2 WHERE id = $1 AND stock >= $2`, bookID, qty)
	if err != nil {
		return err
	}
	if n, err := res.RowsAffected(); err != nil {
		return err
	} else if n == 0 {
		return ErrOutOfStock
	}
	if _, err := tx.ExecContext(ctx,
		`INSERT INTO sales (book_id, qty) VALUES ($1, $2)`, bookID, qty); err != nil {
		return err
	}
	return tx.Commit()
}

The UPDATE checks and changes the stock in one statement, so two customers buying the last copy at the same moment cannot both succeed; the second sees zero affected rows. The deferred Rollback undoes everything on any early return. Inside a transaction, use tx, not db, for every statement. With the books above, SellBook(ctx, db, 2, 5) returns ErrOutOfStock (only 3 in stock), while SellBook(ctx, db, 2, 2) succeeds and leaves 1. The PostgreSQL Tutorial explains what the database does underneath.

Beyond the basics

As projects grow, teams add a migration tool such as goose or golang-migrate to apply numbered .sql files in order, and many use sqlc, which generates type-safe Go functions from SQL queries you write. If you need PostgreSQL-only features such as COPY or LISTEN, pgx also has its own native interface with a pool called pgxpool. ORMs such as GORM exist too; they save typing but hide the SQL, so learn plain database/sql first.

Common mistakes

• Opening a new *sql.DB per request. Open one pool at start-up and share it.
• Forgetting rows.Close() or rows.Err(). Connections leak, or errors go unnoticed.
• Scanning NULL into a plain type. Use sql.Null[T] or a pointer.
• Using db instead of tx inside a transaction. Those statements run outside the transaction.

Key points

• database/sql is a common API; the pgx driver (github.com/jackc/pgx/v5/stdlib) registers "pgx" for PostgreSQL.
• *sql.DB is a concurrency-safe connection pool: open it once, set limits, and PingContext at start-up.
• Use placeholders ($1) for every value; never build SQL from strings.
• QueryRowContext(...).Scan reads one row and reports sql.ErrNoRows; QueryContext needs rows.Close and rows.Err.
• Transactions with BeginTx, defer tx.Rollback() and Commit make several changes all-or-nothing.

var ErrBadPrice = errors.New("price must be positive")

func UpdatePrice(ctx context.Context, db *sql.DB, id int64, price int) error {
	if price <= 0 {
		return ErrBadPrice
	}
	res, err := db.ExecContext(ctx, `UPDATE books SET price = $1 WHERE id = $2`, price, id)
	if err != nil {
		return err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		return ErrNotFound
	}
	return nil
}