layout | title |
---|---|
article |
Surprises, Antipatterns and Limitations |
Although database/sql
is simple once you're accustomed to it, you might be
surprised by the subtlety of use cases it supports. This is common to Go's core
libraries.
As mentioned throughout this site, if you don't use database/sql
as intended,
you can certainly cause trouble for yourself, usually by consuming some
resources or preventing them from being reused effectively:
- Opening and closing databases can cause exhaustion of resources.
- Failing to read all rows or use
rows.Close()
reserves connections from the pool. - Using
Query()
for a statement that doesn't return rows will reserve a connection from the pool. - Failing to be aware of how prepared statements work can lead to a lot of extra database activity.
Here's a surprising error. You can't pass big unsigned integers as parameters to statements if their high bit is set:
_, err := db.Exec("INSERT INTO users(id) VALUES", math.MaxUint64) // Error
This will throw an error. Be careful if you use uint64
values, as they may
start out small and work without error, but increment over time and start
throwing errors.
Some things can change connection state, and that can cause problems for two reasons:
- Some connection state, such as whether you're in a transaction, should be handled through the Go types instead.
- You might be assuming that your queries run on a single connection when they don't.
For example, setting the current database with a USE
statement is a typical
thing for many people to do. But in Go, it will affect only the connection that
you run it in. Unless you are in a transaction, other statements that you think
are executed on that connection may actually run on different connections gotten
from the pool, so they won't see the effects of such changes.
Additionally, after you've changed the connection, it'll return to the pool and potentially pollute the state for some other code. This is one of the reasons why you should never issue BEGIN or COMMIT statements as SQL commands directly, too.
The database/sql
API provides an abstraction of a row-oriented database, but
specific databases and drivers can differ in behavior and/or syntax, such as
prepared statement placeholders.
The Go driver doesn't support multiple result sets from a single query in any way, and there doesn't seem to be any plan to do that, although there is a feature request for supporting bulk operations such as bulk copy.
This means, among other things, that a stored procedure that returns multiple result sets will not work correctly.
Invoking stored procedures is driver-specific, but in the MySQL driver it can't be done at present. It might seem that you'd be able to call a simple procedure that returns a single result set, by executing something like this:
err := db.QueryRow("CALL mydb.myprocedure").Scan(&result) // Error
In fact, this won't work. You'll get the following error: Error 1312: PROCEDURE mydb.myprocedure can't return a result set in the given context. This is because MySQL expects the connection to be set into multi-statement mode, even for a single result, and the driver doesn't currently do that (though see this issue).
The database/sql
doesn't explicitly have multiple statement support, which means
that the behavior of this is backend dependent:
_, err := db.Exec("DELETE FROM tbl1; DELETE FROM tbl2") // Error/unpredictable result
The server is allowed to interpret this however it wants, which can include returning an error, executing only the first statement, or executing both.
Similarly, there is no way to batch statements in a transaction. Each statement in a transaction must be executed serially, and the resources in the results, such as a Row or Rows, must be scanned or closed so the underlying connection is free for the next statement to use. This differs from the usual behavior when you're not working with a transaction. In that scenario, it is perfectly possible to execute a query, loop over the rows, and within the loop make a query to the database (which will happen on a new connection):
rows, err := db.Query("select * from tbl1") // Uses connection 1 for rows.Next() { err = rows.Scan(&myvariable) // The following line will NOT use connection 1, which is already in-use db.Query("select * from tbl2 where id = ?", myvariable) }
But transactions are bound to just one connection, so this isn't possible with a transaction:
tx, err := db.Begin() rows, err := tx.Query("select * from tbl1") // Uses tx's connection for rows.Next() { err = rows.Scan(&myvariable) // ERROR! tx's connection is already busy! tx.Query("select * from tbl2 where id = ?", myvariable) }
Go doesn't stop you from trying, though. For that reason, you may wind up with a corrupted connection if you attempt to perform another statement before the first has released its resources and cleaned up after itself. This also means that each statement in a transaction results in a separate set of network round-trips to the database.
Previous: The Connection Pool Next: Related Reading and Resources