Skip to content
Tutorials Center
My learning

Topics

Lesson 118 of 147 Β· Databases

Databases with database/sql

Use Go's database/sql package - drivers, Open and Ping, Query, QueryRow, Exec and Scan, NULL handling, prepared statements, transactions, contexts and pool settings, plus a tiny driver you can run.

Almost every real application stores data in a database. Go's standard library does not contain a database, but it contains something more valuable: database/sql, a small, uniform API for talking to SQL databases. You write db.QueryRow(...) and rows.Scan(...) the same way whether the database is PostgreSQL, MySQL or SQLite. The package also manages a connection pool for you, retries stale connections, and gives you transactions and prepared statements.

What database/sql does not contain is any knowledge of a particular database. That comes from a driver, a third-party package you import for its side effect. Because the browser editor on this site can only run standard-library code, no real driver is available here. So this lesson does two things: it shows you the real-world code in read-only blocks, and it runs everything that can run, including a tiny in-memory teaching driver that we implement ourselves. Writing a driver is the best way to see exactly what database/sql asks of a database, and what it handles for you.

Drivers and the standard API

The architecture has three layers:

TEXT
your code  ->  database/sql (pool, Tx, Rows, Scan, NULL types)  ->  driver  ->  database server
                                                       (pgx, mysql, sqlite3, ...)

A driver registers itself under a name in its init function. You import the package with a blank identifier (_) so Go runs init even though you use no names from it, and then ask database/sql for that driver by name:

Go
import (
	"database/sql"

	_ "github.com/jackc/pgx/v5/stdlib" // registers "pgx" (PostgreSQL)
)

db, err := sql.Open("pgx", "postgres://app:secret@localhost:5432/shop?sslmode=disable")

Popular drivers (all third-party, so read-only here) are:

Database Driver import Driver name Example DSN
PostgreSQL github.com/jackc/pgx/v5/stdlib pgx postgres://user:pass@host:5432/db
MySQL, MariaDB github.com/go-sql-driver/mysql mysql user:pass@tcp(host:3306)/db?parseTime=true
SQLite (pure Go) modernc.org/sqlite sqlite file:app.db?_pragma=busy_timeout(5000)
SQLite (cgo) github.com/mattn/go-sqlite3 sqlite3 file:app.db?cache=shared
SQL Server github.com/microsoft/go-mssqldb sqlserver sqlserver://user:pass@host:1433?database=db

The second argument of sql.Open is the data source name (DSN), whose format is defined by the driver. Never hard-code credentials; read the DSN from the environment or a secrets store.

sql.Open does not connect

This surprises almost everybody. sql.Open only validates its arguments and creates a *sql.DB; it does not talk to the database. The first real connection is made lazily when you run a query. To check that the database is reachable, call db.Ping() (or db.PingContext(ctx)) at startup.

Also remember that *sql.DB is not a single connection but a pool. It is safe for concurrent use and meant to live as long as your program: create it once, share it, and close it on shutdown. Opening a *sql.DB per request is a classic mistake.

Go Open is lazy, Ping connects

Press Run (or Ctrl + Enter) to see the result here.

Program input Β· what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with bufio.Scanner or fmt.Scan), it gets the next line from this box.

Open succeeds even though the server is unreachable. The failure only shows up on Ping and Exec. An unknown driver name is the one error Open does report, and its message hints at the usual cause: a forgotten blank import. After Close, the pool refuses further use.

Query, QueryRow and Exec

There are three calls you use constantly, and picking the right one matters:

  • db.Exec(query, args...) runs a statement that returns no rows (INSERT, UPDATE, DELETE, DDL). It returns a sql.Result with RowsAffected() and, for drivers that support it, LastInsertId(). PostgreSQL does not support LastInsertId; use INSERT ... RETURNING id with QueryRow instead.
  • db.Query(query, args...) returns a *sql.Rows for many rows. You must loop with rows.Next(), call rows.Scan for each row, close the rows (defer rows.Close()) and check rows.Err() after the loop.
  • db.QueryRow(query, args...) is for at most one row and returns a *sql.Row. Its Scan reports sql.ErrNoRows when nothing matched. Check it with errors.Is, as explained in Wrapping and Inspecting Errors.

