Examples
A detailed introduction to the database/sql package is available here: http://go-database-sql.org/
First, you should understand that a sql.DB is not a connection. When you use sql.Open() you get a handle for a database. The database/sql package manages a pool of connections in the background, and doesn't open any connections until you need them.
Therefore sql.Open() doesn't directly open a connection. As a result, sql.Open() does not return an error, if the server isn't available or the connection data (Username, Password) isn't correct. If you want to check this before making queries (e.g at application startup) you can use db.Ping().
db, err := sql.Open("mysql", "user:password@/dbname")
if err != nil {
panic(err.Error()) // Just for example purpose. You should use proper error handling instead of panic
}
defer db.Close()
// Open doesn't open a connection. Validate DSN data:
err = db.Ping()
if err != nil {
panic(err.Error()) // proper error handling instead of panic in your app
}
// Use the DB normally, execute the querys etc
[...]Assume an empty table with the following layout:
+--------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+-------+
| number | int(11) | NO | PRI | NULL | |
| squareNumber | int(11) | NO | | NULL | |
+--------------+---------+------+-----+---------+-------+
In this example we prepare two statements - one for inserting tuples (rows) and one to query.
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
)
func main() {
db, err := sql.Open("mysql", "user:password@/database")
if err != nil {
panic(err.Error()) // Just for example purpose. You should use proper error handling instead of panic
}
defer db.Close()
// Prepare statement for inserting data
stmtIns, err := db.Prepare("INSERT INTO squareNum VALUES( ?, ? )") // ? = placeholder
if err != nil {
panic(err.Error()) // proper error handling instead of panic in your app
}
defer stmtIns.Close() // Close the statement when we leave main() / the program terminates
// Prepare statement for reading data
stmtOut, err := db.Prepare("SELECT squareNumber FROM squarenum WHERE number = ?")
if err != nil {
panic(err.Error()) // proper error handling instead of panic in your app
}
defer stmtOut.Close()
// Insert square numbers for 0-24 in the database
for i := 0; i < 25; i++ {
_, err = stmtIns.Exec(i, (i * i)) // Insert tuples (i, i^2)
if err != nil {
panic(err.Error()) // proper error handling instead of panic in your app
}
}
var squareNum int // we "scan" the result in here
// Query the square-number of 13
err = stmtOut.QueryRow(13).Scan(&squareNum) // WHERE number = 13
if err != nil {
panic(err.Error()) // proper error handling instead of panic in your app
}
fmt.Printf("The square number of 13 is: %d", squareNum)
// Query another number.. 1 maybe?
err = stmtOut.QueryRow(1).Scan(&squareNum) // WHERE number = 1
if err != nil {
panic(err.Error()) // proper error handling instead of panic in your app
}
fmt.Printf("The square number of 1 is: %d", squareNum)
}Maybe you already encountered this error:
sql: Scan error on column index 1: unsupported driver -> Scan pair: <nil> -> *string
Normally you would use sql.NullString in such a case. But sometimes you don't care if the value is NULL, you just want to treat it as an empty string.
You can do this with a small workaround, which takes advantage of the fact, that a nil-[]byte gets converted to an empty string.
Instead of using *string as a rows.Scan(...) destination, you simple use *[]byte (or *sql.RawBytes), which can take the nil value:
[...]
var col1, col2 []byte
for rows.Next() {
// Scan the value to []byte
err = rows.Scan(&col1, &col2)
if err != nil {
panic(err.Error()) // Just for example purpose. You should use proper error handling instead of panic
}
// Use the string value
fmt.Println(string(col1), string(col2))
}package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
)
func main() {
// Open database connection
db, err := sql.Open("mysql", "user:password@/dbname")
if err != nil {
panic(err.Error()) // Just for example purpose. You should use proper error handling instead of panic
}
defer db.Close()
// Execute the query
rows, err := db.Query("SELECT * FROM table")
if err != nil {
panic(err.Error()) // proper error handling instead of panic in your app
}
// Get column names
columns, err := rows.Columns()
if err != nil {
panic(err.Error()) // proper error handling instead of panic in your app
}
// Make a slice for the values
values := make([]sql.RawBytes, len(columns))
// rows.Scan wants '[]interface{}' as an argument, so we must copy the
// references into such a slice
// See http://code.google.com/p/go-wiki/wiki/InterfaceSlice for details
scanArgs := make([]interface{}, len(values))
for i := range values {
scanArgs[i] = &values[i]
}
// Fetch rows
for rows.Next() {
// get RawBytes from data
err = rows.Scan(scanArgs...)
if err != nil {
panic(err.Error()) // proper error handling instead of panic in your app
}
// Now do something with the data.
// Here we just print each column as a string.
var value string
for i, col := range values {
// Here we can check if the value is nil (NULL value)
if col == nil {
value = "NULL"
} else {
value = string(col)
}
fmt.Println(columns[i], ": ", value)
}
fmt.Println("-----------------------------------")
}
if err = rows.Err(); err != nil {
panic(err.Error()) // proper error handling instead of panic in your app
}
}Feel free to contribute your own examples!