Cloud Services - JDBC SQL Shell (sqlline)

Managing multiple cloud database deployments requires a flexible and powerful command-line interface. While there are many SQL clients available, sqlline stands out for its simplicity, Java-based portability, and advanced features like federated queries. In this post, we’ll look at how to package sqlline with the InterSystems JDBC driver for easy use against InterSystems Cloud SQL.

Why sqlline?

sqlline is a shell for issuing SQL to relational databases via JDBC. It’s lightweight and supports a wide range of commands for managing sessions, logging, and even connecting to multiple databases simultaneously.

Building the Shell Container

Packaging the shell as a Docker container ensures that all dependencies (including the specific JDBC driver and TLS certificates) are always available and correctly configured.

# Build the container
cd isc-cloud-jdbc-sql-shell
docker build -t isc-cloud-jdbc-sql-shell .

Usage Examples

Session Logging

You can easily log your entire session to a file for auditing or debugging.

-- Start logging to a local file
!set script /output/debug_omop_jdbc.log

Federated SQL

One of the most “earth shattering” features is the ability to connect to multiple deployments at once. By using the !connect command, you can add multiple sources and even run queries against !all of them.

Direct Management

Creating schemas and copying tables across deployments becomes a straightforward task from the shell.

# Example Output
ISC > CREATE SCHEMA OMOPCDM54BAK;
No rows affected (0.091 seconds)
ISC > CREATE TABLE OMOPCDM54BAK.person AS SELECT * FROM OMOPCDM54.person;
1,101,000 rows affected (5.84 seconds)

Conclusion

The combination of sqlline and the InterSystems JDBC driver provides a high-performance, developer-friendly way to manage your cloud data deployments. Whether you’re doing local development or orchestrating multi-cloud migrations, this SQL shell is an essential tool in your belt. 🎉