· Feb 6, 2023 4m read

Welcome irissqlcli - advanced terminal for IRIS SQL

Let me introduce my new project, which is irissqlcli, REPL (Read-Eval-Print Loop)  for InterSystems IRIS SQL 

  • Syntax Highlighting
  • Suggestions (tables, functions)
  • 20+ output formats
  • stdin support
  • Output to files 

Install it with pip

pip install irissqlcli

Or run with docker

docker run -it caretdev/irissqlcli irissqlcli iris://_SYSTEM:SYS@host.docker.internal:1972/USER

Connect to IRIS

$ irissqlcli iris://_SYSTEM@localhost:1972/USER -W
Password for _SYSTEM:
Server:  InterSystems IRIS Version 2022.3.0.606 xDBC Protocol Version 65
Version: 0.1.0
[SQL]_SYSTEM@localhost:USER> select $ZVERSION
| Expression_1                                                                                            |
| IRIS for UNIX (Ubuntu Server LTS for ARM64 Containers) 2022.3 (Build 606U) Mon Jan 30 2023 09:05:12 EST |
1 row in set
Time: 0.063s
[SQL]_SYSTEM@localhost:USER> help
| Command  | Shortcut          | Description                                                |
| .exit    | \q                | Exit.                                                      |
| .mode    | \T                | Change the table format used to output results.            |
| .once    | \o [-o] filename  | Append next result to an output file (overwrite using -o). |
| .schemas | \ds               | List schemas.                                              |
| .tables  | \dt [schema]      | List tables.                                               |
| \e       | \e                | Edit command with editor (uses $EDITOR).                   |
| help     | \?                | Show this help.                                            |
| nopager  | \n                | Disable pager, print to stdout.                            |
| notee    | notee             | Stop writing results to an output file.                    |
| pager    | \P [command]      | Set PAGER. Print the query results via PAGER.              |
| prompt   | \R                | Change prompt format.                                      |
| quit     | \q                | Quit.                                                      |
| tee      | tee [-o] filename | Append all results to an output file (overwrite using -o). |
Time: 0.012s

$ irissqlcli --help
Usage: irissqlcli [OPTIONS] [URI] [USERNAME]

  -h, --host TEXT         Host address of the IRIS instance.
  -p, --port INTEGER      Port number at which the IRIS instance is listening.
  -U, --username TEXT     Username to connect to the IRIS instance.
  -u, --user TEXT         Username to connect to the IRIS instance.
  -W, --password          Force password prompt.
  -v, --version           Version of irissqlcli.
  -n, --nspace TEXT       namespace name to connect to.
  -q, --quiet             Quiet mode, skip intro on startup and goodbye on
  -l, --logfile FILENAME  Log every query and its results to a file.
  --irissqlclirc FILE     Location of irissqlclirc file.
  --auto-vertical-output  Automatically switch to vertical output mode if the
                          result is wider than the terminal width.
  --row-limit INTEGER     Set threshold for row limit prompt. Use 0 to disable
  -t, --table             Display batch output in table format.
  --csv                   Display batch output in CSV format.
  --warn / --no-warn      Warn before running a destructive query.
  -e, --execute TEXT      Execute command and quit.
  --help                  Show this message and exit.

or in Python Embedded mode (requires %Service_CallIn enabled)

$ irissqlcli iris+emb:///USER
Server:  IRIS for UNIX (Ubuntu Server LTS for ARM64 Containers) 2022.2 (Build 368U) Fri Oct 21 2022 16:39:41 EDT
Version: 0.1.0

It supports stdin, so you can pipe some SQL file with a bunch of SQL queries and irissqcli commands. For instance, this command, will produce 3 files in different formats (out of more than 20 formats available)

$ cat <<EOF | irissqlcli iris://_SYSTEM:SYS@localhost:1972/USER

.mode csv;
tee -o test.csv;
from information_schema.tables 

.mode latex;
tee -o test.tex;
from information_schema.tables 

.mode html;
tee -o test.html;
from information_schema.tables 


Additionally it's possible to run a web terminal with docker

docker run -d --name irissqlcli \
  --restart always \
  -p 7681:7681\
  caretdev/irissqlcli-web irissqlcli iris://_SYSTEM:SYS@host.docker.internal:1972/USER


And with docker-compose

version: '3'
    image: intersystemsdc/iris-community
      - 1972
      - 52773
      - -a
      - '##class(Security.Users).UnExpireUserPasswords("*")'
    image: caretdev/irissqlcli-web
      - 7681:7681
      - IRIS_HOSTNAME:iris
      - IRIS_PORT=1972

Please vote for the project in the contest

Discussion (20)3
Log in or sign up to continue

In my point of view, this app is a game changer.

Let me explain why.

Imagine you have an DDL statement that you want to execute on a database.

File: misc/init.sql

