1

According to Go Database SQL, it says to use Exec for modifying data, and Query is bad because the underlying connection is kept open until Rows.Close() is called. However, there are two cases I can think of that asks what the correct pattern is.

1) Using sql.Tx for multiple updates, which will hold the connection until the Tx.Rollback or Tx.Commit. In this case would it matter?

2) Returning data on an Update, Insert or Delete (especially when using a non-int-based row ID, like UUID). In this case, QueryRow seems appropriate, especially since it returns Row which doesn't have a Close just Scan. However, a Tx could be opened to write and then immediately read, but this is often a lot of extra work.

Am I missing something with these two cases?

I'm using Postgres, but from what I'm reading about Go's DB integration, the database implementation shouldn't matter in most cases.

Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
Spencer Kormos
  • 8,381
  • 3
  • 28
  • 45
  • I've read this question, but it doesn't really answer my question or address my cases: https://stackoverflow.com/questions/50664648/why-even-use-db-exec-or-prepared-statements-in-golang – Spencer Kormos Aug 04 '19 at 00:22
  • Not a go programmer, but it looks like the docs are just saying that query returns an object that you need to do something with (close) and never ignore... Nothing specific about why doing updates in query is actually bad. – le3th4x0rbot Aug 04 '19 at 00:29

1 Answers1

2

TLDR: There's nothing wrong with using db.Query or db.QueryRow with INSERT, UPDATE, or DELETE. It is actually the right tool for the job if your SQL commands contain the RETURNING clause.

2) is, in a way, already answered in the linked tutorial by way of actually excluding your proposed scenario, since it is talking explicitly about cases where you do not care about the returned rows and you therefore choose to assign the returned value to the blank identifier.

What if you don’t care about the result? What if you just want to execute a statement and check if there were any errors, but ignore the result? Wouldn’t the following two statements do the same thing?

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

The answer is no. They do not do the same thing, and you should never use Query() like this. The Query() will return a sql.Rows, which reserves a database connection until the sql.Rows is closed.

The statement "... you should never use Query() like this ..." does not refer to db.Query being used with DELETE, UPDATE, or INSERT, instead it states, and correctly so, that you should never call Query and assign its result to _, i.e. you should never do _, err := db.Query("..., whatever the sql command is.

Note that the same applies to QueryRow, i.e. you should never discard the returned sql.Row value or in any other way omit calling its Scan method. This is because sql.Row is just a simple wrapper around sql.Rows and its Scan method closes the underlying sql.Rows before it returns (link).

1) As far as I understand, all the above still holds even for transactions. If you have an sql.Rows instance, created by an sql.Tx, and that sql.Rows instance hasn't yet been closed, the sql.Tx itself will be prevented from closing (link). That is, make sure that when you use tx.Query you close the returned rows, and when you use tx.QueryRow you call the returned row's Scan method.

mkopriva
  • 35,176
  • 4
  • 57
  • 71
  • "... in a way, already answered in the linked tutorial by way of actually excluding your proposed scenario...", it could be interpreted that way, but with regard to development, I don't often rely on lack of information as tacit acceptance. :grin: – Spencer Kormos Aug 04 '19 at 17:58
  • Nice highlight on the example code ignoring the returned `Rows` variable. i didn't pick up on that. Also nice comment around the Transaction being held open. I didn't realize that either, and very glad I mentioned this case as well. – Spencer Kormos Aug 04 '19 at 17:59