When you first start working with the database/sql package, it's incredibly easy to fall into a specific trap. I see it all the time with developers coming from languages like Java or Python where ORMs (Object-Relational Mappers) do all the heavy lifting.

The "Magic Mapping" Myth

The biggest misconception I encounter is the belief that rows.Scan() is "smart." New Go developers often assume that if they have a struct that matches the columns in their database table, they can just pass a pointer to that struct into Scan() and Go will magically figure out which column goes into which field based on the names.

// ❌ This is the "Magic Mapping" mistake. It will not work.
type User struct {
    ID    int
    Email string
}

for rows.Next() {
    var u User
    err := rows.Scan(&u) // This will cause a runtime error
    if err != nil {
        log.Fatal(err)
    }
}

Here is why that fails: rows.Scan() doesn't know anything about your struct's fields. It doesn't look at tags or names. It expects a variadic list of pointers. When you pass &u, you're passing a single pointer to a struct, but the database is returning multiple columns. The types don't match, the counts don't match, and your program will panic or return an error.

Manual Mapping via Explicit Pointers

In Go, you have to be explicit. If you selected three columns in your SQL query, you must pass exactly three pointers to Scan() in the exact same order those columns appear in the query. It's a bit tedious, I know, but it's also why Go's database performance is so predictable.

Let's look at how we actually handle this using a Product example. Notice how the order in the SELECT statement dictates the order in the Scan() call.

type Product struct {
    ID    int
    Name  string
    Price float64
}

// Note the specific order: id, name, price
rows, err := db.Query("SELECT id, name, price FROM products")
if err != nil {
    return err
}
defer rows.Close()

var products []Product

for rows.Next() {
    var p Product
    // We pass pointers to the specific fields of the struct
    err := rows.Scan(&p.ID, &p.Name, &p.Price)
    if err != nil {
        return err
    }
    products = append(products, p)
}

I want to highlight a crucial detail here: the var p Product declaration happens inside the loop. If you declare it outside, you'll end up with a slice where every single element is a copy of the last row scanned because you're reusing the same memory address. Always declare your temporary row variable inside the Next() loop.

Dealing with the "Null" Headache

One more thing that usually trips people up is the NULL value. If your database column allows NULL and you try to scan it into a standard Go string or int, Scan() will return an error. Go's basic types cannot be nil.

To handle this, you have two choices. You can either ensure your SQL query uses COALESCE to provide a default value (my personal preference for simplicity), or you can use the sql.NullString, sql.NullInt64, and sql.NullFloat64 types provided by the standard library.

type Product struct {
    ID          int
    Description sql.NullString // Use this if the DB column is nullable
    Price       float64
}

// ... inside the loop
err := rows.Scan(&p.ID, &p.Description, &p.Price)

// To use the value, you check the Valid flag
if p.Description.Valid {
    fmt.Println("Desc:", p.Description.String)
} else {
    fmt.Println("No description provided.")
}

📋 Practical Task

Exercise: Building a Book Catalog Scanner

You are building a small library management tool. You have a database table called books with the following schema: id (INT), title (TEXT), author (TEXT), and published_year (INT, nullable).

Your Task: Write a function FetchBooks(db *sql.DB) ([]Book, error) that does the following:

• Defines a Book struct that can handle the nullable published_year.
• Executes a query to select all columns from the books table.
• Iterates through the rows and correctly scans the data into the Book struct.
• Returns a slice of all books found.

Make sure you handle the rows.Close() properly and check for errors both during the query and during the scanning process.