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
$ 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.
Remember I told you about encodings? 😉
$ 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)
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
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)
Works like a charm. Good! Plus one point! 🙂
Connect to a specific database
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=>
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.
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)
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!
$ 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
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.