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

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?

Answer

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:

_, 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)