Building a SQL Query Generator Using ToolJet + Gemini API
Tags: tooljet, lowcode, ai, gemini
Published: 2024-05-30 13:03:44

## Introduction
This tutorial will guide you through the process of building an AI-driven SQL query generator using [ToolJet](https://github.com/ToolJet/ToolJet), a low-code visual app builder, and the Gemini API, a powerful natural language processing API. The resulting application will enable users to input prompts in plain English, which will then be translated into executable SQL queries. We'll be using ToolJet's visual app builder to create a user-friendly UI, and ToolJet's low-code query builder to connect it to the Gemini API endpoints.

-------------------------------------------------------------

## Prerequisites:

- **ToolJet** (https://github.com/ToolJet/ToolJet) : An open-source , low-code business application builder. [Sign up](https://www.tooljet.com/signup) for a free ToolJet cloud account or [run ToolJet on your local machine](https://docs.tooljet.com/docs/setup/try-tooljet/) using Docker.
- **Gemini API Key** : Log into [Google AI Studio](https://aistudio.google.com/app/apikey) using your existing Google credentials. Within the AI Studio interface, you'll be able to locate and copy your API key.

Here is a quick preview of our final application:
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/amxvh44ue5inhf9cyoh8.png)

-------------------------------------------------------------


## Crafting our UI

- Login to your [ToolJet account](https://app.tooljet.com/). Navigate to ToolJet dashboard and click on the **Create new app** button on the top left corner. ToolJet comes with 45+ built-in components. This will let us set up our UI in no time.
- Drag and drop the **Container** component onto the canvas from the component library on the right side. Adjust the height and width of the **Container** component appropriately.
- Similarly, drag-and-drop the **Icon** and two **Text** components inside your container. We'll use these two **Text** components for our header and byline texts.
- Select the **Icon** component, navigate to its properties panel on the right and select the database icon under its **Icon** property.
- Change the colour of the **Icon** and **Text** components according to your preference. Here we'll use a shade of blue(HEX code: #4A7EE2).
- Change the font size and content of the **Text** component appropriately.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2tezdxuu2q9zq5fpazaj.png)

- Drag and drop the **Dropdown** component into the Container. We'll use this component for choosing between the models offered by the **Gemini** API.
- Rename this component as _modelDropdown_. Renaming the components will help quickly access their data during development.
- Similarly, drag-and-drop three **Textarea** components into the Container. We'll use these components for our Data Schema input, Text Query input and the third one to display the generated SQL query.
- Rename the three **Textarea** components as _databaseSchemaInput_, _textPrompt_, and _generatedQuery_ respectively.
- Adjust the height and width of the **Textarea** components appropriately.
- Under the **Properties** section, clear the Default value input and enter an appropriate Placeholder text.
- Drag and drop another **Text** component. We'll use this as a label for our generated query **Textarea** component. Change the colour, font size and content appropriately.
- Let's add our last component, drag-and-drop a **Button** component. We'll use this to trigger the SQL query generation. Change the colour, size and content appropriately.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/l1p7ggxevjpxwgviumtc.png)

-------------------------------------------------------------


## Creating Queries

ToolJet allows connecting to third-party APIs using its REST API query feature. We'll use this to integrate our UI with the Gemini API endpoints. We'll create two separate REST API queries:

1. The first query will fetch a list of all the AI models provided by the **Gemini** API.
2. The second query will be a POST request that sends user inputs to the **Gemini** API endpoint. It will return the generated SQL query based on those inputs.

We'll also utilise ToolJet's **Workspace Constants** to securely store our **Gemini** API key. Workspace Constants are resolved server-side. This ensures the actual values of the constants are not sent with network payloads; instead, the server resolves these values, thereby keeping them secure from client-side exposure.

- To create a **Workspace constant**, click on the ToolJet logo in the top left corner. From the dropdown, select **Workspace constants**.
- Click on the **Create new constant** button. Set the name as _GEMINI_API_KEY_ and enter your **Gemini** API key in the value input.
- Click on the **Add constant** button. This constant will now be available across our workspace and can be accessed using `{{constants.GEMINI_API_KEY}}`.
- Navigate back to your app and open the Query Manager.
- Click the **+ Add** button and choose the **REST API** option.
- Rename the query as _getModels_.
- Keep the Request Method as **GET** and paste the following URL in the URL input. This is the Gemini API endpoint that will return the models available to us. 
```
https://generativelanguage.googleapis.com/v1beta/models?key={{constants.GEMINI_API_KEY}}
```
- To ensure that the query runs every time the application loads, enable the **Run this query on application load?** toggle.
- Similarly, create another query and name it as _getSqlQuery_.
- In the **Request** parameter, choose **POST** as the Method from the drop-down and paste the following URL.

```
https://generativelanguage.googleapis.com/v1beta/{{components.modelDropdown.value}}:generateContent?key={{constants.GEMINI_API_KEY}}
```
- Navigate to the Body section of the _getSqlQuery_. Toggle on **Raw JSON** and enter the following code:

```
{{
  `{
   "contents": [{
     "parts": [{
       "text": "Data Schema: ${components.databaseSchemaInput.value.replaceAll("\n"," ")}, Text Prompt: Write a standard SQL query that will ${components.textPrompt.value.replaceAll("\n"," ")}. Return with correct formatting but without any code highlighting and any backticks"
      },],
    },],
  }`
}}
```

-------------------------------------------------------------


## Integrating the UI with Queries

Now that we have successfully built our UI and queries, the next step is to integrate them.

- Select the **Button** component, under the **Properties** section, and click the **New event handler** button to create a new event.
- Choose **On click** as the **Event**, **Run Query** as the **Action**, and select _getSqlQuery_ as the **Query**.
- Select the **Dropdown** component, under the **Properties** section, and enter the following code for the Option values and labels.

**Option values**:
 

```
{{queries.getModels.data.models.map(item => item.name)}}
```
**Option labels**: 


```
{{queries.getModels.data.models.map(item => item.displayName)}}
```
- Select the _generatedQuery_ **Textarea** component, under the **Properties** section, and enter the following code for the Default value input.


**Default value**: 

```
{{queries.getSqlQuery.data.candidates[0].content.parts[0].text}}
```

Our AI-powered SQL query generator is complete. Let's provide some sample data to test it out.

Database Schema:
```
Orders (id, product_id, address, customer_name, is_paid)
Products (id, quantity, moq)
Customers (id, name, email, phone, addresses)
```

Text Prompt/Query:
```
find all the prepaid orders from a customer named Alex JR
```

**Expected Output:**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1rjpn413n42hejyu2ofc.png)

-------------------------------------------------------------


## Conclusion

Congratulations on successfully building an AI-powered SQL query generator using ToolJet and the Gemini API. You can now input prompts in plain English which are then accurately translated into executable SQL statements.
To learn and explore more about ToolJet, check out the [ToolJet docs](https://docs.tooljet.com/docs/) or connect with us and post your queries on [Slack](https://join.slack.com/t/tooljet/shared_invite/zt-2ij7t3rzo-qV7WTUTyDVQkwVxTlpxQqw).