Common Pitfalls When Using database/sql in Go

The following list is excerpted from the eBook, The Ultimate Guide to Building Database-Driven Apps With Go.

Common Pitfalls

• 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 TIME_WAIT status.
• 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 db.QueryRow() and .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/sql handle 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.
• Using 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 WRITE followed by SELECT * FROM tbl1 and you’re likely to block and wait. If you need a guarantee of a single statement being used, you need to use a sql.Tx.
• Accessing the db while working with a tx. A sql.Tx is 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 NULL into a variable unless it is one of the NullXXX types 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.

Updated 3/23/2017