Building a SQL Query Generator Using ToolJet + Gemini API

#tooljet #lowcode #ai #gemini

Introduction

This tutorial will guide you through the process of building an AI-driven SQL query generator using 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 for a free ToolJet cloud account or run ToolJet on your local machine using Docker.
Gemini API Key : Log into Google AI Studio 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:

Crafting our UI

Login to your ToolJet account. 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.

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.

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:

The first query will fetch a list of all the AI models provided by the Gemini API.
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:

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 or connect with us and post your queries on Slack.