DEV Community: Aman Regu

Building a Regex Generator with Gemini AI and ToolJet ⚙️

Aman Regu — Tue, 02 Jul 2024 13:02:20 +0000

Introduction

This tutorial will guide you through the process of building an AI-powered Regex 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 requests in plain English, which will then be translated into Regular Expressions (Regex). We'll use 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 endpoint.

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 can locate and copy your API key.

Here is a quick preview of our final application:

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

Assembling our UI

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 Text component onto the canvas. We'll use them as our header.
For the Icon component, navigate to the properties panel on the right and select the appropriate icon under the Icon property.
Change the colour of the Icon and Text component according to your preference.
Drag and drop one Text component and one Textarea component inside your canvas. We'll use these components for the label and input for our text query. Rename the Textarea component to textQueryInput.
Again, drag-and-drop two Text components and two Text Input components inside your container. We'll use them for displaying the generated regex expressions and testing a string against the generated regex expression respectively.
Rename the Text Input components as generatedRegex and testString respectively.
Lastly, drag and drop two Button components inside your container. We'll use them for initiating the regex expression generation and adding a copy to clipboard functionality respectively.
Rename the added Button components to generateRegex and copyToClipboard respectively.

Setting up Queries

ToolJet allows you to connect to various external data sources, including databases, external APIs, and services using its powerful low code query builder. For this tutorial, We'll be using the REST API query feature to connect with the Gemini API endpoints.

Using ToolJet's Workspace Constants feature, create a new constant named GEMINI_API_KEY with your Gemini API key.
In the query panel, click on the + Add button and choose the REST API option.
Rename the query to getRegexPattern.
In the Request parameter, choose POST as the Method from the drop-down and paste the following URL.

https://generativelanguage.googleapis.com/v1beta/models/gemini-1.5-pro:generateContent?key={{constants.GEMINI_API_KEY}}

Navigate to the Body section of getRegexPattern. Toggle on Raw JSON and enter the following code:

{{
  `{
   "contents": [{
     "parts": [{
       "text": "Text Prompt: Based on pattern, generate a regex without any code highlighting, formatting or backticks, Pattern: ${JSON.stringify(components.textQueryInput.value).replace(/\\?"/g, '\\"')}"
      },],
    },],
  }`
}}

Binding Queries to the UI Components

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

Select the generateRegex Button component and navigate to the properties panel on the right. Click on the + New event handler button. Change the Action to Run query and select the getRegexPattern query. This will trigger the getRegexPattern every time we click the generateRegex button.
Next, select the generatedRegex Text Input component and navigate to the properties panel on the right. Paste the following code into the Default value field:

{{queries.getRegexPattern.data.candidates[0].content.parts[0].text.trim()}}

Select the copyToClipboard Button component and navigate to the properties panel on the right. Click on the + New event handler button. Change the Action to Copy to clipboard. Paste the following code in the Text field under the Action Options subsection:

{{components.generatedRegex.value}}

Similarly, select the testString Text Input component and navigate to the properties panel on the right. Paste the following code in the Regex field under the Validation subsection:

{{queries.getRegexPattern.data.candidates[0].content.parts[0].text.trim()}}

We have successfully integrated everything together. Now let's test the application with the text query below:
Match any string that is at least 8 characters long, contains at least one lowercase letter, one uppercase letter, and one number

Conclusion

Congratulations on successfully creating an AI-powered Regex generator with ToolJet and the Gemini API. You can now input natural language prompts to generate Regular Expressions effortlessly.

To learn and explore more about ToolJet, check out the ToolJet docs or connect with us and post your queries on Slack.

Building a Mock Data Generator with Google Sheets, Gemini AI & ToolJet ⚙️

Aman Regu — Tue, 25 Jun 2024 13:33:12 +0000

Introduction

This tutorial will guide you through the process of building an AI-driven Mock Data Generator using ToolJet, a low-code visual app builder, and the Gemini API, a powerful natural language processing API. We'll also use ToolJet's build-in integration with Google Sheets to store our mock data. The resulting application will enable users to generate mock data based on the sample format data present in the spreadsheet. We'll use 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 and our Google Sheets data source.

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 : The Gemini API is an advanced AI service provided by Google AI Studio. It enables developers to integrate powerful content generation capabilities into their applications.
Google account with access to Google Sheets: Log into Google Sheets using your Google account and create a new spreadsheet. Add column names to define the structure of your data. Additionally, you can create at least one row of data.

Here is a quick preview of our final application:

Step 1: Prepare your Google Sheets Document

We will be starting this tutorial by setting up the Google Sheets document with the following data.

Step 2: Connecting Google Sheets to ToolJet

Once the spreadsheet is ready, let’s connect our Google Sheet to ToolJet. Follow the steps mentioned below.

On the ToolJet dashboard, locate the Data Sources section on the left sidebar. Click on the +Add button under the Google Sheets plugin.
Choose the Read and write option since we will be adding the mock data to our Google Sheet.
Once you click on Connect Data source, you will be redirected to grant access to ToolJet to your Google Sheets; grant the access and click Save data source.
Now that you have successfully connected Google Sheets to your ToolJet account, click the Apps icon on the left sidebar and select Create an app. Let’s name our app Mock Data Generator. Now that we’ve set up our App, it’s time to create the UI.

Step 3: Building the UI

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 the Text component onto your canvas. We'll use them as our logo and header.
For the Icon component, navigate to the properties panel on the right and select the appropriate icon under the Icon property.
Change the color of the Icon and Text component according to your preference.
Drag and drop the Dropdown component inside your container. We'll use this dropdown to choose between the available sheets. Rename this component to selectSheet.
Similarly, drag and drop two Button components inside the container. We'll use these buttons for generating mock data and saving the data to the Google Sheet.
Next, add a Table Component to display the generated mock data.

Step 4: Setting up Queries

1. Fetching the Sheets

Expand the Query Panel at the bottom and click the Add button to create a query - rename this query to getSheets.
Choose Data Source as googlesheets, and Operation as Get spreadsheet info.
In the Spreadsheet ID section, enter the spreadsheet ID of your sheet. To access the spreadsheet ID, check your Google Sheet's URL, the format should be: https://docs.google.com/spreadsheets/d/<SPREADHEET_ID>/edit#gid=0.
To ensure that this query runs every time the application loads, toggle Run this query on application load?
Enable the Transformations toggle and enter the following code:

return data.sheets.map(item => item.properties.title);

This will return all the sheet names in an Array. We'll use this to populate the values in our dropdown component.

2. Fetching initial sample data

Similarly, create another query and rename it to getInitialData.
Choose the Operation as Read data from a spreadsheet.
Enter the following code in the Sheet field:

{{components.selectSheet.value}}

3. Generating Mock Data using Gemini API

Using ToolJet's Workspace Constants feature, create a new constant named GEMINI_API_KEY with your Gemini API key.
In the query panel, click on the + Add **button and choose the **REST API option. Rename the query to generateMockData.
In the Request parameter, choose POST as the Method from the drop-down and paste the following URL. https://generativelanguage.googleapis.com/v1beta/models/gemini-1.5-pro:generateContent?key={{constants.GEMINI_API_KEY}}
Navigate to the Body section of getSqlQuery. Toggle on Raw JSON and enter the following code:

 {{
  `{
   "contents": [{
     "parts": [{
       "text": "Sample Data: ${JSON.stringify(queries.getInitialData.data[0]).replace(/\\?"/g, '\\"')}, Text Prompt: Based on the sample data, only return an Array with 10 objects with same type of mock data without any code highlighting, formatting or backticks"
      },],
    },],
  }`
}}

4. Inserting data into our Google Sheet

Create another query and choose Data Source as googlesheets, and Operation as Append data to a spreadsheet.
Enter the following code in the Sheet field:

{{components.selectSheet.value}}

Enter the following code in the Rows field:

{{JSON.parse(queries.generateMockData.data.candidates[0].content.parts[0].text)}}

Step 5: Binding Queries to the UI Components

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

Select the Dropdown component, under the Properties section, and enter the following code for both Option values and labels fields: {{queries.getSheets.data}}
Select the Generate Data Button component, under the Properties section, click the New event handler button to create a new event.
Choose On click as the Event, Run Query as the Action, and select getInitialData as the Query.
Select the getInitialData query, and click the New event handler button to create a new event.
Choose Query Success as the Event, Run Query as the Action, and select generateMockData as the Query.
Next, Select the Table component. In the properties panel on the right, enter the following code in the Data field.

