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

I think that you can only use https://gorm.io/. GoOrm handles encoding and decoding by itself. So not additional code and has good support for all types of complex queries. In the code below, db.First() takes destination in which you want to parse your result to.

First(dest interface{}, conds ...interface{})

func GetUserById(ctx *context.Context, id int) (*User, error) {
    db := database.GetDB(ctx)
    userModel := User{}
    result := db.First(&userModel, User{Id: id})

    if result.Error != nil {
        return nil, errors.New("User not found")
    }

    return &userModel, nil

Answer 2

There are already some good answers here, I would like to add an SQL library I wrote just for tackling this issue of how complex it is to do a simple query on the most used Golang libraries; its called KSQL (not the Kafka one, the K stands for KISS or Keep It Stupid Simple).

Using it you could scan a user into a struct like this:

import (
    "context"
    "fmt"

    "github.com/vingarcia/ksql"
    "github.com/vingarcia/ksql/adapters/kpgx"
)

type User struct {
    Id    int    `ksql:"id"`
    Name  string `ksql:"name"`
    Score int    `ksql:"score"`
}

// Just a simple struct containing the table name and
// the name of the ID column or columns:
var UsersTable = ksql.NewTable("users", "id")

func main() {
    ctx := context.Background()
    dsn := "host=localhost user=postgres password=postgres dbname=postgres port=5432 sslmode=disable TimeZone=UTC"
    db, err := kpgx.New(ctx, dsn, ksql.Config{})
    if err != nil {
        panic("failed to connect database")
    }

    // Lets insert a user so the query below has something to return:
    err = db.Insert(ctx, UsersTable, &User{
        Name:  "SomeUser",
        Score: 42,
    })
    if err != nil {
        panic(err)
    }

    var user User
    err = db.QueryOne(ctx, &user, "FROM users WHERE name = $1", "SomeUser")
    if err != nil {
        panic(err)
    }

    fmt.Printf("%+v\n", user) // {Id:1 Name:SomeUser Score:42}
}

Answer 3

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.

Answer 4

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
}

Answer 5

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 6

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

Full example

Answer 7

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.

Answer 8

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

Answer 9

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