The full-text functionality of sqlite3 along with the powerful SQL indexing and trigger features allows us to easily keep notes with references in an sqlite3 database. In this document I present a workflow for doing so.

First, let’s examine the pros and cons of this workflow:

Pros:

• Your notes are kept in one file, and are portable on every OS and CPU architecture sqlite3 supports.
• You can write your notes in any kind of plain text format, for example troff or markdown.
• You can compose, edit, download your files with the sqlite3 CLI and your editor of choice.
• You get reference link calculations for free.
• You get full-text search for free.
• You can group notes into collections.
• You can optionally tag your notes with keywords.
• You can attach any (binary or not) file to those collections and refer to them from your notes.

Cons:

• Your database may get corrupted (versus one note file getting corrupted) but it’s mostly recoverable. Always backup in anything you do.
• You will need familiarity with the command line and SQL, but with the proper mindset this shouldn’t be a problem.
• References in text files do not obey FOREIGN KEY constraints; if you delete a note, the dangling reference in text and in indices remains. You can easily search for and fix them in AFTER INSERT triggers, of course.

The schema

You can use anything you like as long as it has a basic property: your notes table must have a unique id that you can reference in plain text.

For this demo, I use the bibliothecula schema which has UUIDs for primary keys and allows you to tag or add other arbitrary metadata (and files) to each document. In this model, the document is our notes collection and the files of this document can include plain text ones that are our notes.

The table used for files in bibliothecula is BinaryMetadata; since it’s binary it can also hold plain text data. This is the CREATE statement for BinaryMetadata: