usql: universal psql?

usql? But why?

usql is a universal command-line interface for many database. But why are we still using CLI (command line utilities) in the 21st century? And what is wrong with psql?

Despite the widespread availability and ease of use of GUI-based tools, many people still prefer to use command line utilities for a number of reasons. These reasons include the ability to automate repetitive tasks, the ability to quickly and easily perform tasks that would be more time-consuming or difficult using a GUI, and the ability to access and control the computer from a remote location.

One example of a popular command line utility is psql, the command-line application for PostgreSQL. This application allows users to interact with a PostgreSQL database by typing SQL commands, as well as various psql-specific commands that can be used to list databases, connect to a specific database, view table schemas, and more. The ability to quickly and easily execute SQL commands using psql can be particularly useful for database administrators, as well as developers who are working with PostgreSQL databases.

One potential downside of using psql as a command-line application is it can be more difficult to use for those who are not familiar with typing commands and navigating a text-based interface. Additionally, because psql does not have a graphical user interface, it can be more difficult to visualize and interact with the data, especially for large and complex datasets. Also you should not forget about the poor support provided by Windows, like lack of tab completion, issues with encoding, etc. For example, you can read about my fight with Sublime Text and psql under Windows.

Another potential con of psql is that it is only designed to work with PostgreSQL databases, so users who work with other types of databases need to use a different tool. This can be particularly frustrating for users who are accustomed to using psql and are familiar with its commands, but need to switch to a different tool in order to work with another kind of database.

Is usql really good?

I don't know yet, even though I've already committed to the project. I will open two terminals side by side. In one I'll be using psql and in another usql. And we will see how different and/or better the second one is. I'll skip the installation part and start the test immediately.

Connection

[shell]
$ psql -U pasha -h 127.0.0.1 timetable
psql (13.0)
WARNING: Console code page (65001) differs from Windows code page (1251)
8-bit characters might not work correctly. See psql reference
page 'Notes for Windows users' for details.
Type 'help' for help.
[/shell]

Remember I told you about encodings? 😉

[shell]
$ usql -U pasha -h 127.0.0.1 timetable
usql.exe: error: unknown short flag '-U', try --help

$ usql -u pasha -h 127.0.0.1 timetable
usql.exe: error: unknown short flag '-u', try --help

...

$ usql postgresql://pasha@127.0.0.1/timetable
error: pq: SSL is not enabled on the server

$ usql postgresql://pasha@127.0.0.1/timetable?sslmode=disable
Connected with driver postgres (PostgreSQL 13.0)
Type 'help' for help.

pg:pasha@127/Test=> conninfo
Connected with driver postgres (dbname=Test host=127.0.0.1 sslmode=disable user=pasha)

[/shell]

If you are used to connection parameters in psql then you're in trouble. Because usql only supports the DSN connection string. Which is absolutely fine for me, frankly speaking.

The SSL thing is not that good. I never enable it on my test environment and I believe the application should not force you to use SSL during the connection. But lib/pq library does exactly that and since it's used in usql, we have this behavior.

And the command prompt is corrupted. It should show the full host name or IP address instead of "127".

List databases

[shell]
pg:pasha@127/timetable=> l
List of databases
Catalog | Owner | Encoding | Collate | Ctype | Access privileges
----------------------+-----------+----------+-------------+-------------+-------------------
Test | pasha | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
clustergw | pasha | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
contrib_regression | pasha | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
pgx_test | pasha | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | pasha | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
regression | pasha | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | pasha | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/pasha +
| | | | | pasha=CTc/pasha
template1 | pasha | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/pasha +
| | | | | pasha=CTc/pasha
test | pasha | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
timetable | scheduler | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
МояУкраїнськаБазочка | pasha | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(11 rows)
[/shell]

Works like a charm. Good! Plus one point! 🙂

Connect to a specific database

[shell]
pg:pasha@172/timetable=> c Test
error: stat Test: no such file or directory

pg:pasha@127/timetable=> c postgresql://pasha@127.0.0.1/Test
error: pq: SSL is not enabled on the server

(not connected)=> c postgresql://pasha@127.0.0.1/Test?sslmode=disable
Connected with driver postgres (PostgreSQL 13.0)

pg:pasha@127/Test=>
[/shell]

