Basics

This is intended for MySQL, but is (probably?) generally applicable.

In these examples, the queries are performed on a transaction tx (using BeginTx), but can also be called on a plain DB object.

Standard insert query

tx.Exec - executes a query without returning any rows

_, err = tx.Exec(`
	INSERT INTO reported_url (document_uri, request_uri)
	VALUES (?, ?)
`, report.DocumentUri, report.RequestUri)

Select a single row

QueryRow

err := tx.QueryRow(
	`SELECT name, device_id	FROM analytics_event WHERE event_id=?`,
	event.EventID).Scan(&prevName, &prevDeviceID)

Errors are deferred until Row's Scan method is called

Select multiple rows

Query

rows, err := tx.Query(`
	SELECT t.name, t.age
	FROM test t
`)
if err != nil {
	return err
}
defer rows.Close() // Close, even if we don't enumerate all results

// Iterate over each row and scan it
for rows.Next() {
	var name string
	var age int
	if err := rows.Scan(&name, &age); err != nil {
		return err
	}
}

Internal DB Module

Our internal database module is gitlab.x.com/x/go/database which implements context and transactions.

include "gitlab.com/x/go/database/mysql"

dbConnectString := "root:password@tcp(127.0.0.1:3306)/serviceworker"
db, err := mysql.Open(dbConnectString)
if err != nil {
	log.Fatalf("Error opening database connection: %+v", err)
}
defer db.Close()

// use request.Context if associated with an HTTP request
ctx := context.TODO()

if err = db.TransactWithRetryContext(
	ctx, func(ctx context.Context, tx *sql.Tx) 
	error {

	// Everything in this block will be treated as a single transaction

}); err != nil {
	// ...
}

Misc

Nullable types

Go doesn't support nullable types - nil is not a valid integer, and 0 is the zero-value for an integer.