Written by Philipp on 2020-02-20

Hidden Champion – SQLite Databases in Go

DB . Go

If you use a smartphone, macOS, or Chrome, the chances are good that you are a SQLite user in some form. SQLite is probably the most widely used database, without ever standing particularly in the spotlight.

According to Wikipedia, “through integration in Mozilla Firefox, in the common mobile phone operating systems (Android, iOS, Symbian OS, Windows Phone) and integration in every PHP installation, SQLite is the most widespread and most used database system in the world.”

Symbian OS and Windows Phone are long gone, and PHP has only included SQLite as an extension since version 5.3. Still, SQLite is very common even today.

Its lead developer Dr. Richard Hipp recounted the history of SQLite very impressively in a talk.

The executive summary reads no less impressively:

• Full-featured SQL
• Billions and billions of deployments
• Single-file database
• Public domain source code
• All source code in one file (sqlite3.c)
• Small footprint
• Max DB size: 140 terabytes
• Max row size: 1 gigabyte
• Faster than direct file I/O
• Aviation-grade quality and testing
• Zero-configuration
• ACID transactions, even after power loss
• Stable, enduring file format
• Extensive, detailed documentation
• Long-term support

High time, then, to take a closer look at SQLite!

As part of a smaller Go project, I had to implement a storage for schema-less data. Ideally, the database should be embeddable, have few dependencies, and bring little performance overhead.

All examples can also be found in the corresponding GitHub repository.

A Simple Start

To use SQLite in Go, we only need to include a suitable SQLite driver. We use github.com/mattn/go-sqlite3 here.
This module implements the standard Go database/sql interface. This means no further dependencies are needed. The created binary contains all functions needed to use SQLite functionality.

It takes two imports to work with SQLite:

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

After creating a SQLite database, we can also create a simple table:

func main() {
    db, err := sql.Open("sqlite3", "simple.sqlite")
    if err != nil {
        log.Panic(err)
    }

    sqlStmt := "CREATE TABLE data (id TEXT not null primary key, content TEXT);"
    _, err := db.Exec(sqlStmt)
    if err != nil {
        log.Panic(err)
    }
}

This creates the table data with the two fields id and content, where id is the primary key.

It makes sense to also create an index on primary key fields:

sqlStmt = "CREATE UNIQUE INDEX idx_data_id ON data(id);"
_, err = db.Exec(sqlStmt)
if err != nil {
    return err
}

The database that is now created is persisted in the file simple.sqlite. We can also open this file with the SQLite CLI tool:

$ sqlite3 simple.sqlite
SQLite version 3.24.0 2018-06-04 14:10:15
Enter ".help" for usage hints.
sqlite> .headers ON
sqlite> .mode column
sqlite> select * from sqlite_master;
type        name        tbl_name    rootpage    sql
----------  ----------  ----------  ----------  --------------------------------------------------------------
table       data        data        2           CREATE TABLE data (id TEXT not null primary key, content TEXT)
index       sqlite_aut  data        3
sqlite>

The table sqlite_master, which is queried here, holds a special role in SQLite. This table contains all metadata of the database. If you want to check whether a table already exists in the database, this is best done with a query against sqlite_master.

The two settings .headers ON and .mode column cause the sqlite3 output to be formatted a little nicer.

CRUD Functions

CRUD (Create, Update, Read, Delete) functions can be implemented with Go in SQLite just like in any other database.

Advanced functions are supported as well, such as prepared statements or transactions:

sqlStmt := fmt.Sprintf(`INSERT INTO data
    (id, content)
    VALUES
    (?, ?)`)

tx, err := db.Begin()
if err != nil {
    log.Panic(err)
}

stmt, err := tx.Prepare(sqlStmt)
if err != nil {
    return err
}
defer stmt.Close()

for i := 1; i < 10; i++ {
    id := fmt.Sprintf("%d", i)
    content := fmt.Sprintf("content #%d %s", i, shortuuid.New())
    log.Printf("Inserting %s: %s", id, content)
    _, err := stmt.Exec(id, content)
    if err != nil {
        log.Panic(err)
    }
}
tx.Commit()

Querying the individual values follows the standard database/sql pattern: binding the values of individual columns to individual variable references:

queryStmt := "SELECT id, content FROM data"
rows, err := db.Query(queryStmt)
if err != nil {
    return err
}

for rows.Next() {
    var id, content string
    err = rows.Scan(&row.id, &row.content)
    if err != nil {
        return err
    }
    log.Printf("Result: %s: %s", id, content)
}

