How to Use the Chat2DB Command-Line Interface for Database Operations

The Chat2DB CLI is a Maven-based Java client that connects to a running Chat2DB server via the MCP protocol to execute SQL, manage datasources, and invoke AI features from the terminal.

The Chat2DB command-line interface provides a terminal-based alternative to the graphical UI for the OtterMind/Chat2DB project. This lightweight Java application enables developers to script database operations, automate workflows, and leverage AI-assisted query generation without leaving the terminal.

Architecture and Communication Flow

The CLI follows a layered architecture that mirrors the main application’s design. Understanding these layers helps troubleshoot connectivity issues and extend functionality.

Transport Layer and REST Endpoints

At the foundation, the transport layer sends HTTP requests to the Chat2DB server’s REST endpoints such as /api/datasource and /api/sql/execute. The target URL derives from the CHAT2DB_SERVER_URL environment variable, defaulting to http://127.0.0.1:10825. On the server side, chat2db-community-server/chat2db-community-web/src/main/java/xyz/ottermind/chat2db/web/controller/SqlController.java handles these incoming execution requests.

MCP Client Wrapper

The McpClient class in chat2db-cli/src/main/java/xyz/ottermind/chat2db/cli/mcp/McpClient.java encapsulates the transport logic. It serializes request objects to JSON and deserializes responses back into Java POJOs, implementing the MCP (Message Control Protocol)—a lightweight JSON-over-HTTP protocol defined by the core project.

Command Dispatch with Picocli

The CLI parses user input using the Picocli library. The main entry point at chat2db-cli/src/main/java/xyz/ottermind/chat2db/cli/Chat2DbCli.java registers sub-commands that delegate to specific handlers. For example, chat2db-cli/src/main/java/xyz/ottermind/chat2db/cli/commands/SqlCommand.java implements the sql exec functionality by building request objects and forwarding them to the McpClient.

Installation and Prerequisites

Before using the CLI, you must have a running Chat2DB server instance—either the desktop application, a Docker container, or the headless server mode. The CLI acts purely as a client and cannot operate standalone.

Install the CLI binary from the releases page or use Homebrew on macOS and Linux:

brew install chat2db-cli

Verify the installation by checking the version:

chat2db --version

Configuration Management

The CLI loads runtime settings from a chat2db-cli.properties file or corresponding environment variables. The configuration resolution follows a specific priority order implemented in chat2db-cli/src/main/java/xyz/ottermind/chat2db/cli/config/ConfigLoader.java.

Search locations include:

• The current working directory
• The ~/.chat2db-cli/ directory
• The path specified by the CHAT2DB_CLI_CONF environment variable

Key configuration properties:

• chat2db.server.url – The Chat2DB backend URL (defaults to http://127.0.0.1:10825)
• chat2db.api.key – Optional authentication token for secured servers

Example configuration:

export CHAT2DB_SERVER_URL=http://127.0.0.1:10825
export CHAT2DB_API_KEY=your_secret_key

Core Commands and Usage Examples

The CLI organizes functionality into three primary sub-commands: datasource, sql, and ai.

Managing Datasources

List existing connections or add new databases using the datasource command group. The following example adds a PostgreSQL datasource:

chat2db datasource add \
  --name my_pg \
  --type postgresql \
  --host localhost \
  --port 5432 \
  --username chat2db_user \
  --password secret

Verify the configuration by listing available datasources:

chat2db datasource list

Executing SQL Queries

The sql exec command sends queries to the server via the SqlCommand class. Results return as formatted tables or CSV:

# Execute a simple query

chat2db sql exec \
  --datasource my_pg \
  --query "SELECT version();"

# Export results to CSV

chat2db sql exec \
  --datasource my_pg \
  --query "SELECT * FROM orders LIMIT 100" \
  --output csv > orders.csv

AI-Assisted Query Generation

Leverage the built-in AI assistant to generate SQL from natural language prompts:

chat2db ai gen \
  --prompt "Show the top 10 customers by total order amount last month"

The CLI transmits this prompt to the server’s AI endpoints and returns the generated SQL statement, which you can then execute or refine.

Summary

• The Chat2DB CLI requires a running Chat2DB server and communicates via HTTP REST endpoints using the MCP protocol.
• Configuration resolves through chat2db-cli.properties or environment variables loaded by ConfigLoader.java.
• The Picocli-based dispatcher in Chat2DbCli.java routes commands to handlers like SqlCommand.java.
• All operations rely on the McpClient class to serialize requests and deserialize JSON responses.
• Supported operations include datasource management, raw SQL execution, and AI-powered query generation.

Frequently Asked Questions

Does the Chat2DB CLI work without the graphical application?

No. The CLI is a thin client that requires a running Chat2DB server to function. You can run the server as a Docker container, in headless mode, or via the desktop application, but the backend must be listening on the configured URL (default http://127.0.0.1:10825) for the CLI to connect via the MCP protocol.

How does the CLI authenticate with the Chat2DB server?

Authentication uses the chat2db.api.key property defined in chat2db-cli.properties or the CHAT2DB_API_KEY environment variable. The McpClient includes this key in request headers when communicating with endpoints defined in SqlController.java and other server-side controllers.

Can I use the CLI in CI/CD pipelines?

Yes. Because the CLI supports CSV output and exit codes based on execution success, you can integrate it into automation workflows. Configure the server URL and API key via environment variables, then script SQL execution commands without interactive prompts.

Where are the CLI dependencies defined?

The Maven build configuration resides in chat2db-cli/pom.xml, which declares dependencies on Picocli for command parsing, Jackson for JSON handling, and the MCP client libraries. This modular structure keeps the CLI lightweight while maintaining compatibility with the core Chat2DB data models.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}