Using SQLite from Go

In the book, we used MySQL server as a way to store relational data from our applications. In this post, we will see how to use work with SQLite from Go.

The demo code used is available in this repository.

The table names and operations are intentionally chosen to losely match that of the official Go Project tutorial, Accessing a relational database which uses MySQL as the database server.

Let’s get started!

Prerequisites

Overview

SQLite is file-based database engine.

Thus, we do not have a separate server process for it.

We will create the database, create a table, insert rows and query them back using:

Initialize your module

Initializing the database

To initialize the database, i.e. to create the file which will be used for the database, import the two libraries:

Once we have initialized the database, we can create a table, album using the ExecContext() method:

This is a code snippet from app.go which encapsulates the initialization in a function, initDatabase():


import (
	"database/sql"
	_ "modernc.org/sqlite"
)


var db *sql.DB

func initDatabase(dbPath string) error {
	var err error
	db, err = sql.Open("sqlite", dbPath)
	if err != nil {
		return err
	}
	_, err = db.ExecContext(
		context.Background(),
		`CREATE TABLE IF NOT EXISTS album (
			id INTEGER PRIMARY KEY AUTOINCREMENT, 
			title TEXT NOT NULL, 
			artist TEXT NOT NULL, 
			price REAL NOT NULL
		)`,
	)
	if err != nil {
		return err
	}
	return nil
}

Types for Album

We have defined two types for each album. The first, Album is to represent an album we are going to add to the table:

type Album struct {
	Title  string
	Artist string
	Price  float32
}

The second type, AlbumDbRow is used to represent an album that we retrieve from the database:

type AlbumDbRow struct {
	ID int
	Album
}

An album retrieved from the database, in addition to all the fields of Album will have an additional field, ID representing their identifier (a row number) in the album. Thus, we embed the Album struct and define ID as an additional field.

Inserting data

Once we have created the table, we can insert data using the INSERT SQL statement.

To execute the SQL statement, we will once again use the db.ExecContext() method.

The addAlbum() function in app.go shows how we can do so:

func addAlbum(a *Album) (int64, error) {
	result, err := db.ExecContext(
		context.Background(),
		`INSERT INTO album (title, artist, price) VALUES (?,?,?);`, a.Title, a.Artist, a.Price,
	)
	// rest of the function
}

Querying multiple rows

To query data, we will execute the SELECT statement.

As we expect to retrieve multiple rows, we use the db.QueryContext() method.

The albumsByArtist() function shows an example, with some comments in line

func albumsByArtist(artist string) ([]AlbumDbRow, error) {

        // this slice will contain all the albums retrieved

	var albums []AlbumDbRow
	rows, err := db.QueryContext(
		context.Background(),
		`SELECT * FROM album WHERE artist=?;`, artist,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	// rest of the function
}

The returned value, rows is an object of type *sql.Rows which we then iterate over using a for loop and calling the rows.Next() method.


	// we iterate over each row retrieved, scanning each row
	// into an object of type album, successively appending each
	// scanned album into a slice, albums[]
	
	for rows.Next() {
		
		var album AlbumDbRow

		if err := rows.Scan(
			&album.ID, &album.Title, &album.Artist, &album.Price,
		); err != nil {
			return nil, err
		}
		albums = append(albums, album)
	}
	return albums, err
}

Query for a single row

When we expect only one row to be returned by a SELECT query, we use the db.QueryRowContext() method. If the query returns more than one row, when we call the Scan() method, the rest of the rows are discarded.

The albumByID() function is an example demonstrating how to query a single row:

func albumByID(id int) (AlbumDbRow, error) {
	
	var album AlbumDbRow

	row := db.QueryRowContext(
		context.Background(),
		`SELECT * FROM album WHERE id=?`, id,
	)
	
	err := row.Scan(&album.ID, &album.Title, &album.Artist, &album.Price)

	// error handling

Demo of the application

Clone or download the example code from here.

Build and run:

$ go build

$ SQLITE_DB_PATH=app.db ./go-sqlite-demo

2023/06/27 13:13:54 recreating table: album
database initialized..
test data inserted..
querying test data by album ID..
{1 {Giant Steps John Coltrane 63.99}}
{2 {Jeru Gerry Mulligan 17.99}}
{3 {Sarah Vaughan Sarah Vaughan 34.98}}
{4 {Blue Train John Coltrane 56.99}}

You will see that a new file, app.db has been created in the same directory.

Using sqlite3 to interact with the database

If you have installed sqlite3, you can interact with app.db using it:


$ sqlite3 app.db
SQLite version 3.42.0 2023-05-16 12:36:15
Enter ".help" for usage hints.

sqlite> .tables
album

sqlite> .database
main: /home/echorand/work/github.com/practicalgo/go-sqlite-demo/app.db r/w
sqlite> .schema album
CREATE TABLE album (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        title TEXT NOT NULL,
                        artist TEXT NOT NULL,
                        price REAL NOT NULL
                );


sqlite> select * from album;

1|Giant Steps|John Coltrane|63.9900016784668
2|Jeru|Gerry Mulligan|17.9899997711182
3|Sarah Vaughan|Sarah Vaughan|34.9799995422363
4|Blue Train|John Coltrane|56.9900016784668

Using in-memory databases for testing

To write tests for an application that uses SQLite database, we can use an in-memory database.

To do so, we will use the special filename, :memory:

err := initDatabase(":memory:")

See an example test function in app_test.go.

Conclusion

In this post, we learned how to use SQLite to store data from a Go application.

We used database/sql and the cgo free https://gitlab.com/cznic/sqlite to read and write data from a file-based SQLite database.

We also saw how we can use an in-memory database for testing.

Learn more