OK, you need to use full DSN to connect to another database in the same cluster. Or use special notation, specifying the driver name and parameters. That's the price for support of many data sources including files and non relative databases. You must be precise as much as possible with a connection string.

Note the (not connected) prompt. You will see that a lot whenever a connection to another data source failed. usql even has a special Z command to close the database connection and switch to this mode. By comparison, psql will fail in a disconnected state only in rare cases of broken connections.

List objects in the database

According to the manual, many d commands are available. See below for a full list.

Let's try some, especially ss, which suppose to show stats for a table or a query. Interesting to see what will it return.

[shell]
pg:pasha@127/timetable=> d
List of relations
Schema | Name | Type
-----------+--------------------+----------
public | location | table
timetable | chain_chain_id_seq | sequence
...
timetable | task | table
(11 rows)

pg:pasha@127/timetable=> dt
List of relations
Schema | Name | Type
-----------+----------------+-------
public | location | table
...
timetable | task | table
(9 rows)

pg:pasha@127/timetable=> x
Expanded display is on.

pg:pasha@127/timetable=> df
List of functions
-[ RECORD 1 ]-------------------------------------------------------------------------------------------------------- Schema | public
Name | difference
Result data type | integer
Argument data types | text, text
Type | FUNCTION
...
-[ RECORD 36 ]--------------------------------------------------------------------------------------------------------
Schema | timetable
Name | validate_json_schema
Result data type | boolean
Argument data types | schema jsonb, data jsonb, root_schema jsonb
Type | FUNCTION

pg:pasha@127/timetable=> ss timetable.log
Column stats
Schema | Table | Name | Average width | Nulls fraction | Distinct values | Dist. fraction
-----------+-------+--------------+---------------+----------------+-----------------+----------------
timetable | log | ts | 0 | 0 | 0 | 1.0000
timetable | log | pid | 0 | 0 | 0 | 1.0000
timetable | log | log_level | 0 | 0 | 0 | 1.0000
timetable | log | client_name | 0 | 0 | 0 | 1.0000
timetable | log | message | 0 | 0 | 0 | 1.0000
timetable | log | message_data | 0 | 0 | 0 | 1.0000
(6 rows)
[/shell]

Some of the bugs were fixed immediately during this blog post writing! Wow! 🎉

And you can't list roles available. In psql there is the du command for that.

OK, how quickly can I get the info which commands from psql are present in usql?

List of common commands in usql and psql

Let's have some fun! We are programmers, after all!

[shell]
$ comm --total -12
<(usql -c '?' | grep -E -o '^s{2}\w+' | sort -u) <(psql --help=commands | grep -E -o '^s{2}\w+' | sort -u) C H T a c cd conninfo copy copyright crosstabview d da df di dm dn dp ds dt dv e echo f g gexec gset gx i ir l o p password prompt pset q qecho r set setenv t timing unset w warn watch x 8 54 47 total [/shell] The comm command is used to compare the contents of two files or output streams and find the lines that are common to both. The --total flag indicates that the command should also output a summary line showing the number of lines that are unique to each file or output stream. The -12 flag indicates that the command should suppress lines that are unique to the first or second file/output stream, thus it returns only lines that are common to both. Omit this option if you want to see a 3-column output and check which commands are available solely in either psql or usql.

The <(...) syntax is used to create a temporary named pipe that can be used as an input to another command. In this case, the usql -c "?" command is executed and its output is piped through grep to extract all commands (using the -E -o '^s{2}\w+' flags) and then sorted using the sort command. All duplicates are ignored using the -u flag. The same is done for the psql --help=commands command.

Bottom line, there are:

• 8 unique commands to usql;
• 54 unique commands to psql;
• 47 common commands in usql and psql.

But let's wrap up! This is supposed to be a "quick-look-at-usql" post.

Finally

In conclusion, both the usql and psql command-line applications offer a powerful tool for managing and querying databases. While they share many similar features, usql has a few additional capabilities such as

• the ability to query across multiple databases and data sources,
• syntax highlighting (what I love the most about it),
• and psql command compatibility (not complete, but nevertheless).

However, it should be noted that usql is still in active development and may have some limitations or bugs compared to the more established psql application. Overall, the choice between the two ultimately depends on the specific needs and preferences of the user.