Query values go through placeholders, never string concatenation. The placeholder syntax depends on the database:

Database Placeholder
PostgreSQL $1, $2, ...
MySQL, MariaDB, SQLite ?
SQL Server @p1, @p2, ... (or named @name with sql.Named)
Oracle :1, :name
Go
rows, err := db.QueryContext(ctx,
	"SELECT id, name, email FROM users WHERE created_at > $1 ORDER BY id", since)
if err != nil {
	return nil, fmt.Errorf("list users: %w", err)
}
defer rows.Close() // releases the connection even if we return early

var users []User
for rows.Next() {
	var u User
	if err := rows.Scan(&u.ID, &u.Name, &u.Email); err != nil {
		return nil, fmt.Errorf("scan user: %w", err)
	}
	users = append(users, u)
}
if err := rows.Err(); err != nil { // errors that ended the iteration early
	return nil, fmt.Errorf("iterate users: %w", err)
}
return users, nil

Do not forget rows.Err(). rows.Next() returns false both at the normal end and when something went wrong (a dropped connection mid-result), and only Err distinguishes them. Also remember that an open Rows holds a connection from the pool; forgetting to close it, or abandoning the loop early without Close, slowly drains the pool until every query hangs.

Placeholders carry values only. You cannot use them for table names, column names or ORDER BY directions: ORDER BY $1 sorts by a constant. If a column name must be dynamic, check it against a fixed allow-list in Go (switch col { case "name", "created_at": ... }) and then build the query text. Never concatenate user input into SQL; that is how SQL injection happens.

NULL values and custom types

A SQL column can hold NULL, but a Go string or int cannot, so scanning NULL into one fails with an error such as converting NULL to string is unsupported. There are three ways to deal with it:

  1. sql.Null[T] (generic, Go 1.22) or the older sql.NullString, sql.NullInt64, sql.NullBool, sql.NullFloat64, sql.NullTime. They have a value field and a Valid flag.
  2. A pointer (*string) scanned from the column: nil means NULL. Convenient, and it marshals to null in JSON.
  3. Make the column NOT NULL with a default value, so the problem never appears. Often the best answer.

Behind these types stand two small interfaces that you can implement for your own types. sql.Scanner has Scan(src any) error and describes how to read a column value; driver.Valuer has Value() (driver.Value, error) and describes how to send your type as a query argument. That is how an enum, a money type or a JSON column is stored. Since these methods can be called directly, we can try them without any database at all:

Go NULL types, Scanner and Valuer

Press Run (or Ctrl + Enter) to see the result here.

Program input Β· what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with bufio.Scanner or fmt.Scan), it gets the next line from this box.

Every driver delivers a small set of Go types to Scan: int64, float64, bool, []byte, string, time.Time and nil. The standard library converts between them and your destination variables (an int64 into an int, a []byte into a string), and fails with a clear error when a conversion is impossible or lossy. The mirror image is driver.Valuer: whatever your type returns from Value must be one of those same base types.

A teaching driver

To see how everything fits together, here is a complete, if tiny, driver: an in-memory "database" that understands five kinds of statements (CREATE TABLE, INSERT, SELECT, UPDATE, DELETE) with ? placeholders. It is a teaching driver: the SQL parser is a handful of regular expressions, it supports no types, joins or indexes, and it serializes transactions with a lock. But the plumbing is real. Everything below database/sql is these interfaces from the database/sql/driver package:

  • driver.Driver: Open(dsn) returns a connection.
  • driver.Conn: Prepare(query) returns a statement; Begin starts a transaction; Close.
  • driver.Stmt: NumInput() (how many placeholders), Exec(args), Query(args), Close.
  • driver.Rows: Columns(), Next(dest) (returns io.EOF at the end), Close.
  • driver.Tx: Commit and Rollback.

On top of that driver, the main function below uses the real database/sql API, exactly as you would with PostgreSQL.

Go database/sql on top of a tiny in-memory driver

Press Run (or Ctrl + Enter) to see the result here.

