See in spanish/Ver en español

GeminiPostSQL ChatBot | AI-Assisted Blazor ChatBot for PostgreSQL

Note

Check out other versions of this program:

• WinForms
• REST API
• NuGet
• Android

This ChatBot program uses Google's AI 'Gemini 2.0 Flash' to make queries to PostgreSQL databases.
The AI interprets natural language into SQL queries, run them, and transform the results into natural language again.

📋 Prerequisites

To make this program work, you'll need a PostgreSQL Server and a Gemini API Key.

Note

I'll use pgAdmin to build the PostgreSQL Server.

🛠️ Setup

If you don't have it, download pgAdmin 4 from the official website.
Now, create the PostgreSQL Server and set up a database with a few tables and insert values.

Next, obtain your Gemini API Key by visiting Google AI Studio. Ensure you're logged into your Google account, then press the blue button that says 'Create API key' and follow the steps to set up your Google Cloud Project and retrieve your API key. Make sure to save it in a safe place.
Google allows free use of this API without adding billing information, but there are some limitations.

In Google AI Studio, you can monitor the AI's usage by clicking 'View usage data' in the 'Plan' column where your projects are displayed. I recommend monitoring the 'Quota and system limits' tab and sorting by 'actual usage percentage,' as it provides the most detailed information.

You now have everything needed to make the program work.
Simply put that data you just got into the code's parameters strings.

📖 About the Blazor ChatBot

The natural-sql-natural process methods works like this:

This method maps the database structure into a JSON that Gemini analyzes to create an SQL query to fulfill the user's request, which is then run by the PostgreSQL Server, returning the requested data.

Then, this data is processed by Gemini again to summary the results into natural language.

Because the AI needs to process two requests (text to query and result table to text) the token usage is pretty high, thats why the remembering capability of the ChatBot is limited to the 5 prior messages, as is not meant to keep a single chat for too long.

The chats are not stored so the ChatBot consists in a volatile single-session chat. Press F5 to clear the current chat.

💻 Technologies Used

• Programming Language: C#
• Framework: Blazor, .Net 8.0 Framework(13.0.3)
• Other:
  • PostgreSQL (16.3)
  • pgAdmin 4 (8.9)
  • Gemini API Key (2.0 Flash)
• Recommended IDE: Visual Studio 2022