Author: Jeremy Morgan
Part three of a five part series on building a web application with Go. Get the Project source code here
Part 3: Retreiving a Single Record
In the last part of our series, we made a request and grabbed the first 10 records out of the database. Next, we’re going to learn how to retrieve a single record.
As long as we know the ID of the record, we can retrieve the data in JSON format from our API.
In this step of the tutorial, we are going to:
- Retrieve a single record from the database by its ID.
This is important for bringing up records for editing or inspection.
Step 8: Get a single Person (GetPersonById)
To start, let’s jump back into our model. Open up /models/person.go
We’re going to add a new method. Here’s the full func in its entirety:
func GetPersonById(id string) (Person, error) {
stmt, err := DB.Prepare("SELECT id, first_name, last_name, email, ip_address from people WHERE id = ?")
if err != nil {
return Person{}, err
}
person := Person{}
sqlErr := stmt.QueryRow(id).Scan(&person.Id, &person.FirstName, &person.LastName, &person.Email, &person.IpAddress)
if sqlErr != nil {
if sqlErr == sql.ErrNoRows {
return Person{}, nil
}
return Person{}, sqlErr
}
return person, nil
}
Let’s break this down.
func GetPersonById(id string) (Person, error) {
We declare the GetPersonById func and pass in a string for an Id. Why a string when we know it’s an integer? It’s easier than doing several conversions. You can stop things (fail early) if something other than an integer is passed in, but for simplicity, we’re going to just treat it as a string.
stmt, err := DB.Prepare("SELECT id, first_name, last_name, email, ip_address from people WHERE id = ?")
Next, we prepare a select statement for execution. Why am I doing this here? I want to introduce the idea of preparing statements before execution. You should do this any time you’re accepting external input.
You have no idea what people will try to send your program. To prevent SQL injection attacks, it’s always best to prepare your input so it can fail before any SQL is executed. If you’ve waited for an SQL error, that’s too long.
So this bit of code prepares your select statement and gets it ready for execution. Again we’re being explicit about the fields (don’t do select *), and if you notice at the end we have a FROM clause with a question mark. That’s a placeholder for our id string that we’re passing in.
if err != nil {
return Person{}, err
}
And of course, we check for an error, and if there is an error, we’ll send a blank Person back with the error message.
person := Person{}
Next, we’ll create an instance of the Person struct. This is what we’ll be returning at the end of the function.
sqlErr := stmt.QueryRow(id).Scan(&person.Id, &person.FirstName, &person.LastName, &person.Email, &person.IpAddress)
Now that we’ve prepared this statement, we can execute it with a call to QueryRow.
We pass the id in as a parameter to QueryRow then scan in the row that’s returned to our person instance we created earlier.
if sqlErr != nil {
if sqlErr == sql.ErrNoRows {
return Person{}, nil
}
return Person{}, sqlErr
}
return person, nil
Here we do a bit of error checking. One assumption we’re making here: if the query returns 0 rows, that’s not necessarily an “error” in the conventional sense. So we catch that with sqlErr == sql.ErrNoRows and return a blank Person struct and nil for the error. That way, the calling application knows we found nothing, but it’s not an error.
If we do throw some error however, we will return it:
return Person{}, sqlErr
If neither of these conditions are met, we’ll return an empty Person and empty error. We may never reach this line of code in production, but it’s important to include it for completeness.
That’s all we need to add to /models/person.go so let’s go back to main.go and add the code to call the GetPersonById method.
Step 9: Calling GetPersonById
Now that we have the func complete, we need to call it. We’ll do that in main.go. Add the following method:
func getPersonById(c *gin.Context) {
id := c.Param("id")
person, err := models.GetPersonById(id)
checkErr(err)
// if the name is blank we can assume nothing is found
if person.FirstName == "" {
c.JSON(http.StatusBadRequest, gin.H{"error": "No Records Found"})
return
} else {
c.JSON(http.StatusOK, gin.H{"data": person})
}
}
Again this is the entire finished method.
Let’s go through it.
id := c.Param("id")
Here again, we’re grabbing the id parameter from our URI. If you want, you can convert this to an integer and throw an error if it’s anything but an integer. In a production application, you’ll want to do several checks on this parameter to make sure nothing funny is being passed in. For the purposes of this tutorial, we’ll grab the id from the Gin context.
person, err := models.GetPersonById(id)
checkErr(err)
Next, we make a call to our GetPersonById method we just created and pass that method in. We’ll check for errors, of course.
if person.FirstName == "" {
c.JSON(http.StatusBadRequest, gin.H{"error": "No Records Found"})
return
} else {
c.JSON(http.StatusOK, gin.H{"data": person})
}
Here we’re doing a check to see if the first name is blank. Since nobody in our database has a first name missing, we can assume that we found no records.
This empty result will return to tell the calling application no records were found.
So let’s run it!
Running the Application
So now we have the function, and we’re calling it, let’s run the application:
go build
./personweb
Now, I’ll send a GET and append an ID to the URI: `/api/v1/person/37’, and I get this:
A single record in JSON with the id, first name, last name, email, and IP address. Awesome!!
In part four we’ll build functionality to insert and update records.
Series: Building a Web App with Go and SQLite:
- Part 1. Create a Gin Web API Service
- Part 2. Connect to our Database
- Part 3. Selecting a Single Record (this page)
- Part 4. Inserting and Updating Records
- Part 5. Deleting Records and Sending Options
How much do you know about Go? Find out your Go SkillIQ here! Take a free skill assessment.