Using command-line programs with CrateDB

This section provides a quick overview about a few CLI programs, and how to use them for connecting to CrateDB clusters. We recommend to use crash, psql, http (HTTPie), or curl.

You can use them to quickly validate HTTP and PostgreSQL connectivity to your database cluster, or to conduct basic scripting.

Before running the command-line snippets outlined below, please use the correct settings instead of the placeholder tokens <hostname>, <username> and <password>.

When using CrateDB Cloud, <hostname> will be something like <clustername>.{aks1,eks1}.region.{azure,aws}.cratedb.net.

crash

image

The CrateDB Shell is an interactive command-line interface (CLI) tool for working with CrateDB. For more information, see the documentation about crash.

CRATEPW=<password> \
    crash --hosts 'https://<hostname>:4200' --username '<username>' \
    --command "SELECT 42.42;"
# No authentication. 
crash --command "SELECT 42.42;"
 

psql

image

psql is a terminal-based front-end to PostgreSQL. It enables you to type in queries interactively, issue them to PostgreSQL, and see the query results. For more information, see the documentation about psql.

PGUSER=<username> PGPASSWORD=<password> \
    psql postgresql://<hostname>/crate --command "SELECT 42.42;"
# No authentication.
psql postgresql://crate@localhost:5432/crate --command "SELECT 42.42;"

HTTPie

image

The HTTPie CLI is a modern, user-friendly command-line HTTP client with JSON support, colors, sessions, downloads, plugins & more. For more information, see the documentation about HTTPie.

http https://<username>:<password>@<hostname>:4200/_sql?pretty" \
    stmt="SELECT 42.42;"
http "localhost:4200/_sql?pretty" \
    stmt="SELECT 42.42;"

curl

image

The venerable curl is the ubiquitous command line tool and library for transferring data with URLs. For more information, see the documentation about curl.

This example combines it with jq, a lightweight and flexible command-line JSON processor.

echo '{"stmt": "SELECT 42.42;"}' \
    | curl "https://<username>:<password>@<hostname>:4200/_sql?pretty" --silent --data @- | jq
echo '{"stmt": "SELECT 42.42;"}' \
    | curl "localhost:4200/_sql?pretty" --silent --data @- | jq