Why Even Use *Db.Exec() or Prepared Statements in Golang

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

"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

Why use prepared statements instead of Query / Exec with go sql package?

Prepared statement already bound to concrete connection to DB, contains low-level driver.Stmt and can be used concurrently by multiple go-routings. So it's quite handy to prepare and use, and things work faster.

Go prepared statements vs querying directly

Keep in mind that the database/sql package is a package designed to cover the functionality of all theoretically possible SQL database systems without getting in the way of platform-specifics.

SQL driver instances are injected at runtime through sql.Register. These drivers do not necessarily build on ODBC, it could be something else.

Some SQL-databases support escaping query parameters, some don't – some support "compiling" of prepared statements, others don't.

By making it a 2-step process, the driver can decide where and how to escape query parameters and how they will be inserted into the query.

you are allowed to use the ODBC parameters and send parameters to such things as db.QueryRow() and db.Query()

The methods have this signature for convenience only, in the background the driver still escapes and queries separately. You can see this happen here and here, respectively.

golang sql driver's prepare statement

The differences can be subtle, sometimes important, and sometimes effectively nonexistent.

In general a prepared statement 1. gets prepared with the server (SQL parsed, execution plan generated, etc.), 2. is executed with the additional parameters, and then 3. is closed. It lets you reuse the same SQL with different parameters passed in each time, it can help guard against SQL injection, may provide some performance enhancements (driver/protocol specific, YMMV) and prevent repeated steps, as in execution plan generation and SQL parsing in the prepare step above.

For someone writing source code a prepared statement may be more convenient than concatenating strings and sending those to the DB server.

The DB.Query() method takes SQL as a string, and zero or more arguments (as does Exec(), or QueryRow()). A SQL string with no additional arguments will query exactly what you wrote. However, provided a SQL string with placeholders and additional arguments, a prepared statement is being done for you under the hood.

The DB.Prepare() method explicitly performs a prepared statement, which you then pass arguments to, as in: stmt.Exec(...args).

There are a couple of things worth thinking about, in terms of the differences between the two and why to use one or the other.

You can use DB.Query() without arguments. This can be very efficient since it can bypass the prepare --> execute --> close sequence that the prepared statement necessarily goes through.

You can also use it with additional arguments, and placeholders in the query string, and it will execute a prepared statement under the covers as I mentioned above. The potential problem here is that when you are making a number of queries, each is resulting in an under-the-hood prepared statement. Since there are extra steps involved this can be rather inefficient as it re-prepares, executes and closes each time you do that query.

With an explicit prepared statement you can possibly avoid that inefficiency as you are attempting to reuse the SQL that you earlier prepared, with potentially different arguments.

But that doesn't always work out as you might expect... Because of the underlying connection pool that is managed by db/sql, your "database connection" is quite virtual. The DB.Prepare() method will prepare the statement against a particular connection and then try to get that same connection back when it is time to execute, but if that connection is unavailable it will simply grab one that is available and re-prepare and execute against that. If you're using that same prepared statement over and over again then you might, unknowingly, also be preparing it over and over again. This obviously mostly comes to light when you're dealing with heavy traffic.

So obviously which you for what circumstance use depends on your specific use case, but I hope the details above help clarify for you enough that you can make the best decision in each case.

Update

Given the update in OP there is essentially no difference when the query only needs to be performed once, as queries with arguments are done as prepared statements behind the scenes.

Use the direct methods, e.g. DB.Query() and its analogs, vs. explicitly using prepared statements, as it will result in somewhat simpler source code.

Since prepared statements, in this case, are being utilized for security reasons, it may be worth the effort to handle the security concerns by other means and use plaintext queries instead, as it will improve performance. Any gains, however, may be irrelevant unless there is sufficient traffic (or the traffic is predicted to grow considerably in the future) to necessitate lightening the load on the server. Again it comes down to the real-world use case.

For anyone interested in some metrics on the difference between prepared statements and direct plaintext queries, there is a good article here (which also does an excellent job of explaining much of the above).

golang sql/database prepared statement in transaction

a defer statement is a good way to make sure something runs no matter how you exit the function.

In this particular case, it seems to not matter, since all the error handlers use log.Fatal. If you replace the log.Fatals with return statements, and remove the defers, you now have to cleanup in many places:

tx, err := db.Begin()
if err != nil {
    return nil,err
}
stmt, err := tx.Prepare("INSERT INTO foo VALUES (?)")
if err != nil {
    tx.Rollback()
    return nil,err
}
defer 
for i := 0; i < 10; i++ {
    _, err = stmt.Exec(i)
    if err != nil {
        tx.Rollback()
        return nil,err
    }
}
err = tx.Commit()
if err != nil {
    stmt.Close()
    tx.Rollback()
    return nil,err
}
stmt.Close()
return someValue, nil

