Dolt, the world's first SQL Database which can version control every revision of data within it, has a variety of ways to interact with it. One of which is our home grown SQL Shell, which is a terminal based interface to the database, similar to mysql or psql. Recently we had a user ask how to get paged output for a large query. We didn't have that feature, but we do now!

Terminals? ICK!

I'm the first to confess that interactive terminals are not everyone's cup o' tea. If that describes you, check out our other interfaces like our web interface or our workbench application.

Terminals can help in a pinch when you need to connect to a remote server via SSH. Or, you may just prefer the simplicity of a terminal interface. Whatever your situation, we have you covered.

The Problem

Have you ever been in the situation where you run a query and the output is so large that it scrolls off the screen? Perhaps the lines are too long, and they wrap which makes it hard to read. Or maybe you want to do a text search on the output or want to scroll back and forth. These are all problems that can be solved with a pager.

One of the common pagers is less.

less README.md

If you've used Git on the command line, you've probably used a pager without knowing it. When Git has output which is too large for the screen, by default it pipes the output to less so you can page through it.

In a SQL shell, you may run a select * query, and be overwhelmed with results. I created a table with 1000 rows to demonstrate this. This is all fake data generated by chatGPT, so don't worry about the content. Anyway, a simple query like this results in confusing output:

zsh> dolt sql
# Welcome to the DoltSQL shell.
# Statements must be terminated with ';'.
# "exit" or "quit" (or Ctrl-D) to exit. "\help" for help.
db/main> select * from demo_users;

Talk about 'ick'. No wonder terminals get a bad rap.

The Solution

If you would prefer readable output, you can enable the pager with the following:

db/main> \pager on
db/main> select * from demo_users;

As you can see, the output now has a visible header line and columns which look like columns. The text goes to the right off the screen rather than wrapping. You can scroll up and down with the arrow keys, and search with /. When you're done looking at the output, you can press q to exit the pager.

If you want to turn the pager off, you can do so with the following:

db/main> \pager off

Conclusion

This is a simple quality of life feature for Dolt SQL Shell users. We decided to keep this simple and always used less -FSRXd as the pager. If you have a different pager or custom options you would like to use, let us know. Adding features in this area is pretty straightforward, and we always want to here how people are using Dolt. Come tell us on Discord!