{{JSON.parse(queries.generateMockData.data.candidates[0].content.parts[0].text)}}

Select the Save to Google Sheets Button component, under the Properties section, click the New event handler button to create a new event.
Choose On click as the Event, Run Query as the Action, and select insertData as the Query. We have successfully integrated our queries into our UI.

Now let's test the application with the following sample data format:

Table Preview:
Click on the Save to Google Sheets Button.

Conclusion

Congratulations on successfully building an AI-driven Mock Data Generator using ToolJet and Gemini API.

To learn and explore more about ToolJet, check out the ToolJet docs or connect with us and post your queries on Slack.

Building a SQL Report Generator using Gemini AI + ToolJet 📊

Aman Regu — Tue, 18 Jun 2024 13:00:56 +0000

Introduction

This tutorial will guide you through the process of building an AI-driven SQL custom report 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 requests in plain English, which will then be translated into custom reports. We'll use 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 and our data sources. The final product will enable users to preview generated reports and download them in PDF, Excel, or CSV formats.

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 can locate and copy your API key.

Here is a quick preview of our final application:

Building our UI

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 three Text components inside your Container. We'll use these Text components for our header and label texts.
For the Icon component, navigate to the properties panel on the right and select the appropriate icon under the Icon property.
Change the colour of the Icon and Text component according to your preference.
Change the font size and content of the Text component appropriately.
Drag and drop the Textarea component inside your Container. We'll use this component as an input for our text query.
Rename the Textarea component to textPrompt.
Next, drag and drop the Table component onto the Container. We'll use this component to display a preview of our report. The Table component comes built-in with the functionality to download the displayed data. This will allow us to download our generated report in PDF, Excel, or CSV formats.
Now let's add a Button component that initiates the report generation process. Change the colour, size and content appropriately.

Setting up Queries

Apart from its built-in database and data sources, ToolJet allows you to connect to various external data sources, including databases, external APIs, and services. For this tutorial, we'll be using ToolJet's built-in PostgreSQL sample data source. The queries we'll set up will be applicable to an external PostgreSQL data source as well.

We'll also be using the REST API query feature to connect with the Gemini API endpoints.

In the query panel, click the + Add button and choose the Sample data source option.
Rename the query to getDatabaseSchema.
In the dropdown, choose the SQL mode and enter the code below. This will fetch all the table names in our database along with their column names.

SELECT table_name, string_agg(column_name, ', ') AS columns
FROM information_schema.columns
WHERE table_schema = 'public'
GROUP BY table_name

To ensure that the query runs every time the application loads, enable the Run this query on application load? toggle.

Now, let's create another query that will connect to the Gemini AI API and generate our custom SQL report query.

Using ToolJet's Workspace Constants feature, create a new constant named GEMINI_API_KEY with your Gemini API key.
In the query panel, click on the + Add button and choose the REST API option.
Rename the query to getSqlQuery.
In the Request parameter, choose POST as the Method from the drop-down and paste the following URL.

https://generativelanguage.googleapis.com/v1beta/models/gemini-1.5-pro:generateContent?key={{constants.GEMINI_API_KEY}}

Navigate to the Body section of getSqlQuery. Toggle on Raw JSON and enter the following code:

{{
  `{
   "contents": [{
     "parts": [{
       "text": "Data Schema: ${JSON.stringify(queries.getTablesWithColumns.data.map(item => ({ ...item, table_name: "public." + item.table_name }))).replace(/"([^"]+)":/g, '$1:').replace(/"/g, '\\"')}, Text Prompt: Write a standard SQL query for a custom SQL report that will ${components.textPrompt.value.replaceAll("\n"," ")}. Return without formatting and without any code highlighting and any backticks"
      },],
    },],
  }`
}}

Let's add our final query which will retrieve the data from the sample data source that we need for our custom report.

Similarly, create another Sample data source query, rename it to getReportData and enter the code below:

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

Binding Queries to the UI Components

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

