26. The miraj-cli console

miraj-cli is the MIRAJ interactive SQL console: it opens a data folder (or connects to a running server, see 26.7), executes SQL statements and displays the results. It is used both for interactive exploration (REPL) and for running .sql scripts in batch mode (scheduled tasks, migrations, deployments).

26.1 General syntax#

miraj-cli [--root <folder>] [--config <file.xml>] [-d <database>] [-e "<SQL>"]
          [--lang en|fr|zh|hi|es|ar|pt|ru|de|ja] [--json] [--timing] [--no-history]
          [<file.sql>]
miraj-cli --host <host> [--port <port>] [--user <account>] [--password]
          [--protocol auto|miraj|postgresql] [TLS options] [-d <database>] [-e "<SQL>"]
          [--json] [--timing] [--no-history] [<file.sql>]

The option names of the embedded console are case-insensitive (--ROOT is equivalent to --root); the connection options (26.7) are written in lowercase.

Exit codes:

26.2 Command-line options#

Notes:

• -e and <file.sql> take precedence over the REPL, in that order: if -e is present, it is executed and the console exits without reading any file or opening a REPL; otherwise, if there is a .sql argument, that file is executed; otherwise, the REPL starts.
• -d <database> can be combined with -e or with a .sql file: the database is selected before execution.
• The executed .sql file may begin with a UTF-8 BOM, which is ignored automatically. On a data folder opened by the console, it is executed as a single batch of statements separated by ;, up to the first error (exit code 1); the DELIMITER command is not recognized there (error 1064): a script that contains it, like those of miraj-dump, is reloaded with miraj-dump restore or with the console connected to a server (26.7).
• The console embeds the engine: it opens the data folder itself and takes its exclusive lock (miraj.lck). As long as a miraj-server holds that folder, it cannot open it (exit code 2): connect to the server with the connection options (26.7).

26.3 Internal REPL commands#

In the REPL, an SQL statement can span several lines; it is sent to the engine as soon as a line ends with ;. The following commands, most of them prefixed with \, are not SQL and run immediately, without a semicolon:

Example of interrupting a query that runs too long:

miraj> SET cte_max_recursion_depth = 4294967295;
OK, 0 ligne(s) affectée(s)
miraj> WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 1000000000) SELECT COUNT(*) FROM s;
ERREUR 1317 (70100) : Query execution was interrupted
miraj> SELECT 2;
+---+
| 2 |
+---+
| 2 |
+---+
1 ligne(s)

26.4 Typical interactive use#

> miraj-cli --root C:\donnees\miraj --timing
MIRAJ 1.0.0 Entreprise - tapez \help pour l'aide, \q pour quitter.
miraj> \l
+--------------------+
| Database           |
+--------------------+
| information_schema |
| miraj              |
| performance_schema |
| shop               |
| sys                |
+--------------------+
5 ligne(s) (60.08 ms)
miraj> USE shop;
OK, 0 ligne(s) affectée(s) (56.24 ms)
shop> \d
+----------------+
| Tables_in_shop |
+----------------+
| articles       |
+----------------+
1 ligne(s) (60.12 ms)
shop> \d articles
+-------+---------------+------+-----+---------+----------------+
| Field | Type          | Null | Key | Default | Extra          |
+-------+---------------+------+-----+---------+----------------+
| id    | int(11)       | NO   | PRI | NULL    | auto_increment |
| nom   | varchar(50)   | NO   |     | NULL    |                |
| prix  | decimal(10,2) | YES  |     | NULL    |                |
| stock | int(11)       | YES  |     | 0       |                |
+-------+---------------+------+-----+---------+----------------+
4 ligne(s) (52.23 ms)
shop> SELECT nom, prix
    -> FROM articles
    -> WHERE prix > 10;
+--------+-------+
| nom    | prix  |
+--------+-------+
| Cahier | 12.90 |
+--------+-------+
1 ligne(s) (53.49 ms)
shop> \json
Résultats au format JSON
shop> SELECT nom, prix FROM articles WHERE prix > 10;
[
  {"nom": "Cahier", "prix": 12.90}
]
1 ligne(s) (58.58 ms)
shop> \q

The prompt shows the current database (shop>) or miraj> if no database is selected; it becomes -> while continuing an unfinished multi-line statement.

26.5 Running SQL scripts in batch mode#

Three ways to run a script without going through the interactive REPL:

# File given as an argument: executed, then the console exits
miraj-cli --root C:\donnees\miraj -d shop migration.sql

# Inline statement or script with -e (several statements separated by ;)
miraj-cli --root C:\donnees\miraj -e "USE shop; DELETE FROM articles WHERE stock = 0;"

# From the REPL, without leaving the current session
miraj> source migration.sql

