Go Wiki: SQLInterface
Introduction
The database/sql
package provides a generic interface around SQL (or SQL-like) databases. See the official documentation for details.
This page provides example usage patterns.
Database driver
The database/sql
package must be used in conjunction with a database driver.
See https://go.dev/s/sqldrivers for a list of drivers.
The documentation below assumes a driver has been imported.
Connecting to a database
Open
is used to create a database handle:
db, err := sql.Open(driver, dataSourceName)
Where driver specifies a database driver and dataSourceName specifies database-specific connection information such as database name and authentication credentials.
Note that Open
does not directly open a database connection: this is deferred
until a query is made. To verify that a connection can be made
before making a query, use the
PingContext
method:
if err := db.PingContext(ctx); err != nil {
log.Fatal(err)
}
After use, the database is closed using Close
.
Executing queries
ExecContext
is used for queries where no rows are returned:
result, err := db.ExecContext(ctx,
"INSERT INTO users (name, age) VALUES ($1, $2)",
"gopher",
27,
)
Where result contains the last insert ID and number of rows affected. The availability of these values is dependent on the database driver.
QueryContext
is used for retrieval:
rows, err := db.QueryContext(ctx, "SELECT name FROM users WHERE age = $1", age)
if err != nil {
log.Fatal(err)
}
defer rows.Close()
for rows.Next() {
var name string
if err := rows.Scan(&name); err != nil {
log.Fatal(err)
}
fmt.Printf("%s is %d\n", name, age)
}
if err := rows.Err(); err != nil {
log.Fatal(err)
}
QueryRowContext
is used where only a single row is expected:
var age int64
err := db.QueryRowContext(ctx, "SELECT age FROM users WHERE name = $1", name).Scan(&age)
Prepared statements can be created with PrepareContext
:
age := 27
stmt, err := db.PrepareContext(ctx, "SELECT name FROM users WHERE age = $1")
if err != nil {
log.Fatal(err)
}
rows, err := stmt.Query(age)
// process rows
ExecContext
, QueryContext
and QueryRowContext
can be called on statements. After use, a
statement should be closed with Close
.
Transactions
Transactions are started with BeginTx
:
tx, err := db.BeginTx(ctx, nil)
if err != nil {
log.Fatal(err)
}
The ExecContext
, QueryContext
, QueryRowContext
and PrepareContext
methods already covered can be
used in a transaction.
A transaction must end with a call to Commit
or Rollback
.
Dealing with NULL
If a database column is nullable, one of the types supporting null values should be passed to Scan.
For example, if the name column in the names table is nullable:
var name sql.NullString
err := db.QueryRowContext(ctx, "SELECT name FROM names WHERE id = $1", id).Scan(&name)
...
if name.Valid {
// use name.String
} else {
// value is NULL
}
Only NullByte
, NullBool
, NullFloat64
, NullInt64
, NullInt32
NullInt16
, NullString
and NullTime
are implemented in
database/sql
. Implementations of database-specific null types are left
to the database driver. User types supporting NULL
can be created by implementing interfaces database/sql/driver.Valuer
and database/sql.Scanner
.
You can also pass pointer types. Be careful for performance issues as it requires extra memory allocations.
var name *string
err := db.QueryRowContext(ctx, "SELECT name FROM names WHERE id = $1", id).Scan(&name)
Getting a table
If you want an struct array from your SQL query.
func getTable[T any](rows *sql.Rows) (out []T) {
var table []T
for rows.Next() {
var data T
s := reflect.ValueOf(&data).Elem()
numCols := s.NumField()
columns := make([]interface{}, numCols)
for i := 0; i < numCols; i++ {
field := s.Field(i)
columns[i] = field.Addr().Interface()
}
if err := rows.Scan(columns...); err != nil {
fmt.Println("Case Read Error ", err)
}
table = append(table, data)
}
return table
}
Make sure to deal with nulls from the database.
type User struct {
UUID sql.NullString
Name sql.NullString
}
rows, err := db.Query("SELECT * FROM Users")
cases := getTable[User](rows)
This content is part of the Go Wiki.