sqlite3 command-line - How to show less/more output

Question

I am using the sqlite3 command-line programme on Ubuntu.

I'd like to see its output paginated on the screen.

So for example, I'd start it like so :

 # sqlite3
 SQLite version 3.6.22
 Enter ".help" for instructions
 Enter SQL statements terminated with a ";"
 sqlite> _

Then, at the sqlite prompt, if I type .help:

 sqlite> .help

I see a long 'page' of help info, which comes to rest showing the final few lines on the screen.

I am not a fast reader and so cannot read all that info in just a few milliseconds.

So, how do I ensure that I only see a screenful at a time?

In normal command line parlance this could be piped to | less or | more but, that does not seem to work within this sqlite interactive shell.

Answer 1

It does not seem to have something built-in. However, you can use Cntrl+PgUp and Cntrl+PgDn to move up and down from different pages in a terminal (in Ubuntu is gnome-terminal by default, so that should work).

Also, you can call commands from the shell, like in:

$ echo .help | sqlite3 2>&1 | more

sqlite3 prints the output in stderr, therefore the redirection 2>&1. For SQL commands, you can use:

$ sqlite3 my.db "select * from my_table;" | more

And so on.

Answer 2

From the dbcli collection the sqlite compatible litecli emerged in 2018. It can use either the $PAGER environment variable or a defined pager in ~/.config/litecli/config. As the util offers color support, I set it to less -SniFXR(?).

Another method is to let sqlite3 write to an output file/pipe and use less -f -S /tmp/sqlpipe in a second terminal window or tmux pane to read it. Enable with

.shell mkfifo /tmp/sqlpipe
.output /tmp/sqlpipe

It will look like this.

For not having to repeatedly enter the commands, a ~/.sqliterc can be used to set them at startup. Further formatting improves the output, though in the end litecli is a much more comfortable solution.