Interact with your database using human queries through OpenAI GPT.

• 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)

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

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

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

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

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.

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:

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]

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

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

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

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.

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

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

# 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

pnpm run dev:cli

pnpm run dev:web