SQLInterface

Aseem Kishore edited this page Jun 17, 2018 · 5 revisions

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 http://golang.org/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 Ping function:

if err := db.Ping(); err != nil {
  log.Fatal(err)
}

After use, the database is closed using Close.

Executing queries

Exec is used for queries where no rows are returned:

result, err := db.Exec(
	"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.

Query is used for retrieval:

rows, err := db.Query("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)
}

QueryRow is used where only a single row is expected:

var age int64
row := db.QueryRow("SELECT age FROM users WHERE name = $1", name)
err := row.Scan(&age)

Prepared statements can be created with Prepare:

age := 27
stmt, err := db.Prepare("SELECT name FROM users WHERE age = $1")
if err != nil {
	log.Fatal(err)
}
rows, err := stmt.Query(age)
// process rows

Exec, Query and QueryRow can be called on statements. After use, a statement should be closed with Close.

Transactions

Transactions are started with Begin:

tx, err := db.Begin()
if err != nil {
	log.Fatal(err)
}

The Exec, Query, QueryRow and Prepare functions 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 NullString
err := db.QueryRow("SELECT name FROM names WHERE id = $1", id).Scan(&name)
...
if name.Valid {
	// use name.String
} else {
	// value is NULL
}

Only NullBool, NullFloat64, NullInt64 and NullString are implemented in database/sql. Implementations of database-specific null types are left to the database driver.

Clone this wiki locally
You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.
Press h to open a hovercard with more details.