If you use defer, it is harder to forget one place you need to clean something up.

Raw sql transactions with golang prepared statements

Yes Go has a great implementation of sql transactions. We start the transaction with db.Begin and we can end it with tx.Commit if everything goes good or with tx.Rollback in case of error.

type Tx struct { }
Tx is an in-progress database transaction.
A transaction must end with a call to Commit or Rollback.
After a call to Commit or Rollback, all operations on the transaction fail with ErrTxDone.
The statements prepared for a transaction by calling the transaction's Prepare or Stmt methods are closed by the call to Commit or Rollback.

Also note that we prepare queries with the transaction variable tx.Prepare(...)

Your function may looks like this:

func doubleInsert(db *sql.DB) error {

    tx, err := db.Begin()
    if err != nil {
        return err
    }

    {
        stmt, err := tx.Prepare(`INSERT INTO table_1 (thing_1, whatever)
                     VALUES($1,$2);`)
        if err != nil {
            tx.Rollback()
            return err
        }
        defer stmt.Close()

        if _, err := stmt.Exec(thing_1, whatever); err != nil {
            tx.Rollback() // return an error too, we may want to wrap them
            return err
        }
    }

    {
        stmt, err := tx.Prepare(`INSERT INTO table_2 (thing_2, whatever)
                     VALUES($1, $2);`)
        if err != nil {
            tx.Rollback()
            return err
        }
        defer stmt.Close()

        if _, err := stmt.Exec(thing_2, whatever); err != nil {
            tx.Rollback() // return an error too, we may want to wrap them
            return err
        }
    }

    return tx.Commit()
}

I have a full example here

Cross-database prepared statement binding (like and where in) in Golang

What is the cross-database way to bind arguments?

With database/sql, there is none. Each database has its own way to represent parameter placeholders. The Go database/sql package does not provide any normalization facility for the prepared statements. Prepared statement texts are just passed to the underlying driver, and the driver typically just sends them unmodified to the database server (or library for embedded databases).

How to bind arguments for LIKE-statement correctly?

You can use parameter placeholders after a like statement and bind it as a string. For instance, you could write a prepared statement as:

SELECT a from bla WHERE b LIKE ?

Here is an example (error management handling omitted).

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
)

// > select * from bla ;
// +------+------+
// | a    | b    |
// +------+------+
// | toto | titi |
// | bobo | bibi |
// +------+------+

func main() {

    // Open connection
    db, err := sql.Open("mysql", "root:XXXXXXX@/test")
    if err != nil {
         panic(err.Error())  // proper error handling instead of panic in your app
    }
    defer db.Close()

    // Prepare statement for reading data
    stmtOut, err := db.Prepare("SELECT a FROM bla WHERE b LIKE ?")
    if err != nil {
        panic(err.Error()) // proper error handling instead of panic in your app
    }
    defer stmtOut.Close()

    var a string
    b := "bi%"    // LIKE 'bi%'
    err = stmtOut.QueryRow(b).Scan(&a)
    if err != nil {
        panic(err.Error()) // proper error handling instead of panic in your app
    }
    fmt.Printf("a = %s\n", a)
}

Note that the % character is part of the bound string, not of the query text.

How to bind arguments for IN statement correctly?

None of the databases I know allows binding a list of parameters directly with a IN clause. This is not a limitation of database/sql or the drivers, but this is simply not supported by most database servers.

You have several ways to work the problem around:

you can build a query with a fixed number of placeholders in the IN clause. Only bind the parameters you are provided with, and complete the other placeholders by the NULL value. If you have more values than the fixed number you have chosen, just execute the query several times. This is not extremely elegant, but it can be effective.
you can build multiple queries with various number of placeholders. One query for IN ( ? ), a second query for IN (?, ?), a third for IN (?,?,?), etc ... Keep those prepared queries in a statement cache, and choose the right one at runtime depending on the number of input parameters. Note that it takes memory, and generally the maximum number of prepared statements is limited, so it cannot be used when the number of parameters is high.
if the number of input parameters is high, insert them in a temporary table, and replace the query with the IN clause by a join with the temporary table. It is effective if you manage to perform the insertion in the temporary table in one roundtrip. With Go and database/sql, it is not convenient because there is no way to batch queries.

Each of these solutions has drawbacks. None of them is perfect.

Why Even Use *Db.Exec() or Prepared Statements in Golang