5 Useful Database Command Line Tools
Tags: database, cli
Published: 2022-04-26 03:40:15

Mycli - Python
--------------

[https://github.com/dbcli/mycli](https://github.com/dbcli/mycli)

_star 10.3k_

A Terminal Client for MySQL with AutoCompletion and Syntax Highlighting.

### Features

*   Auto-completion as you type for SQL keywords as well as tables, views and columns in the database.
*   Syntax highlighting using Pygments.
*   Smart-completion (enabled by default) will suggest context-sensitive completion.
    *   `SELECT * FROM <tab>` will only show table names.
    *   `SELECT * FROM users WHERE <tab>` will only show column names.
*   Support for multiline queries.
*   Favorite queries with optional positional parameters. Save a query using `\fs alias query` and execute it with `\f alias` whenever you need.
*   Timing of sql statements and table rendering.
*   Config file is automatically created at `~/.myclirc` at first launch.
*   Log every query and its results to a file (disabled by default).
*   Pretty prints tabular data (with colors!)
*   Support for SSL connections

Usage  
 

    $ mycli --help
    Usage: mycli [OPTIONS] [DATABASE]
    
      A MySQL terminal client with auto-completion and syntax highlighting.
    
      Examples:
        - mycli my_database
        - mycli -u my_user -h my_host.com my_database
        - mycli mysql://my_user@my_host.com:3306/my_database
    
    Options:
      -h, --host TEXT               Host address of the database.
      -P, --port INTEGER            Port number to use for connection. Honors
                                    $MYSQL_TCP_PORT.
    
      -u, --user TEXT               User name to connect to the database.
      -S, --socket TEXT             The socket file to use for connection.
      -p, --password TEXT           Password to connect to the database.
      --pass TEXT                   Password to connect to the database.
      --ssh-user TEXT               User name to connect to ssh server.
      --ssh-host TEXT               Host name to connect to ssh server.
      --ssh-port INTEGER            Port to connect to ssh server.
      --ssh-password TEXT           Password to connect to ssh server.
      --ssh-key-filename TEXT       Private key filename (identify file) for the
                                    ssh connection.
    
      --ssh-config-path TEXT        Path to ssh configuration.
      --ssh-config-host TEXT        Host to connect to ssh server reading from ssh
                                    configuration.
    
      --ssl-ca PATH                 CA file in PEM format.
      --ssl-capath TEXT             CA directory.
      --ssl-cert PATH               X509 cert in PEM format.
      --ssl-key PATH                X509 key in PEM format.
      --ssl-cipher TEXT             SSL cipher to use.
      --ssl-verify-server-cert      Verify server's "Common Name" in its cert
                                    against hostname used when connecting. This
                                    option is disabled by default.
    
      -V, --version                 Output mycli's version.
      -v, --verbose                 Verbose output.
      -D, --database TEXT           Database to use.
      -d, --dsn TEXT                Use DSN configured into the [alias_dsn]
                                    section of myclirc file.
    
      --list-dsn                    list of DSN configured into the [alias_dsn]
                                    section of myclirc file.
    
      --list-ssh-config             list ssh configurations in the ssh config
                                    (requires paramiko).
    
      -R, --prompt TEXT             Prompt format (Default: "\t \u@\h:\d> ").
      -l, --logfile FILENAME        Log every query and its results to a file.
      --defaults-group-suffix TEXT  Read MySQL config groups with the specified
                                    suffix.
    
      --defaults-file PATH          Only read MySQL options from the given file.
      --myclirc PATH                Location of myclirc file.
      --auto-vertical-output        Automatically switch to vertical output mode
                                    if the result is wider than the terminal
                                    width.
    
      -t, --table                   Display batch output in table format.
      --csv                         Display batch output in CSV format.
      --warn / --no-warn            Warn before running a destructive query.
      --local-infile BOOLEAN        Enable/disable LOAD DATA LOCAL INFILE.
      -g, --login-path TEXT         Read this path from the login file.
      -e, --execute TEXT            Execute command and quit.
      --init-command TEXT           SQL statement to execute after connecting.
      --charset TEXT                Character set for MySQL session.
      --password-file PATH          File or FIFO path containing the password
                                    to connect to the db if not specified otherwise
      --help                        Show this message and exit.
    

![Mycli](https://github.com/dbcli/mycli/raw/main/screenshots/tables.png)

Pgcli - Python
--------------

[https://github.com/dbcli/pgcli](https://github.com/dbcli/pgcli)

_star 10.3k_

Postgres CLI with autocompletion and syntax highlighting

### Features

*   Auto-completes as you type for SQL keywords as well as tables and columns in the database.
    
*   Syntax highlighting using Pygments.
    
*   Smart-completion (enabled by default) will suggest context-sensitive completion.
    
    > *   `SELECT * FROM <tab>` will only show table names.
    > *   `SELECT * FROM users WHERE <tab>` will only show column names.
    
*   Primitive support for `psql` back-slash commands.
    
*   Pretty prints tabular data.
    

$ pip install -U pgcli

or

$ sudo apt-get install pgcli # Only on Debian based Linux (e.g. Ubuntu, Mint, etc)
$ brew install pgcli  # Only on macOS

Sqlline - Java
--------------

[https://github.com/julianhyde/sqlline](https://github.com/julianhyde/sqlline)

_star 502_

Shell for issuing SQL to relational databases via JDBCIf you have [Coursier](https://github.com/coursier/coursier) installed, you can quickly connect to a [demo Hypersonic database](https://github.com/julianhyde/foodmart-data-hsqldb) with:

    $ coursier launch sqlline:sqlline:1.12.0 org.hsqldb:hsqldb:2.5.0 net.hydromatic:foodmart-data-hsqldb:0.4 -M sqlline.SqlLine -- -u jdbc:hsqldb:res:foodmart -n FOODMART -p FOODMART -d org.hsqldb.jdbcDriver
    0: jdbc:hsqldb:res:foodmart> select avg("shelf_height" * "shelf_width" * "shelf_depth") as "avg_volume" from "product";
    +-------------------------+
    |       avg_volume        |
    +-------------------------+
    | 2147.3845245442353      |
    +-------------------------+
    1 row selected (0.01 seconds)
    0: jdbc:hsqldb:res:foodmart> !quit
    

Iredis - Python
---------------

[https://github.com/laixintao/iredis](https://github.com/laixintao/iredis)

_star 1.8k_

Interactive Redis: A Terminal Client for Redis with AutoCompletion and Syntax Highlighting.

### Features

*   Advanced code completion. If you run command `KEYS` then run `DEL`, IRedis will auto-complete your command based on `KEYS` result.
*   Command validation. IRedis will validate command while you are typing, and highlight errors. E.g. try `CLUSTER MEET IP PORT`, IRedis will validate IP and PORT for you.
*   Command highlighting, fully based on redis grammar. Any valid command in IRedis shell is a valid redis command.
*   Human-friendly result display.
*   _pipeline_ feature, you can use your favorite shell tools to parse redis' response, like `get json | jq .`.
*   Support pager for long output.
*   Support connection via URL, `iredis --url redis://example.com:6379/1`.
*   Support cluster, IRedis will auto reissue command for `MOVED` response in cluster mode.
*   Store server configuration: `iredis -d prod-redis` (see [dsn](#using-dsn) for more).
*   `peek` command to check the key's type then automatically call `get`/`lrange`/`sscan`, etc, depending on types. You don't need to call the `type` command then type another command to get the value. `peek` will also display the key's length and memory usage.
*   Ctrl + C to cancel the current typed command, this won't exit IRedis, exactly like bash behaviour. Use Ctrl + D to send a EOF to exit IRedis.
*   Ctrl + R to open **reverse-i-search** to search through your command history.
*   Auto suggestions. (Like [fish shell](http://fishshell.com/).)
*   Support `--encode=utf-8`, to decode Redis' bytes responses.
*   Command hint on bottom, include command syntax, supported redis version, and time complexity.
*   Official docs with built-in `HELP` command, try `HELP SET`!
*   Written in pure Python, but IRedis was packaged into a single binary with [PyOxidizer](https://github.com/indygreg/PyOxidizer), you can use cURL to download and run, it just works, even you don't have a Python interpreter.
*   Hide password for `AUTH` command.
*   Says "Goodbye!" to you when you exit!
*   For full features, please see: [iredis.io](https://www.iredis.io)

Or you can download the executable binary with cURL(or wget), untar, then run. It is especially useful when you don't have a python interpreter(E.g. the [official Redis docker image](https://hub.docker.com/_/redis/) which doesn't have Python installed.):  
 

    wget  https://github.com/laixintao/iredis/releases/latest/download/iredis.tar.gz \
     && tar -xzf iredis.tar.gz \
     && ./iredis
    

![Iredis](https://raw.githubusercontent.com/laixintao/iredis/master/docs/assets/logo.png)

Usql - Go
---------

[https://github.com/xo/usql](https://github.com/xo/usql)

_star 7.2k_

Universal command-line interface for SQL databases

If the following or similar error is encountered when attempting to run `usql`:

$ usql
dyld: Library not loaded: /usr/local/opt/icu4c/lib/libicuuc.68.dylib
  Referenced from: /Users/user/.local/bin/usql
  Reason: image not found
Abort trap: 6

![Usql](https://raw.githubusercontent.com/xo/usql-logo/master/usql.png)

Find more command line tools: [https://cliapps.com](https://cliapps.com)