How to Build a Web App with Go and SQLite

Building a Web App with Go and SQLite Part 3

In part three of our series, we will get a single person from the database and display it with our API.

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:

How to build a web app with SQLite and Go

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:


How much do you know about Go? Find out your Go SkillIQ here! Take a free skill assessment.



Related tags:

programming   golang   sqlite   tutorials   gin  
About the author

Jeremy Morgan is a tech blogger, speaker and author. He has been a developer for nearly two decades and has worked with a variety of companies from the Fortune 100 to shoestring startups.

Jeremy loves to teach and learn, writing here on and on his Tech Blog as well as building Pluralsight Courses.

10-day free trial

Sign Up Now