Entries can also be deleted. When calling db.Exec(...), besides an error variable, a Result is always returned, which provides information about the modifying operation:

queryStmt := "DELETE FROM data WHERE id > 5 "
tx, err := db.Begin()
if err != nil {
    return err
}

result, err := db.Exec(queryStmt)
if err != nil {
    return err
}
tx.Commit()

id, _ := result.LastInsertId()
numRows, _ := result.RowsAffected()

log.Printf("LastInsertId %d, RowsAffected: %d", id, numRows)

This can be quite useful in a log entry, for example:

2020/02/17 19:33:27 LastInsertId 0, RowsAffected: 4

UPSERT

In some situations it happens that an entry you want to write already exists with an identical ID.

If you run an INSERT on the identical record in Go, an error of the type error UNIQUE constraint failed is returned.

You now want to update the existing entry – which has the identical ID – instead.

Some DBMS already offer a built-in operation for this, a so-called UPSERT. SQLite writes:

UPSERT is a special syntax addition to INSERT that causes the INSERT to behave as an UPDATE or a no-op if the INSERT would violate a uniqueness constraint. UPSERT is not standard SQL.

It also says “An UPSERT is an ordinary INSERT statement that is followed by the special ON CONFLICT clause shown above.”

With exactly this combination of INSERT and ON CONFLICT handling, it looks like this:

type entry struct {
    id      string
    content string
}

func upsertEntry(db *sql.DB, row *entry) error {

    sqlStmt := fmt.Sprintf(`INSERT INTO data
        (id, content)
        VALUES
        (?, ?)
        ON CONFLICT (id)
        DO UPDATE SET content=?
        WHERE id = ?`)

    tx, err := db.Begin()
    if err != nil {
        return err
    }

    stmt, err := tx.Prepare(sqlStmt)
    defer tx.Commit()

    if err != nil {
        return err
    }
    defer stmt.Close()

    _, err := stmt.Exec(
        row.id, row.content,
        row.content,
        row.id)
    if err != nil {
        return err
    }
}

JSON

In today’s environment, we are increasingly dealing with schema-less data.
So it’s either about mapping schema-less data to a fixed table structure, or being able to deal with arbitrary data structures.

The latter point has led to the great popularity of NOSQL (Not only SQL) databases.

Many NOSQL databases use the concept of documents (usually JSON), which is supported as a dedicated type and can be stored and queried within the database.

SQLite can also be made to support JSON, more specifically documents. To have extensions alongside the DBMS core, SQLite supports a plugin concept – so-called extensions. For SQLite to use a desired extension, the binary has to be compiled with a certain flag.

Since the Go plugin is compiled from source the first time anyway, this can be controlled very easily via a Go build tag:

go build --tags="json1" main.go

If you just want to run the application, this works with the same flag:

go run --tags="json1" main.go

The json1 extension offers a range of features that greatly simplify working with JSON data.

In our fictional example, we want to store a series of tweets and query them afterwards.

A tweet consists of the following JSON structure:

{
    "id": "123122000",
    "date": "2020-01-08 08:12:23",
    "author": "dave",
    "text": "hi @alice!",
    "mentions":["alice"]
}

We want to store our tweets in a database without having to adjust the table structure when the JSON structure changes in the future.
Furthermore, we want to run two queries:

• Find all tweets by an author
• Find all tweets in which a user has been mentioned (aka mentions)

We don’t need any adjustments to the existing table structure.
It still looks like this:

CREATE TABLE data (
     id TEXT not null primary key,
     content TEXT
 )

What changes are the INSERT statements. We now use the json(...) function to mark records that should be inserted as JSON:

sqlStmt := fmt.Sprintf(`INSERT INTO data
    (id, content)
    VALUES
    (?, json(?))`)

tx, err := db.Begin()
if err != nil {
    return err
}

stmt, err := tx.Prepare(sqlStmt)
if err != nil {
    return err
}
defer stmt.Close()

// utils.GetID returns a SHA1 hash of the content
id, err := utils.GetID(tweet)
if err != nil {
    return err
}

content, err := json.Marshal(tweet)
if err != nil {
    return err
}

_, err := stmt.Exec(id, string(content))
if err != nil {
    return err
}

Marshalling the tweet into JSON and then back into a string is not necessary, but it ensures that only valid JSON is stored in the database.

The ID is derived directly from the content (sha1 hash).

If we run a query with the standard sqlite3 CLI tool, the result looks like a normal string:

