Creating a SQL generator app with ChatGPT, PostgreSQL, and ToolJet

In this tutorial, you'll learn how to build a SQL Generator application using ChatGPT, ToolJet, and PostgreSQL.

Users can state the kind of data they want to retrieve from the database in plain English sentences, then ChatGPT converts them to the appropriate SQL syntax required to fetch the data from the database.
We'll be using ToolJet for the application interface.

What is ToolJet?

ToolJet is an open-source low-code framework that enables us to build full-stack web applications within a few minutes. With ToolJet, you can create standalone fully-functional full-stack applications or embed applications into other websites.

ToolJet allows you to build applications that use relational and non-relational databases, REST APIs, and cloud storage like Google Cloud Storage, AWS S3, and Minio. It is an excellent development tool helping individuals, developers, and businesses create and ship products faster.

Before we continue
I would be super happy if you could give us a star! And let me also know in the comments section. ❤️

https://github.com/ToolJet/ToolJet

What is ChatGPT?

ChatGPT is the most talked about technology at the moment. It is an AI language model trained by OpenAI to answer questions, provide information, and engage in human language conversations with users.

ChatGPT performs tasks such as writing, debugging, and explaining code snippets. With its latest language model GPT-4, ChatGPT can accept images as prompts and provides answers based on the given prompt or commands.

In this article, we'll use ChatGPT to generate SQL query syntax from plain English words. Before we begin, let's set up the application's database.

Setting up an ElephantSQL (PostgreSQL) database

ElephantSQL enables us to create a PostgreSQL database on the cloud instead of your local machine. Follow the steps below to create a PostgreSQL database:

Create an ElephantSQL account here.

Add a new database instance. No credit card or billing information is required.

Once you've created the database instance, your database information is displayed.

Congratulations! You've successfully created the database needed for this application. Next, let's design the application interface and connect the database to the application.

Designing the application interface with ToolJet

Here, you'll learn how to create a ToolJet account and design a fully functional application with ToolJet.

If you are new to ToolJet, create an account.

Create a Workspace and a new app called SQL Generator.

Design a user interface similar to the image below. ToolJet enables you to create applications' interfaces by dragging and dropping various UI components.

From the image above, I added the following:

• the Table component with columns name and age,
• the Text component that serves as a form label,
• the TextInput component that accepts the SQL query in plain English.
• a read-only Text Input component that displays the generated SQL query, and
• two Buttons. One for generating the SQL query from ChatGPT and the other for executing the query on the PostgreSQL database.

How to communicate with ChatGPT in ToolJet

Here, you'll learn how to communicate with ChatGPT via Rest API in ToolJet.

ToolJet allows us to communicate with external resources or create custom functions via a panel, known as Query Panel. In ToolJet, any function that communicates with either of the following: database, API, cloud storage, and runs a JavaScript or Python code is called a Query.

Setting up an OpenAI account

Log in or create an OpenAI account here.

Click Personal on the navigation bar and select View API keys from the menu bar to create a new secret key.

Generate a new API key and copy it somewhere on your computer. We'll use it in the upcoming section.

Communicating with the ChatGPT API in ToolJet

On your Query Panel, select REST API and create a POST request to the ChatGPT endpoint - https://api.openai.com/v1/chat/completions.

As shown in the image above, I added the following to the Query Headers variables.

Content-Type: application/json
Authorization: Bearer <YOUR_CHATGPT_API_KEY>

Enable Transformations and copy the code below into the code editor to retrieve the exact response needed from the request.

const choices = data.choices[0];
return choices.message.content;

Click Body, enable Raw JSON, and copy the code below into the code editor - it contains parameters sent along with the POST request.

{
    "model": "gpt-3.5-turbo",
    "messages": [
        {
            "role": "user",
            "content": "Write the SQL query for this operation: {{components.textarea1.value}}. I need only the SQL query. No explanations, please."
        }
    ]
}

The code snippet above uses the GPT-3.5 Turbo model provided by OpenAI, and the content variable contains the prompt sent to the ChatGPT API.
ToolJet allows users to access the attributes and values of each component. Hence, the {{components.textarea1.value}} represents the value (plain English command) provided by the user.

PS: Yours may not be textarea1, if so, change it to the component’s name for the user's input.

Finally, click the Save button.🎉

So far, we've configured the ChatGPT query. Next, let's execute the query when a user clicks the Generate SQL Command button.

From the image above, I added an on Click event to the button, and the ChatGPT query runs when a user clicks the button. It takes the user's input and sends a request for its equivalent SQL syntax when a user clicks the button.

To display the result, update the default value of the read-only Text Input component by setting it to {{queries.chatgptQuery.data}}.

Congratulations!🎊 You've been able to communicate with ChatGPT. Next, let's learn how to connect and communicate with the PostgreSQL database created earlier.

How to communicate with a PostgreSQL database in ToolJet

Here, you'll learn how to communicate with a PostgreSQL database in ToolJet. First, let me walk you through connecting a PostgreSQL database to ToolJet.

Connecting a PostgreSQL database to ToolJet

Click Add a Datasource from the Query Panel on ToolJet, select PostgreSQL from the list of databases, and provide the required information as shown below.

From the image above, the host is the same as the server name on ElephantSQL (excluding the brackets). The username and database name are the same, and copy and paste the password into its field.

Scroll down the page and click Test Connection.

If the connection is verified, we can start making queries to the database.

Communicating the PostgreSQL database in ToolJet

Here, you'll learn how to send the SQL queries generated by ChatGPT to the PostgreSQL database and view the queried data within the Table component on the user interface.

Before we begin, let's add some data to the database. Click on the PostgreSQL query, select SQL mode, and run the code snippet below to create a new table containing the data below.``


CREATE TABLE People(NAME varchar(255), AGE int);
INSERT INTO People(name, age) VALUES(‘David’, 19);
INSERT INTO People(name, age) VALUES(‘Tabby’, 21);
INSERT INTO People(name, age) VALUES('Teja', 24);
INSERT INTO People(name, age) VALUES('Julius', 27);
INSERT INTO People(name, age) VALUES('Kedrick', 26);
SELECT * FROM people;


You can view the data on ElephantSQL once it has been uploaded.

Next, let's receive the SQL command from the ChatGPT and display the data within the Table component on the user interface.

Update the SQL editor to contain the query retrieved from ChatGPT and save it.

Add an on Click event that runs the database query to the Execute button.

Lastly, update the Table component to display the data retrieved from the database.

Congratulations!🎉 You've completed the application for this tutorial. Click the Release button and the share icon to host and make the application public.

Here is the desktop version of the application: https://app.tooljet.com/applications/480a5bbb-3607-4c19-ac95-038d4e56cc2a.
You can also import this application into your ToolJet workspace by downloading its JSON file

Conclusion

So far, you have learnt how to:

• add a PostgreSQL database to ToolJet
• communicate with ChatGPT via REST API in ToolJet, and
• design applications in a few minutes with ToolJet.

ToolJet is both an excellent development tool and open-source - meaning its code is readily available for everyone to modify and improve. It has a large community of developers and talented contributors constantly maintaining and improving the software. As a user, you can be sure of getting the best performance when you use ToolJet.

Are you interested in contributing to ToolJet? Feel free to check out our GitHub repo - https://github.com/ToolJet/ToolJet to contribute and raise issues about ToolJet.

Thank you for reading!