Home Applications irissqlcli

irissqlcli

This application is not supported by InterSystems Corporation. Please be notified that you use it at your own risk.
5
2 reviews
2
Awards
584
Views
0
IPM installs
3
3
Details
Releases
Reviews
Awards
Issues
Pull requests
Articles
A REPL for InterSystems IRIS SQL

What's new in this version

fixed issues with suggestions
fixed work with encrypted IRIS Server

A REPL for InterSystems IRIS SQL

This is a InterSystems IRIS client that does auto-completion and syntax highlighting.

Based on pgcli

irissqlcli

Quick Start

With Python

pip install -U irissqlcli

Or with homebrew

brew tap caretdev/tap
brew install irissqlcli

Usage

$ irissqlcli [uri]

or

$ irissqlcli iris://[user[:password]@][netloc][:port][/namespace]

or

$ irissqlcli iris+emb://[/namespace]

Examples:

$ irissqlcli iris://_SYSTEM:SYS@localhost:1972/USER

$ irissqlcli iris+emb:///

For more details:

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

Options:
-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
exit.
-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
prompt.
-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.

irissqlcli also supports environment variables for login options (e.g. IRIS_HOSTNAME, IRIS_PORT, IRIS_NAMESPACE, IRIS_USERNAME, IRIS_PASSWORD).

Features

The irissqlcli is written using prompt_toolkit_.

  • Auto-completes as you type for SQL keywords as well as tables and
    columns in the database.

  • Syntax highlighting using Pygments.

  • Smart-completion (enabled by default) will suggest context-sensitive
    completion.

    • SELECT * FROM <tab> will only show table names.
    • SELECT * FROM users WHERE <tab> will only show column names.
  • Pretty prints tabular data.

Config

A config file is automatically created at ~/.config/irissqlcli/config at first launch.
See the file itself for a description of all available options.

Docker

irisqlcli can be run from within Docker. This can be useful to try irissqlcli without
installing it, or any dependencies, system-wide.

To create a container from the image:

$ docker run --rm -ti caretdev/irissqlcli irissqlcli <ARGS>

To access InterSystems IRIS databases listening on localhost, make sure to run the
docker in “host net mode”. E.g. to access a database called “foo” on the
IRIS server running on localhost:1972 (the standard port):

$ docker run --rm -ti --net host caretdev/irissqlcli irissqlcli iris://_SYSTEM:SYS@localhost:1972/USER

or without host net mode

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

IPython

irisqlcli can be run from within IPython console. When working on a query,
it may be useful to drop into a irissqlcli session without leaving the IPython console, iterate on a
query, then quit irissqlcli to find the query results in your IPython workspace.

Assuming you have IPython installed:

$ pip install sqlalchemy~=1.4.0 ipython-sql sqlalchemy-iris

After that, run ipython and load the irissqlcli.magic extension:

$ ipython

In [1]: %load_ext irissqlcli.magic

or
$ ipython --ext irissqlcli.magic

Connect to a database:

In [2]: %irissqlcli iris://_SYSTEM:SYS@localhost:1972/USER
self.dialect <class 'sqlalchemy_iris.iris.IRISDialect_iris'>
sqlalchemy.MetaData <class 'sqlalchemy.sql.schema.MetaData'>
Server:  InterSystems IRIS Version 2022.2.0.368 xDBC Protocol Version 65
Version: 0.1.0
[SQL]_SYSTEM@localhost:USER> select top 10 table_schema,table_name from information_schema.tables
+--------------------+----------------+
| TABLE_SCHEMA       | TABLE_NAME     |
+--------------------+----------------+
| %CSP_Util          | CSPLogEvent    |
| %CSP_Util          | Performance    |
| %Calendar          | Hijri          |
| %Compiler_Informix | ConversionRule |
| %Compiler_Informix | ImportedObject |
| %Compiler_Informix | Symbol         |
| %Compiler_LG       | WrapperPropDef |
| %Compiler_TSQL     | sysSymbol      |
| %DeepSee           | IDList         |
| %DeepSee           | TempSourceId   |
+--------------------+----------------+
10 rows in set
Time: 0.074s
[SQL]_SYSTEM@localhost:USER>

Exit out of irissqlcli session with Ctrl + D and find the query results:

[SQL]_SYSTEM@localhost:USER>                                                                                                                                     
Goodbye!
Done.
Out[2]: 
[('%DocDB', 'Database'),
 ('%ExtentMgr', 'GUID'),
 ('%ExtentMgr', 'GlobalRegistry'),
 ('%ExtentMgr_Catalog', 'Extent'),
 ('%ExtentMgr_Catalog', 'Index'),
 ('%ExtentMgr_Catalog', 'Property'),
 ('%ExtentMgr_Catalog', 'ShardIdRanges'),
 ('%SYS_Maint', 'Bitmap'),
 ('%SYS_Maint', 'BitmapResults'),
 ('%SYS_Maint', 'Bitmap_Message')]

The results are available in special local variable _, and can be assigned to a variable of your
choice:

In [3]: my_result = _
Made with
Version
0.5.123 Mar, 2023
Python package
irissqlcli
Category
Developer Environment
Works with
InterSystems IRIS
First published
01 Feb, 2023