Go Reference ยท Networking and the Web
Go database/sql package
Reference for Go database/sql: DB, Query, QueryRow, Exec, Scan, NullString, ErrNoRows, transactions, connection pooling and writing portable SQL code.
The package database/sql is Go's generic interface to SQL databases. It does not talk to any database by itself. You import a driver (for example github.com/jackc/pgx/v5/stdlib, github.com/go-sql-driver/mysql or modernc.org/sqlite) whose init function registers a name, then call sql.Open("name", dsn). Everything after that uses the same API, whatever the database.
*sql.DB is not a single connection. It is a thread-safe handle to a pool of connections, meant to be created once and shared by the whole program. It opens and closes connections as needed.
db, err := sql.Open("sqlite", "app.db") row := db.QueryRowContext(ctx, "SELECT name FROM users WHERE id = ?", 7) err = row.Scan(&name)
Try it
Real drivers are third-party modules, which the site's runner does not have. This example registers a tiny in-memory driver (the stand-in, about 60 lines) so you can run the genuine database/sql calls: Exec, Query, QueryRow, Scan, ErrNoRows, NullString and transactions.
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.
Syntax
func Open(driverName, dataSourceName string) (*DB, error) func (db *DB) PingContext(ctx context.Context) error func (db *DB) ExecContext(ctx context.Context, query string, args ...any) (Result, error) func (db *DB) QueryContext(ctx context.Context, query string, args ...any) (*Rows, error) func (db *DB) QueryRowContext(ctx context.Context, query string, args ...any) *Row func (db *DB) PrepareContext(ctx context.Context, query string) (*Stmt, error) func (db *DB) BeginTx(ctx context.Context, opts *TxOptions) (*Tx, error) func (db *DB) SetMaxOpenConns(n int) func (db *DB) SetMaxIdleConns(n int) func (db *DB) SetConnMaxLifetime(d time.Duration) func (r *Rows) Next() bool func (r *Rows) Scan(dest ...any) error func (r *Rows) Err() error func (r *Rows) Close() error func (r *Row) Scan(dest ...any) error
Every method has a context-free twin (Exec, Query, QueryRow, Begin) that uses context.Background().
| Name | Description |
|---|---|
query |
SQL text. Placeholders depend on the driver: ? (MySQL, SQLite), $1 (PostgreSQL), @p1 (SQL Server). |
args |
Values bound to the placeholders. They are sent as data, never spliced into the SQL, which prevents SQL injection. |
dest |
Pointers to receive each column, in order. The count must match the column count. |
Returns and errors: QueryRow never fails by itself; the error appears in Scan. sql.ErrNoRows means the query matched nothing. Open only validates its arguments, it does not connect, so call PingContext to test the connection. sql.ErrTxDone is returned for use of a finished transaction.
Usage notes
- Close what you open.
rows.Close()returns the connection to the pool. A loop that finishes normally closes the rows for you, but an earlyreturndoes not, so alwaysdefer rows.Close(). - Check
rows.Err()after the loop. A network failure in the middle looks like a normal end of rows toNext. - NULLs. Scanning a NULL into a plain
stringorintfails. Usesql.NullString,sql.NullInt64,sql.Null[T](Go 1.22) or a pointer such as*string. - Pooling. Set
SetMaxOpenConnsto protect your database, andSetConnMaxLifetimeso connections are recycled before a load balancer drops them. - Transactions. A
*sql.Txis bound to one connection. Usetx.Execand notdb.Execinside it. Pairdefer tx.Rollback()with a finaltx.Commit(). - Never build SQL with
fmt.Sprintffrom user input. Identifiers (table and column names) cannot be bound as parameters, so check them against a whitelist. - Use context variants with timeouts so a slow query does not hold a request forever.
- Prepared statements (
db.Prepare) help only when you run the same statement many times; the driver may already cache them.
Related
- context package and errors.Is()
- JSON encoding for returning rows from an API
- Tutorial: database/sql and SQL patterns