About

Please note: this package should be considered "alpha" - while you are more than welcome to use it, you should expect that getting it to work for you will require quite a bit of self-help on your part. At the same time, it may be a great opportunity for those that want to contribute.

odbc-cli is an interactive command line query tool intended to work for DataBase Management Systems (DBMS) supported by ODBC drivers.

As is the case with the remaining clients derived from the python prompt toolkit library, odbc-cli also supports a rich interactive command line experience, with features such as auto-completion, syntax-highlighting, multi-line queries, and query-history.

Beyond these, some distinguishing features of odbc-cli are:

  • Multi DBMS support: In addition to supporting connections to multiple DBMS, with odbc-cli you can connect to, and query multiple databases in the same session.
  • An integrated object browser: Navigate between connections and objects within a database.
  • Small footprint and excellent performance: One of the main motivations is to reduce both the on-disk, as well as in-memory footprint of the existing Microsoft SQL Server client, while at the same time improve query execution, and time spent retrieving results.
  • Out-of-database auto-completion: Mostly relevant to SQL Server users, but auto-completion is "aware" of schema and table structure outside of the currently connected catalog / database.
odbc-cli objectbrowser

odbc-cli objectbrowser

Usage

Starting / Exiting the client

After installing the python module, you can fire up the client by executing

odbc-cli

or, alternatively:

python -m odbcli.__main__

You may exit the client at any point by entering [CTRL+q]

Connecting to databases

odbc-cli discovers and populates a list of available connections by querying your ODBC driver manager. Therefore, in order to see your connections in the client, you must have them listed in the INI configuration files appropriate to your driver manager. For example, with unixODBC, consider starting the client by specifying the following environment variables:

ODBCSYSINI=/path/to/driver/configuration/file ODBCINSTINI=name-of-driver-configuration-file ODBCINI=/path/and/name/of/user/dbms/configuration/file odbc-cli

The client starts with the object browser visible to the right, listing all the connections discovered by the driver manager.

Example: My unixODBC configuration files

On my Debian box the default location for the driver configuration file is /etc/odbcinst.ini - its content may look like:

[SQLite3]
Description=SQLite3 ODBC Driver
Driver=libsqlite3odbc.so
Setup=libsqlite3odbc.so
UsageCount=1

[MySQL ODBC 8.0 ANSI Driver]
Driver=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc8a.so
UsageCount=1

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.5.so.2.1
UsageCount=2

[PostgreSQL Unicode]
Description=PostgreSQL ODBC driver (Unicode version)
Driver=psqlodbcw.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1

My DSN configuration file is also in its default location /home/[user]/.odbc.ini - its content may look like:

[sqlite_db]
Description = SQLite DB
Driver = SQLite3
Database = /path/to/on/disk/database.db
Timeout = 2000

[mysql_db]
Description = MySQL DB
Driver = MySQL ODBC 8.0 ANSI Driver
Server = my-mysql2

[mssql_oem_db]
Description = MSSQL
Driver = ODBC Driver 17 for SQL Server
Server = mymssql
Port = 1433

[postgres_db]
Description = PostgresUnicode
Driver = PostgreSQL Unicode
Servername = mypostgres
Port = 5432
Database = adventureworks

Since my configuration files are at their default locations, I do not need to populate any environment variables to alert unixODBC to where my configuration is stored, rather I jump into the client with a simple odbc-cli invocation.

The object browser

This panel can be shown/hidden by using the [CTRL+t] key combination. Initially, only the available connections are listed - these play the role of "root" nodes in a hierarchical tree of database objects. You can navigate between these objects using the arrow keys: in addition to the up and down keys, pressing the right arrow is equivalent to asking the object to "expand" and itemize enclosing objects, be it catalogs, schemas, or tables. Pressing the left arrow whilst in the object browser is equivalent to asking the object to "collapse" and hide its "children". Therefore, when a connection is selected, pressing the right arrow will bring up a username/password connection dialog. After successfully connecting to the database, you can expand/collapse the connection, catalogs, schemas, and tables - expanding a table brings up information about its column structure.

This buffer is searchable - pressing the search key appropriate to your editing mode (vim or emacs) brings up a search bar at the top of the buffer that you can then use to zero-in on the particular table, for example, that you may be looking for.

Pressing the left arrow when a connection that is fully collapsed is highlighted brings up a disconnect dialog. Pressing the return key on connections that are connected allows you to toggle each of them as "active" - meaning, the one used to execute queries in the main window against.

The preview window

Pressing the return key when a table or a view is highlighted in the object browser, brings up the preview window. There are three elements in this window: the input/filtering buffer, the Done button, and the main output buffer. Pressing the tab button will toggle the focus between these three elements.

  • You can preview table contents - the equivalent of SELECT * - by simply pressing return while the cursor is in the filtering input buffer at the top of the preview window (equivalent to no filtering, and an unadulterated SELECT). Repeatedly pressing the return key will page through the results.
  • In addition you can filter (or order) the output by entering a WHERE ... or similar qualifiers in the input buffer. The filtering box has two great features (also shared by the main query execution buffer):
    • Suggestions from history: It will match any text you have typed in the filtering box to a most recent filtering you have previously entered for the same table. You will appreciate this feature if you often come back to preview the same table and use the same filter. At this time, only the most recent filter (matching the current input) is suggested, and there is no way to recall a longer history.
    • Auto-completion: When typing "WHERE ", for example you should see a completion window offering up the columns of the table you are previewing. Note: Connections are never cloned in odbc-cli; therefore if you are already paging through results (which, for the connection translates as being in the midst of a fetch operation), completion may not work as. This is because in order to display a menu of columns, for example, I/O to the server may be needed, which in turn may be unavailable in the midst of a fetch.

Tab-ing over to the Done button and pressing return will close the preview window (and cancel any pending fetch).

odbc-cli tablepreview

odbc-cli tablepreview

The main query execution buffer

Closing the object browser ([CTRL+t]) leaves you focused on the main buffer allowing you to execute statements against the currently active connection. After executing a query the results are piped to the system pager, or less if it is available. If it is not, and there is no system pager configured, consider pip-installing pypager.

This buffer sports both, an as-you-type auto-completion, as well as suggestions drawn from the history file.

Cancelling queries

Outside of queries to support auto completion, all statements are executed in a separate thread. Whether in the main execution buffer, or in the table preview, this enables the user to gracefully cancel a long-running query by pressing [CTRL+c].

Configuring the client

A limited set of configuration options are made available in the config file in the directory:

  • If the XDG_CONFIG_HOME environment variable is defined then, within its odbcli sub-folder.
  • Else, if the platform is Windows, then in os.getenv("USERPROFILE") + "\\AppData\\Local\dbcli\\odbcli\\"
  • Otherwise in ~/.config/odbcli/

TODO: Document the options.

Acknowledgements

This project would not be possible without the most excellent python prompt toolkit library. In addition, idea and code sharing between the clients that leverage this library is rampant, and this project is no exception - a big thanks to all the dbcli contributors.