Using the PostgreSQL Pager With MariaDB Xpand

I'm not an anti-GUI person. In fact, I wrote three books about web GUI development with Java. However, I also like the command-line interface (CLI), especially text-based UIs. After a year of exploring MariaDB and the DevOps world, I got to discover and play with many text-based CLI tools that I didn't know even existed. These tools are especially useful when connecting to remote servers that don't have a GUI.

One special CLI tool that I frequently use is the mariadb SQL client (or mysql in the MySQL world)—a CLI program used to connect to MariaDB-compatible databases. With it, you can send SQL queries and other commands to the database server.

The MariaDB CLI-based SQL client

The mariadb SQL client has multiple configuration options, one of which is the possibility to set a terminal pager. If you are familiar with Linux, you have probably heard or used the more and less pagers. You can set a pager through the environment variable PAGER and mariadb will automatically use it. Alternatively, you can set a pager only for the current session using the mariadb prompt. For example, to use the less pager run the following command once you are connected to the database:

pager less

The next time you run a SQL query, you’ll be able to navigate through the result set using the arrow keys on your keyboard.

Setting a pager using the mariadb SQL client

The less pager is useful but not the best for SQL result sets that are shown as tables. There’s an open-source tool called pspg (see the documentation and source code on GitHub), initially developed for PostgreSQL but which later added support for several other databases, including MariaDB. Since the mariadb SQL client is able to connect to MariaDB Xpand databases, I gave it a try, and it worked perfectly. Keep reading to find out how to try it out.

The easiest way to get an Xpand database up and running is by creating a service on SkySQL (it’s free). However, you can also run a local instance using Docker. Here’s the snippet you need:

docker run --name xpand \
  -d \
  -p 3306:3306 \
  --ulimit memlock=-1 \
  mariadb/xpand-single

Databases are more fun when there’s data in them. A simple yet interesting demo database is available on this website. On Linux-like operating systems, run the following commands (change the IP address in the last command if your Xpand database is running somewhere else):

sudo apt install curl -y
curl https://www.mariadbtutorial.com/wp-content/uploads/2019/10/nation.zip --output nation.zip
unzip nation.zip
mariadb -h 127.0.0.1 -u xpand < nation.sql
rm nation.zip nation.sql

Remember to install pspg:

apt install pspg -y

Connect to the database using the mariadb SQL client with a custom and cooler prompt that shows “Xpand”:

mariadb -h 127.0.0.1 -u xpand --prompt="Xpand [\d]> " nation

I learned this tip from my colleague Patrick Bossman (Product Manager at MariaDB) during a webinar on MariaDB Xpand + Docker. I recommend watching it if you want to learn more.

Connecting to MariaDB Xpand using a custom prompt

Set the pspg pager for the current session:

pager pspg -s 14 -X --force-uniborder --quit-if-one-screen

A nice feature in pspg is that it shows the fancy text-based UI only when it makes sense (--quit-if-one-screen). So if your query returns only a few rows that fit in the screen, it will just show them right there on the screen as usual. For example, try running the following query:

select * from continents;

Nothing new to see here.

The pspg pager won't activate if only a few rows are shown

However, try the following:

select * from countries;

A navigable text-based interface allows you to explore the data more efficiently.

The pspg pager rendering data from MariaDB Xpand

You can search for a row, order, export to CSV, freeze columns, mark rows, and even use the mouse to interact with the tool, among other things.

Some of the menu options in pspg

I hope this tool helps you the next time you have to interact with a database via SSH and the command line. You can find more information about how to install pspg on your operating system, configuration options, and documentation on the GitHub repository for the project. If you want to learn more about distributed SQL and the MariaDB Xpand database, watch this short video, take a look at this datasheet, and explore some of the blog posts and documentation.