Author: Jeremy Morgan
Part two of a five part series on building a web application with Go. Get the Project source code here
Part 2: Connect to our Database
In part one of this tutorial, we created a web API with Gin and verified it worked. Now we’re going to connect to the SQLite database with Go.
We will be using the built-in database/sql interface and the go-sqlite3 library for this project.
So let’s jump right in.
Step 5: Connect to a SQLiteDatabase
In our previous article how to use SQLite with Go we created a simple database that stores a person:
We will download that database from here and put it in our project folder.
Next, let’s create a folder named models and create a new Go file named person.go.
In person.go we’ll want to add the following:
package models
import (
"database/sql"
_ "github.com/mattn/go-sqlite3"
)
This creates a new file where our database operations take place. It will handle connecting to and running operations against our file.
The first step is connecting to the database. Below the imports, add the following global variable:
var DB *sql.DB
This is where we’re going to persist our connection. Let’s create a function to connect to our SQLite database:
func ConnectDatabase() error {
db, err := sql.Open("sqlite3", "./names.db")
if err != nil {
return err
}
DB = db
return nil
}
This function takes no input parameters and creates a database connection with sql.Open.
The Open method comes from the database/sql package, however, there are no database drivers included in the standard library, so you must import one. We imported the go-sqlite3 library for our driver. A complete list of third party providers can be found here.
An important note: the sqlite3 driver returns a *DB. We’re storing it in the DB variable globally to use it without reconnecting. However, behind the scenes, the database/sql library maintains a connection pool. It’s safe for concurrent use, and you usually don’t need to close the connection.
This will establish our database connection.
Step 6: Creating our Model
In this project, the model is super simple. So we’ll create a struct that matches our table:
type Person struct {
Id int `json:"id"`
FirstName string `json:"first_name"`
LastName string `json:"last_name"`
Email string `json:"email"`
IpAddress string `json:"ip_address"`
}
This is what we’ll use to pass our records from the database to the web and vice versa. Notice we are decorating these fields with JSON names. We can specify exactly what we want the JSON names to look like.
Step 7: Get a list of Persons (getPersons)
func GetPersons(count int) ([]Person, error) {
rows, err := DB.Query("SELECT id, first_name, last_name, email, ip_address from people LIMIT " + strconv.Itoa(count))
if err != nil {
return nil, err
}
defer rows.Close()
people := make([]Person, 0)
for rows.Next() {
singlePerson := Person{}
err = rows.Scan(&singlePerson.Id, &singlePerson.FirstName, &singlePerson.LastName, &singlePerson.Email, &singlePerson.IpAddress)
if err != nil {
return nil, err
}
people = append(people, singlePerson)
}
err = rows.Err()
if err != nil {
return nil, err
}
return people, err
}
OK, so this is a lot of code here. Let’s break it down.
We declare the function GetPersons:
func GetPersons(count int) ([]Person, error) {
We will pass in an int that is a count of how many persons we want to return. For now, this is a pretty simple query, but if we pass in a count of 10, the first ten records will return.
For the return in the function, we have a slice []Person, and an error.
So anytime something fails within this function, we’ll return nil for the slice and the error. We’ll then read it later.
rows, err := DB.Query("SELECT id, first_name, last_name, email, ip_address from people LIMIT " + strconv.Itoa(count))
if err != nil {
return nil, err
}
defer rows.Close()
Here we are running a DB.Query and it’s s simple select statement with a LIMIT appended to it. We will select each field we want (don’t ever use select *) and limit the amount returned.
We check for an error on the query and defer the Close() method for the rows to ensure it doesn’t stay open after we’re done.
next, we will create a new slice, people:
people := make([]Person, 0)
Then we’ll iterate through the results:
for rows.Next() {
We will create an instance of the Person struct:
singlePerson := Person{}
Then we’ll use rows.Scan to scan the values returned in the row and bind them to the singlePerson object we just created.
err = rows.Scan(&singlePerson.Id, &singlePerson.FirstName, &singlePerson.LastName, &singlePerson.Email, &singlePerson.IpAddress)
if err != nil {
return nil, err
}
We do another quick check for a Scan error.
Then we append our new singlePerson object into the slice of people to be returned:
people = append(people, singlePerson)
And that’s it! Now we can extract data from the database. This concludes our work in person.go for now, so let’s go back to main.go so we can put this model to work.
first, add the following import:
"PersonWeb/models"
This is what I named my project. Yours may be different. But make sure you add your /models folder into your imports.
Also, to save space, I’ve created the following function:
func checkErr(err error) {
if err != nil {
log.Fatal(err)
}
}
This function checks errors and dumps the application when we have one.
In main.go, look for func getPersons.
func getPersons(c *gin.Context) {
c.JSON(http.StatusOK, gin.H{"message": "getPersons Called"})
}
Let’s change it up. We’re going to use it to call our database and return some pretty JSON:
func getPersons(c *gin.Context) {
persons, err := models.GetPersons(10)
checkErr(err)
if persons == nil {
c.JSON(http.StatusBadRequest, gin.H{"error": "No Records Found"})
return
} else {
c.JSON(http.StatusOK, gin.H{"data": persons})
}
}
Here we make a single call to the function we just created, and we get 10 records from our database:
persons, err := models.GetPersons(10)
Then we check for errors:
checkErr(err)
Finally, we do a check on the results from the database. If it’s nil and no error was thrown, we can assume there weren’t any records.
if persons == nil {
c.JSON(http.StatusBadRequest, gin.H{"error": "No Records Found"})
return
}
Otherwise, we’re good! Let’s send a 200 OK with our data:
else {
c.JSON(http.StatusOK, gin.H{"data": persons})
}
We’re just about to test it. So at the top of func main() { let’s add the connection to the database:
err := models.ConnectDatabase()
checkErr(err)
This will create a database connection that will persist in our application.
So once again, we’ll build it and run it:
go build
./personweb
And now, when we send a GET request to /api/v1/person we get this:
And if you look at your console, you’ll see the requests in realtime:
Awesome! We are working with data now. This method gets a list of the first ten records in the database. But what if we want an individual record?
In Part three we’ll select a single person from the database.
Series: Building a Web App with Go and SQLite:
- Part 1. Create a Gin Web API Service
- Part 2. Connect to our Database (this page)
- Part 3. Selecting a Single Record
- 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.