Sqlite Litecli
Dec 6th, 2019 - written by Kimserey with .
SQLite comes built in with a command line tool allowing us to execute all sorts of database commands (DDL/DML) but also special dot commands allowing us to list the tables or check the schemas. Being low level by nature, it provides opportunities for application to be built on top of. LiteCLI is an example of a friendly CLI for SQLite. Today we will look at the feature that LiteCLI provides making it a great companion for your SQLite application.
Autocompletion
One of the most convenient feature provided by LiteCLI is the autocompletion for tables, columns, dot SQLite functions and all keywords of the SQL.
Fuzzy match is also supported enabling us to type part of a table or column name and letting LiteCLI resolve the name. Suggestions in the fish shell style are also provided where the rest of the query is inferred from what was entered.
Ouput Display
The ouput display is also a major enhancement with different format available.
The default way of showing result has pager enabled defaulting to less on macOS and linux.
LiteCLI supports multiple format which can be changed using \T simple. All the format supported can be found in the default configuration file.
Configuration
The default configuration can also be changed for our own preferences. We do so by updating the config file under:
1
/.config/litecli/config
This file should be automatically created on first launch of LiteCLI. In the config file we can then disable the pager.
1
enable_pager = False
Another useful configuration is to allow multi lines and let the table display go in vertical mode.
1
2
multi_line = True
auto_vertical_output = True
Useful when data from a column have many characters.
Favorite Queries
LiteCLI supports favorite queries where we are able to save queries in aliases. To do so we use \fs [name] [query] for favorite save and \fd [name] for favorite delete. Positional arguments, starting by index 1, are supported as well, making favorite queries reusable for different arguments.
1
2
3
\fs select_user SELECT id, username, email, registration_date FROM user
JOIN user_profile ON user.id = user_profile.user_id
WHERE user.username = '$1
For example this query will save a favorite query joining user and user_profile filtering the result by username. The username is a positional argument/
Favorite queries are saved in the config file, therefore if we end up saving wrong queries, we can simply delete them from the configuration file.
We can then use our favorite query with \f [query name] [arguments].
We can list all our favorite queries with \f.
Conclusion
Today we looked into LiteCLI, an enhanced CLI for SQLite bringing SQLite to a higher level of user friendliness. We started by looking at the autosuggestions, then moved to the output display, then moved to looking at the configuration file and lastly completed the post by looking at favorite queries. Hope you like this post and I see you on the next one!