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.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:18.104.22.168 \ --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.36 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.