CrateDB CLIs (Karyn)

CrateDB is SQL-based and supports multiple connection methods. This guide will help you connect to CrateDB from different environments and tools, whether you're running CrateDB locally, in the cloud, or using it in production.

Prerequisites

Before connecting, ensure that:

• CrateDB is running (locally, on a server, or in the cloud).

• You know the host address, port, and optionally the username/password.

• Default port: 5432 (PostgreSQL protocol), 4200 (HTTP REST API)

If you're using CrateDB Cloud, your connection details are available in the Cloud Console:

• Use the provided hostname and credentials

• Make sure to allow your IP in the firewall settings

• Follow the same steps as above using DBeaver, psql, or application libraries

1. Connect Using a SQL Client

CrateDB supports the PostgreSQL wire protocol, so you can use standard PostgreSQL-compatible tools like:

DBeaver, DataGrip, or pgAdmin

1. Create a new connection

2. Select PostgreSQL as the database type

3. Fill in:

   • Host: your-hostname (e.g., localhost, or my-cratedb.cloud.cratedb.net)

   • Port: 5432

   • Database: doc (default schema)

   • Username: default is crate

   • Password: leave blank or use the one you configured

4. Test and connect

CrateDB uses PostgreSQL v10+ protocol compatibility. Some PostgreSQL clients may not support all CrateDB features like dynamic columns.

2. Connect via the Command Line

Using crash (CrateDB Shell)

crash is CrateDB’s interactive SQL shell.

# Install crash (if not already installed)
pip install crash

# Connect to CrateDB
crash --host localhost

Use --host, --port, --user, and --password to customize your connection.

Using psql (PostgreSQL CLI)

CrateDB is compatible with psql:

psql -h localhost -p 5432 -U crate

You can add -d doc to connect to the default schema explicitly.

3. Connect Programmatically

You can connect to CrateDB from applications using standard PostgreSQL client libraries.

Python (psycopg2)

import psycopg2

conn = psycopg2.connect(
    host="localhost",
    port=5432,
    user="crate",
    database="doc"
)
cursor = conn.cursor()
cursor.execute("SELECT name FROM sys.cluster")
print(cursor.fetchone())

Node.js (node-postgres)

const { Client } = require('pg');

const client = new Client({
  host: 'localhost',
  port: 5432,
  user: 'crate',
  database: 'doc'
});

client.connect()
  .then(() => client.query('SELECT name FROM sys.cluster'))
  .then(res => console.log(res.rows[0]))
  .finally(() => client.end());

Java (JDBC)

conn = DriverManager.getConnection(
    "jdbc:postgresql://localhost:5432/doc", "crate", "");

4. Connect via REST API (Optional)

CrateDB also exposes a RESTful HTTP API on port 4200.

Example with curl

curl -X POST "http://localhost:4200/_sql" -H 'Content-Type: application/json' \
  -d '{"stmt": "SELECT name FROM sys.cluster"}'

Example Response

{
  "cols": ["name"],
  "rows": [["crate-cluster"]],
  "rowcount": 1,
  "duration": 0.1
}

Use HTTPS and authentication for production environments.

Troubleshooting

• Connection refused? Check that CrateDB is running and that the correct port is open.

• Authentication failed? Ensure you’re using the correct user/password.

• SSL errors? CrateDB Cloud requires SSL. Use SSL-enabled clients or configure your connection string accordingly.