How to Tag Records in SQLite3

In this post I'll show how I was able to filter database records by tags. All information is stored in a single table, so I do not need to use joins. This is possible thanks to SQLite's support for JSON functions.

Create `Post` Table

Let's start by opening a terminal window and enter the SQLite3 command line:

sqlite3

Next, we define a simple table that holds the title and tags of blog posts:

CREATE TABLE Post (
  title TEXT NOT NULL,
  tags JSON NOT NULL
);

Add Posts Data

Now, add some posts to the newly created table:

INSERT INTO Post (title, tags) VALUES ('Buccaneers win Super Bowl', '["sport", "nfl", "buccaneers"]');

INSERT INTO Post (title, tags) VALUES ('Wright wins World Darts Championship', '["sport", "nfl"]');

Note, the value for tags is a valid JSON array string. You can also build such a string by using the json_array function:

INSERT INTO Post (title, tags) VALUES ('The json_array Function of SQLite3', json_array('sqlite', 'json'));

Filter Posts by Tag

Before we start to query the data let us switch the output mode to get nicely formatted columns:

.mode column

Get all posts:

SELECT * FROM Post;

title                                 tags                          
------------------------------------  ------------------------------
Buccaneers win Super Bowl             ["sport", "nfl", "buccaneers"]
Wright wins World Darts Championship  ["sport", "nfl"]              
The json_array Function of SQLite3    ["sqlite","json"]

Get all posts tagged with sport:

SELECT Post.* FROM Post, json_each(Post.tags) t 
WHERE json_valid(Post.tags) AND t.value = 'sport';

title                                 tags                          
------------------------------------  ------------------------------
Buccaneers win Super Bowl             ["sport", "nfl", "buccaneers"]
Wright wins World Darts Championship  ["sport", "nfl"]

As you can see, the post "The json_array Function of SQLite3" is not part of the result set anymore. Simple as that we are now able to filter posts by tags.

Conclusion

This technique can be used whenever you need to tag/label records with a list of strings. One example (tag blog posts) I've just shown. Another example would be to store permissions of API keys (repo:create, gist, ...). If you can think of other use cases please let me know.