CODEWITHSUNDEEP
sqlgo
Spencer Kormos
Spencer Kormos

Reputation: 8451

When returning data on an insert or update, do I still need to use `Exec`?

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.

Upvotes: 1

Views: 1387

Answers (1)

mkopriva
mkopriva

Reputation: 38303

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.

Upvotes: 2

Related Questions