Vector Vector - 5 months ago 56
SQL Question

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

All of the examples I've seen for using

sql.Row
, access return values from queries by position:
sql.Rows.scan()
requires a correctly typed variable correctly positioned in the
scan()
arguments corresponding to the appropriate column, to retrieve each column value returned, such as in the following example:

Example Based on GoDocs (with small mod):

rows, err := db.Query("SELECT name,age FROM users WHERE age>=50")
if err != nil {
log.Fatal(err)
}
for rows.Next() {
var name string
var age int

if err := rows.Scan(&name,&age); err != nil {
log.Fatal(err)
}
fmt.Printf("%s is %d\n", name, age)
}
if err := rows.Err(); err != nil {
log.Fatal(err)
}


&name
and
&age
must be positioned correctly (columns 0 and 1) for Rows.Scan() to retrieve the correct values with the correct types.

Over my years of development for production systems, I have come to studiously avoid this practice because it's not robust: A database change in the layout of the columns will easily break your code if it's based on column positions.

It is far more robust to use column names for retrieving values - this insulates you from changes to the database that add or remove columns that screw up your position based code. For example, in Delphi and C#, all dataSets, including columns returning values from queries, support
FieldByName('age').asInteger
or
fields['age'].value,
etc.

Any way to accomplish this in Go? If not, this is a big drawback in Go database support and a serious disappointment - not at all safe, as mentioned.

Edit:

Also (perhaps this is a new question): The examples I've seen seem to require you to retrieve all the columns returned by the query, or the positions of the columns will be skewed.

Suppose there is utility query in a locked-down database that I cannot modify or add to, and it retrieves several columns, but I only need one of them for my current task. Based on the current
sql.Rows.Scan()
model, I have to retrieve all the values from the query in my application code, even though I don't need them, whereas if I could query
"columnByName"
that would not be necessary - I could just bring into my application code the data I need. Any work-around for this?

Answer

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?

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