How to Use database/sql in Go
You write a Go service that needs to store data. You import database/sql, pick a driver, and call sql.Open. The function returns a *sql.DB object. You assume you now have a single connection to the database. You write a query, run it, and get results. Then you deploy to production. The service handles ten requests fine. At fifty requests, it starts timing out. At a hundred, it crashes. The problem isn't your SQL. The problem is that *sql.DB is not a connection. It's a connection pool. Treating it like a single connection breaks under load.
database/sql is the standard library package for SQL databases. It doesn't talk to any database directly. It defines the interface. You provide the driver. The driver speaks PostgreSQL, MySQL, SQLite, or whatever. database/sql translates your generic calls into driver-specific actions.
Think of *sql.DB as a reception desk at a hotel. The desk isn't a room. The desk manages a pool of rooms. When you ask for a room, the desk hands you one. When you're done, you return it. If all rooms are occupied, the desk waits until one frees up. *sql.DB is thread-safe. You create one instance per database and share it across all goroutines. Never create a new *sql.DB for every request. That's like building a new reception desk every time a guest arrives.
Here's the skeleton: open the pool, check the error, defer close, execute a query, scan the result.
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/mattn/go-sqlite3" // blank import registers the driver
)
func main() {
// Open creates the pool. It does not connect to the database yet.
db, err := sql.Open("sqlite3", ":memory:")
if err != nil {
log.Fatal(err)
}
// Close releases pool resources when main returns.
defer db.Close()
// Ping forces a connection to verify the database is reachable.
if err := db.Ping(); err != nil {
log.Fatal(err)
}
// QueryRow returns a single row. Scan extracts columns into variables.
var name string
err = db.QueryRow("SELECT 'Alice' AS name").Scan(&name)
if err != nil {
log.Fatal(err)
}
fmt.Println(name)
}
sql.Open returns immediately. It parses the DSN. It sets up the pool config. It does not open a network socket. If you skip Ping, you might not know the database is down until you run a query. *sql.DB is safe for concurrent use. Share it. QueryRow is a shortcut for Query that expects one row. Scan copies data from the row buffer into your Go variables. You must pass pointers. Scan(&id) not Scan(id). If you pass a value, the compiler rejects it with cannot use id (type int) as *int value in argument.
The community accepts the if err != nil boilerplate because it makes the unhappy path visible. Don't hide errors. Check them. sql.ErrNoRows is a specific error value. Check for it when using QueryRow. If you don't, you treat "not found" the same as a database crash.
sql.Open lies. It returns a pool, not a connection. Always Ping to verify.
Here's a production-ready function: it accepts a context, checks for cancellation, scans into a struct, and returns a typed error.
// User represents a row in the users table.
type User struct {
ID int
Name string
}
// GetUser retrieves a user by ID. It respects context cancellation.
func GetUser(ctx context.Context, db *sql.DB, id int) (*User, error) {
// QueryRowContext links the query to the context.
// Cancellation stops the query and frees the connection.
row := db.QueryRowContext(ctx, "SELECT id, name FROM users WHERE id = ?", id)
var u User
// Scan populates the struct fields.
err := row.Scan(&u.ID, &u.Name)
if err != nil {
if err == sql.ErrNoRows {
return nil, fmt.Errorf("user %d not found", id)
}
return nil, fmt.Errorf("query failed: %w", err)
}
return &u, nil
}
context.Context always goes as the first parameter, conventionally named ctx. Functions that take a context should respect cancellation and deadlines. QueryRowContext binds the query to the context. If the context is cancelled, the query stops and the connection returns to the pool. This prevents hanging queries from blocking resources.
Scan copies data into the struct fields. The driver matches columns by position. The first column goes to u.ID. The second goes to u.Name. If the types don't match, you get a runtime error. The compiler doesn't check database schemas. It only checks that you passed pointers.
Error wrapping with %w preserves the error chain. You can unwrap it later with errors.Is or errors.As. This lets callers distinguish between "not found" and "database down".
Accept interfaces, return structs. GetUser returns a *User. Callers get concrete data. The function signature doesn't leak implementation details.
Context is plumbing. Run it through every long-lived call site.
Forgetting to close rows leaks a connection. Query returns a *sql.Rows. You must call rows.Close(). Use defer rows.Close(). If you don't, the connection stays checked out. The pool runs dry. The service hangs.
If you pass a string variable to Scan for an integer column, the driver returns a runtime error. The compiler only checks types of the arguments to Scan, not the database schema. You get sql: Scan error on column index 0, name "id": converting driver.Value type string to int: invalid syntax.
sql.Open can return an error if the DSN is malformed or the driver isn't registered. If you ignore it, db is nil. Calling db.Query panics with runtime error: invalid memory address or nil pointer dereference.
Use _ to discard a value intentionally. result, _ := ... says "I considered the second return value and chose to drop it". Use it sparingly with errors. Dropping an error silently is a bug waiting to happen.
The worst goroutine bug is the one that never logs. The worst database bug is the connection leak that kills the pool at 3 AM.
*sql.DB has defaults. MaxOpenConns defaults to 0, which means unlimited. MaxIdleConns defaults to 2. If you have a high-load service, unlimited connections can overwhelm the database. Set db.SetMaxOpenConns(25). This caps the pool size. If all 25 are busy, new queries wait. This protects the database from connection storms.
Database columns can be NULL. Go variables cannot. If you scan a NULL into an int, you get an error. Use sql.NullInt64 or sql.NullString. These types have a Valid boolean. Check Valid before using the value. If Valid is false, the database returned NULL.
Transactions group multiple queries into a single unit of work. Call tx, err := db.Begin(). Execute queries on tx. Call tx.Commit() to save changes. Call tx.Rollback() to undo them. defer tx.Rollback() is safe even if the transaction is committed. It's a no-op after commit. This pattern ensures you always clean up, even if a query fails.
Trust gofmt. Argue logic, not formatting. Most editors run gofmt on save. Don't waste time debating indentation.
Use database/sql when you need a standard interface that works across PostgreSQL, MySQL, SQLite, and others. Use QueryRow when you expect a single result and want to avoid iterator boilerplate. Use Query when you need to process multiple rows and must remember to close the rows iterator. Use Exec for statements that modify data but return no result set. Use Prepare when you run the same query pattern repeatedly with different parameters. Use an ORM or query builder when your schema is complex and you want to trade some control for developer velocity. Use the driver directly only when you need features like custom serialization that the standard interface blocks.