When you are querying for SQL records, I find that you generally fall into one of three use cases:
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.
Some setup required...
This is part of the series Using PostgreSQL with Go. In this series we cover everything necessary to install and interact with a Postgres database, starting with installation and then we work through using raw SQL, Go’s database/sql
standard library, and finally we explore some third party options.
The first section in this article assumes you have a single table named users
in a Postgres database along with a few records to query. It also assumes that you have a valid connection to your database in your code. You can either setup the DB on your own (there is an SQL snippet below to help), or follow the previous articles in the series to set your database up.
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');
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)
}
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.
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.
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:
database/sql
package (not yet published)This article is part of the series, Using PostgreSQL with Go.
Sign up for my mailing list and I'll send you a FREE sample from my course - Web Development with Go. The sample includes 19 screencasts and the first few chapters from the book.
You will also receive emails from me about Go coding techniques, upcoming courses (including FREE ones), and course discounts.
Jon Calhoun is a full stack web developer who teaches about Go, web development, algorithms, and anything programming. If you haven't already, you should totally check out his Go courses.
Previously, Jon worked at several statups including co-founding EasyPost, a shipping API used by several fortune 500 companies. Prior to that Jon worked at Google, competed at world finals in programming competitions, and has been programming since he was a child.
More in this series
This post is part of the series, Using PostgreSQL with Go.
Spread the word
Did you find this page helpful? Let others know about it!
Sharing helps me continue to create both free and premium Go resources.
Want to discuss the article?
See something that is wrong, think this article could be improved, or just want to say thanks? I'd love to hear what you have to say!
You can reach me via email or via twitter.
©2018 Jonathan Calhoun. All rights reserved.