Select the Button component and navigate to the properties panel on the right. Click on the + New event handler button. Change the Action to Run query and select the getSqlQuery query.
Next, navigate to the getSqlQuery query and click on the + New event handler button. Change the Action to Run query and select the getReportData query.
Next, Select the Table component. In the properties panel on the right, enter the following code in the Data field.

{{queries.getReportData.data}}

We have successfully integrated our queries into our UI. Now let's test the application with the prompt below:
list the names of customers along with the products they have ordered, including the order date and the total quantity ordered for each product.

You can click on the + button on the Table footer to download this report in PDF, Excel, or CSV formats.

Conclusion

Congratulations on successfully building an AI-powered SQL report generator using ToolJet and the Gemini API. You can now input prompts in plain English and generate reports across multiple tables in your PostgreSQL instance.
To learn and explore more about ToolJet, check out the ToolJet docs or connect with us and post your queries on Slack.

Building an Intelligent Reimbursement Tracking App using OCR with Gemini API + ToolJet 🚀

Aman Regu — Thu, 06 Jun 2024 13:25:06 +0000

Introduction

This tutorial will guide you through building an intelligent reimbursement tracking app with OCR using ToolJet and the Gemini API. The app will allow users to upload images of receipts, extract text from the images using OCR, and store the extracted information in a database. We'll also add an AWS S3 integration to store the receipt images.

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:

Building 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 instructional text.
Select the Icon component, navigate to its properties panel on the right and select the ZoomMoney icon under its Icon property.
Change the font size and content of the Text component appropriately.
Drag and drop the File Picker and the Button component inside your container. We'll use the File Picker component to allow users to upload images of their receipts. The Button component will be used to trigger the OCR process.
Rename the File Picker component to fileUploader.
Adjust the width of the File Picker component according to your preference.
Change the colour and text of the Button component according to your preference.

We have implemented the user interface for uploading receipts. The next step is to create the UI for administrators to approve or reject expense submissions.

Click on the Pages icon on the left side of the screen. On the header of the Pages Panel, click on the + button to create a new page.
Drag and drop the Container component onto the canvas. Adjust the height and width of the Container component appropriately.
Drag and drop the Icon and a Text component inside your container. We'll use these two components for our logo and header text.
Drag and drop the Table component inside the container. We'll use the Table component to display the list of expense submissions. We'll later also add Action Buttons to approve, reject, and view the receipt image for each submission.
Drag and drop the Modal component inside the container. Open the Modal component and drag and drop the HTML component inside the Modal. We'll use the HTML component to display the receipt image.

Creating Queries

ToolJet allows connecting to third-party APIs using its REST API query feature. We'll use this to connect to the Gemini API to extract the text from the uploaded receipt images. ToolJet comes with built-in integration with AWS S3, we'll use this to store the receipt images.

Using ToolJet's Workspace Constants feature, create a new constant named GEMINI_API_KEY with your Gemini API key.
In the query panel, click the + Add button and choose the REST API option.
Rename the query to extractTextFromImage. In the Request parameter, choose POST as the Method from the drop-down and paste the following URL.

https://generativelanguage.googleapis.com/v1beta/models/gemini-1.5-pro:generateContent?key={{constants.GEMINI_API_KEY}}

Navigate to the Body section of the extractTextFromImage. Toggle on Raw JSON and enter the following code:

{
  "contents": [
    {
      "parts": [
        {
          "text": "In this image of a receipt, analyze the receipt image and return the following information in JSON format without any formatting or syntax highlighting: total_amount, date."
        },
        {
          "inline_data": {
            "mime_type":"image/jpeg",
              "data": "{{components.fileUploader.file[0].base64Data}}"
          }
        }
      ]
    }
  ]
}

Next, we'll create a query to save extracted text to the ToolJet database.
Click on the ToolJet logo on the top left corner and select the Database option.
Click on the Create new table button and name the table reimbursement_requests. Add the following columns to the table: id, name, email, total_amount, status and receipt_date.

Navigate back to the Query panel and click on the + Add button. Choose the ToolJet Database option.
Rename the query to addReceiptData.
Select the reimbursement_requests table name from the drop-down.
In the Operation drop-down, choose Create row and enter the following data for the columns.

