Building a SQL Report Generator using Gemini AI + ToolJet 📊

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:

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.

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.