Building a SQL Report Generator using Gemini AI + ToolJet 📊
Tags: lowcode, gemini, javascript, ai
Published: 2024-06-18 13:00:56

## Introduction
This tutorial will guide you through the process of building an AI-driven SQL custom report 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 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](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 can locate and copy your API key.

Here is a quick preview of our final application:


![SQL Report Builder Preview](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xb7brsesfnu9t6ny61h1.png)

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

Login to your [ToolJet account](https://app.tooljet.com/). 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.


![SQL Report Builder UI](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5b1r1pifxlt1kj74xe0e.png)


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


## 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](https://docs.tooljet.com/docs/org-management/workspaces/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._

![SQL Report Example](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pbdvss1cd8b7mjucowym.png)

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](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).