Running a Query

TL;DR

How to open the shell mode?

Run anyquery in your terminal without any arguments.

How to run a query as a flag argument?

Run anyquery -q "SELECT * FROM table" in your terminal.

How to run a query from stdin?

Run echo "SELECT * FROM table" | anyquery.

How to run a query using the MySQL server?

Run anyquery server. See the documentation for more information.

How to exit the shell mode?

Type .exit in the shell.

Introduction

Thanks for installing anyquery. There are four different ways to run queries with anyquery:

Shell mode
As a flag argument
From stdin
Using the MySQL server

Shell mode

The most used mode is the shell. Just run

anyquery

to enter the shell mode in SQL mode by default. Example of running the shell Anyquery will prompt you for a SQL query, run it, and finally return the result until you exit. To exit the shell, type .exit.

You can also run initialization queries before entering the shell. For example, to create a table and insert some data, create a file init.sql with the following content:

CREATE TABLE IF NOT EXISTS my_table (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO my_table (name) VALUES ('Alice');
INSERT INTO my_table (name) VALUES ('Bob');

Then run

anyquery --init init.sql

to run the initialisation queries before entering the shell.

Commands

SQLite commands

The shell supports a subset of the SQLite shell commands. Here is a list of the supported commands:

.cd DIRECTORY - Change the working directory to DIRECTORY.
.databases - List the currently attached databases.
.help - Show the help message.
.exit - Exit the shell.
.indexes - List all the indexes.
.mode MODE - Change the output mode. The commonly used modes are plain, pretty, json, markdown, and html.
.json - Alias for .mode json.
.jsonl - Alias for .mode jsonl.
.csv - Alias for .mode csv.
.output FILE - Redirect the output to FILE.
.print STRING - Print STRING.
.shell COMMAND - Run COMMAND in the current directory using exec. Therefore, no shell globbing, piping, etc., is supported.
.tables - List all the tables.
.language LANG - Change the query language. The supported languages are sql, prql, and pql.
.prql - Alias for .language prql.
.pql - Alias for .language pql.
.sql - Alias for .language sql.

MySQL commands

The shell also supports a subset of the MySQL shell commands. Here is a list of the supported commands:

SHOW DATABASES - List the current databases.
SHOW TABLES - List the tables in the current database.
SHOW CREATE TABLE table - Show the create table statement for the table.
SHOW CREATE VIEW view - Show the create view statement for the view.
DESCRIBE table - Describe the table (does not work with file tables).
EXPLAIN table - Alias for DESCRIBE table.

PostgreSQL commands

The shell also supports a subset of the PostgreSQL shell commands with slightly different semantics. Here is a list of the supported commands:

\l - List the current databases.
\dt - List the tables in all databases.
\d table - Describe the table.
\d+ table - Describe the table with more details.
\dv - List the views in all databases.
\d+ view - Describe the view with more details.
\di - List the indexes in all databases.

Keyboard shortcuts

The shell’s line editor is based on readline, so the usual bash-style shortcuts work and your ~/.inputrc is honored (see the GNU readline documentation). Here are the most useful ones in the default Emacs mode:

Shortcut	Action
`Ctrl-R`	Search history backwards
`Ctrl-X Ctrl-E`	Edit the current query in `$VISUAL` / `$EDITOR` (falls back to VS Code, nano, vim or vi, or to VS Code and Notepad on Windows)
`Ctrl-_`	Undo
`Ctrl-A` / `Ctrl-E`	Move to the start / end of the line
`Alt-B` / `Alt-F`	Move one word back / forward
`Ctrl-K`	Cut to the end of the line
`Ctrl-Y`	Paste the last cut
`Alt-Backspace`	Delete the previous word
`Alt-U`	Uppercase the word (e.g. `select` becomes `SELECT`)
`Alt-#`	Comment out the line
`Ctrl-L`	Clear the screen
`Ctrl-C` / `Ctrl-D`	Quit the shell

Syntax highlighting

The shell highlights SQL as you type, with the Atom One Dark colors on truecolor terminals with a dark background, and basic terminal colors otherwise. Set the NO_COLOR environment variable to disable it.

Completion

Press Tab to show a menu of suggestions (e.g. table name, file paths, etc.). Press Tab again to cycle through the matches or Shift-Tab to cycle backwards.

Plugin tables and their columns show their description in the menu. When a table takes parameters, they are shown below the prompt as you type. To get suggestions as you type, add this to your ~/.inputrc:

$if anyquery
  set autocomplete on
$endif

History

The history is saved across sessions in ~/.local/state/anyquery/history on Linux, ~/Library/Application Support/anyquery/history on macOS, and %LOCALAPPDATA%\anyquery\history on Windows. Set the ANYQUERY_HISTORY environment variable to use another file, or set it to an empty value to disable saving. Queries starting with a space are not saved.

As you type, the rest of the last matching query from your history is shown in grey after the cursor. Press → (Right arrow) at the end of the line to accept it, or keep typing to ignore it. To turn it off, add set history-autosuggest off inside the $if anyquery block of your ~/.inputrc.

Vi mode

To use Vi keybindings, add this line to your ~/.inputrc:

set editing-mode vi

Press Esc to enter normal mode and i to go back to insert mode. In normal mode, v opens the query in $VISUAL / $EDITOR, and text objects, registers, and visual mode are supported.

This setting also applies to other readline programs, such as bash. To enable it only for anyquery, wrap it in an $if anyquery block:

$if anyquery
  set editing-mode vi
$endif

As a flag argument

To run a query as a one-off command without entering the shell, you can use the -q flag. For example, to run SELECT * FROM table, you can run

anyquery -q "SELECT * FROM table"

By default, the output is in the pretty mode unless the result is piped to another command. In that case, the output is in plain mode (i.e. tab-separated values without headers).

# Pretty mode because the output is not piped
(base) julien@MacBook-Air-Julien ~ % anyquery -q "SELECT owner, name FROM github_my_stars LIMIT 4"
+-------------+--------------+
|    owner    |     name     |
+-------------+--------------+
| CloudCannon | pagefind     |
| surjithctly | astro-navbar |
| typst       | typst        |
| noodle-run  | noodle       |
+-------------+--------------+
4 results

# Plain mode because the output is piped
(base) julien@MacBook-Air-Julien ~ % anyquery -q "SELECT owner, name FROM github_my_stars LIMIT 4" | cat
CloudCannon     pagefind
surjithctly     astro-navbar
typst   typst
noodle-run      noodle

As specified in the reference, you can change the output mode using the --format flag. As a shortcut, you can use the --json, --plain, and --csv flags to respectively output in JSON, plain, and CSV formats.

To use a different query language, you can use the --language flag. The supported languages are sql, prql, and pql.

From stdin

You can also run a query from stdin. As soon as anyquery encounters a semicolon, it will run the query, which is useful for streaming data. For example, to run SELECT * FROM table, you can run

echo "SELECT * FROM table" | anyquery

All flags and options specified in the previous section are also available when running a query from stdin.

Using the MySQL server

anyquery can also act as a MySQL server so that you can use your favorite MySQL client to connect to it. To start the server, run

anyquery server

By default, the server listens on 127.0.0.1:8070. You can change the host and port using the --host and --port flags. For example, to listen on 0.0.0.0:3306, you can run

anyquery server --host "0.0.0.0" --port 3306

Still by default, it will open a new database named anyquery.db in the current directory. You can change the database using the --database flag. To open an in-memory database, pass the flag --in-memory. And to open in read-only mode, pass the flag --readonly.

# Open the database my_db
anyquery server --database my_db
# Open an in-memory database
anyquery server --in-memory
# Open the database my_db in read-only mode
anyquery server --database my_db --readonly

To connect to the server, you can use any MySQL client. For example, to connect using the mysql client, you can run

mysql -h "127.0.0.1" -P 8070