Program input Β· what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with bufio.Scanner or fmt.Scan), it gets the next line from this box.

Read the output against the code; each section demonstrates one piece of the real API:

  • Exec returns a Result. Our driver reports a "last ID" equal to the row number and one affected row, like a database with auto-numbering would.
  • Scan receives int64 and string values from the driver and converts them into int and string for you. The NULL email in row 2 arrives as nil, and sql.NullString records Valid == false.
  • ErrNoRows is what QueryRow(...).Scan returns when no row matches. The wrong number of arguments is caught by database/sql itself because our NumInput told it how many placeholders to expect; if a driver returns -1, the check is skipped.
  • A prepared statement (db.Prepare) parses the SQL once and can then be executed many times. In real databases that saves parse time and is safe against injection; remember to Close it. For one-off queries, db.Query(sql, args) prepares and executes internally.
  • Transactions group statements into one all-or-nothing unit. After Rollback the deletion and the insert are undone (the row count is back to what it was), and after Commit the deletion stays. In this toy driver a transaction locks the whole database, so you must not use db from the same goroutine while a Tx is open; real databases allow it.
  • A cancelled context is detected by database/sql before it even calls the driver.

Transactions in practice

The reliable shape for a transaction in application code is: begin, defer tx.Rollback(), do the work, commit. Calling Rollback after a successful Commit is harmless (it returns sql.ErrTxDone), so the deferred call covers every early return and every panic:

Go
func transfer(ctx context.Context, db *sql.DB, from, to int, cents int64) error {
	tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})
	if err != nil {
		return err
	}
	defer tx.Rollback() // no-op after a successful Commit

	res, err := tx.ExecContext(ctx,
		"UPDATE accounts SET balance = balance - $1 WHERE id = $2 AND balance >= $1", cents, from)
	if err != nil {
		return fmt.Errorf("debit: %w", err)
	}
	if n, _ := res.RowsAffected(); n == 0 {
		return errors.New("insufficient funds or unknown account")
	}
	if _, err := tx.ExecContext(ctx,
		"UPDATE accounts SET balance = balance + $1 WHERE id = $2", cents, to); err != nil {
		return fmt.Errorf("credit: %w", err)
	}
	return tx.Commit()
}

Inside the function, use tx.ExecContext and tx.QueryContext, never db.…: a call on db takes a different connection from the pool and runs outside the transaction. Keep transactions short, because they hold locks and a connection. Isolation levels (LevelReadCommitted, LevelRepeatableRead, LevelSerializable) are passed through TxOptions; which levels exist depends on the database. If you use LevelSerializable, be ready to retry on serialization failures.

Context-aware calls

Every method has a …Context variant: QueryContext, QueryRowContext, ExecContext, PrepareContext, BeginTx, PingContext. Use them everywhere in server code. When the context is cancelled or its deadline passes, database/sql asks the driver to abort the statement, so an HTTP request that times out or whose client disconnects stops its queries too instead of letting them run to completion. A typical handler derives the context from the request (r.Context()) and adds a deadline of its own:

Go
ctx, cancel := context.WithTimeout(r.Context(), 2*time.Second)
defer cancel()
err := db.QueryRowContext(ctx, "SELECT count(*) FROM orders WHERE user_id = $1", id).Scan(&n)
if errors.Is(err, context.DeadlineExceeded) {
	http.Error(w, "database too slow", http.StatusGatewayTimeout)
	return
}

The mechanism is the same as in The context Package. Queries without a context can never be cancelled, which is a common cause of hung servers.

The connection pool

*sql.DB opens connections on demand, keeps idle ones for reuse and closes them under rules you control:

Setting Meaning Default
SetMaxOpenConns(n) Maximum simultaneous connections; further requests wait unlimited
SetMaxIdleConns(n) Idle connections kept for reuse 2
SetConnMaxLifetime(d) Close connections older than d (helps with load balancers and failovers) forever
SetConnMaxIdleTime(d) Close connections idle longer than d forever

