Common Pitfalls When Using database/sql in Go

Posted by Alex Slotnick on Mar 23, 2017 12:15:00 PM

Updated 3/23/2017

Here at VividCortex, 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 ourselves, in hopes that you won’t also make them when the time comes.

The following list is excerpted from our ebook, The Ultimate Guide to Building Database-Driven Apps with Go.


Pitfalls Img.jpg
Image Credit


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 db objects. 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 that the rows.Next() loop can exit abnormally.
  • Using db.Query() for non-SELECT queries. Don’t tell Go that you want to iterate over a result set if there won’t be one, or you’ll leak connections.
  • Assuming that 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.

What are some other pitfalls you've encountered when using database/sql in Go? Share them in the comments below.

Subscribe to Email Updates

Posts by Topic

see all