A non-zero exit code signals a failed script; combined with --json, the output of a script executed with -e can be consumed directly by another program:

miraj-cli --root C:\donnees\miraj -d shop --json -e "SELECT id, nom FROM articles" > articles.json
if ($LASTEXITCODE -ne 0) { Write-Error "Échec du script" }

26.6 Practical tips#

• Persistent history: each statement submitted in the REPL is appended to %APPDATA%\MIRAJ\cli-history.sql (or ~/.config/MIRAJ/cli-history.sql outside Windows), with up to 1000 entries kept. This history is reloaded at the next startup and feeds \history and \!n. --no-history (or -e, which disables it automatically) ignores it, which is useful for scripts called in a loop from a scheduled task.
• Timing: --timing (or \timing during a session) is useful for quickly comparing two formulations of the same query without instrumenting a client.
• JSON format: --json (or \json) makes it easy to pass results to another command-line tool (ConvertFrom-Json in PowerShell, jq, etc.) without writing a dedicated client.
• Configuration file: at the first startup, miraj-cli writes a commented template miraj_config.xml in the root folder (or at the path given by --config) and regenerates miraj_default.xml, the reference of all engine variables. Editing miraj_config.xml then restarting the console applies the settings; an invalid or unreadable file makes the console exit with code 2. Unlike miraj-server, the console does not look for a miraj_config.xml next to its executable.
• Passwords: a statement that contains a password or an encryption key (CREATE USER … IDENTIFIED BY, SET PASSWORD, etc.) can still be replayed with \!n in the session but is never written to the history file.
• Errors: a failing statement displays ERREUR <code> (<SQLSTATE>) : <message>, followed by position : ligne L, colonne C when the parser knows it.
• Warnings: session warnings (truncations, default values applied, etc.) are displayed after the result of each statement that produced any.

26.7 Console on a running server#

With a connection option (--host, --port, --user, --password, --protocol, --ssl, --ssl-ca, --ssl-insecure, --no-ssl), the console connects to the server instead of opening a data folder (--root, --config and --lang then have no effect; the message language is the server's).

miraj-cli --port 7007 -d shop
miraj-cli --host 10.0.0.5 --user admin --password --ssl-ca ca.pem migration.sql

> miraj-cli --port 7007 -d essai
MIRAJ 1.0.0 - connecté (langage MIRAJ) - tapez \help pour l'aide, \q pour quitter.
essai> SELECT 1 = 1 AS vrai;
+------+
| vrai |
+------+
|    1 |
+------+
1 ligne(s)
essai> SELECT * FROM absente;
ERREUR 1146 (42S02) : Table 'essai.absente' doesn't exist
essai> SELECT CAST('abc' AS SIGNED) AS n;
+---+
| n |
+---+
| 0 |
+---+
1 ligne(s)
Avertissement : Truncated incorrect INTEGER value: 'abc'
essai> \q

The same session on an instance in PostgreSQL language:

MIRAJ 1.0.0 - connecté (langage PostgreSQL) - tapez \help pour l'aide, \q pour quitter.
miraj=> SELECT 1 = 1 AS vrai;
+------+
| vrai |
+------+
| t    |
+------+
1 ligne(s)
miraj=> SELECT * FROM absente;
ERREUR 42P01 : relation "absente" does not exist
miraj=> \q

• Instance language: recognized at connection time (--protocol auto, the default; miraj or postgresql force it, see 30.12). Statements are written in that language; the prompt shows it: database> in the MIRAJ language, database=> in the PostgreSQL language (miraj> / miraj=> with no current database).
• Errors: ERREUR 1146 (42S02) : … in the MIRAJ language, ERREUR 42P01 : … (SQLSTATE) in the PostgreSQL language; warnings and notices follow the result. Only those raised by the statement itself are displayed, once: an error is not repeated as a warning, nor after the following statements.
• -d <database>: the database is selected without displaying anything (the banner comes first); an unknown database displays the error, and the console returns exit code 1 when it exits.
• .sql file: split into statements in the instance's language (strings, comments, DELIMITER in the MIRAJ language; standard strings, E'…', $$…$$ in the PostgreSQL language), read as it goes and executed up to the first error (exit code 1), like miraj-dump restore, indicating the line of the faulty statement. A miraj-dump export can be reloaded this way.

  > miraj-cli --port 7007 e.sql
  OK, 0 ligne(s) affectée(s)
  ERREUR 1146 (42S02) : Table 'shop.absente' doesn't exist
    e.sql, ligne 2

• The \d, \l, \json, \timing commands, the history and source work the same way; a boolean result is displayed as t / f in the PostgreSQL language.
• Exit code 2: connection impossible.