replace variables with structs queriing with golang

Question

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?

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
– RayfenWindspear, Commented Aug 3, 2017 at 16:15

TehSphinX · Accepted Answer · 2017-08-02 08:10:18Z

2

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

answered Aug 2, 2017 at 8:10

TehSphinX

7,5402 gold badges29 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

sensorario Over a year ago

Yet, it is better. But instead of just declare each variables for each fields you just suggest to create a struct. Is this the common way you use each time you scan rows?

TehSphinX Over a year ago

Yes I think so. You could try to generalise using reflect but then you kind of go around the type system of go -- making it slower and prone to errors.

TehSphinX Over a year ago

You can also have a look at gorm. I have not used it but it seems to pup up everywhere. That way you avoid doing the database layer yourself.

RayfenWindspear · Accepted Answer · 2017-08-02 09:39:23Z

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

Collectives™ on Stack Overflow

replace variables with structs queriing with golang

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related