How do I convert a database row into a struct
SqlGoSql Problem Overview
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.
Sql Solutions
Solution 1 - Sql
Go package tests often provide clues as to ways of doing things. For example, from [database/sql/sql_test.go
][1],
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. [1]: https://golang.org/src/database/sql/sql_test.go?h=TestQuery#L252
Solution 2 - Sql
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.
Solution 3 - Sql
Here's one way to do it - just assign all of the struct values manually in the Scan
function.
func getUser(name string) (*User, error) {
var u User
// this calls sql.Open, etc.
db := getConnection()
// note the below syntax only works for postgres
err := db.QueryRow("SELECT * FROM users WHERE name = $1", name).Scan(&u.Id, &u.Name, &u.Score)
if err != nil {
return &User{}, err
} else {
return &u, nil
}
}
Solution 4 - Sql
rows, err := connection.Query("SELECT `id`, `username`, `email` FROM `users`")
if err != nil {
panic(err.Error())
}
for rows.Next() {
var user User
if err := rows.Scan(&user.Id, &user.Username, &user.Email); err != nil {
log.Println(err.Error())
}
users = append(users, user)
}
Solution 5 - Sql
there's package just for that: sqlstruct
unfortunately, last time I checked it did not support embedded structs (which are trivial to implement yourself - i had a working prototype in a few hours).
just committed the changes I made to sqlstruct
Solution 6 - Sql
Here is a library just for that: scany.
You can use it like that:
type User struct {
Name string
Id int
Score int
}
// db is your *sql.DB instance
// ctx is your current context.Context instance
// Use sqlscan.Select to query multiple records.
var users []*User
sqlscan.Select(ctx, db, &users, `SELECT name, id, score FROM users`)
// Use sqlscan.Get to query exactly one record.
var user User
sqlscan.Get(ctx, db, &user, `SELECT name, id, score FROM users WHERE id=123`)
It's well documented and easy to work with.
Disclaimer: I am the author of this library.
Solution 7 - Sql
use : go-models-mysql sqlbuilder
val, err = m.ScanRowType(row, (*UserTb)(nil))
or the full code
import (
"database/sql"
"fmt"
lib "github.com/eehsiao/go-models-lib"
mysql "github.com/eehsiao/go-models-mysql"
)
// MyUserDao : extend from mysql.Dao
type MyUserDao struct {
*mysql.Dao
}
// UserTb : sql table struct that to store into mysql
type UserTb struct {
Name sql.NullString `TbField:"Name"`
Id int `TbField:"Id"`
Score int `TbField:"Score"`
}
// GetFirstUser : this is a data logical function, you can write more logical in there
// sample data logical function to get the first user
func (m *MyUserDao) GetFirstUser() (user *User, err error) {
m.Select("Name", "Id", "Score").From("user").Limit(1)
fmt.Println("GetFirstUser", m.BuildSelectSQL().BuildedSQL())
var (
val interface{}
row *sql.Row
)
if row, err = m.GetRow(); err == nil {
if val, err = m.ScanRowType(row, (*UserTb)(nil)); err == nil {
u, _ := val.(*UserTb)
user = &User{
Name: lib.Iif(u.Name.Valid, u.Nae.String, "").(string),
Id: u.Id,
Score: u.Score,
}
}
}
row, val = nil, nil
return
}