How do I convert a database row into a struct

Question

Let's say I have a struct:

type User struct {     Name  string     Id    int     Score int } 

And a database table with the same schema. What's the easiest way to parse a database row into a struct? I've added an answer below but I'm not sure it's the best one.

Answer 1

Go package tests often provide clues as to ways of doing things. For example, from database/sql/sql_test.go,

func TestQuery(t *testing.T) {     /* . . . */     rows, err := db.Query("SELECT|people|age,name|")     if err != nil {             t.Fatalf("Query: %v", err)     }     type row struct {             age  int             name string     }     got := []row{}     for rows.Next() {             var r row             err = rows.Scan(&r.age, &r.name)             if err != nil {                     t.Fatalf("Scan: %v", err)             }             got = append(got, r)     }     /* . . . */ }  func TestQueryRow(t *testing.T) {     /* . . . */     var name string     var age int     var birthday time.Time     err := db.QueryRow("SELECT|people|age,name|age=?", 3).Scan(&age)     /* . . . */ } 

Which, for your question, querying a row into a structure, would translate to something like:

var row struct {     age  int     name string } err = db.QueryRow("SELECT|people|age,name|age=?", 3).Scan(&row.age, &row.name) 

I know that looks similar to your solution, but it's important to show how to find a solution.

Answer 2

I recommend github.com/jmoiron/sqlx.

From the README:

sqlx is a library which provides a set of extensions on go's standard database/sql library. The sqlx versions of sql.DB, sql.TX, sql.Stmt, et al. all leave the underlying interfaces untouched, so that their interfaces are a superset on the standard ones. This makes it relatively painless to integrate existing codebases using database/sql with sqlx.

Major additional concepts are:

• Marshal rows into structs (with embedded struct support), maps, and slices
• Named parameter support including prepared statements
• Get and Select to go quickly from query to struct/slice

---

The README also includes a code snippet demonstrating scanning a row into a struct:

type Place struct {     Country       string     City          sql.NullString     TelephoneCode int `db:"telcode"` } // Loop through rows using only one struct place := Place{} rows, err := db.Queryx("SELECT * FROM place") for rows.Next() {     err := rows.StructScan(&place)     if err != nil {         log.Fatalln(err)     }      fmt.Printf("%#v\n", place) } 

Note that we didn't have to manually map each column to a field of the struct. sqlx has some default mappings for struct fields to database columns, as well as being able to specify database columns using tags (note the TelephoneCode field of the Place struct above). You can read more about that in the documentation.