Querying for a single record using Go's database/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.
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 first use case - querying for a single record.

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 a single record

When working with SQL, the first thing you are likely going to want to do after creating a record is to query it. It is pretty natural to want to see what you just saved in your database and verify that it is actually there. This type of query will often fall into the use case of only caring about a single record, so we will start there.

To do this, we are going to use the QueryRow() method. This is a method provided by the DB type and is used to execute an SQL query that is expected to return a single row.

Notice that I said it expects a single row - this means that it does not expect to get 0 rows back, and you will receive an ErrNoRows error when this happens (when you call Scan()). This is an important detail to keep in mind because other methods like Query() will not return an error when no rows are returned. Interestingly enough, this will not result in an error if your SQL returns multiple rows, and will instead just use the first result and discard the rest.

When we call QueryRow() it will return a single *Row, which is a fairly simple object from our perspective. It only has one exported method, Scan(), which will attempt to copy the data returned from a query into the provided destinations (the arguments you pass into the method are the destinations). If it succeeds it will return nil, otherwise it will return an error. This is where you will see the ErrNoRows error if no records were returned - it isn’t possible to copy data that isn’t there so it returns an error when this happens.

Let’s take a look at this in action.

sqlStatement := `SELECT id, email FROM users WHERE id=$1;`
var email string
var id int
// Replace 3 with an ID from your database or another random
// value to test the no rows use case.
row := db.QueryRow(sqlStatement, 3)
switch err := row.Scan(&id, &email); err {
case sql.ErrNoRows:
  fmt.Println("No rows were returned!")
case nil:
  fmt.Println(id, email)
default:
  panic(err)
}

Breaking down the sample code

Now let’s take a moment to dissect what is going on in the example code from the last section. In the first line we construct our SQL statement.

sqlStatement := `SELECT id, email FROM users WHERE id=$1;`

We have covered this in other posts in this series, so we won’t dig into this too much, but the short version is that this is going to mostly mimic raw SQL with variables like $1 used to replace any variables we want to insert into our query. In this case we use it to insert the ID of a record we want to query.

After that we declare a few variables. This is pretty standard Go code and you should be familiar with it.

Next we use the QueryRow() method on the db variable (which is of the type sql.DB). Like we have done in the past, we pass the SQL statement as the first argument, and any data we want to provide to use to construct the SQL statement is passed in as additional arguments. After executing the query, QueryRow() will return a pointer to a sql.Row.

Errors will be deferred until you call the Scan() method on the returned Row object, and QueryRow() should never return nil (at least as it is currently written in Go 1.8), so you likely don’t need to check if this nil.

After that we have the following line of code:

switch err := row.Scan(&id, &email); err { ... }

This line is doing two things. First it calls the row.Scan() method, passing a pointer to our id and email variables in as arguments. This is intended to tell Scan() to copy the retrieved data into the memory locations used by those variables, and Scan() will return nil if this is successful, otherwise it returns an error.

The latter half of the line - the err { part - tells our program to use the value of err in the switch statement. This allows us to add cases for each of the scenarios we are likely to encounter.

The first case we check for is the one that occurs when no rows are returned.

case sql.ErrNoRows:
  fmt.Println("No rows were returned!")

I am intentionally pointing this one out because this is the simplest error case to recover gracefully from. In this case we simply print that there were no rows returned, but in your own application you might do something like redirect a user to a 404 page.

In this example we only queried for two attributes (id and email), but you can use * to query for all of them if you want.

The second scenario we cover is when there wasn’t an error at all.

case nil:
  fmt.Println(id, email)

In the sample code we simply print out the retrieved values, but in your code this might be your case where your function continues to execute, or it might return the retrieved data.

The final case we cover is the default case. This will only happen when there is an error (ie it isn’t nil), and that error is not the ErrNowRows error.

default:
  panic(err)

In this sample snippet we panic, but in your application you likely want to gracefully handle the error. This might simply mean sending the user to a 500 page, but more often than not this is an indication that something is either wrong with your database, or you have a poorly constructed SQL query.

We can also query for more data

In the original example we only queried for a few columns, but you often want to retrieve an entire record. This is also possible, and is shown below using a User type that matches our users table.

type User struct {
  ID        int
  Age       int
  FirstName string
  LastName  string
  Email     string
}

sqlStatement := `SELECT * FROM users WHERE id=$1;`
var user User
row := db.QueryRow(sqlStatement, 3)
err := row.Scan(&user.ID, &user.Age, &user.FirstName,
  &user.LastName, &user.Email)
switch err {
case sql.ErrNoRows:
  fmt.Println("No rows were returned!")
  return
case nil:
  fmt.Println(user)
default:
  panic(err)
}

This time when we call Scan() we pass in pointers to the fields of the User object so that they can be filled in with the user record retrieved from our database. While this might seem like a lot of code to write at first, the added benefit is that we can explicitly decide what data gets mapped to each field in our User type.

In future posts in this series we will discuss how some third party libraries (like sqlx and SQLBoiler) use struct tags to simplify this, while others (like GORM) use the struct itself to define what the database should look like.

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 remaining two ways to query records with the database/sql package:

• Querying for multiple records with Go's sql package
• Querying with multiple results sets using Go’s database/sql package (not yet published)