Skip to content

Latest commit

 

History

History
327 lines (228 loc) · 11.7 KB

index.adoc

File metadata and controls

327 lines (228 loc) · 11.7 KB
Raw

JDBSEE - CLI

Introduction

Jdbsee CLI provides a small command line interface to access databases through jdbc drivers.

Features

Jdbsee CLI supports running SQL queries on a database and exporting results in CSV, JSON or plain text tables.

In order to avoid passing a lot of parameters at every command line invocation, it supports persisting db connection settings at two levels: driver and alias.

Persistent driver settings are meant to reuse references to local jar paths and/or dependencies automatically resolvable from online maven repositories.

Alias settings are meant to save jdbc url and db access credentials for later reuse.

Available commands

This is the set of available commands and related sub-commands:

shell

Provides an interactive shell with command completion to execute the whole set of commands within the application.

  • quit or exit quit the application

  • help print command help

run

Executes SQL queries on previously defined db alias or passing db settings as options.

driver

Provides persistent settings related to jdbc drivers. Keeps references to local jars, maven-style dependencies and driver class name.

  • create register new driver settings

  • list list registered driver settings

  • show show details about driver settings

  • delete delete driver settings

  • help print command help

alias

Provides persistent settings related to database connection. Keeps reference to driver settings, jdbc url and credentials.

  • create register a new persistent alias to database connection

  • list list registered db aliases

  • show show details about persistent driver settings

  • delete delete driver settings

  • jar-add add jars to driver settings

  • jar-remove remove jars from driver settings

  • dependency-add add automatically resolved dependencies to driver settings

  • dependency-remove remove dependencies from driver settings

  • help print command help

describe

Provides info about database and jdbc driver.

  • tables list all tables

  • views list all views

  • driver show driver info

  • help print command help

Usage

Invoke the distributable binary passing appropriate commands and params

Jdbsee help message

shell command

Open the interactive shell and execute multiple commands within the running application:

  • type jdbsee shell to open the interactive shell

  • hit TAB to get a list of available commands

  • use left/right arrows or TAB to navigate through th elist of available commands

  • type some chars to filter available commands by name

  • hit ENTER to automatically type the selected command, then ENTER again to execute it or TAB for further hints

Jdbsee shell message

In order to execute multiple queries within the same sql session:

  • use the jdbsee connect command to open the interactive sql session (i.e. jdbsee connect -d "com.h2database:h2:1.4.196" -l "jdbc:h2:mem:test")

  • type your query and hit ENTER for every query

  • use up/down arrows to navigate into the history

  • type quit and hit ENTER to exit

Jdbsee connect message

run command

Execute SQL queries against a database. Database connection (driver, jdbc url and credentials) can be reused from a previously defined alias or passed explicitly.

The retrieved data is printed as a plain text table or exported in CSV or JSON format.

Usage: run [-hP] [-a=<alias>] [-c=<driverClassName>] [-l=<url>]
           [-m=<driverClassMatches>] [-o=OUTPUT_FILE] [-p=<password>]
           [-t=OUTPUT_FORMAT] [-u=<username>] [-d=<deps>]... [-j=<jars>]...
           [-x=<execute>]... [QUERY]
Executes queries passing on-the-fly the db settings (driver, username)
      [QUERY]                 The SQL to run.
  -a, --alias=<alias>         Database alias, to reuse previously persisted
                                settings
  -c, --driver-class=<driverClassName>
                              External driver class name (detected from URL if
                                not specified)
  -d, --dependency=<deps>     Maven artifact dependency to be resolved for
                                driver class loading
  -h, --help                  display this help message
  -j, --jar=<jars>            External jar file to search for the driver classes
  -l, --url=<url>             The JDBC url (if provided along with alias
                                overrides its url)
  -m, --driver-class-match=<driverClassMatches>
                              Regex used to detect driver class by name.
                                Defaults to '(.*)Driver(.*)'
                                Default: (.*)Driver(.*)
  -o, --output-file=OUTPUT_FILE
                              File to use for saving output
  -p, --password=<password>   The password (if provided along with alias
                                overrides its url)
                                Default:
  -P, --ask-for-password      Ask for database password before connecting
  -t, --output-format=OUTPUT_FORMAT
                              Select output format. One between TABLE, CSV,
                                JSON, JSON_PRETTY
                                Default: TABLE
  -u, --user=<username>       The username (if provided along with alias
                                overrides its url)
                                Default:
  -x, --execute=<execute>     Additional SQL commands to be executed before the
                                specified QUERY

