CLI Database

See the parent document: Using the Terminal as a Daily Driver for more about terminal productivity applications.

What is a good productivity setup without a database? My database of choice is SQLite. It fits the bill perfectly for all my use cases. It is light, fast, ubiquitous, and very useful. SQLite comes with its own command line interface, sqlite3.

It is surprising all the places you can find SQLite databases in use.

SQLite

• SQLite Home Page
• SQLite Documentation
• SQLite Command Line Shell Documentation

SQLite3 is the database tool that is included when installing SQLite. Running sqlite3 allows creating tables, building queries, and managing your databases in general.

The .mode command is used to change the way table listings are printed on the screen. SQLite3 has a markdown option. The LiteCLI equivalent is github. This is great for copying and pasting query results into a Markdown file.

LiteCLI

• LiteCLI Documentation
• LiteCLI Source Code

LiteCLI is a tool from the dbCLI project which also has tools for other databases (e.g. Postgres, MySQL, SQL server, Redis). The nice thing that LiteCLI brings to the table is the typing help as you enter commands.

The config file for LiteCLI is stored in ~/.config/litecli/config. A couple of things I changed in my config were to change the color scheme to monokai and the keyboard entry mode to vi.

There is also a config option to change the way table listing are printed on the screen with the table_config option in the config file. From within LiteCLI, output can also be changed with the \T command. This is very handy if you will be copying and pasting the results into another document. I like the github format which is good for Markdown tables.

LiteCLI has the ability to save your favorite queries, which is very handy for those queries you visit frequently.

Queries can also be executed from the command line and exit.

litecli -e "select author, title from books limit 5;" library.sqlite

It the -t flag is added to the command line, the table format will be display as specified in the config file.

litecli -t -e "select author, title from books limit 5;" library.sqlite

Harlequin

Harlequin is my latest SQL database tool discovery. It bills itself as “The SQL IDE for your terminal”. It has a ton of features, allowing you to browse tables, columns, edit queries, run queries, and display query results, and more.

• Harlequin Website
• Documentation

It has mouse support in the terminal, which is nice. Harlequin also comes with a good collection of built-in themes. This looks like a keeper.

Some Helpful SQL

Get a list of tables in a database:

-- sqlite command
.tables
-- or SQL
select name from sqlite_schema where type = 'table';

Get the structure of a table (table name is ‘books’ in the examples):

PRAGMA table_info(books)
-- or SQL to return the create table statement
SELECT sql FROM sqlite_schema WHERE name='books';
-- or
.schema books

Bonus

Here are a couple of places that I know where SQLite is used. SQLite support is baked into python and PHP programming languages. Firefox and Thunderbird make massive use of SQLite. Having these databases installed on you computer opens up a number of data mining possibilities.

Firefox

Firefox stores your bookmarks in a SQLite database. This could be the start for cleaning up duplicate bookmarks.

cd ~/.mozilla/firefox/
# find the sub-folder with the bookmarks.
cat installs.ini
# look for the Default line to get the name of the sub-folder
...
Default=5h77sptc.default-release

cd 5h77sptc.default-release

sqlite3 places.sqlite

sqlite> select url, title from moz_places limit 5;

Calibre

The Calibre database is named metadata.db and is stored in the root folder of your calibre library. Here is a sample query that can be run against it.

Warning: It’s probably OK to pull data from Calibre, but I wouldn’t advise trying to write data. That could have disastrous consequences for your data if something went wrong.

select books.sort as books, books.id as bookID, author as authorID, authors.sort as author from books join books_authors_link on books.id = books
             _authors_link.book join authors on books_authors_link.author=authors.id where authors.sort='Austen, Jane' order by books.sort;

The resulting table looks something like this:

Joplin

The Joplin note-taking application stores data in SQLite. On my Linux machine it is stored in ~/.config/joplin-desktop/database.sqlite.

Taskwarrior

Taskwarrior stores its data in a SQLite database. The default location is ~/.task/taskchampion.sqlite3. One interesting thing about the data store is the information for each task is stored in the tasks table data field as a JSON string.