Go Using db.Query to return more than one column

Question

In the Go SQL docs they give an example here of a query that only returns 1 column (poor example in my opinion, at least return 2...)

age := 27
rows, err := db.Query("SELECT name FROM users WHERE age=?", age)
if err != nil {
        log.Fatal(err)
}
for rows.Next() {
        var name string
        if err := rows.Scan(&name); err != nil {
                log.Fatal(err)
        }
        fmt.Printf("%s is %d\n", name, age)
}
if err := rows.Err(); err != nil {
        log.Fatal(err)
}

The docs state here that Scan copies the columns in the current row into the values pointed at by dest.

How does this work with a struct, lets say I have a struct

type User struct{
    Name string
    Age int
}

and I modify my query to SELECT name, age from users where age=?

How do I unpack *Rows into my struct? I did find this example, but it didn't deal with structs. I will be following Active Record pattern conventions so my structs will map to my database via snake case conversion.

github.com/jmoiron/sqlx might help simplify this.

elithrar
– elithrar

2014-01-06 12:02:38 +00:00
Commented Jan 6, 2014 at 12:02 — elithrar
– elithrar, Commented Jan 6, 2014 at 12:02

val · Accepted Answer · 2014-01-06 10:28:14Z

4

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.

answered Jan 6, 2014 at 10:28

val

8,72935 silver badges34 bronze badges

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

4 Comments

Lee Over a year ago

If I first selected age and then name e.g. select age, name would the above fall over, i.e is it ordinal?

val Over a year ago

I believe so, yes. If you were to invert the query, the call to err := convertAssign(dest[i], sv) would fail and the row.Scan method would return an error, as it cannot convert int to string

Lee Over a year ago

That makes it impossible to do this with reflection as you cannot guarantee that the model fields are in ordinal sync with the columns of the database (well not without being pedantic and impractical)

val Over a year ago

I think the idea is to have it as low-level as possible for efficiency reasons. Nothing prevents you to implement a more user-friendly/flexible access to it on top of it (e.g. building a map[string]interface{} or even building the query from your reflected struct fields...)

Collectives™ on Stack Overflow

Go Using db.Query to return more than one column

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related