GeminiPostSQL 1.1.0

See in spanish/Ver en español

GeminiPostSQL NuGet | AI-Assisted NuGet Package for PostgreSQL

This NuGet package uses Google's AI 'Gemini 2.0 Flash' to make queries to PostgreSQL databases.
The AI interprets natural language into SQL queries using one method, with its pros and cons.

📋 Prerequisites

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

🛠️ 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 are 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 further more detailed information.

You now have everything needed to make the program work.
Simply put that data you just got into the Client object.

📖 About the NuGet package

To use it, you must initialize the Client which has three methods:

GenerateSQL(string input): Translates natural language to SQL queries. In case the Client's parameter 'mapOnGenerateSQL' is activated, if 'jsonMap' is empty, it will automatically map the database into JSON. Returns the generated SQL in case of success, else will return an error message (they all start with '[ERROR]'). In case the Client's parameter 'runOnGenerateSQL' is activated, if succeeds, the method will return the a serialized JSON of the result table.

MapDatabase(): Maps the database into a serialized JSON. Returns a boolean value, depending if the mapping process were successful or not, if it is, the JSON is saved in the Client's parameter 'jsonMap'.

RunQuery(string generatedSql): Runs the generated query (or any) in the database server. Return the result table for the generated SQL sentence. If it fails, it will return a table with only one column named 'Error' with the description of the error.

🚀 Releases

The version will be released using these versioning policies:
New major features and critical bug fixes will cause the immediate release of a new version, while other minor changes or fixes will wait one week since the time the change is introduced in the repository before being included in the new version, so that other potential changes can be added.

The version number will follow this format:
[Major Feature].[Minor Feature].[Bug Fixes]

💻 Technologies Used

• Programming Language: C#
• Framework: .Net 8.0 Framework
• NuGet Packages:
  • Npgsql (8.0.5)
  • RestSharp (112.1.0)
  • Newtonsoft.Json (13.0.3)
• Other:
  • PostgreSQL (16.3)
  • pgAdmin 4 (8.9)
  • Gemini API Key (2.0 Flash)
  • Images (Icons source, later retouched by me):
    • FreeIcons
• Recommended IDE: Visual Studio 2022