How to use rows.Scan of Go's database/sql

Question

I use database/sql and define a struct mapping to DB table columns(tag field):

// Users ...
type Users struct {
    ID            int64  `field:"id"`                      
    Username      string `field:"username"`           
    Password      string `field:"password"`           
    Tel           string `field:"tel"`                   
}

then I query:

        rows, err := db.Query(sql)  // select * from users
        if err != nil {
            fmt.Println(err)
        }
        defer rows.Close()
        for rows.Next() {
            user := new(Users)

            // works but I don't think it is good code for too many columns
            err = rows.Scan(&user.ID, &user.Username, &user.Password, &user.Tel)

            // TODO: How to scan in a simple way 


            if err != nil {
                fmt.Println(err)
            }
            fmt.Println("user: ", user)
            list = append(list, *user)
        }
        if err := rows.Err(); err != nil {
            fmt.Println(err)
        }

As you can see for rows.Scan() , I have to write all columns , and I don't think it's a good way for 20 or more columns .

How to scan in a clear way.

Have your type implement Scanner. Or use github.com/jmoiron/sqlx. — Sridhar, Jun 10 '19 at 11:36
I have used only `database/sql` and `github.com/go-sql-driver/mysql` . — Rife, Jun 10 '19 at 11:40
@Rife you can accomplish this yourself with `reflect`, but honestly if you really want this you should probably consider adopting `sqlx`, as it already solved this problem for you. — Dominic Barnes, Jun 10 '19 at 17:33
@DominicBarnes futhermore is reflect considered bad practice due to performance issues. — alexfwulf, Jun 10 '19 at 20:19
@alexfwulf it has implications, but I pointed out `sqlx` as it implements this solution and uses `reflect` to do so, so you wouldn't need to implement it yourself. — Dominic Barnes, Jul 04 '19 at 05:45

Rife · Accepted Answer · 2019-07-08T07:02:06.110

12

It's a good practice for using reflect:

    for rows.Next() {
        user := Users{}

        s := reflect.ValueOf(&user).Elem()
        numCols := s.NumField()
        columns := make([]interface{}, numCols)
        for i := 0; i < numCols; i++ {
            field := s.Field(i)
            columns[i] = field.Addr().Interface()
        }

        err := rows.Scan(columns...)
        if err != nil {
            log.Fatal(err)
        }
        log.Println(user)
    }

edited Jul 08 '19 at 07:02

answered Jul 08 '19 at 06:34

Rife

487
2
7
18

1

what if my `Users` interface has `json tag` also? I tried this approach and it does not work. – cpchung Apr 24 '20 at 00:03

score 4 · Answer 2 · answered Jun 10 '19 at 11:36

4

You may consider using jmoiron's sqlx package. It has support for assigning to a struct.

Excerpt from the readme:

type Place struct {
    Country string
    City    sql.NullString
    TelCode int
}
 places := []Place{}
 err = db.Select(&places, "SELECT * FROM place ORDER BY telcode ASC")
 if err != nil {
     fmt.Println(err)
      return
 }

answered Jun 10 '19 at 11:36

alexfwulf

199
1
13

sqlx is a good package, but how to achive that through only database/sql and github.com/go-sql-driver/mysql . – Rife Jun 10 '19 at 11:41

How to use rows.Scan of Go's database/sql

2 Answers2

Linked