golang sql driver's prepare statement

Question

With regards to golang's sql driver, what is the difference between the two statements below?

// store.DB is *sql.DB type
rows, err := store.DB.Query(SQL, args ...)
// err != nil
defer rows.Close()

and

// store.DB is *sql.DB type
stmt, err := store.DB.Prepare(SQL)
// err != nil
defer stmt.Close()

rows, err := stmt.Query(args ...)
// err != nil
defer rows.Close()

It seems that they are the same? Are there any subtle difference?

Update:

We don't need to perform many stmt.Exec or stmt.Query after db.Prepare, only one exec or query is performed after each prepare. And when we use db.Query or db.Exec, we pass arguments to the methods instead of using raw SQL string (for security consideration).

I have found one reference links: http://go-database-sql.org/prepared.html
It seems that both way are using prepared statement, what's the difference?

Answer 1

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).