Query vs Exec vs Prepare in Go

Golang with SQL

Go comes with a database/sql library to talk to any relational database. In a bid to abstract some of the low level complexities in connecting to the db and managing the connection pool, the exposed APIs seem to do a bit more than you actually expect them to do. This eventually leads to some confusion around how to use these APIs.

Hence, I’ve tried to analyze the behavior of the major APIs that one must be aware of before using them.

I did my experiments with my go application running on a virtual box (ubuntu) and a mysql server on my host machine.

Query tried: insert into items (name, price, description) values (‘brownie’,240,’sizzling’)

Query

We should always use db.Query whenever we want to do a select and we should never ignore the returned rows of Query but iterate over it (else we’ll leak the db connection!)

Doing Query(query) will not use a prepared statement (see the wireshark capture below)

• Notice that only 1 TCP request was sent from client to server(minus login)
• Connection will be released automatically to the pool when the returned rows are iterated, or we can call rows.Close() explicitly when we are done.
• Usage — db.Query(“insert into items (name, price, description) values(‘brownie’, 240, ‘sizzling’)”)

wireshark capture of Query(query)

Doing Query(queryTemplate, params) will use a prepared statement under the covers.

• Notice that 3 TCP requests were sent from client to server(minus login)
• Connection will be released automatically to the pool when the returned rows are iterated, or we can call rows.Close() explicitly when we are done.
• Usage — db.Query(“insert into items (name, price, description) values(?,?,?)”, “brownie”, 240, “sizzling”)

wireshark capture of Query(query, params)

Exec

We should always use db.Exec whenever we want to do an insert or update or delete.

Doing Exec(query) will not use a prepared statement, so lesser TCP calls to the SQL server

• Notice that only 1 TCP request was sent from client to server(minus login)
• Releases the connection automatically to the pool.
• Usage — db.Exec(“insert into items (name, price, description) values(‘brownie’, 240, ‘sizzling’)”)

wireshark capture of Exec(query)

Doing Exec(queryTemplate, params) will use prepared statement under the covers, so more number of TCP calls to the SQL server.

• Notice that 3 TCP requests were sent from client to server(minus login)
• Releases the connection automatically to the pool.
• Usage — db.Exec(“insert into items (name, price, description) values(?,?,?)”, “brownie”, 240, “sizzling”)

wireshark capture of Exec(query, params)

Prepare

This should be used only when we want to prepare once at the start of the program and execute N number of times during the course of the program.

• Notice that 2 TCP requests were sent from client to server(minus login).
• We need to close the statement explicitly when we don’t need the prepared statement anymore. Else, we’ll fail to free up allocated resources both on the client as well as server!
• Usage — stmt.Exec(“insert into items (name, price, description) values(?,?,?)”, “brownie”, 240, “sizzling”)

wireshark capture of Prepare(query)

Conclusion

One of the major confusions we ran into, while using this sql package for the first time was, we didn’t really know that it was creating prepared statements underneath even when we did not not explicitly instruct to do so.

Hopefully the points above clarify, when are prepared statements invoked and how we can avoid them.