When and Why Should I Use `db.Exec()` and Prepared Statements in Go?

Why even use *DB.exec() or prepared statements in Golang?

"Why even use db.Exec()"

While you can use db.Exec and db.Query interchangeably for the same SQL statements, they yield different results. db.Exec returns the number of affected rows (if supported by the driver), while db.Query returns a rows object.

For instance, to count the number of rows deleted in a DELETE statement:

res, err := db.Exec(`DELETE FROM my_table WHERE expires_at = `, time.Now())
numDeleted, err := res.RowsAffected() // Returns the number of deleted rows

Alternatively (but less efficient):

rows, err := db.Query(`DELETE FROM my_table WHERE expires_at =  RETURNING *`, time.Now())
numDeleted := 0
for rows.Next() { numDeleted++ } // Iterate over deleted rows

db.Exec should also be used when you don't need the query results, only an error check:

if _, err := db.Exec(`<my_sql_query>`); err != nil { panic(err) }

"or prepared statements in Golang?"

The documentation's claim that Go always uses prepared statements under the covers may not be correct. The driver implementation seems to determine if a new prepared statement is created for each db.Query call.

However, manually calling db.Prepare and caching the resulting db.Stmt can enhance performance for frequently executed queries. Refer to the PostgreSQL documentation for more details: https://www.postgresql.org/docs/current/static/sql-prepare.html

The above is the detailed content of When and Why Should I Use `db.Exec()` and Prepared Statements in Go?. For more information, please follow other related articles on the PHP Chinese website!