Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I'm using golang with Postgresql.

It says here that for operations that do not return rows (insert, delete, update) we should use exec()

If a function name includes Query, it is designed to ask a question of the database, and will return a set of rows, even if it’s empty. Statements that don’t return rows should not use Query functions; they should use Exec().

Then it says here :

Go creates prepared statements for you under the covers. A simple db.Query(sql, param1, param2), for example, works by preparing the sql, then executing it with the parameters and finally closing the statement.

If query() uses under the covers prepared statements why should I even bother using prepared statements?

"Why even use db.Exec()":

It's true that you can use db.Exec and db.Query interchangeably to execute the same sql statements however the two methods return different types of results. If implemented by the driver the result returned from db.Exec can tell you how many rows were affected by the query, while db.Query will return the rows object instead.

For example let's say you want to execute a DELETE statement and you want to know how many rows were deleted by it. You can do it either the proper way:

res, err := db.Exec(`DELETE FROM my_table WHERE expires_at = $1`, time.Now())
if err != nil {
    panic(err)
numDeleted, err := res.RowsAffected()
if err != nil {
    panic(err)
print(numDeleted)
or the more verbose and objectively costlier way:
rows, err := db.Query(`DELETE FROM my_table WHERE expires_at = $1 RETURNING *`, time.Now())
if err != nil {
    panic(err)
defer rows.Close()
var numDelete int
for rows.Next() {
    numDeleted += 1
if err := rows.Err(); err != nil {
    panic(err)
print(numDeleted)
There's a 3rd way you could do this with a combination of postgres CTEs, SELECT COUNT, db.QueryRow and row.Scan but I don't think an example is necessary to show how unreasonable an approach that would be when compared to db.Exec.
Another reason to use db.Exec over db.Query is when you don't care about the returned result, when all you need is to execute the query and check if there was an error or not. In such a case you can do this:
if _, err := db.Exec(`<my_sql_query>`); err != nil {
    panic(err)
On the other hand, you cannot (you can but you shouldn't) do this:
if _, err := db.Query(`<my_sql_query>`); err != nil {
    panic(err)
Doing this, after a short while, your program will panic with an error that says something akin to too many connections open. This is because you're discarding the returned db.Rows value without first making the mandatory Close call on it, and so you end up with the number of open connections going up and eventually hitting the server's limit.
"or prepared statements in Golang?":
I don't think the book you've cited is correct. At least to me it looks like whether or not a db.Query call creates a new prepared statement every time is dependent upon the driver you are using.
See for example these two sections of queryDC (an unexported method called by db.Query): without prepared statement and with prepared statement.
Regardless of whether the book is correct or not a db.Stmt created by db.Query would be, unless there is some internal caching going on, thrown away after you close the returned Rows object. If you instead manually call db.Prepare and then cache and reuse the returned db.Stmt you can potentially improve the performance of the queries that need to be executed often.
To understand how a prepared statement can be used to optimize performance you can take a look at the official documentation: https://www.postgresql.org/docs/current/static/sql-prepare.html
                Thanks for adding the new part. So this prepare how do you normally cache it? Can you give an example of it. Also I understand that the plan can change how to know when to cache a performant plan?
– CommonSenseCode
                Sep 14, 2018 at 8:10
                @commonSenseCode If I have a query that I know I'll execute often and I believe a prepared statement would be more optimal then I just prepare the statement at the program's start up and keep it in memory for the whole of the program's life, either in a map of statements or as an exported variable... I don't know if this is a good or bad approach but it worked just fine in my case... I haven't yet worked on a project where too many such statements would become a problem by eating up all the memory or anything.
– mkopriva
                Sep 14, 2018 at 8:30
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.