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.