25. The miraj-cli console

miraj-cli is the Miraj interactive SQL console: it opens a data folder, 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).

25.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>]

Option names are case-insensitive (--ROOT is equivalent to --root).

Exit codes:

25.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.
• 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 a network client (mysql, see chapter 26).

25.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:

25.4 Typical interactive use#

> miraj-cli --root C:\data\miraj --timing
Miraj 1.0 Enterprise - tapez \help pour l'aide, \q pour quitter.
miraj> \l
+----------+
| Database |
+----------+
| shop     |
+----------+
1 ligne(s) (0.42 ms)
miraj> USE shop;
OK, 0 ligne(s) affectée(s) (0.10 ms)
shop> \d
+-----------+
| Table     |
+-----------+
| articles  |
+-----------+
1 ligne(s) (0.08 ms)
shop> \d articles
+--------+---------------+------+-----+---------+----------------+
| Field  | Type          | Null | Key | Default | Extra          |
+--------+---------------+------+-----+---------+----------------+
| id     | int           | NO   | PRI | NULL    | auto_increment |
| nom    | varchar(50)   | NO   |     | NULL    |                |
| prix   | decimal(10,2) | YES  |     | NULL    |                |
+--------+---------------+------+-----+---------+----------------+
3 ligne(s) (0.15 ms)
shop> SELECT nom, prix
    -> FROM articles
    -> WHERE prix > 10;
+---------+-------+
| nom     | prix  |
+---------+-------+
| Cahier  | 12.90 |
+---------+-------+
1 ligne(s) (0.31 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) (0.12 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.

25.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:\data\miraj -d shop migration.sql

# Inline statement or script with -e (several statements separated by ;)
miraj-cli --root C:\data\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:\data\miraj -d shop --json -e "SELECT id, nom FROM articles" > articles.json
if ($LASTEXITCODE -ne 0) { Write-Error "Échec du script" }

25.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: line L, column 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.