Chat-DBT

Interact with your database using human queries through OpenAI GPT.

https://user-images.githubusercontent.com/24897252/233864066-2110a65e-3337-40c2-a1e5-3756e21d6ed6.mp4

Features

• Supported databases: PostgreSQL, ClickHouse
• Both Command line and Web interfaces
• Pipe from/to standard input/output
• Keeps the history between queries (unless specified otherwise)
• Use OpenAI to auto-correct SQL errors
• Multiple result formats (table, JSON, CSV)

Getting started

npm i -g chat-dbt
chat-dbt --database postgres://username:password@localhost:5432/postgres --key openai-key

Usage

All the available options can be shown using chat-dbt --help

Command-line interface

chat-dbt --database postgres://username:password@localhost:5432/postgres --key openai-key

Web interface

chat-dbt web --database postgres://username:password@localhost:5432/postgres --key openai-key

https://user-images.githubusercontent.com/24897252/233865764-2a8c4716-f052-47f5-9e48-0ec3a4cc818f.mp4

Database connection string

ClickHouse

chat-dbt --database clickhouse://username:[email protected]?secure=true

The secure option will translate into an https entrypoint. Its default is false, which corresponds to http. No other ClickHouse option is supported, please file an issue or create a pull request if you need some of them.

Adapting context between queries

By default, Chat-DBT keeps a history of previous exchanges with OpenAI. Although this feature provides more context to OpenAI and enables queries using previous results, it uses more tokens and is therefore more costly. If you plan to extract a significant amount of data to send back to OpenAI, you may reach the token limit quickly. Here's an example of how context can be reused between queries:

https://user-images.githubusercontent.com/24897252/235167307-8d5fe81e-567a-43be-8300-852930ce9238.mp4

You can either disable the history with the --history-mode=none option, or only keep the previous queries without sending their database result with the --history-mode=queries option. Please note that the previous query will however always be sent when you asked to retry a query that failed.

chat-dbt --history-mode=[all|none|queries]

Handling of errors

Sometimes OpenAI's response may include an incorrect SQL query that fails. In such cases, you have the following options:

• Retry: In this case, the error will be sent back to OpenAI, and it will be asked to correct its response.
• Edit prompt: You can reformulate the request to OpenAI for it to adjust its response.
• Edit SQL: You can manually change the SQL query generated by OpenAI to correct its error and then execute it.

It is possible to automatically request corrections from OpenAI while sending errors back to it. This feature is deactivated by default, but you can enable it by using the --auto-correct nb-attempts flag, where nb-attempts is the number of attempts OpenAI will have to solve the error.

Each attempt is iterative and builds upon the previous ones, so OpenAI is supposed to take the context into account to reach a successful query eventually.

chat-dbt --auto-correct 3

Working with input and output files

You can use a file as a source of a batch of instructions, that you can pipe through chat-dbt, for instance, given the following instructions.txt file:

list authors
add a famous author from the 20th century
list authors

You can then execute the instructions with:

cat instructions.txt | chat-dbt

It is also possible to define which part of the output should be redirected to stderr, stdout, or nowhere, with the --output-sql, --output-result and --output-info options. For instance, the following instruction will output the SQL query to stderr, and the SQL result into authors.csv:

echo "list authors" | chat-dbt \
    --output-sql stderr \
    --output-result stdout \
    --output-info none \
    --format csv > authors.csv

Environment variables

export DB_CONNECTION_STRING=postgres://username:password@localhost:5432/postgres
export OPENAI_API_KEY=sk-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
export OPENAI_ORGANIZATION=org-xxxxxxxxxxxxxxxxxxxxxxxx
chat-dbt

Chat-DBT will also read the secrets mentioned above from a .env file, if it exists:

DB_CONNECTION_STRING=postgres://username:password@localhost:5432/postgres
OPENAI_API_KEY=sk-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
OPENAI_ORGANIZATION=org-xxxxxxxxxxxxxxxxxxxxxxxx

You can also pass a different .env file name as an option:

chat-dbt --env .env.custom

Choose another OpenAI model

The OpenAI model is set to gpt-4 by default. You can choose another chat model with the --model option, for instance:

chat-dbt --model gpt-3.5-turbo

You can have a look at the list of compatible chat completion models in the OpenAI documentation.

Ask for confirmation before executing the SQL query

You may not feel comfortable executing a query before previewing it. To preview the SQL query and confirm before running it, use the --confirm option. This option prompts you for confirmation and allows you to modify the SQL query if needed before its execution.

chat-dbt --confirm

Change the format of the result

By default, Chat-DBT renders the results as a table. You can however output the result in CSV or JSON, in passing the --format option:

chat-dbt --format json
chat-dbt --format csv

Development

# Clone the repository
git clone https://github.com/plmercereau/chat-dbt
cd chat-dbt

# Install Node dependencies
pnpm i

# Create a .env.local file
cp .env.local.example .env.local

# Then, edit the .env.local file to fill your OpenAI API key and organisation

# Start the demo database
docker-compose up -d

Develop the CLI

pnpm run dev:cli

Develop the Web interface

pnpm run dev:web