Querying for multiple records with Go's sql package

When you are querying for SQL records, I find that you generally fall into one of three use cases:

1. You want to retrieve a single record. For example, you might want to look up a specific user. (see this article)
2. You want to retrieve many records from the same table (or through joins). Eg you might want all of the comments created by a specific user.
3. You are interested in multiple result sets. This use case is a little rarer, but it generally pops up when you want to use some intermediary data between queries. One example of this might be if you wanted to create a temporary table of users with a certain attribute, and then query for many pieces of information about that user.

Up until recently, only the first two use cases were supported by Go’s database/sql package. To achieve the last you would need to pass data back and forth between queries (or construct different SQL queries). In Go 1.8 support for multiple result sets was added, making this much simpler.

In this article we are going to cover the second use case - querying for a multiple records.

Required data for this article

This article assumes you have a single table named users in your Postgres database along with a few records to query. It also assumes that you have a valid connection to your database in your code (see earlier posts in this series if you need help with this any of this).

You can use the following SQL code to create a table and insert a few records if you haven’t already.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  age INT,
  first_name TEXT,
  last_name TEXT,
  email TEXT UNIQUE NOT NULL
);
INSERT INTO users (age, email, first_name, last_name)
VALUES (30, 'jon@calhoun.io', 'Jonathan', 'Calhoun');
INSERT INTO users (age, email, first_name, last_name)
VALUES (52, 'bob@smith.io', 'Bob', 'Smith');
INSERT INTO users (age, email, first_name, last_name)
VALUES (15, 'jerryjr123@gmail.com', 'Jerry', 'Seinfeld');

Querying for many records

In the last article we discussed how to use the QueryRow() method to query for a single row. Most of what we are going to cover here is very similar, but rather than using the QueryRow() method we will be using the Query() method.

Let’s jump right into an example and then explain what is going on.

rows, err := db.Query("SELECT id, first_name FROM users LIMIT $1", 3)
  if err != nil {
    // handle this error better than this
    panic(err)
  }
  defer rows.Close()
  for rows.Next() {
    var id int
    var firstName string
    err = rows.Scan(&id, &firstName)
    if err != nil {
      // handle this error
      panic(err)
    }
    fmt.Println(id, firstName)
  }
  // get any error encountered during iteration
  err = rows.Err()
  if err != nil {
    panic(err)
  }

Just like QueryRow(), this allows us to pass in arguments and avoid any sort of SQL injection. This is what we are doing on the first line with the $1 part of the SQL statement, and then the 3 argument passed into the Query() method.

One of the major differences between QueryRow() and Query() is how errors are handled. With QueryRow(), error handling is deferred until you call Scan(), and an error is returned when 0 records are found. Query() behaves very differently from this.

For starters, it won’t return an error when no records are found. You can actually test this with the code above by changing the LIMIT to 0. All that happens is our for loop never actually runs.

The next thing to notice is that calling Query() returns both a pointer to Rows, along with an error. This means that you could encounter an error before you even start to iterate over the results, so you need to check for one first.

Assuming there were no errors, our *Rows object shouldn’t be nil and we can go ahead and defer a call to close the rows. We do this by calling defer rows.Close().

In most cases you won’t actually need to manually close the rows object because this happens when the Next() method is called and there aren’t anymore result sets left, but in the case of an error you may need to manually call Close(). It is also an idempotent method, meaning that you can call it multiple times without any negative side effects, so I suggest calling it via defer as long as Rows isn’t nil.

After that we enter the for loop where we iterate over each record returned by our SQL statement. We do this by calling rows.Next(), which returns true when the next row is successfully prepared, and false otherwise.

Generally speaking, a false return value from rows.Next() means that there are no more records, but this isn’t always the case. We will see in the next article in this series how multiple result sets can affect this, and it is also possible to get a false return value when there is an error preparing the next row. That is why you see the call to rows.Err() near the end of the sample - this is our way of verifying that there wasn’t an error when calling rows.Next().

I suspect the code was designed this way to make writing for loops simpler, but the downside to this is that it is very easy to forget to check for errors, so don’t forget to call rows.Err() and check for errors!.

While in the rows.Next() loop you can use the rows variable pretty similar to how you would a single Row. You call Scan() on each individual row and pass in destinations for the data. The method call will return an error if there is one, and nil otherwise. The one exception is that you won’t ever receive the ErrNoRows error in this case, but you might receive an error complaining about Scan() being called before Next() was called.

In addition to the Scan() method, Rows also has a few other helper methods such as:

• rows.Columns() - This method is used to retrieve the names of each column returned by the SQL query. This is useful if you are writing a library like sqlx and you want to map struct tags to columns in the database.
• rows.ColumnTypes() - This method is used to retrieve information about each column, such as the type, length, or whether it is nullable. I haven’t used it much myself, so I’m not sure how well supported it is with Postgres.

Finally we can print out our data (or do whatever else you need with it) and move along with our program. Just be sure to call rows.Err() and check for any errors!

Common errors

The most common error you will likely encounter at this point is something like:

sql: expected 2 destination arguments in Scan, not 1

This means that you are passing the incorrect number of arguments into the Scan() method. You need to pass in enough arguments for all of the columns retrieved by your SQL statement. If you are unsure of how many this is, I suggest calling the rows.Columns method which will return a slice of column names that are being returned by your SQL statement.

Summary

Much like the rest of the articles discussing using Go’s database/sql package, this article didn’t really have that much Go code in it. Instead, the database/sql package is designed to help you write safe SQL queries and then get out of your way. The upside to this is that if you already know SQL you don’t need to learn too much to quickly become productive.

The downside to this approach is that you may sometimes find yourself writing a bit more code, like passing in pointers to every field in a User object to the Scan() method. In future posts in this series we will discuss how to combat some of this repetitiveness using third party libraries like sqlx, SQLBoiler, and GORM, but first we need to cover the final way to query records with the database/sql package - querying with multiple result sets (not yet published).