Consideration: For safety reasons, the agent is not designed to edit, create, or delete tables. It is only designed to generate SQL queries that read data from existing tables. It has a built-in safety mechanism that prevents it from generating DDL statements. Please take note of this.

sqlgen - The Ultimate 'SQL Generator'

Meet SQL Generator (sqlgen 🤖⚡), An AI agent that converts user specifications into SQL queries whose outputs' analyses are given to the user

Note: This project is still under development!!!

Installation and Usage

Prerequisites

• Latest version of Python 3
• Groq API key
• Postgres installed locally
• WSL Ubuntu installed (if you're on a Windows machine)

Steps

Note: If you're wondering why we should install Postgres instead of Snowflake is because Postgres is what we'll stick to for now - we like Postgres. However, the default database will be Snowflake in the future, so stay tuned!

Phase 1: Set up the Postgres Database

1. Launch pgAdmin:

Open pgAdmin (you can find it in your Applications folder or by searching for it on your Mac or Windows). This will start the pgAdmin application where you can manage your PostgreSQL databases.

2. Connect to Your PostgreSQL Server:

• If it's your first time opening pgAdmin, you’ll need to connect to the PostgreSQL server. Typically, the default connection settings for PostgreSQL are as follows:

  • Host: localhost
  • Username: postgres (default superuser)
  • Password: (the password you set during PostgreSQL installation)

• After entering your password, click "Save" and connect to the PostgreSQL instance.

3. Create a New Database:

• In the Browser panel on the left, right-click on the "Databases" node and select Create > Database.
• In the window that appears, enter your database name (e.g., mydatabase) and click Save.

4. Create a New User (Role):

• In the Browser panel, expand the "Login/Group Roles" section under your server node.
• Right-click on Login/Group Roles and select Create > Login/Group Role.
• In the new window:
  • Under the General tab, enter your username (e.g., myuser).
  • Under the Definition tab, set a password for the user (e.g., mypassword). Store it somewhere safe in case you forget it.
• Once done, click Save.

5. Grant Privileges on Your Database to the New User:

• In the Browser panel, navigate to your database (mydatabase).
• Right-click on mydatabase and select Properties.
• In the properties window, go to the Privileges tab.
• Add the new user (myuser) by clicking the + button, then select the user from the list.
• Ensure that you check the appropriate privileges (like Connect, Create, Temporary).
• Click Save.

6. Disconnect and Exit:

• Once you’re finished, you can disconnect from the server by right-clicking on the server node in the Browser panel and selecting Disconnect.

That’s it! This setup allows you to begin using PostgreSQL on your local machine.

Phase 2: Clone the Repository, Install Dependencies, and Run the Script

First, clone this repository:

git clone https://github.com/auzuma/sqlgen.git

Then, install the dependencies:

python3 -m pip install -r requirements.txt

Then, set your GROQ_API_KEY environment variable, as well as the DB_NAME, DB_USER, and DB_PASSWORD in the .env file:

GROQ_API_KEY=your_groq_api_key
DB_NAME=mydatabase # replace with the database name you created in Phase 1
DB_USER=myuser # replace with the username you created in Phase 1
DB_PASSWORD=mypassword # replace with the password you created in Phase 1

Then, run the script:

python3 sqlgen.py

This will prompt you to enter your user request, and then it will generate a SQL query that works in Snowflake. Then the agent will run the query on your local postgres and return the results.

Additionally, you can prompt it via the API endpoint:

curl -X POST http://localhost:5000/generate-sql -H "Content-Type: application/json" -d '{"query": "I need to know the total sales for each product in the last 30 days."}'

In powershell:

Invoke-RestMethod -Uri "http://localhost:5000/generate-sql" `
                 -Method Post `
                 -Headers @{"Content-Type"="application/json"} `
                 -Body '{"query": "I need to know the total sales for each product in the last 30 days."}'

or

curl -Uri "http://localhost:5000/generate-sql" `
     -Method Post `
     -Headers @{"Content-Type"="application/json"} `
     -Body '{"query": "I need to know the total sales for each product in the last 30 days."}'

This will return the SQL query output by the agent in the response body.

If you're not sure where to start, please consider checking out the example_prompts file for some inspiration.

Contribution and Support

Though we welcome any contributions, please note that this project is still under development. Also, we don't have any contribution guidelines yet - so please consider waiting for the project to become more stable before contributing.

For now, please consider starring the repo if you're interested in the project!

And if you have any questions or run into any issues, please consider opening a new issue on the repo!

Thanks for your interest in the project! 🤗

Licensing

This project is licensed under the Apache 2.0 License. See the LICENSE file for details.