How to Retrieve a Column Value by Name Using Golang Database/Sql

Is it possible to retrieve a column value by name using GoLang database/sql

Yes, it is possible to do this without having to manually match up the column positions. There are some third-party libraries you can use to do this, such as sqlx or gorp. I would recommend sticking with one of these instead of rolling your own.

Named matching does have a slight penalty. Named matching is no different than matching up the column positions yourself. It just does this work for you at runtime - possibly on every query execution. This is true in any other language.

Why at runtime? The query is written as a string. It has to be parsed to determine the position.

If you were to make your own library, how do you do this on your own?

• Rows.Columns to get column names and positions.

• Passing a slice of pointers []interface{} to Rows.Scan to get the values.

• reflect.Value and Value.Addr to get a pointer to the destination value.

• Value.FieldByName to get the Value of a struct field if you want to map to struct fields.

Ok, so lets see how this works.

type Person struct {
    Id int
    Name string
}
rows, err := db.Query("SELECT id, name FROM person;")
if err != nil {
    // handle err
    log.Fatal(err)
}
columnNames, err := rows.Columns() // []string{"id", "name"}
if err != nil {
    // handle err
    log.Fatal(err)
}
people = make([]Person, 0, 2)
for rows.Next() {
    person := Person{}
    // person == Person{0, ""}
    pointers := make([]interface{}, len(columnNames))
    // pointers == `[]interface{}{nil, nil}`
    structVal := reflect.ValueOf(person)
    for i, colName := range columnNames {
        fieldVal := structVal.FieldByName(strings.Title(colName))
        if !fieldVal.IsValid() {
            log.Fatal("field not valid")
        }
        pointers[i] = fieldVal.Addr().Interface()
    }
    // pointers == `[]interface{}{&int, &string}`
    err := rows.Scan(pointers...)
    if err != nil {
        // handle err
        log.Fatal(err)
    }
    // person == Person{1, "John Doe"}
    people = append(people, person)
}

How to retrieve values in GoLang database/sql, when my structure in db(here postgres) is unknown?

Since you don't know the structure up front you can return the rows as a two dimensional slice of empty interfaces. However for the row scan to work you'll need to pre-allocate the values to the appropriate type and to do this you can use the ColumnTypes method and the reflect package. Keep in mind that not every driver provides access to the columns' types so make sure the one you use does.

rows, err := db.Query("select * from foobar")
if err != nil {
    return err
}
defer rows.Close()

// get column type info
columnTypes, err := rows.ColumnTypes()
if err != nil {
    return err
}

// used for allocation & dereferencing
rowValues := make([]reflect.Value, len(columnTypes))
for i := 0; i < len(columnTypes); i++ {
    // allocate reflect.Value representing a **T value
    rowValues[i] = reflect.New(reflect.PtrTo(columnTypes[i].ScanType()))
}

resultList := [][]interface{}{}
for rows.Next() {
    // initially will hold pointers for Scan, after scanning the
    // pointers will be dereferenced so that the slice holds actual values
    rowResult := make([]interface{}, len(columnTypes))
    for i := 0; i < len(columnTypes); i++ {
        // get the **T value from the reflect.Value
        rowResult[i] = rowValues[i].Interface()
    }

    // scan each column value into the corresponding **T value
    if err := rows.Scan(rowResult...); err != nil {
        return err
    }

    // dereference pointers
    for i := 0; i < len(rowValues); i++ {
        // first pointer deref to get reflect.Value representing a *T value,
        // if rv.IsNil it means column value was NULL
        if rv := rowValues[i].Elem(); rv.IsNil() {
            rowResult[i] = nil
        } else {
            // second deref to get reflect.Value representing the T value
            // and call Interface to get T value from the reflect.Value
            rowResult[i] = rv.Elem().Interface()
        }
    }

    resultList = append(resultList, rowResult)

}
if err := rows.Err(); err != nil {
    return err
}

fmt.Println(resultList)

Go SQL driver get interface{} column values

See this https://stackoverflow.com/questions/20271123/go-lang-sql-in-parameters answer which my answer is based on. Using that you can do something like this:

