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.

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!

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 (seeearlier 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, '[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');

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

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 final way to query records with the database/sql
package:

  • 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 multiple records with Go’s sql package

喜欢 (0)or分享给?

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

使用声明 | 英豪名录

登录

忘记密码 ?

切换登录

注册