SQLLine – One CLI for connecting to Relational Databases

If you are in the Python world (or not), you probably are using dbcli/pgcli for accessing the Postgres database. pgcli is Postgres CLI with autocompletion and syntax highlighting. Similarly, there are CLIs for MySQL, SQLite, Redis, etc. If you are in the mood to explore other CLI tools, you can trey SQLLine. It's one CLI for many RDBMS.

Command-line shell for issuing SQL to relational databases via JDBC.

A fork of Marc Prud'hommeaux's sqlline project, also incorporating changes made by the LucidDB project, now modernized, mavenized and forkable in github

SQLLine

It's a single CLI that works with any database supporting JDBC connection. Almost all open-source relational databases today support JDBC.

Java Database Connectivity (JDBC) is an application programming interface (API) for the programming language Java, which defines how a client may access a database. It is a Java-based data access technology used for Java database connectivity. It is part of the Java Standard Edition platform.

Wikipedia

JDBC is somewhat similar to DB-API defined as per PEP 249 -- Python Database API Specification v2.0, in Java world. So any database provider can use this specification to build and provide their Driver. And everyone else can just use it.

Now that the definitions are out of our way. SQLLine comes as a single jar, which you can download and run. But for the database support, you will have to install the JDBC Driver jars. In general, the database providers supply them. There are also community produced JDBC drivers. Here are the quick links to the JDBC drivers of Postgres, MySQL, MariaDB, SQLite (alt), H2, Clickhouse. Once you download them, you can put them in the same folder as SQLLine. SQLLine will load them when it starts.

But I use JBang, so I run or install it using Jbang. You can run by giving JBang the Maven coordinates of SQLLine.

jbang run  \
--deps org.apache.calcite:calcite-core:1.28.0 \
--deps org.apache.calcite:calcite-avatica:1.6.0 \
--deps com.h2database:h2:2.0.204 \
--deps mysql:mysql-connector-java:8.0.27 \
--deps org.hsqldb:hsqldb:2.5.0  \
--deps mysql:mysql-connector-java:5.1.6  \
--deps mysql:mysql-connector-java:5.1.10 \
--deps org.postgresql:postgresql:42.3.1 \
--deps org.xerial:sqlite-jdbc:3.42.0.3 \
--deps org.mariadb.jdbc:mariadb-java-client:2.1.2 \
--deps ru.yandex.clickhouse:clickhouse-jdbc:0.3.1-patch \
 sqlline:sqlline:1.12.0

That should download and run SQLLine along with all the JDBC drivers. Once you get the SQLLine console, you can run a scan to see all the drivers.

sqlline version 1.12.0
sqlline> !scan
scan complete in 91ms
11 driver classes found
Compliant Version Driver Class
no        1.1     com.github.housepower.jdbc.ClickHouseDriver
no        8.0     com.mysql.cj.jdbc.Driver
no        8.0     com.mysql.jdbc.Driver
yes       0.0     org.apache.calcite.avatica.remote.Driver
yes       1.28    org.apache.calcite.jdbc.Driver
yes       2.0     org.h2.Driver
yes       2.5     org.hsqldb.jdbc.JDBCDriver
no        2.1     org.mariadb.jdbc.Driver
no        42.3    org.postgresql.Driver
no        3.42    org.sqlite.JDBC
no        0.0     ru.yandex.clickhouse.ClickHouseDriver
sqlline> 

Then you can connect to a DB by using the connection string and driver class, for example, for my locally running MariaDB.

!connect jdbc:mariadb://localhost:3306 root password org.mariadb.jdbc.Driver
# That will return the promt like below
0: jdbc:mariadb://localhost:3306>

Once you get the prompt, you can run all kinds of queries and commands on the DB. Exploring them will be a full-fledged blog post. Until then, install SQLLine and run the same CLI and commands against all kinds of databases.