Common Pitfalls When Using database/sql in Go
We’re huge fans of the Go language and its database access library, database/sql. As you’ve probably seen firsthand, the surface area of database/sql is pretty small, but there’s a lot you can do with it. That includes plenty of risk for error and deceptive mistakes. This blog post is dedicated to some of the past mistakes we’ve made, in hopes that you won’t also make them when the time comes.
The following list is excerpted from the eBook, The Ultimate Guide to Building Database-Driven Apps With Go.
- Deferring inside a loop. A long-lived function with a query inside a loop, and defer
rows.Close()inside the loop, will cause both memory and connection usage to grow without bounds.
- Opening many
dbobjects. Make a global
sql.DB, and don’t open a new one for, say, every incoming HTTP request your API server should respond to. Otherwise you’ll be opening and closing lots of TCP connections to the database. It’s a lot of latency, load, and TCP connections in
- Not doing
rows.Close()when done. Forgetting to close the rows variable means leaking connections. Combined with growing load on the server, this likely means running into max_connections errors or similar. Run
rows.Close()as soon as you can, even if it’ll later be run again (it’s harmless). Chain
.Scan()together for the same reason.
- Prepared statement bloat. If code will be run at high concurrency, consider whether prepared statements are the right solution, since they are likely to be reprepared multiple times on different connections when connections are busy.
- Cluttering the code with strconv or casts. Scan into a variable of the type you want, and let
.Scan()convert behind the scenes for you.
- Cluttering the code with error-handling and retry. Let
database/sqlhandle connection pooling, reconnecting, and retry logic for you.
- Forgetting to check errors after
rows.Next(). Don’t forget, the
rows.Next()loop can exit abnormally.
db.Query()for non-SELECT queries. Don’t tell Go you want to iterate over a result set if there won’t be one, or you’ll leak connections.
- Assuming subsequent statements use the same connection. Run two statements one after another and they’re likely to run on two different connections. Run
LOCK TABLES tbl1 WRITEfollowed by
SELECT * FROM tbl1and you’re likely to block and wait. If you need a guarantee of a single statement being used, you need to use a
- Accessing the db while working with a tx. A
sql.Txis bound to a transaction, but the db is not, so access to it will not participate in the transaction.
- Being surprised by a
NULL. You can’t scan a
NULLinto a variable unless it is one of the
NullXXXtypes provided by the database/sql package (or one of your own making, or provided by the driver). Examine your schema carefully, because if a column can be
NULL, someday it will be, and what works in testing might blow up in production.