Using A SQLite Database For Local Data In A Golang Application

When developing an application with the Go programming language, you might find yourself needing to save data locally. If you’ve been keeping up you’ll remember that I’ve written about storing data remotely with Golang in a Couchbase NoSQL database, but never anything locally. Probably the easiest way to store data locally is with a SQLite database as it is a solid technology that has been around for a while.

We’re going to see how to use a SQLite database in our Golang application to read and write local data.

SQLite isn’t an option out of the box when it comes to developing with the Go programming language. There are several packages available, but probably the most popular is the go-sqlite3 package by a guy that goes by mattn on GitHub.

To use this package, execute the following from the Command Prompt or Terminal:

go get github.com/mattn/go-sqlite3

It took me a few minutes to download this package, significantly longer than any other package that I’ve downloaded for Go. When it’s done, we can start developing a super simple application.

Go ahead and create a new project with the following code. We’ll break it down in a moment.

package main

import (
    "database/sql"
    "fmt"
    "strconv"

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

func main() {
    database, _ := sql.Open("sqlite3", "./nraboy.db")
    statement, _ := database.Prepare("CREATE TABLE IF NOT EXISTS people (id INTEGER PRIMARY KEY, firstname TEXT, lastname TEXT)")
    statement.Exec()
    statement, _ = database.Prepare("INSERT INTO people (firstname, lastname) VALUES (?, ?)")
    statement.Exec("Nic", "Raboy")
    rows, _ := database.Query("SELECT id, firstname, lastname FROM people")
    var id int
    var firstname string
    var lastname string
    for rows.Next() {
        rows.Scan(&id, &firstname, &lastname)
        fmt.Println(strconv.Itoa(id) + ": " + firstname + " " + lastname)
    }
}

In the above code we are creating and opening a local database called nraboy.db using the sqlite3 driver for Go. Just to be clear, we are using the database/sql API with the go-sqlite3 driver.

Using the Prepare, Exec, and Query functions, we can interact with the database. For this example we are creating a new SQLite table with three columns if it doesn’t already exist. Then we are using a parameterized query to insert hard-coded data into the database. Once we have data in the database we can query for it and loop through the results.

There are other commands included in the API, but if you’re like me, the functions from this example will likely be all you’ll need to be successful.

If you wanted to use MySQL or another database that had a database/sql driver, you could use the same APIs as SQLite.

A video version of this tutorial can be seen below.

Nic Raboy

Nic Raboy is an advocate of modern web and mobile development technologies. He has experience in C#, JavaScript, Golang and a variety of frameworks such as Angular, NativeScript, and Unity. Nic writes about his development experiences related to making web and mobile development easier to understand.