sql.DB is a abstraction of DB relation operations. It is not a connection
Thus, to check if a connection is open, we need to 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)
}
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
}
}
_, err = db.Exec("BEGIN")
_, err = db.Exec("UPDATE account SET balance = 100 WHERE user = 83")
_, err = db.Exec("COMMIT")
tx, err := db.Begin()
// ...
_, err = db.Exec("UPDATE account SET balance = 100 WHERE user = 83")
// ...
err = tx.Commit()
rows, _ := tx.Query("SELECT id FROM master_table")
for rows.Next() {
var mid, did int
rows.Scan(&mid)
tx.QueryRow("SELECT id FROM detail_table WHERE master = ?", mid).Scan(&did) // **BOOM**
}
package main
import (
"database/sql"
"database/sql/driver"
"errors"
_ "github.com/go-sql-driver/mysql"
"log"
"strings"
)
type LowercaseString string
// Implements driver.Valuer.
func (ls LowercaseString) Value() (driver.Value, error) {
return driver.Value(strings.ToLower(string(ls))), nil
}
// Implements sql.Scanner. Simplistic -‐-‐ only handles string and []byte
func (ls *LowercaseString) Scan(src interface{}) error {
var source string
switch src.(type) {
case string:
source = src.(string)
case []byte:
source = string(src.([]byte))
default:
return errors.New("Incompatible type for LowercaseString")
}
*ls = LowercaseString(strings.ToLower(source))
return nil
}
func main() {
db, err := sql.Open("mysql",
"root:@tcp(:3306)/test")
if err != nil {
log.Fatal(err)
}
defer db.Close()
_, err = db.Exec(
"CREATE TABLE IF NOT EXISTS test.hello(world varchar(50))")
if err != nil {
log.Fatal(err)
}
_, err = db.Exec("DELETE FROM test.hello")
if err != nil {
log.Fatal(err)
}
// Insert a row that's not lowercased, and one that is.
var normalString string = "I AM UPPERCASED NORMAL STRING"
var lcString LowercaseString = "I AM UPPERCASED MAGIC STRING"
_, err = db.Exec("INSERT INTO test.hello VALUES(?), (?)", normalString, lcString)
if err != nil {
log.Fatal(err)
}
rows, err := db.Query("SELECT * FROM test.hello")
if err != nil {
log.Fatal(err)
}
defer rows.Close()
for rows.Next() {
var s1 LowercaseString
err = rows.Scan(&s1)
if err != nil {
log.Print(err)
}
log.Print(s1)
}
}
type GzippedText []byte
func (g GzippedText) Value() (driver.Value, error) {
b := make([]byte, 0, len(g))
buf := bytes.NewBuffer(b)
w := gzip.NewWriter(buf)
w.Write(g)
w.Close()
return buf.Bytes(), nil
}
func (g *GzippedText) Scan(src interface{}) error {
var source []byte
// let's support string and []byte
switch src.(type) {
case string:
source = []byte(src.(string))
case []byte:
source = src.([]byte)
default:
return errors.New("Incompatible type for GzippedText")
}
reader, _ := gzip.NewReader(bytes.NewReader(source))
defer reader.Close()
b, err := ioutil.ReadAll(reader)
if err != nil {
return err
}
*g = GzippedText(b)
return nil
}