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.

This is part of the series Using PostgreSQL with Golang
. 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.

If you would like to receive email notifications when new articles are published in this series, pleasesign up for my mailing list!

Querying for a single record

Note: This section 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 (seeearlier posts in this series if you need help with this any of this).

You can use the following SQL 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, '[email protected]', 'Jonathan', 'Calhoun');  
INSERT INTO users (age, email, first_name, last_name)  
VALUES (52, '[email protected]', 'Bob', 'Smith');  
INSERT INTO users (age, email, first_name, last_name)  
VALUES (15, '[email protected]', '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 only 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

Did you enjoy this article?Sign up for my mailing listand I will send you an email when I publish new articles like it – typically one email per week.

I will also send you the first three chapters from my book, Web Development with Go, and the occasional email update about other things I am working on.

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:

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

This post is part of a series called Using PostgreSQL with Go
, and it covers everything from installing Postgres, using raw SQL, and finally using PostgreSQL with Go. Not familiar with something discussed in this article or just want to refresh your memory? Check out the rest of the series!

稿源:Calhoun.io (源链) | 关于 | 阅读提示

本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 综合编程 » Querying for a single record using Go’s database/sql package

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录