Database Access with database/sql

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.

Learn Database Access with database/sql in our free Go course — an interactive lesson with worked examples, a practice exercise and a quick reference.

Part of the free Go course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

What You'll Learn in This Lesson

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.

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 .

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.

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.

🎯 Your Turn

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

❌ 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.

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

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

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

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

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

Practice quiz

What does sql.Open actually do?

  • Prepares a *sql.DB pool but defers real connections until needed
  • Immediately opens a live connection to the database
  • Runs your first query
  • Creates the database file

Answer: Prepares a *sql.DB pool but defers real connections until needed. sql.Open just validates arguments and sets up the pool; a real connection is made lazily, which is why you ping to verify.

Why must you import a driver like github.com/lib/pq with a blank identifier (_)?

  • It is optional and has no effect
  • To use its exported functions directly
  • So its init() registers the driver with database/sql
  • To avoid a compile error in tests only

Answer: So its init() registers the driver with database/sql. The blank import runs the driver's init(), which calls sql.Register so database/sql can find it by name.

Which method runs a query that returns MANY rows?

  • db.QueryRow
  • db.Query
  • db.Open
  • db.Exec

Answer: db.Query. db.Query returns *sql.Rows you iterate; QueryRow is for at most one row, Exec is for statements with no rows.

After db.Query, what must you do with the *sql.Rows?

  • Call rows.Open()
  • Convert it to JSON first
  • Nothing, it is freed automatically
  • Loop with rows.Next(), Scan, then close it (defer rows.Close())

Answer: Loop with rows.Next(), Scan, then close it (defer rows.Close()). Iterate with rows.Next(), read each row with rows.Scan, and close the rows to release the connection back to the pool.

What error does QueryRow().Scan return when no row matches?

  • sql.ErrNoRows
  • a panic
  • nil
  • io.EOF

Answer: sql.ErrNoRows. QueryRow defers errors to Scan; when there is no row, Scan returns the sentinel sql.ErrNoRows for you to handle.

Why use a placeholder ($1 or ?) instead of building SQL with string concatenation?

  • It is required by Go syntax
  • It prevents SQL injection by sending values separately
  • It runs faster only
  • It capitalizes the values

Answer: It prevents SQL injection by sending values separately. Parameterized queries send the values apart from the SQL text, so user input can never be interpreted as code.

How should you read a column that may be NULL into Go?

  • It is impossible
  • Always use interface{}
  • A plain string (NULL becomes empty)
  • A sql.Null type such as sql.NullString

Answer: A sql.Null type such as sql.NullString. Scanning a NULL into a plain string fails; sql.NullString (and friends) carry both a value and a Valid bool.

Which method runs an INSERT, UPDATE, or DELETE that returns no rows?

  • db.Query
  • db.QueryRow
  • db.Exec
  • db.Scan

Answer: db.Exec. db.Exec runs statements that modify data and returns a Result with RowsAffected and LastInsertId.

How do you start a transaction with database/sql?

  • sql.Tx()
  • db.Begin() (or BeginTx with a context)
  • db.Transaction()
  • db.Open() in transaction mode

Answer: db.Begin() (or BeginTx with a context). db.Begin (or db.BeginTx(ctx, opts)) returns a *sql.Tx; you then Commit on success or Rollback on failure.

Why pass a context to QueryContext/ExecContext?

  • So a slow query can be cancelled or time out
  • It is purely decorative
  • To pick the driver
  • To format the SQL

Answer: So a slow query can be cancelled or time out. Context-aware methods let a deadline or cancellation abort a long-running query and free the connection.