Building an application with Go and SQLite
Tags: go, programming, tutorial, sql
Published: 2022-04-15 21:34:59

---
title: Building an application with Go and SQLite
published: true
description: Go and SQLite combined can produce small, screaming fast applications that run anywhere. Follow along as we build a complete Go application with a SQLite database backend from start to finish.
tags: go, programming, tutorial, sql
cover_image: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/frhd1u2vnswns6gewlof.jpg
canonical_url: https://www.allhandsontech.com/programming/golang/how-to-use-sqlite-with-go/
---

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

[Go](https://www.pluralsight.com/paths/go-core-language?utm_source=allhandsontech&utm_medium=organic-web&utm_campaign=allhandsontech) is one of the hottest languages in development right now, known for its simple structure and blazing performance.

[SQLite](https://www.sqlite.org/index.html) 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.

So we're going to combine these two 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:

- Creating
- Reading
- Updating
- Deleting

With SQLite. You can follow along and build this application or use this as a reference to build something of your own. I'll start with pre-populated database you can [download here](https://github.com/JeremyMorgan/GoSqliteDemo/blob/main/names.db). 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 that looks like this:

So let's get started!

## Creating the menu system

We will build a CLI application that accesses a SQLite database. I'm going to use [The WMenu package from Dixonwille](https://github.com/dixonwille/wmenu) 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](https://golang.org/doc/tutorial/create-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:

![How to use SQLite with Go][0]

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

![How to use SQLite with Go][1]

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.

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

## The SQLite database

The database we're using for this exercise is available here.

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

![How to use SQLite with Go][2]

The create statement is here:

```sql
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](https://www.mockaroo.com/), my favorite test data generator. We will access this data, and add to it without our Go CLI application.

![How to use SQLite with Go][3]

In the spirit of CRUD, we'll build out our application in that order, **C**reate, **R**ead, **U**pdate, **D**elete.

## 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](https://github.com/mattn/go-sqlite3) 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 a SQLite 3 database. It's a simple step. Then we [defer](https://tour.golang.org/flowcontrol/12) 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 })
```

And add the parameter to our function

```
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](https://sqlitebrowser.org) 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 it into the database.  We create a new SQL statement, `stmt`. We use `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. Then defer the close method and print our results.

Save the file, and let's run it.

![How to use SQLite with Go][4]

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:

![How to use SQLite with Go][5]

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

![How to use SQLite with Go][6]

This is because the newline (when you press enter) is scanned into the buffer, and it saves into the database this way. But we can fix this problem easily.

We can trim the suffix of each of those strings like this:

```
firstName = strings.TrimSuffix(firstName, "\n")
```
To remove the newline before it's inserted into the database.

So 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:

![How to use SQLite with Go][7]

Now we can see that the newline was removed, and it even looks proper in the database:

![How to use SQLite with Go][8]

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

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

To

```
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 in. 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).

We'll 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.

We 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:

![How to use SQLite with Go][9]

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 newlines attached to it. So 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:

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

with:

```
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
```

We create another bufio scanner to read in the ID you want to update.

Then we search for that id with `getPersonById` and store it in `currentPerson`.

Then we 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, we'll create a variable named `affected` and call the `updatePerson` method, and 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.

![How to use SQLite with Go][10]

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 newlines are gone:

![How to use SQLite with Go][11]

And 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:

Change:

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

to

```
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):

![How to use SQLite with Go][12]

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

![How to use SQLite with Go][13]

And now record 1001 is gone:

![How to use SQLite with Go][14]

And now we can successfully **C**reate, **R**ead, **U**pdate, and **D**elete records with our application!

## Exiting the program

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

Replace

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

with

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

```

Now let's build and run it:

![How to use SQLite with Go][15]

Great! Now 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](https://github.com/JeremyMorgan/GoSqliteDemo).

If you have any questions, or comments, [let me know](https://www.twitter.com/JeremyCMorgan).


If you'd like to learn more about Go:

- [The Go Playground](https://play.golang.org/)
- [The Go Core Language Path](https://www.pluralsight.com/paths/go-core-language?utm_source=allhandsontech&utm_medium=organic-web&utm_campaign=allhandsontech)

To learn more about SQLite:

- [SQLite tutorials](https://www.sqlitetutorial.net)
- [SQLite in Five minutes or less](https://www.sqlite.org/quickstart.html)

[0]: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ir7lekrd8s4wygh78l7p.png
[1]: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/h19apnoy84jhx57ufjz6.png
[2]: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bxo3jtjza89j33hc6139.png
[3]: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/p5bw61g6lm6e85o64046.png
[4]: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uf731a30vsdb6hsqjnvu.png
[5]: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gqvbgwmerhn8eh858hut.png
[6]: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wn5171rg6d8btp8axexk.png
[7]: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/iii7cl0svqije6sasmic.png
[8]: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qf5y1vpuymq3xat6abtj.png
[9]: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5f636g7matatylvosad8.png
[10]: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/05bmklwqygqzcqqy7feg.png
[11]: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/u01x4wz67n9w588zrq13.png
[12]: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hvcqvr0y281awwzvslqy.png
[13]: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/90w96ti01te71d3dsex2.png
[14]: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/47rsxt3cx2ahj7avxgj2.png
[15]: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9jlou1q4p8ucymslwyls.png