Database Access with database/sql

Reviewed & published by Brayan K

Go's standard library database/sql package talks to SQL databases through a swappable driver. You'll open a connection pool with sql.Open, run queries and statements, scan rows into structs, use prepared statements and contexts, and wrap work in transactions — all type-safe and injection-safe.

Part of the free Go course at LearnCodingFast — hands-on lessons with worked examples and the output they print, plus practice exercises and a quick quiz.

What You'll Learn in This Lesson

• Open a *sql.DB pool with sql.Open and a driver
• Run Query, QueryRow, and Exec
• Scan rows into Go variables and structs
• Handle sql.ErrNoRows and NULL with sql.Null types
• Use placeholders and contexts to stay safe and cancellable
• Wrap statements in a transaction with Begin/Commit/Rollback

1️⃣ Open the pool: sql.Open

sql.Open builds a *sql.DB connection pool — it doesn't actually connect yet. Import the driver with a blank identifier so its init() registers it, tune the pool, and call Ping to fail fast.

package main

import (
    "database/sql"
    "log"
    "time"

    _ "github.com/lib/pq" // blank import: its init() registers "postgres"
)

func main() {
    // sql.Open does NOT connect — it builds a *sql.DB connection POOL and
    // validates the arguments. The first real connection happens lazily.
    db, err := sql.Open("postgres", "postgres://user:pass@localhost/app?sslmode=disable")
    if err != nil {
        log.Fatalf("open failed: %v", err)
    }
    defer db.Close()

    // Tune the pool. A *sql.DB is safe for concurrent use by many goroutines.
    db.SetMaxOpenConns(25)
    db.SetMaxIdleConns(25)
    db.SetConnMaxLifetime(5 * time.Minute)

    // Ping forces an actual connection so you fail fast on bad credentials.
    if err := db.Ping(); err != nil {
        log.Fatalf("cannot reach db: %v", err)
    }
    log.Println("connected")
}

2️⃣ One row: QueryRow and ErrNoRows

For a single row, use QueryRow and read it with Scan. Always pass values as placeholders ($1 for Postgres, ? for MySQL) — never string-concatenate user input. When nothing matches, Scan returns sql.ErrNoRows.

package main

import (
    "database/sql"
    "errors"
    "fmt"
)

type User struct {
    ID   int
    Name string
}

// QueryRow fetches AT MOST one row. Use a placeholder ($1 for postgres,
// ? for mysql/sqlite) so the value is sent separately — this is what
// stops SQL injection.
func findUser(db *sql.DB, id int) (User, error) {
    var u User
    row := db.QueryRow("SELECT id, name FROM users WHERE id = $1", id)

    // Scan copies the columns into your variables, IN ORDER.
    if err := row.Scan(&u.ID, &u.Name); err != nil {
        if errors.Is(err, sql.ErrNoRows) {
            return User{}, fmt.Errorf("user %d not found", id)
        }
        return User{}, err // some other failure
    }
    return u, nil
}

3️⃣ Many rows: Query + iterate

When a query returns several rows, use db.Query to get *sql.Rows, defer rows.Close(), loop with rows.Next(), and Scan each one. Check rows.Err() after the loop in case it ended early.

package main

import "database/sql"

func listAdmins(db *sql.DB) ([]User, error) {
    // db.Query returns *sql.Rows for MANY rows.
    rows, err := db.Query("SELECT id, name FROM users WHERE admin = $1", true)
    if err != nil {
        return nil, err
    }
    defer rows.Close() // ALWAYS close: returns the connection to the pool

    var users []User
    for rows.Next() {              // advance to the next row
        var u User
        if err := rows.Scan(&u.ID, &u.Name); err != nil {
            return nil, err
        }
        users = append(users, u)
    }
    // rows.Err() reports an error that ended the loop early (e.g. network).
    if err := rows.Err(); err != nil {
        return nil, err
    }
    return users, nil
}

4️⃣ Changing data: Exec, transactions, and NULL

Exec runs statements that modify data. To make several changes all-or-nothing, wrap them in a transaction: Begin, then Commit on success or Rollback on failure. Read nullable columns with sql.Null* types.

package main

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

// A transaction makes several statements all-or-nothing. Begin, then
// Commit on success or Rollback on failure.
func transfer(db *sql.DB, from, to, cents int) error {
    ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
    defer cancel()

    tx, err := db.BeginTx(ctx, nil) // context-aware transaction
    if err != nil {
        return err
    }
    // defer Rollback so any early return undoes partial work.
    // After a successful Commit, this Rollback is a harmless no-op.
    defer tx.Rollback()

    // ExecContext runs statements that change data and return no rows.
    if _, err := tx.ExecContext(ctx,
        "UPDATE accounts SET cents = cents - $1 WHERE id = $2", cents, from); err != nil {
        return err
    }
    if _, err := tx.ExecContext(ctx,
        "UPDATE accounts SET cents = cents + $1 WHERE id = $2", cents, to); err != nil {
        return err
    }
    return tx.Commit() // make both updates permanent together
}

// NULL columns need sql.Null* types — a plain string Scan fails on NULL.
func nickname(db *sql.DB, id int) (string, error) {
    var nick sql.NullString
    err := db.QueryRow("SELECT nickname FROM users WHERE id = $1", id).Scan(&nick)
    if err != nil {
        return "", err
    }
    if nick.Valid {
        return nick.String, nil // a real value
    }
    return "(none)", nil        // the column was NULL
}

