Goal

To demonstrate ChatGPT’s ability to interpret natural language and convert it into executable SQL commands.

Preamble

In an attempt to make querying databases more accessible to business users, it was thought that the Large Language Model ChatGPT (Chat Generative Pre-trained Transformer) could be used to interpret natural language queries and convert them into SQL-compliant queries. These queries would then be executed against a database and the returned result set displayed to the user.

Note: No actual data is to be provided to ChatGPT - only the database metadata so it can formulate the queries accurately.

Data

A Postgres database was setup containing the AdventureWorks demo database.

Method

Note that ideally, this would involve a cloud-based solution where both the front-end and database would be hosted on an AWS, Azure, Google or similar cloud platform. However, this PoC was built locally, on a laptop.

The first thing to organize was an OpenAI API token so that the OpenAI API could be used. Each query submitted is only around a fraction of a cent, proving to be very economical.

With the token in hand, a Python script was written that was designed to:

• Connect to the OpenAI API utilizing the token

• Use the text-davinci-003 engine (there are numerous models to use, but it was found to be both the most economical and accurate version of ChatGPT for this purpose)

• Accept a text prompt which contained both the full schema of the database together with the natural language query provided from the user. A number of additional instructions were appended to the end of the prompt which mainly related to ensuring only SQL commands appropriate to PostgreSQL and the given schema were generated.

• Send the prompt with all the above instructions to OpenAI

• Receive the response and ensure it was a valid SQL query

• Clean up the query if required and submit it to the database

• Receive the result set from the database and return it to the calling process

• The Python script was designed to run in Flask as a continuously-running server

Additionally, a front-end html file was created that accepted the user’s queries, submitted them to the Python script within the flask server and received the response which was displayed as a table of results.

Results

After a bit of experimentation, a working proof-of-concept was built.

The Python script was able to be successfully run as a flask server on a local port.

Once running, the front-end could be run, and it looked like this:

An example query such as:

“display the list of departments together with the number of employees in each - also, order this list in descending order”

was entered and submitted.

The results could be seen both on the server and in the front-end.

The users query was clearly translated by the ChatGPT model into a usable SQL query which was then executed against the database, with the result set returned to the front-end.

Many further queries were trialled during the course of developing this PoC. Some results were not as expected which led to the modification of the prompt, but eventually a relatively stable application was developed which would be suitable for use within a commercial environment. Obviously a disclaimer needs to be provided to users of the system to ensure that the results make sense. If they raise suspicions then asking an analyst if the associated SQL query makes sense would be warranted.

It seems that this technology can only improve, and may just be the missing link that could provide business users a way to directly ask the data for answers to business problems without always needing to rely on highly qualified analysts.