Loading JDBC drivers

Before opening any connection to the database, the application needs to load the appropriate jdbc driver.

In order to load the driver the application tries to:

  1. determine the driver class

  2. load it through a java classloader

Determining the driver class name

The application supports 3 ways to define the driver class to use:

explicit by driver class name (-c switch)

pass the driver class FQN to explicitly load it

explicit by regex (-m switch)

pass a regex for driver class FQN matching to limit the number of scanned classes

implicit by url

the driver manager will try to detect which driver is compatible for the specified url

Driver class loading strategies

In order to load the jdbc driver, its code must be accessible through a jvm classloader.

The application supports the following classloading strategies:

automatically from the application classpath

works for bundled drivers, that are drivers distributed along with the application. Please note that the distribution of 3rd party libraries can lead to license issues.

from the dropins folder

copy within the dropins folder some jar files containing the jdbc drivers and their contents will be scanned.
The folders scanned for additional jars are the application distribution subfolder named dropins and the ${HOME}/.jdbsee/dropins folder (within user home).

downloading as dependency

passing the -d switch along with a maven-style dependency - with the usual groupId:artifactId:version notation - it will be automatically downloaded and scanned for jdbc drivers

from explicitly referenced jar files

passing the -j switch along with the path of a jar within the local filesystem it will be scanned for jdbc drivers

Persistent settings

Driver and alias settings are stored on a hypersql database backed by text tables.
This means settings are actually stored as csv files within the $HOME/.jdbsee/data folder.

Output types

The application supports multiple output formats:

TABLE

rows formatted as a plain text table

CSV

values separated by semicolons

JSON, JSON_PRETTY

json in a raw or pretty printed flavor

The data is written to the standard output, while informational messages are sent to the standard error, so that output data can be redirected to an output file.

Examples

Automatic downloading drivers

Use the -d switch to automatically download drivers

jdbsee run -u postgres -p postgres \
  -d "org.postgresql:postgresql:42.2.1" \
  -l "jdbc:postgresql://localhost:5432/test" \
  "SELECT * FROM contacts;"

Loading drivers from external jars

Use the -j switch to load drivers from filesystem

jdbsee run -u postgres -p postgres \
  -j "/path/to/postgresql.jar" \
  -l "jdbc:postgresql://localhost:5432/test" \
  "SELECT * FROM contacts;"

Loading drivers from the dropins subfolder

Copy your jdbc driver jars into the app distribution under the dropins folder, and they will be scanned for jdbc drivers

jdbsee run -u postgres -p postgres \
  -l "jdbc:postgresql://localhost:5432/test" \
  "SELECT * FROM contacts;"

Register driver/alias settings and use them

jdbsee driver create -d "com.h2database:h2:1.4.196" h2 // (1)

jdbsee driver list // (2)
┌───────────────────┬───────────────────┬───────────────────┬──────────────────┐
│ID                 │NAME               │CLASS NAME         │CLASS SEARCH REGEX│
├───────────────────┼───────────────────┼───────────────────┼──────────────────┤
│0                  │h2                 │-                  │-                 │
└───────────────────┴───────────────────┴───────────────────┴──────────────────┘

alias create h2 h2memtest "jdbc:h2:mem:test" // (3)

jdbsee alias list // (4)
┌───────────────────┬───────────────────┬───────────────────┬──────────────────┐
│ID                 │NAME               │DRIVER             │URL               │
├───────────────────┼───────────────────┼───────────────────┼──────────────────┤
│0                  │h2memtest          │h2                 │jdbc:h2:mem:test  │
└───────────────────┴───────────────────┴───────────────────┴──────────────────┘

jdbsee run -a h2memtest "SELECT 1 AS foo" // (5)
┌──────────────────────────────────────────────────────────────────────────────┐
│FOO                                                                           │
└──────────────────────────────────────────────────────────────────────────────┘
│1                                                                             │
└──────────────────────────────────────────────────────────────────────────────┘

  1. Register a driver

  2. Check registered driver

  3. Register an alias

  4. Check registered alias

  5. Run a test query using them