Should I Use `DB.exec()` or Prepared Statements in Go for Queries Without Result Rows?

Deciphering the Conundrum of Using *DB.exec() and Prepared Statements in Golang

Introduction

In the realm of database manipulation using Go and Postgresql, the lingering question arises: why bother with DB.exec() or prepared statements when Go seemingly automates their creation? This article delves into the intricacies of executing operations that do not return rows and elucidates the rationale behind using prepared statements.

Why Use DB.exec()?

While it's true that DB.Query() and DB.exec() can be used interchangeably to execute SQL statements, they differ in the type of result they return. DB.exec() provides insights into the number of rows affected by the query, whereas DB.Query() returns a result set.

For instance, suppose you wish to execute a DELETE statement and ascertain the number of deleted rows. Using DB.exec() is the preferred approach:

res, err := db.Exec(`DELETE FROM my_table WHERE expires_at = `, time.Now())
if err != nil {
    panic(err)
}

numDeleted, err := res.RowsAffected()
if err != nil {
    panic(err)
}
print(numDeleted)

Alternatively, the more cumbersome approach would involve using DB.Query() and loop through the rows to count them:

rows, err := db.Query(`DELETE FROM my_table WHERE expires_at =  RETURNING *`, time.Now())
if err != nil {
    panic(err)
}
defer rows.Close()

var numDeleted int
for rows.Next() {
    numDeleted += 1
}
if err := rows.Err(); err != nil {
    panic(err)
}
print(numDeleted)

Moreover, when the result of a query is irrelevant and you merely need to execute it, DB.exec() offers a concise solution:

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

In contrast, leaving out the close call for the returned DB.Rows value when using DB.Query() will eventually lead to an overload of open connections due to memory leaks.

Delving into Prepared Statements

While it's been suggested that Go automatically creates prepared statements, the reality appears to be more nuanced. The behavior of DB.Query() seems to hinge on the underlying driver being used.

Nevertheless, preparing and reusing DB.Stmt can improve performance for frequently executed queries. A glimpse into the official Postgresql documentation reveals how prepared statements can optimize operations.

Conclusion

Understanding the distinctions between DB.exec() and prepared statements in Golang helps developers execute operations that do not return rows efficiently and leverage the benefits of prepared statements when seeking performance optimizations.

The above is the detailed content of Should I Use `DB.exec()` or Prepared Statements in Go for Queries Without Result Rows?. For more information, please follow other related articles on the PHP Chinese website!