replace variables with structs queriing with golang

0
votes

I've this snippet of code that works:

db, err := sql.Open("mysql", "pwd@tcp(ip:port)/db")
if err != nil {
    panic(err.Error())
}
rows, err := db.Query("select username from users")
if err != nil {
    panic(err.Error())
}
var (
    username string
)
for rows.Next() {
    err = rows.Scan(
        &name,
    )
    if err != nil {
        panic(err)
    }
    fmt.Println(username)
}

But, ... is it possible to substitute

var (
    username string
)

and err = rows.Scan( &name, )

with a struct?

I ask this because every time I want to add new field I need to

  • add field inside the query
  • create new variable in "var" block
  • add variable to scan

May I define a struct and update fields in just one place? Or, ... are there some best practice to build queries and fetch them?

2
go
Shouldn't it be &username in the rows.Scan()? - schrej
I submitted an edit for that. Turns out I made 2k rep today so I could directly edit it now, but that effectively bumps it in the lists, so it wouldn't be productive. - RayfenWindspear

2 Answers

2
votes

You can do something like this:

type user struct {
    Name  string
    Pass  string
    Email string
    Age   int
}

func main() {
    db, err := sql.Open("mysql", "pwd@tcp(ip:port)/db")
    if err != nil {
        panic(err.Error())
    }
    rows, err := db.Query("select user, pass, email, age from users")
    if err != nil {
        panic(err.Error())
    }
    var (
        users []user
    )
    for rows.Next() {
        u := user{}
        err = rows.Scan(
            &u.Name, &u.Pass, &u.Email, &u.Age,
        )
        if err != nil {
            panic(err)
        }

        users = append(users, u)
    }
    fmt.Println(users)
}
1
votes

If you are willing to use a library, https://github.com/jmoiron/sqlx is perfect for the job.

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

There is some basic usage in the github readme https://github.com/jmoiron/sqlx as well as some "standard" documentation the maintainer has written http://jmoiron.github.io/sqlx/ and finally, the godoc http://godoc.org/github.com/jmoiron/sqlx

One thing to note, is that sqlx made the design choice similar to the way the go compiler forces you to use variables that you have created. So if you select a column that isn't in your struct, it throws an error. The reasons for this are sound and promote good sql practices. Select * when you only need one column is crazy expensive. See his notes here http://jmoiron.github.io/sqlx/#safety