Using the PostgreSQL Pager With MariaDB Xpand
Learn how to use the pspg pager with MariaDB databases to visualize and interact with data in an effective way when performing DevOps tasks.
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
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
less pagers. You can set a pager through the environment variable
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:
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
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.
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
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
Connecting to MariaDB Xpand using a custom prompt
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.
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.
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
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.