ChatSQL Server

ChatSQL is a command-line tool that lets you query a PostgreSQL database using natural language. It uses Google Gemini to convert your questions into SQL, and FastMCP to execute them against your database.

---

🚀 Tech Stack

• Python 3.11+
• PostgreSQL (via psycopg2)
• FastMCP (Modular Composable Protocol)
• Google Gemini (for Natural Language to SQL conversion)
• Rich (for pretty command-line interface output)
• python-dotenv (for environment variable configuration)

---

🧠 Use Case

You ask a question in plain English, and ChatSQL generates and executes the SQL, then displays the results.

Input: "Get all employees earning more than 50000"

Generated SQL: SELECT * FROM employees WHERE salary > 50000;

Output:

+----+---------+--------+------------+-----------+------------+

| id | name | salary | phone_no | address | department |

+----+---------+--------+------------+-----------+------------+

| 1 | Alice | 75000 | 9876543210 | New York | HR |

| 2 | Bob | 65000 | 1234567890 | Chicago | Finance |

+----+---------+--------+------------+-----------+------------+

---

⚙️ Setup Instructions

✅ 1. Clone the Repository & Configure Environment

> git clone https://github.com/thomasjerard/ChatSQL.git
> cd chatSQL

✅ 2. Prerequisites

• Bash shell is required (default on macOS/Linux).
• Python 3.11+ must be installed and accessible via your terminal. If not installed,

For macOS:

brew install [email protected]

For Linux:

sudo apt install python3.11 python3.11-venv

✅ 3. Create a .env file in the root folder with the following content:

DB_NAME=chattosql
DB_USER=postgres
DB_PASS=yourpassword
DB_HOST=localhost
DB_PORT=5432
GEMINI_API_KEY=your-gemini-api-key

✅ 3. Run the Application

You’ll need two separate terminals:

🖥️ Terminal 1 — Start the Server

./run_server.sh

💬Terminal 2 — Start the Client

./run_client.sh

Once the client starts, you'll see a prompt like:

💬 ChatSQL (Gemini + FastMCP) 🗨️ Ask (NL):

🔧 What Happens Internally

1. Each script (run_server.sh / run_client.sh) performs the following:

2. Deletes any existing virtual environment and creates a fresh one.

3. Loads environment variables from the .env file.

4. Installs required Python dependencies from requirements.txt.

5. Sets up PostgreSQL:

6. Creates the database (chattosql)

7. Adds the user (postgres)

8. Creates a sample employees table

9. Starts the FastMCP server or the ChatSQL CLI client, depending on the script.