Golang, database/sql, Postgres - is using QueryRow with an INSERT bad practice when you need the result?

user_733601 0
Viewed 1682

The Go database/sql Postgres adapter does not support LastInsertId. From the docs:

pq does not support the LastInsertId() method of the Result type in database/sql. To return the identifier of an INSERT (or UPDATE or DELETE), use the Postgres RETURNING clause with a standard Query or QueryRow call.

But the Go database/sql docs recommend not using Query to modify the database because it:

reserves a database connection until the sql.Rows is closed. Since there might be unread data (e.g. more data rows), the connection can not be used. In the example above, the connection will never be released again.

The docs also say "you should never use Query() like this."

Most people recommend using QueryRow to do an INSERT with Postgres if you need the resultant row. I don't fully understand the technical reasoning given by the docs, but its seems to conflict with what I've read. Is it safe and considered good practice to use QueryRow to do an INSERT?

go postgresql sql
2 Answers

That guide, which btw is not the database/sql documentation, is not wrong but you missed the important part that's in the snippet above the quoted paragraph, notice the // BAD comment:

_, err := db.Query("DELETE FROM users") // BAD

Specifically notice the _. This is called the "blank identifier" in Go and it allows you to discard values you don't care about.

When invoking Query you get back an *sql.Rows instance as the first return value, this value needs to be closed after you're done with it, if you discard it you can't close it, if you don't close it you'll leak connections. *sql.Rows is closed by invoking the Close method.

When invoking QueryRow you get back an *sql.Row value, this value needs to be closed after you're done with it, if you discard it you can't close it, if you don't close it you'll leak connections. *sql.Row is closed by invoking the Scan method.

"Is it safe and considered good practice to use QueryRow to do an INSERT?"

Yes and yes, as long as you handle the return value properly.

Doing something like this is absolutely ok:

var id int
row := db.QueryRow("insert ... returning id", params...)
if err := row.Scan(&id); err != nil { // scan will release the connection
    return err
}
fmt.Println(id)

Or:

rows, err := db.Query("insert ... values (...), (...) returning id", params...)
if err != nil {
    return err
}
defer rows.Close() // make sure that the connection is released once the function returns/panics

var ids []int
for rows.Next() {
    var id int
    if err := rows.Scan(&id); err != nil {
        return err
    }
    ids = append(ids, id)
}
if err := rows.Err(); err != nil {
    return err
}
fmt.Println(ids)
user_9659000

Probably you understood database/sql docs too literally. The point is that in the provided example the result is discarded 

if _, err := db.Query(`<query>`); err != nil {

}

While you are doing this there is a high probability that one day you will have an error such as too many connections because you are discarding the result without mandatory Close call.

So the answer is that you can safely use QueryRow with your insert queries. Call to the Scan method after QueryRow closes everything under the hood https://golang.org/src/database/sql/sql.go?s=88812:88857#L3094

user_86281590
Related