Unlimited connections sound good until a traffic spike opens hundreds of them and the database collapses (PostgreSQL, for example, is typically configured for only a few hundred connections in total). Set SetMaxOpenConns below the database's limit divided by your number of application instances, keep SetMaxIdleConns close to it, and set a lifetime of a few minutes. The following program uses a stub driver that takes 20 ms per statement, to show the pool at work with ten concurrent callers.

Go MaxOpenConns limits the pool

Press Run (or Ctrl + Enter) to see the result here.

Program input Β· what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with bufio.Scanner or fmt.Scan), it gets the next line from this box.

With a limit of three, the ten callers share three connections, and the other seven had to wait for a free one (WaitCount). With a limit of one, everything is serialized through a single connection, which also shows that MaxOpenConns(1) is a way to protect databases like SQLite that allow one writer at a time. db.Stats() is worth exporting to your metrics system: a growing wait count or wait duration is the early warning that the pool is too small or that queries hold connections too long.

Common mistakes

  • Forgetting rows.Close() and rows.Err(). Leaked rows exhaust the pool; unchecked errors hide truncated results.
  • Using db.Query for statements without results. Exec releases its connection immediately; an unread Rows does not.
  • Holding a Rows open while making another query on a small pool. Read what you need into a slice, close the rows, then continue.
  • Scanning into the wrong type (NULL into a plain string, a DECIMAL into float64). Use the Null types or pointers, and a decimal library or integer cents for money.
  • Creating a *sql.DB per request, or calling Close after each use.
  • Building SQL with fmt.Sprintf and user input. Use placeholders, and allow-lists for identifiers.
  • Ignoring the context. Always pass one.

When you write a lot of queries, the repeated code above gets tedious. The next lesson, SQL in Go: Repositories, Migrations and sqlc, shows how to organize it.

Check your understanding

Quick check Β· 5 questions

of 5 answered You scored / 5 Β· perfect!

  1. 1. You call sql.Open("pgx", dsn) and it returns a nil error, but the database server is switched off. Why?

    sql.Open validates its arguments and returns a *sql.DB without contacting the server. Use db.Ping or db.PingContext at startup to verify connectivity.
  2. 2. A loop over rows.Next() ends early because the network connection dropped halfway through a big result. Where do you find out?

    Next returns false both at the end of the data and on failure. Always check rows.Err() after the loop to tell the two apart.
  3. 3. What happens if you scan a column containing NULL into a plain string variable?

    A Go string cannot represent NULL. Scan into sql.NullString, sql.Null[string] or a *string, or make the column NOT NULL.
  4. 4. Inside a transaction function you write db.ExecContext(ctx, ...) instead of tx.ExecContext(ctx, ...). What is the consequence?

    db takes any connection from the pool, so the statement is not part of the transaction. Use the Tx value for every statement that belongs to it.
  5. 5. Why is defer tx.Rollback() placed right after BeginTx even though you call tx.Commit() at the end?

    Once committed, a transaction is done and Rollback just returns sql.ErrTxDone. If the function leaves early, the deferred Rollback releases the connection and undoes the work.

Summary

  • database/sql is a driver-independent API with a connection pool. Drivers register themselves by name and are imported with a blank identifier; the DSN format belongs to the driver.
  • sql.Open does not connect; Ping does. A *sql.DB is a long-lived, concurrency-safe pool: create it once and share it.
  • Use Exec for statements without rows, Query for many rows (always defer rows.Close() and check rows.Err()) and QueryRow for one row (sql.ErrNoRows when empty). Pass values through placeholders ($1 or ?), never string concatenation; identifiers need an allow-list.
  • Handle NULL with sql.Null[T], the NullString family, pointers or NOT NULL columns. Custom types implement sql.Scanner and driver.Valuer.
  • Transactions: BeginTx, defer tx.Rollback(), use only tx for the statements, then Commit. Prepared statements parse once and run many times; close them.
  • Use the …Context methods everywhere so cancellation and timeouts reach the database. Tune the pool with SetMaxOpenConns, SetMaxIdleConns, SetConnMaxLifetime and watch db.Stats().
  • A driver is just a handful of interfaces (Driver, Conn, Stmt, Rows, Tx), as the teaching driver in this lesson shows.

Next: SQL in Go: Repositories, Migrations and sqlc