Examples

arnehormann edited this page Nov 17, 2014 · 37 revisions
Clone this wiki locally

A detailed introduction to the database/sql package is available here: http://go-database-sql.org/

A word on sql.Open

First, you should understand the concept 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 of Go 1.1, although it may change in a future version. 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() in Go 1.1 or a simple Query in versions prior to Go 1.1.

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
[...]

Prepared Statements

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)
}

Ignoring NULL values

Note: This might cause problems in Go 1.0 since the conversion from integer types to []byte is missing. The Issue is fixed in Go 1.1+

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))
}

RawBytes

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!