Column Name	Key
email	`{{globals.currentUser.email}}`
name	`{{globals.currentUser.name}}`
total_amount	`{{JSON.parse(queries.extractTextFromImage.data.candidates[0].content.parts[0].text).total_amount}}`
receipt_date	`{{JSON.parse(queries.extractTextFromImage.data.candidates[0].content.parts[0].text).date}}`

Next, we'll create a query to store the receipt image in the AWS S3 bucket.
Create a new data source to connect to the AWS S3 using ToolJet's built-in AWS S3 integration.
Click on the + Add button in the Query panel and choose the newly created AWS S3 data source.
Rename the query to addToS3 and select the operation as Upload object.
Add the following data for the columns:

Column Name	Key
Bucket	bucket name (should already exist)
Key	`{{"reimbursement_id" + "_" + queries.addReceiptData.data[0].id}}`
Content Type	`{{components.fileUploader.file[0].type}}`
Upload Data	`{{JSON.parse(queries.extractTextFromImage.data.candidates[0].content.parts[0].text).date}}`
Encoding	base64

Next, we'll create the query to fetch the list of expense submissions from the ToolJet database.

Click on the + Add button in the Query panel and choose the ToolJet Database option and rename the query to getReimbursementRequests.
Select the reimbursement_requests table from the drop-down.
In the Operation drop-down, choose List rows.
To ensure that the query runs every time the application loads, enable the Run this query on application load? toggle.
Next, create two more ToolJet database queries to approve and reject the expense submissions, named approveRequest and rejectRequest.
For both of these queries, select the reimbursement_requests table and choose the Update row operation and to use the filter field to match the id of the row to be updated using the {{components.reimbursementRequestsTable.selectedRow.id}} variable.
In the columns field, update the Status column to approved for the approveRequest query and rejected for the rejectRequest query.

Let's add our last query to fetch the receipt image from the AWS S3 bucket.

Click on the + Add button in the Query panel and choose the AWS S3 data source and rename the query to getReceiptImage and enter the following data.

Column Name	Key
Operation	Signed url for download
Bucket	bucket name
Key	`{{"reimbursement_id" + "_" + components.table1.selectedRow.id}}`
Expiries in	3600

Binding Queries to the UI Components

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

Select the Button component and navigate to the properties panel on the right. Click on the + New event handler button. Change the Action to Run query and select the extractTextFromImage query.
Next, navigate to the extractTextFromImage query and click on the + New event handler button. Change the Action to Run query and select the addReceiptData query.
Navigate to the addReceiptData query and click on the + New event handler button. Change the Action to Run query and select the addToS3 query.
Our upload receipts feature is now complete. You can test this out by uploading a receipt image and verify the extracted data in your ToolJet database.

Next, we'll implement the admin approval feature.

Navigate to the Admin page and select the Table component. In the properties panel on the right, enter {{queries.getReimbursementRequests.data}} in the Data field.
Click on the + New action button in the properties panel and create three new actions buttons: Approve, Reject, and View Receipt. Bind the approveRequest and rejectRequest to the respective action buttons using Event Handlers.
Change the background colour and text of the action buttons according to your preference.
Click on the View Receipt action button and Add a new event handler. Change the Action to Open Modal and select the displayReceiptImage modal.
Navigate to the displayReceiptImage modal and in the properties panel on the right, uncheck the Use default trigger button toggle.
Inside the modal, click on the HTML component and enter <img src={{queries.getReceiptFromS3.data.url}}> in the Raw HTML field.
Our admin approval feature is now complete. You can test this out by uploading a receipt image and verifying the extracted data in the Table component. You can click on the Approve or Reject buttons to approve or reject the expense submission. You can also view the receipt image by clicking on the View Receipt button.

Conclusion

Congratulations! You've successfully built a powerful reimbursement tracking app with OCR capabilities using ToolJet and the Gemini API. You can now track and manage expense submissions with ease. Feel free to customize the app further by adding more features and functionalities.
To learn and explore more about ToolJet, check out the ToolJet docs or connect with us and post your queries on Slack.

Building a SQL Query Generator Using ToolJet + Gemini API

Aman Regu — Thu, 30 May 2024 13:03:44 +0000

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.