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

Question

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?

With the column names, a utility function giving you an access by column name wouldn't be hard to implement. — Denys Séguret
– Denys Séguret, Commented Feb 24, 2014 at 11:50
@dystroy - AFAIK, not really: I was thinking using a map with the column names as keys and column values as values. But the underlying scan() call would still rely on position and break if the DB schema changed. Maybe you are thinking of something else? — Vector
– Vector, Commented Feb 24, 2014 at 11:55
Getting the field by name does not really protect you from all database changes. Some integration tests for your code would be more robust. — alpe1
– alpe1, Commented Feb 24, 2014 at 12:50
@ComeAndGo I have a function I like to implement whenever I'm working with running mysql queries that basically packages the data into a slice of maps of strings to strings ([]map[string]string). Each slice is the equivalent of a row, the map key string is the column name and the map value string is the column value for that row. Here is a simple playground to demonstrate how it works and share the packageData function code: play.golang.org/p/ctZYCn9EI9 — Verran
– Verran, Commented Feb 24, 2014 at 17:30

Luke · Accepted Answer · 2016-06-27 18:36:05Z

28

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

edited Jun 27, 2016 at 18:36

answered Jan 7, 2015 at 1:02

Luke

14.2k8 gold badges47 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

George Over a year ago

how is this not natively supported?

Luke Over a year ago

Column names are dynamic. Go is statically compiled. So you have to match up the names at runtime. There are libraries that do this for you. This answer shows how it is done.

George Over a year ago

column names are never dynamic, you'd have to recompile the code anyway to change your sql stmt/column names. I'm just surprised at how much of a PITA it is in Go atm. even with sqlx, which is awesome.

Luke Over a year ago

Column names are stored in a string var, which can be populated externally without recompile. That makes them not static. A struct field name is static. Changing those require recompile.

George Over a year ago

even in such extreme cases accessing the results by column names instead of column position is the only thing that would make sense

CommonSenseCode · Accepted Answer · 2018-07-06 12:14:09Z

2

The only sane & clean way to do this is to use: https://github.com/jmoiron/sqlx

Let say you have a Place struct:

type Place struct {
    Country       string
    City          sql.NullString
    TelephoneCode int `db:"telcode"`
}

You scan it easily:

rows, err := db.Queryx("SELECT * FROM place")
for rows.Next() {
    var p Place
    err = rows.StructScan(&p)
}

More info: http://jmoiron.github.io/sqlx/

answered Jul 6, 2018 at 12:14

CommonSenseCode

25.7k39 gold badges151 silver badges200 bronze badges

Comments

Jan Tungli · Accepted Answer · 2023-06-16 11:20:08Z

Maybe a different approach to the data would help, like something like this:

func GetFieldValues(res *sql.Rows) []string {
    m, err := res.ColumnTypes()
    if err != nil {
        return nil
    }
    var (
        //i    int = 0
        mLen int = len(m)
    )
    if res.Next() {
        mValStr := make([]sql.NullString, mLen)
        mVal := make([]any, mLen)
        for i := range mVal {
            mVal[i] = &mValStr[i]
        }

        res.Scan(mVal...) //super

        mmVal := make([]string, mLen)
        for i := 0; i < mLen; i++ {
            if mValStr[i].Valid {
                mmVal[i] = mValStr[i].String
            } else {
                mmVal[i] = "" // empty string is nil in this context --> see func IsNull
            }
        }
        mVal = nil // Free allocated mem
        return mmVal

    } else {
        return nil
    }
}

Jose Luis Uyuni · Accepted Answer · 2023-10-03 22:04:51Z

0

/ *

GetPointersRowSQL uses, generate automatic fileds
people = model.Suspicious{}
for row.Next() {
pointer := util.GetPointersRowSQL(row, &people)
if pointer != nil { err = row.Scan(pointer...) }

} return people, err

**/

func GetPointersRowSQL(rows *sql.Rows, val any) (p []interface{}) {

columnNames, err := rows.Columns()

if err != nil {
    log.Fatal(err.Error())
}
nro := len(columnNames)
pointers := make([]interface{}, nro)

/*for j, colName := range columnNames {
    columnNames[j] = strings.Title(colName)
}*/

structVal := reflect.ValueOf(val).Elem()

for i, colName := range columnNames {
    fieldVal := structVal.FieldByName(strings.Title(colName))
    //fieldVal := structVal.FieldByName(colName)
    if !fieldVal.IsValid() {
        log.Println("field not valid  " + colName)
        return nil
    }
    pointers[i] = fieldVal.Addr().Interface()
}
return pointers

}

edited Oct 3, 2023 at 22:04

answered Sep 24, 2023 at 18:22

Jose Luis Uyuni

11 bronze badge

2 Comments

Jose Luis Uyuni Over a year ago

maybe help this code

Jeremy Caney Over a year ago

Thank you for contributing to the Stack Overflow community. This may be a correct answer, but it’d be really useful to provide additional explanation of your code so developers can understand your reasoning. This is especially useful for new developers who aren’t as familiar with the syntax or struggling to understand the concepts. Would you kindly edit your answer to include additional details for the benefit of the community? Please also evaluate your formatting.

Collectives™ on Stack Overflow

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

4 Answers 4

5 Comments

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related