$ sqlite3 json.sqlite
SQLite version 3.24.0 2018-06-04 14:10:15
Enter ".help" for usage hints.
sqlite> select * from data;

id                                        content
----------------------------------------  ----------------------------------------------------------------------------------------------------------------------------
c1d08ab7952fc8732b1e195da264c70da60b31d9  {"author":"alice","content":"hello world!","data":"2020-01-01 10:15:23","id":"123123123","mentions":[]}
20b08799979a84daf181e1a6e329591ada11fbc4  {"author":"bob","content":"hi @alice!","data":"2020-01-02 08:12:23","id":"123123200","mentions":["alice"]}
bd3fed9eb4b39c47230d8a7d98d09385c5c9f69f  {"author":"hal","content":"hi @alice, hello @bob!","data":"2020-01-03 15:23:23","id":"123122001","mentions":["alice","bob"]}
sqlite>

The most important functions of the extension are:

• json_extract: to extract individual values from a JSON structure
• json_each: to iterate over the first-level elements in a JSON structure
• json_tree: to traverse a complete JSON tree and map to key->value

In our example, we look at json_extract and json_tree. For json_each, I’d like to refer to a suitable blog post about implementing a one-to-many relationship via JSON mapping in SQLite.

The first requirement is finding all tweets by an author. In a classic RDBM system, you would certainly implement this with an additional author column and a simple SELECT – easily done.
But we want to deal with a variable data structure.

The complete Go function looks like this:

func queryCreated(db *sql.DB, user string) ([]Tweet, error) {
    tweets := make([]Tweet, 0)

    queryStmt := fmt.Sprintf(
        `SELECT content
        FROM data
        WHERE json_extract(content, '$.author')='%s'`, user)
    rows, err := db.Query(queryStmt)
    if err != nil {
        return tweets, err
    }

    for rows.Next() {
        var tweet Tweet
        var content string
        err = rows.Scan(&content)
        if err != nil {
            return tweets, err
        }
        err := json.Unmarshal([]byte(content), &tweet)
        if err != nil {
            return tweets, err
        }
        tweets = append(tweets, tweet)
    }
    return tweets, nil
}

With WHERE json_extract(content, '$.author')='%s', each record is checked against the author node of the content field. $.author is nothing more than a path specification in the JSON. If you know jq, this should not be unfamiliar.

The next point is a bit more difficult. If author is still a single field at the top level of the JSON, the values to be selected against a mention are the individual elements of an array within the JSON.

If the complete JSON structure were an array, the json_each function would also be suitable here. But since we have to select further down the JSON structure, we use json_tree here:

func queryMentions(db *sql.DB, user string) ([]Tweet, error) {
    tweets := make([]Tweet, 0)

    queryStmt := fmt.Sprintf(
        `SELECT content
        FROM data, json_tree(data.content, '$.mentions')
        WHERE json_tree.value = '%s'`, user)
    rows, err := db.Query(queryStmt)
    if err != nil {
        return tweets, err
    }

    for rows.Next() {
        var tweet Tweet
        var content string
        err = rows.Scan(&content)
        if err != nil {
            return tweets, err
        }
        err := json.Unmarshal([]byte(content), &tweet)
        if err != nil {
            return tweets, err
        }
        tweets = append(tweets, tweet)
    }
    return tweets, nil
}

The function is applied in two steps. First, json_tree(data.content, '$.mentions') maps the content of data.content.mentions as a JSON tree. In the subsequent WHERE, the individual values of the array can then be checked with json_tree.value = '%s'.
By the way: if mentions were not an array but a key->value map, we could also have done a check against the key (e.g. json_tree.key = 'user' AND json_tree.value = '%s').

Conclusion

I personally knew SQLite as a ubiquitous, embedded, and file-based database, but the concept of extensions was new to me. In hindsight, I also find the stated limits Max DB size: 140 terabytes quite impressive, even though I personally see the use case rather limited to single (or few) request systems – so rather client-based systems or admin services.

A big disadvantage of using the JSON extension will certainly be the decreasing performance at some point, because with SQLite it is not possible to create an index on a JSON key, but only on a complete column with SQLite base types.
Furthermore, C bindings (which are used in the SQLite module) always mean a restriction of Go’s cross-compile functionality. A suitable C compiler must be present on the development system. It is also advisable, if possible, to build the respective binary directly on the target platform.

Currently, SQLite is still the best decision for the use case at hand, because it provides a stable and platform-independent database, whose production readiness has been proven by the sheer number of worldwide deployments.

This article was originally published on the INNOQ blog.