var myMap = make(map[string]interface{})
rows, err := db.Query("SELECT * FROM myTable")
defer rows.Close()
if err != nil {
    log.Fatal(err)
}
colNames, err := rows.Columns()
if err != nil {
    log.Fatal(err)
}
cols := make([]interface{}, len(colNames))
colPtrs := make([]interface{}, len(colNames))
for i := 0; i < len(colNames); i++ {
    colPtrs[i] = &cols[i]
}
for rows.Next() {
    err = rows.Scan(colPtrs...)
    if err != nil {
        log.Fatal(err)
    }
    for i, col := range cols {
        myMap[colNames[i]] = col
    }
    // Do something with the map
    for key, val := range myMap {
        fmt.Println("Key:", key, "Value Type:", reflect.TypeOf(val))
    }
}

Using the reflect package you can then get the Type for each column as needed as demonstrated with the loop at the end.

This is generic and will work with any table, number of columns etc.

read SELECT * columns into []string in go

In order to directly Scan the values into a []string, you must create an []interface{} slice pointing to each string in your string slice.

Here you have a working example for MySQL (just change the sql.Open-command to match your settings):

package main

import (
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "database/sql"
)

func main() {
    db, err := sql.Open("mysql", "user:pass@tcp(localhost:3306)/test?charset=utf8")
    defer db.Close()

    if err != nil {
        fmt.Println("Failed to connect", err)
        return
    }

    rows, err := db.Query(`SELECT 'one' col1, 'two' col2, 3 col3, NULL col4`)
    if err != nil {
        fmt.Println("Failed to run query", err)
        return
    }

    cols, err := rows.Columns()
    if err != nil {
        fmt.Println("Failed to get columns", err)
        return
    }

    // Result is your slice string.
    rawResult := make([][]byte, len(cols))
    result := make([]string, len(cols))

    dest := make([]interface{}, len(cols)) // A temporary interface{} slice
    for i, _ := range rawResult {
        dest[i] = &rawResult[i] // Put pointers to each string in the interface slice
    }

    for rows.Next() {
        err = rows.Scan(dest...)
        if err != nil {
            fmt.Println("Failed to scan row", err)
            return
        }

        for i, raw := range rawResult {
            if raw == nil {
                result[i] = "\\N"
            } else {
                result[i] = string(raw)
            }
        }

        fmt.Printf("%#v\n", result)
    }
}

Is there a way to get the Type for a Column using package database/sql in golang?

You should be able to do it this way:

func printRows(rows *sql.Rows){

    colTypes, err := rows.ColumnTypes()
    for _,s := range colTypes {
      log.Println("cols type:", s.DatabaseTypeName());
    }
}

How to read a row from a table to a map, Without knowing columns

You seem to be on the right track. According to the definition of Rows.Scan, you can supply values of the desired destination type, which would be string here. So changing the type of columns to []string should work:

var db sql.DB
var sqlcommand string

rows, _ := db.Query(sqlcommand)
cols, _ := rows.Columns()

data := make(map[string]string)

if rows.Next() {
    columns := make([]string, len(cols))
    columnPointers := make([]interface{}, len(cols))
    for i, _ := range columns {
        columnPointers[i] = &columns[i]
    }

    rows.Scan(columnPointers...)

    for i, colName := range cols {
        data[colName] = columns[i]
    }
}

How to execute an IN lookup in SQL using Golang?

Query just takes varargs to replace the params in your sql
so, in your example, you would just do

rows, err := stmt.Query(10)

say, this and that of your second example were dynamic, then you'd do

stmt, err := db.Prepare("SELECT * FROM awesome_table WHERE id=$1 AND other_field IN ($2, $3)")
rows, err := stmt.Query(10,"this","that")

If you have variable args for the "IN" part, you can do (play)

package main

import "fmt"
import "strings"

func main() {
    stuff := []interface{}{"this", "that", "otherthing"}
    sql := "select * from foo where id=? and name in (?" + strings.Repeat(",?", len(stuff)-1) + ")"
    fmt.Println("SQL:", sql)
    args := []interface{}{10}
    args = append(args, stuff...)
    fakeExec(args...)
    // This also works, but I think it's harder for folks to read
    //fakeExec(append([]interface{}{10},stuff...)...)
}

func fakeExec(args ...interface{}) {
    fmt.Println("Got:", args)
}

Go Using db.Query to return more than one column

Looking at the source, it seems the copy is done with ... syntax on destination pointers:

func (rs *Rows) Scan(dest ...interface{}) error

So in your example, you can do for instance:

for rows.Next() {
    u := User{} // An empty user
    ...
    if err := rows.Scan(&u.Name, &u.Age); err != nil {
        ...
    }
 }

As long as you pass the exact number of pointers, this should work, whether they are from a struct or not.

---

Related Topics