Usql offers a single user interface for managing multiple database systems
Connecting to Databases
Usql opens a database connection by parsing a string and passing its content to the appropriate database driver. Database connection strings (aka "data source names" or DSNs) can be passed to usql directly on the command line or at any moment during an interactive session. In an interactive session, you can pass all the necessary parameters (driver, database name, etc.) separately via the \c
meta command. In general, DSNs that connect to multi-user database servers like PostgreSQL or MariaDB have the following structure:
driver+transport://user:pass@host/dbname
The driver
part is the name of the driver you wish to use, which corresponds to the type of database, or any of its aliases allowed by usql. When connecting to a PostgreSQL database, for example, the driver may be postgres
or pg
, whereas with MySQL, you should use mysql
or just my
. The user
, pass
(that is, password
), and dbname
components are self-explanatory, and the host
is the name or IP address of the computer hosting the database. If that computer is the same as the computer where you run usql, host
should be the same as localhost
.
The transport
part is not mandatory, and unless you work with a database that requires it, or with non-default connection protocols, you may never need it.
To connect to a database from the Linux command line, use the following command:
#> usql my://marco:mypassword@localhost/customers
or, after launching usql, from its own prompt:
\c my://marco:mypassword@localhost/customers
The steps are slightly different if you want to connect to a serverless, file-based database like SQLite 3. In this case, the DSN has a much simpler structure:
driver:/path/to/file-on-disk
where the driver could be sqlite3
, sq
, or file
, or the command might not specify a driver. Either of the following commands would open an already existing SQLite3 database contained in the single file $HOME/my-sqlite-db.sqlite3
:
#> usql sqlite3://$HOME/my-sqlite-db.sqlite3 #> usql $HOME/my-sqlite-db.sqlite3
The second command will work without errors only if the $HOME/my-sqlite-db.sqlite3
file already exists and is, indeed, an SQLite3 database. If your intention is to make usql create a new, empty SQLite3 database and save it into a file with the specified name, you must use the first command, which includes a recognizable driver name. In this case, be sure to use the right number of slashes: the sq:
and sqlite3:
driver names want two slashes after the colon, whereas the file:
name only requires one:
#> usql sqlite3://$HOME/my-sqlite-db.sqlite3 #> usql file:/$HOME/my-sqlite-db.sqlite3
To close an open connection from inside usql, just type \Z
. In any moment, you can also verify the parameters of the connection you are using by typing \conninfo
.
Editing and Reusing Queries
Once you are connected to a database from inside a usql session, you can communicate with it just as if you were using a native client. Usql also keeps the current query in a dedicated buffer that you can edit and reuse. Use the \e
meta command to edit the buffer and the \w
command to write a query into the buffer. Optional arguments for a file name and line number allow you to edit queries saved to disk in previous sessions. The \p
command shows the buffer contents and \r
resets the buffer.
Use the \g
command to re-execute the query in the buffer. If you also want to execute every value of the result, type \gexec
. The \gset
command stores the result of the query in usql variables for further reuse.
Output Formatting
A nice feature of usql is its many options for how to print the result of a query. You can print in CSV, JSON, or table format, with either plain text or HTML. Print to CSV or JSON with the -C
and -J
options. The options for printing to tables are too many to describe, but they are mostly self-explanatory. \H
toggles the HTML output mode and \T
, followed by a string of valid HTML table attributes, will apply the attributes to the current table.
« Previous 1 2 3 Next »
Buy Linux Magazine
Subscribe to our Linux Newsletters
Find Linux and Open Source Jobs
Subscribe to our ADMIN Newsletters
Support Our Work
Linux Magazine content is made possible with support from readers like you. Please consider contributing when you've found an article to be beneficial.
News
-
Arch Linux 2023.12.01 Released with a Much-Improved Installer
If you've ever wanted to install Arch Linux, now is your time. With the latest release, the archinstall script vastly simplifies the process.
-
Zorin OS 17 Beta Available for Testing
The upcoming version of Zorin OS includes plenty of improvements to take your PC to a whole new level of user-friendliness.
-
Red Hat Migrates RHEL from Xorg to Wayland
If you've been wondering when Xorg will finally be a thing of the past, wonder no more, as Red Hat has made it clear.
-
PipeWire 1.0 Officially Released
PipeWire was created to take the place of the oft-troubled PulseAudio and has finally reached the 1.0 status as a major update with plenty of improvements and the usual bug fixes.
-
Rocky Linux 9.3 Available for Download
The latest version of the RHEL alternative is now available and brings back cloud and container images for ppc64le along with plenty of new features and fixes.
-
Ubuntu Budgie Shifts How to Tackle Wayland
Ubuntu Budgie has yet to make the switch to Wayland but with a change in approaches, they're finally on track to making it happen.
-
TUXEDO's New Ultraportable Linux Workstation Released
The TUXEDO Pulse 14 blends portability with power, thanks to the AMD Ryzen 7 7840HS CPU.
-
AlmaLinux Will No Longer Be "Just Another RHEL Clone"
With the release of AlmaLinux 9.3, the distribution will be built entirely from upstream sources.
-
elementary OS 8 Has a Big Surprise in Store
When elementary OS 8 finally arrives, it will not only be based on Ubuntu 24.04 but it will also default to Wayland for better performance and security.
-
OpenELA Releases Enterprise Linux Source Code
With Red Hat restricting the source for RHEL, it was only a matter of time before those who depended on that source struck out on their own.