	In Browser
	StumbleUpon
	del.icio.us
	Google
	Google Buzz
	reddit
	LinkedIn

	Facebook
	Twitter
	Linkedin
	E-Mail

Generative AI > Google Gemini API > Gemini API for SQL Query Generation

Gemini API for SQL Query Generation

Author: Venkata Sudhakar

Gemini can translate natural language business questions into correct SQL queries. Combined with a schema-aware prompt, it generates queries that match your exact table structure. ShopMax India uses this to let their non-technical merchandising team query sales data without writing SQL, while backend validation ensures only safe read-only queries execute.

The key safety principle: never execute Gemini-generated SQL directly. Always parse and validate the query - check it is a SELECT statement, strip any DDL or DML commands, and use parameterised execution. Log every generated query for audit.

The below example shows how ShopMax India implements natural language to SQL with safety validation.

import google.generativeai as genai
import re

genai.configure(api_key="your-api-key")
model = genai.GenerativeModel("gemini-2.0-flash")

SCHEMA = """
Tables in shopmax_india database:
- orders(order_id, customer_id, city, total_amount, status, created_at)
- order_items(item_id, order_id, product_id, product_name, category, quantity, price)
- products(product_id, name, category, brand, stock_qty, list_price)
- customers(customer_id, name, city, state, registration_date)
"""

def generate_sql(question: str) -> str:
    prompt = f"""Given this database schema for ShopMax India:
{SCHEMA}
Convert this business question to SQL. Rules:
- Use only SELECT statements
- Use table aliases for readability
- Add LIMIT 1000 unless aggregating
- Return SQL only, no explanation

Question: {question}"""

response = model.generate_content(
        prompt,
        generation_config=genai.GenerationConfig(temperature=0.0)
    )
    return response.text.strip().strip("```sql").strip("```").strip()

def validate_sql(sql: str) -> bool:
    sql_upper = sql.upper().strip()
    # Only allow SELECT statements
    if not sql_upper.startswith("SELECT"):
        return False
    # Block dangerous keywords
    banned = ["DROP", "DELETE", "UPDATE", "INSERT", "ALTER", "TRUNCATE", "EXEC"]
    return not any(kw in sql_upper for kw in banned)

Running natural language queries against the database,

It gives the following output,

Question: What are the top 5 cities by total order value this month?
Generated SQL:
SELECT city, SUM(total_amount) as total_value
FROM orders
WHERE created_at >= DATE_TRUNC('month', CURRENT_DATE)
GROUP BY city ORDER BY total_value DESC LIMIT 5

Results: [{"city": "Mumbai", "total_value": 23487500},
          {"city": "Bangalore", "total_value": 19234000}]

Deploy this as an internal tool for the ShopMax India merchandising team. Add a query history table in Firestore, allow team members to save and share useful queries, and build a simple web UI where they type questions and see results - no SQL knowledge required.

Send your comments, suggestions or queries regarding this site to [email protected].