CREATE TABLE test.formation (
    name varchar(50) NULL,
    room varchar(50) NULL

INSERT INTO test.formation
(name, room)
VALUES('formation1', 'salle1');

You can execute this statement in the irissqlcli app by using the following command:

irissqlcli iris://_SYSTEM@localhost:51776/USER -W < misc/init.sql

Before this app, you had to use the terminal to execute this command.

cat <<EOF | iris session iris

do \$SYSTEM.SQL.Schema.ImportDDL("/irisdev/app/misc/init.sql",.log)
if log'="" { write "Error: ",log }

Let's compare the two commands:

tool command comment
irissqlcli irissqlcli iris://_SYSTEM@localhost:51776/USER -W < misc/init.sql One line, easy to read, eeasy to maintaine
terminal cat <<EOF \| iris session iris <br> do \$SYSTEM.SQL.Schema.ImportDDL("/irisdev/app/misc/init.sql",.log) <br>if log'="" { write "Error: ",log } <br>h <br>EOF multiple line, arkward to read, hard to maintain, must understand the syntax of the terminal, had to be on the same machine

One last thing, if I want to extract the data from a table, I can use the following command:

irissqlcli iris://_SYSTEM@localhost:51776/USER -W -e "SELECT * FROM test.formation" --csv

This command will return the following result:


Try to do the same thing with the terminal ;).

cat <<EOF | iris session iris
do ##class(%SQL.Statement).%ExecDirect(,"SELECT * FROM test.formation").%DisplayFormatted("CSV",,,.filesUsed)
do ##class(%Stream.FileCharacter).%OpenId(filesUsed(1)).OutputToDevice()

@Guillaume Rongier 
your terminal example is just poor , (sorry!)  pauvre
I do it his way, easy to read and easy to follow, (your notation)

SQL Command Line Shell
The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
SAMPLES>>  << entering multiline statement mode >>
        1>>CREATE TABLE test.formation (
        2>>    name varchar(50) NULL,
        3>>    room varchar(50) NULL
1.      CREATE TABLE test.formation (
            name varchar(50) NULL,
            room varchar(50) NULL
0 Rows Affected
statement prepare time(s)/globals/lines/disk: 0.0122s/1724/14836/0ms
          execute time(s)/globals/lines/disk: 0.4004s/68198/594881/177ms
                          cached query class: %sqlcq.SAMPLES.cls47
SAMPLES>>  << entering multiline statement mode >>
        1>>INSERT INTO test.formation
        2>>(name, room)
        3>>VALUES('formation1', 'salle1')
1.      INSERT INTO test.formation
        (name, room)
        VALUES('formation1', 'salle1')
1 Row Affected
statement prepare time(s)/globals/lines/disk: 0.0113s/2622/28346/0ms
          execute time(s)/globals/lines/disk: 0.0009s/13/143/0ms
                          cached query class: %sqlcq.SAMPLES.cls48

I'm not tottaly agree with you.

Imagine you are new to iris, you just wish to play with our SQL engine.

With your proposal, you have to know how to connect to a terminal and know the magic command (zzq, BTW i was not aware of this one) to run a query.

Then, you can't do that remotely or with out SSH.

To finish, with your example, you don't show us how to do it from a Shell.

Was able to install with Brew:

$ brew install caretdev/tap/irissqlcli

AEMBP14ESHVAROV:~ eshvarov$ irissqlcli -h localhost -p 1972 -u _SYSTEM -W

Password for _SYSTEM: 

Server:  InterSystems IRIS Version 2022.2.0.368 xDBC Protocol Version 65

Version: 0.4.0


select 1


| HostVar_1 |


| 1         |


1 row in set

Time: 0.044s


Thank you @Dmitry Maslennikov 

This is a great tool.

I am wondering if it will work for everyone. In the world of finance, you do not get SSH access to servers.
Most of the times the super-server port is also closed off for everything except the web gateway.

If the web version can be run on it, it is great - but in banking environment, not everyone is on the "containerised" buzz yet, so this will not be allowed.

Sure, I can probably install and configure the package and set up the web application.

Now there are two things left I want to raise:

  1. Multi-line SQL without having SSH access. Also do not have SCP or SFTP access.
    1. If this is present, and I have missed it, I apologise.
  2. Database transactions.
    1. I have a SQL shell I built a long time ago, which worked with db transactions.
    2. Doing DML, you may want to verify the results before committing it to the DB and have to option to rollback.
      1. This will be really great if the app can handle it.

Access only by web, looks a bit odd, but possible. In my example of irissqlcli-web, I've just used external tool ttyd, which I'm sure can be easily tied with any webserver in use. ttyd is deamon process, supposed to be launched on the server, it opens some port, which can be used in webserver to as a proxy endpoint.

  • Multiline SQL, yes. It supported in a multiple ways, not sure what exactly do you mean
    • A file a bunch of SQL Queries, can be passed to the tool itself as stdin, and it will execute it, along with the tools commands
    • Just paste a bunch of queries, each query or command ended with semicolon. And it will work too.
  • Transactions, yes, sure, everything possible with xDBC, should work here too

In the example below, I've just copied and pasted many lines there and executed them. And done, including changing the output format, rollback transaction

As for verifying results before coming, this sounds wonderful. I don't have anything yet, in way of automation. But I don't see that it's not possible to implement. If you have any examples of how it has probably already been implemented somewhere, let me know, so, do not reinvent the wheel.

And all the features requests are welcome in the GitHub repo

And as for deeper integration SQL and IRIS, I'm sure it is worth looking at dbt project. I'm part of the implementation IRIS support, there.

How to setup a secure connection :

  1. Generate a key pair :
openssl req -x509 -nodes -days 1 -newkey rsa:2048 -subj /CN=* -keyout /irisdev/app/server.key -out /irisdev/app/server.crt
  1. Configure IRIS super server with SSL/TLS :

  1. Use certificate to connect :
python3 -m irissqlcli iris://SuperUser:SYS@localhost:33782/USER -c server.crt