🎯 Your Turn

Fetch one user's name by id. Fill in the two blanks marked ___, then handle the not-found case.

package main

import (
    "database/sql"
    "errors"
)

func userName(db *sql.DB, id int) (string, error) {
    var name string

    // 🎯 YOUR TURN — fill in the blanks marked ___

    // 1) Fetch a single row by id using a placeholder
    row := db.QueryRow("SELECT name FROM users WHERE id = $1", ___) // 👉 id

    // 2) Scan the one column into name
    err := row.___(&name)   // 👉 Scan

    if errors.Is(err, sql.ErrNoRows) {
        return "", errors.New("not found")
    }
    return name, err
}

// ✅ Expected: userName(db, 7) returns "Ada", nil
//    and a missing id returns "", "not found"

Common Errors (and the fix)

❌ Building SQL with + and user input — opens the door to SQL injection.

✅ Use placeholders ($1 / ?) and pass values as arguments.

❌ Forgetting defer rows.Close() — leaks connections from the pool.

✅ Close the rows right after the error check, before iterating.

❌ Scanning a NULL into a plain string/int — Scan returns an error.

✅ Use sql.NullString and check .Valid.

❌ Treating sql.ErrNoRows as a crash — it is a normal not-found.

✅ Handle it with errors.Is and return a friendly result.

🧠 Quick Recall

QueryRow finds nothing; what does Scan return?

sql.ErrNoRows. Check it with errors.Is(err, sql.ErrNoRows) and treat it as a not-found, not a failure.

does sql.Open connect right away?

No. It builds the pool and connects lazily. Call db.Ping() to verify the connection up front.

🎯 Mini-Challenge: List Cheap Products

Write listCheapProducts: query products at or below a price, iterate the rows, scan each into a Product, and return the slice.

package main

import "database/sql"

type Product struct {
    ID    int
    Name  string
    Price int // cents
}

// 🎯 MINI-CHALLENGE: write listCheapProducts.
// 1. Run db.Query for products WHERE price <= $1 (pass maxCents).
// 2. defer rows.Close().
// 3. Loop rows.Next(); Scan id, name, price into a Product.
// 4. Append each to a []Product and return it.
// 5. Don't forget to check rows.Err() after the loop.
//
// ✅ Expected: a slice of every product at or below maxCents.

func listCheapProducts(db *sql.DB, maxCents int) ([]Product, error) {
    // your code here
    return nil, nil
}

🎉 Lesson Complete!

• ✅ sql.Open builds a pooled *sql.DB; Ping verifies it
• ✅ Query for many rows, QueryRow for one, Exec to change data
• ✅ Scan copies columns into variables; close *sql.Rows when done
• ✅ Placeholders stop injection; sql.Null* handles NULL; contexts cancel slow queries
• ✅ Transactions are all-or-nothing: Begin, Commit, or Rollback

Practice quiz

Continue this course

• Previous: gRPC and Protocol Buffers
• Next: Web APIs with the Gin Framework — Build fast JSON REST APIs with routing, binding and middleware in Gin

Frequently asked questions

Does sql.Open connect to the database?

No. sql.Open only validates its arguments and returns a *sql.DB, which is a pool of connections. The first real network connection is created lazily when you run a query. That is why you usually call db.Ping() (or db.PingContext) right after opening — it forces a connection so you fail fast on bad credentials or an unreachable host instead of much later.

Why do I import the driver with a blank identifier, like _ "github.com/lib/pq"?

database/sql is driver-agnostic: it defines interfaces but ships no drivers. A driver package registers itself in its init() by calling sql.Register("postgres", ...). You import it for that side effect only, not to call its functions, so you use the blank identifier _ to import it purely for its init(). Then sql.Open("postgres", ...) can find it by name.

When do I use Query, QueryRow, and Exec?

Use db.Query when a statement returns many rows — you get *sql.Rows to iterate with Next() and Scan. Use db.QueryRow when you expect at most one row; it returns a single Row whose Scan reports sql.ErrNoRows if nothing matched. Use db.Exec for statements that change data and return no rows (INSERT, UPDATE, DELETE); it returns a Result with RowsAffected and LastInsertId.

How do I handle a row that might not exist?

QueryRow defers any error to Scan. When there is no matching row, Scan returns the sentinel sql.ErrNoRows. Check it explicitly with errors.Is(err, sql.ErrNoRows) and treat it as a normal not-found case (often a 404 in a web handler) rather than an unexpected failure. Any other Scan error is a genuine problem to surface.

How do I read columns that can be NULL?

Scanning a SQL NULL into a plain Go type like string or int fails. Use the database/sql Null wrappers — sql.NullString, sql.NullInt64, sql.NullBool, sql.NullTime, and so on. Each has the typed value plus a Valid bool: if Valid is true the column had a value, otherwise it was NULL. Check Valid before using the value.

Why use prepared statements and context with queries?

Prepared statements (db.Prepare / stmt.Exec) compile the SQL once and let you run it many times with different parameters, which is efficient for repeated queries and keeps using placeholders to prevent injection. Passing a context.Context via QueryContext, ExecContext, or BeginTx lets a deadline or cancellation abort a slow query and return its connection to the pool, so one stuck statement cannot tie up resources.