Building an Application With Go and SQLite

Today we're going to learn how to use SQLite with Go. By the time you finish this tutorial, you can easily build CRUD (Create Read Update Delete) applications with Go.

Go is one of the hottest languages in development right now, known for its simple structure and blazing performance. SQLite has been trending among developers for its ability to host small data very quickly, safely, and contained in a single file.

Why SQLite?

• Great Performance
• Tiny footprint
• Self-contained (file-based)

In fact, you're likely already using SQLite every day and you don't know it. It's ubiquitous in mobile phones and devices, and SQLite powers many websites today.

We're going to combine these technologies to show you how easy it is to marry the two.

What This Tutorial Covers

We'll build a CLI (command-line interface) application with Go and SQLite. It will cover the following with SQLite:

• Creating
• Reading
• Updating
• Deleting

You can follow along and build this application or use this as a reference to build something of your own. I'll start with a pre-populated database you can download here. It has a database full of people with their first name, last name, email address, and IP address.

We'll start by creating a menu, then building out methods for each operation, and in the end, you'll have an application. So let's get started!

Creating the Menu System

We will build a CLI application that accesses an SQLite database. I'm going to use The WMenu package from Dixonwille for this, so we have an interface for our data. We'll use this menu and a scanner to accept input to interact with our database.

We won't focus too heavily on the menu system itself because the intent of this article is to show you how to utilize SQLite.

First, we'll create a Go module and work with main.go to start.

Install the WMenu package:

go get github.com/dixonwille/wmenu

Let's create a main.go that looks like this:

package main

import (
    "log"
    "github.com/dixonwille/wmenu/v5"
)

func main() {

    menu := wmenu.NewMenu("What would you like to do?")

    menu.Action(func(opts []wmenu.Opt) error { handleFunc(opts); return nil })

    menu.Option("Add a new Person", 0, true, nil)
    menu.Option("Find a Person", 1, false, nil)
    menu.Option("Update a Person's information", 2, false, nil)
    menu.Option("Delete a person by ID", 3, false, nil)
    menuerr := menu.Run()

    if menuerr != nil {
        log.Fatal(menuerr)
    }
}

We will create a menu with the following options:

• Add a new Person
• Find a Person
• Update a Person's information
• Delete a person by ID

Notice we are calling a function named handleFunc(opts) to handle which option is chosen:

menu.Action(func(opts []wmenu.Opt) error { handleFunc(opts); return nil })

So let's create that function and have it display some values for now:

func handleFunc(opts []wmenu.Opt) {

    switch opts[0].Value {

    case 0:
        fmt.Println("Adding a new Person")
    case 1:
        fmt.Println("Finding a Person")
    case 2:
        fmt.Println("Update a Person's information")
    case 3:
        fmt.Println("Deleting a person by ID")
    case 4:
        fmt.Println("Quitting application")
    }
}

When we run the application, you should see a menu that looks like this:

When you make a selection, it should display what you selected:

This is the menu we will use to interact with our SQLite Go code. I will break it up into CRUD parts, and we'll add to it as we go along.

Let's dive in. We'll start with a quick look at the database.

The SQLite Database

The Schema is pretty simple. We are storing "people" with the following attributes:

The create statement is here:

CREATE TABLE "people" (
    "id"    INTEGER,
    "first_name"    TEXT,
    "last_name"    TEXT,
    "email"    TEXT,
    "ip_address"    TEXT,
    PRIMARY KEY("id" AUTOINCREMENT)
)

I've populated it with fake data from Mockaroo, my favorite test data generator. We will access this data, and add to it without our Go CLI application.

In the spirit of CRUD, we'll build out our application in that order, C*reate, *Read, U*pdate, *Delete.

Connecting to the Database

We'll need to add some additional code to connect to the database. We'll use an object to create a connection, and we can pass it to other parts of the program.

First, to catch errors in our code, let's create a simple function named checkErr that looks like this:

func checkErr(err error) {
    if err != nil {
        log.Fatal(err)
    }
}

Next, let's add the go-sqlite3 library from mattn to connect with SQLite.

go get github.com/mattn/go-sqlite3

This is by far the best SQLite library for Go that I've used. It's easy, fast, and well-documented.

Then, we'll create a connection to our database, in the main() func:

// Connect to database
db, err := sql.Open("sqlite3", "./names.db")
checkErr(err)
// defer close
defer db.Close()

What this does is create a db object. The sql.Open method opens up names.db as an SQLite 3 database. It's a simple step. Then we defer the close of the database.

Now let's refactor our menu code so we can pass this object into our menu actions.

Change this:

menu.Action(func(opts []wmenu.Opt) error { handleFunc(opts); return nil })

to this:

menu.Action(func(opts []wmenu.Opt) error { handleFunc(db, opts); return nil })

Then add the parameter to our function below:

func handleFunc(opts []wmenu.Opt) {

to this:

func handleFunc(db *sql.DB, opts []wmenu.Opt) {

Now we can pass this database connection and use it with our options.

Creating a Model

Let's create a model we can use to store and transport our person records. We'll want to separate this into a new file. I've named it person.go.

In that file, I'll create a struct that matches the datatype of our record:

package main

import (
    _ "github.com/mattn/go-sqlite3"
)

type person struct {
    id         int
    first_name string
    last_name  string
    email      string
    ip_address string
}

Save the file.

Creating a New Record

We will add the functionality to create a new record in the database. You can use DB Browser for SQLite to access this database to verify changes.

First, we'll add some code to handle the data.

If you remember, back in main.go, we have a switch statement within handleFunc that looks like this:

func handleFunc(opts []wmenu.Opt) {

    switch opts[0].Value {

    case 0:
        fmt.Println("Adding a new Person")

Let's change that to handle accepting a new record as input.

case 0:

    reader := bufio.NewReader(os.Stdin)
    fmt.Print("Enter a first name: ")
    firstName, _ := reader.ReadString('\n')
    fmt.Print("Enter a last name: ")
    lastName, _ := reader.ReadString('\n')
    fmt.Print("Enter an email address: ")
    email, _ := reader.ReadString('\n')
    fmt.Print("Enter an IP address: ")
    ipAddress, _ := reader.ReadString('\n')

    newPerson := person{
        first_name: firstName,
        last_name:  lastName,
        email:      email,
        ip_address: ipAddress,
    }

    addPerson(db, newPerson)

    break

Here we use a bufio scanner to read in the first name, last name, email address, and IP address of a new person. We read those values into a buffer one by one, then create a new person struct, and pass that to the addPerson method (which doesn't exist yet).

The menu system will prompt us for these values, one by one, and then save them to the database.

Open up person.go and let's create the addPerson method.

func addPerson(db *sql.DB, newPerson person) {

    stmt, _ := db.Prepare("INSERT INTO people (id, first_name, last_name, email, ip_address) VALUES (?, ?, ?, ?, ?)")
    stmt.Exec(nil, newPerson.first_name, newPerson.last_name, newPerson.email, newPerson.ip_address)
    defer stmt.Close()

    fmt.Printf("Added %v %v \n", newPerson.first_name, newPerson.last_name)
}

This function takes the db object and the new person struct and inserts them into the database. We create a new SQL statement, stmt, using db.Prepare to prepare our insert statement, and protect the application from SQL injection. Then we run stmt.Exec with the parameters we want to insert. Defer the close method and print our results.

Save the file, and let's run it.

You can see our menu come up, ask us for each part of the record, then save it. We can check the database and confirm that it was saved:

Now you may notice something here. Notice the line breaks right after the first name?

This is because the new line (when you press enter) is scanned into the buffer, and it saves into the database this way. This problem can be fixed easily.

To remove the newline before it's inserted into the database, we can trim the suffix of each of those strings like this:

firstName = strings.TrimSuffix(firstName, "\n")

Refactor your case statement to look like this:

case 0:

    reader := bufio.NewReader(os.Stdin)

    fmt.Print("Enter a first name: ")
    firstName, _ := reader.ReadString('\n')
    if firstName != "\n" {
        firstName = strings.TrimSuffix(firstName, "\n")
    }

    fmt.Print("Enter a last name: ")
    lastName, _ := reader.ReadString('\n')
    if lastName != "\n" {
        lastName = strings.TrimSuffix(lastName, "\n")
    }

    fmt.Print("Enter an email address: ")
    email, _ := reader.ReadString('\n')
    if email != "\n" {
        email = strings.TrimSuffix(email, "\n")
    }

    fmt.Print("Enter an IP address: ")
    ipAddress, _ := reader.ReadString('\n')
    if ipAddress != "\n" {
        ipAddress = strings.TrimSuffix(ipAddress, "\n")
    }

    newPerson := person{
        first_name: firstName,
        last_name:  lastName,
        email:      email,
        ip_address: ipAddress,
    }

    addPerson(db, newPerson)

    break

Now, let's rerun it and add another person:

We can see that the new line was removed, and it even looks proper in the database:

We'll go back and fix Lloyd at a later time.

Now we have our Create portion finished! We can now create new people for this database.

Reading a Record

Now we want to read a record, the functionality in our second menu option:

menu.Option("Find a Person", 1, false, nil)

We will create code to find a person by their name.

Add the following to the case statement in handleFunc. Change the following:

case 1:
    fmt.Println("Finding a Person")

to this:

case 1:

        reader := bufio.NewReader(os.Stdin)
        fmt.Print("Enter a name to search for : ")
        searchString, _ := reader.ReadString('\n')
    searchString = strings.TrimSuffix(searchString, "\n")
        people := searchForPerson(db, searchString)

        fmt.Printf("Found %v results\n", len(people))

        for _, ourPerson := range people {
            fmt.Printf("\n----\nFirst Name: %s\nLast Name: %s\nEmail: %s\nIP Address: %s\n", ourPerson.first_name, ourPerson.last_name, ourPerson.email, ourPerson.ip_address)
        }
        break

Here we create another bufio reader to read from standard input (your keyboard). We read in the name you search for into searchString.

Then, we will create a variable named people to store our results. It's populated by the searchForPerson function that we'll create.

This function returns a list of people results based on our search string. It could be one or more results, so we'll print out how many people we find.

Then we'll loop through the results and display them on the screen.

In person.go let's create a searchForPerson function:

func searchForPerson(db *sql.DB, searchString string) []person {

    rows, err := db.Query("SELECT id, first_name, last_name, email, ip_address FROM people WHERE first_name like '%" + searchString + "%' OR last_name like '%" + searchString + "%'")

    defer rows.Close()

    err = rows.Err()
    if err != nil {
        log.Fatal(err)
    }

    people := make([]person, 0)

    for rows.Next() {
        ourPerson := person{}
        err = rows.Scan(&ourPerson.id, &ourPerson.first_name, &ourPerson.last_name, &ourPerson.email, &ourPerson.ip_address)
        if err != nil {
            log.Fatal(err)
        }

        people = append(people, ourPerson)
    }

    err = rows.Err()
    if err != nil {
        log.Fatal(err)
    }

    return people
}

We'll create this function that takes the db object and a search string and returns a slice of person objects (structs).

Run a SELECT statement to select id, first and last name, email, and IP address based on whether the first or last name matches our search string.

Iterate through each row, create a person struct and populate it with the resulting data. Then we append it to our slice and return the completed collection with the function.

Let's build it and run it:

Run the program and select 2 to find a person, and search for a first and last name, and you'll see the results! Great stuff.

Now let's build something to update these records.

Updating a Record

Now we can add people and look them up. What if we want to update the information? If you remember, we inserted Lloyd Christmas with new lines attached to it. Let's build something to update the record and save it.

This one will be a little different. Here's how it works:

• Get an ID from user input.
• Retrieve a record from that ID and put it in a person struct.
• Display the current value when asking for a new value.
• Save new value into a new struct.
• Save the update(s) to the database.

In main.go let's add another case for our third menu option.

Replace the following:

case 2:
    fmt.Println("Update a Person's information")

with this:

case 2:

    reader := bufio.NewReader(os.Stdin)
    fmt.Print("Enter an id to update: ")
    updateid, _ := reader.ReadString('\n')

    currentPerson := getPersonById(db, updateid)

    fmt.Printf("First Name (Currently %s):", currentPerson.first_name)
    firstName, _ := reader.ReadString('\n')
    if firstName != "\n" {
        currentPerson.first_name = strings.TrimSuffix(firstName, "\n")
    }

    fmt.Printf("Last Name (Currently %s):", currentPerson.last_name)
    lastName, _ := reader.ReadString('\n')
    if lastName != "\n" {
        currentPerson.last_name = strings.TrimSuffix(lastName, "\n")
    }

    fmt.Printf("Email (Currently %s):", currentPerson.email)
    email, _ := reader.ReadString('\n')
    if email != "\n" {
        currentPerson.email = strings.TrimSuffix(email, "\n")
    }

    fmt.Printf("IP Address (Currently %s):", currentPerson.ip_address)
    ipAddress, _ := reader.ReadString('\n')
    if ipAddress != "\n" {
        currentPerson.ip_address = strings.TrimSuffix(ipAddress, "\n")
    }

    affected := updatePerson(db, currentPerson)

    if affected == 1 {
        fmt.Println("One row affected")
    }

    break

Create another bufio scanner to read in the ID you want to update. Then search for that id with getPersonById and store it in currentPerson.

Go through each value and display the current value while asking for a new value. If the user presses enter, it will keep the current value. If they type in something new, it will be updated in the currentPerson object.

Then, create a variable named affected and call the updatePerson method. Pass in the db connection method and the currentPerson object with the new information.

If the update is successful, we'll return a message.

Let's create the methods we need in person.go:

func getPersonById(db *sql.DB, ourID string) person {

    rows, _ := db.Query("SELECT id, first_name, last_name, email, ip_address FROM people WHERE id = '" + ourID + "'")
    defer rows.Close()

    ourPerson := person{}

    for rows.Next() {
        rows.Scan(&ourPerson.id, &ourPerson.first_name, &ourPerson.last_name, &ourPerson.email, &ourPerson.ip_address)
    }

    return ourPerson
}

This method takes our db object and an ID as a string. We run a query to select records that match that ID. Then we create a new person object and iterate through the row, and scan in each value to the object. Once complete, we return it.

After displaying the current values and taking in new ones in main.go, we need to process the new person object and update the database. We'll do that with the updatePerson function:

func updatePerson(db *sql.DB, ourPerson person) int64 {

    stmt, err := db.Prepare("UPDATE people set first_name = ?, last_name = ?, email = ?, ip_address = ? where id = ?")
    checkErr(err)
    defer stmt.Close()

    res, err := stmt.Exec(ourPerson.first_name, ourPerson.last_name, ourPerson.email, ourPerson.ip_address, ourPerson.id)
    checkErr(err)

    affected, err := res.RowsAffected()
    checkErr(err)

    return affected
}

Here we use a prepared statement and use the values from the person object passed in to run an UPDATE against the database. We execute the statement and return the rows affected, which should be one.

Save the file and run it. Let's fix Lloyd's record.

Notice the display is all messed up. That's because these records contained a newline, so it looks funny. But we've updated that record now so they are gone.

The next time we go to update it, we see the new lines are gone:

We can now update any record we like and change all the values except for the ID. Great!

Deleting a Record

Finally, we need to delete a record from the database. This is easy.

In main.go, let's add the following case option by changing the following:

case 3:
    fmt.Println("Deleting a person by ID")

to this:

case 3:

    reader := bufio.NewReader(os.Stdin)
    fmt.Print("Enter the ID you want to delete : ")
    searchString, _ := reader.ReadString('\n')

    idToDelete := strings.TrimSuffix(searchString, "\n")

    affected := deletePerson(db, idToDelete)

    if affected == 1 {
        fmt.Println("Deleted person from database")
    }

    break

This will look familiar. We're reading in an ID from standard input. Then, we're trimming the newline and passing that string to a deletePerson method. This method takes our db object and the ID we want to delete and returns affected, which should be 1.

Let's add the deletePerson method to our person.go file:

func deletePerson(db *sql.DB, idToDelete string) int64 {

    stmt, err := db.Prepare("DELETE FROM people where id = ?")
    checkErr(err)
    defer stmt.Close()

    res, err := stmt.Exec(idToDelete)
    checkErr(err)

    affected, err := res.RowsAffected()
    checkErr(err)

    return affected
}

The deletePerson method is pretty simple. It takes in our db connection and the ID to delete. It prepares a statement that's a DELETE and accepts a parameter for id. That is inserted into stmt.Exec and executed. Since there's no output from this command, we look for the rows affected and return that as our output. Easy!

Let's delete a record from the database. We'll find the id of Lloyd (1001):

Then we'll build and run our program, and enter that ID:

Now record 1001 is gone:

Now we can successfully C*reate, *Read, U*pdate, and *Delete records with our application!

Exiting the Program

Finally, we need to be able to exit the program.

Replace the following:

case 4:
    fmt.Println("Quitting application")

with this:

case 4:
    fmt.Println("Goodbye!")
    os.Exit(3)

Now let's build and run it.

Great! We have a complete CRUD app with SQLite and Go!

Conclusion

Fast, small, cross-platform applications are excellent for many use cases. This is why Go is becoming so popular. Fast, small and self-contained databases are the perfect addition to such applications which makes SQLite and Go a natural combination. This stack is no silver bullet and won't solve every problem, but there are many great uses for this.

The source code for this demo application is available here.

If you'd like to learn more about Go:

• The Go Playground

To learn more about SQLite:

• SQLite tutorials
• SQLite in Five minutes or less