A conversational AI assistant that lets anyone query a live bookstore database using plain English. Powered by Gemini AI for natural language understanding and AlloyDB PostgreSQL for data storage, it converts user questions into SQL, executes them, and returns friendly human-readable answers — all through a clean chat interface deployed on Google Cloud Run.

Traditional database systems require users to know SQL to query data — a skill most end users simply don't have. This creates a barrier between business stakeholders and the data they need, forcing them to rely on developers or analysts to answer even basic questions.

The Challenge: How can non-technical users query a live database using plain English — without writing a single line of SQL — while still getting accurate, real-time results from structured data?

This project answers that question by building an AI-enabled database feature using Google Cloud's AlloyDB for PostgreSQL, combined with Gemini AI, to create a natural language interface for a bookstore dataset.

A fully working, cloud-deployed web application where users type questions in plain English and receive meaningful answers sourced directly from a live AlloyDB PostgreSQL database — with no SQL knowledge required.

The system combines three components — a FastAPI backend, Google's Gemini AI model, and an AlloyDB PostgreSQL database — into a seamless pipeline that transforms a plain English question into a real database result and a human-readable answer.

Cloud-run URL : https://bookstore-api-1059652519537.us-central1.run.app/

Step by step:

1. User asks a question — types in plain English, e.g. "Which book has the highest rating?"
2. FastAPI receives the request — the /query endpoint accepts the question as JSON and begins the pipeline
3. Gemini generates SQL — the question and full database schema are sent to Gemini 1.5 Flash, which returns a valid PostgreSQL SELECT query
4. SQL executes on AlloyDB — the generated SQL runs against the live database via psycopg2 and returns structured rows
5. Gemini generates the answer — raw results are sent back to Gemini, which produces a concise, friendly natural language response
6. Response returned to user — the API returns the question, generated SQL, raw results, and AI answer to the chat UI

CREATE TABLE authors (
    id      SERIAL PRIMARY KEY,
    name    TEXT NOT NULL,
    country TEXT
);

CREATE TABLE books (
    id        SERIAL PRIMARY KEY,
    title     TEXT NOT NULL,
    author_id INT REFERENCES authors(id),
    genre     TEXT,
    price     NUMERIC(10,2),
    rating    NUMERIC(2,1),
    stock     INT
);

CREATE TABLE orders (
    id         SERIAL PRIMARY KEY,
    book_id    INT REFERENCES books(id),
    quantity   INT,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Sample data:

• Google Cloud project with AlloyDB instance running
• AlloyDB public IP enabled with your IP in authorized networks
• Gemini API key from aistudio.google.com
• Python 3.11+

bookstore-api/
├── main.py
├── requirements.txt
├── Dockerfile
├── .dockerignore
├── .env
└── static/
    └── index.html

Create a .env file:

DB_HOST=<your-alloydb-public-ip>
DB_PORT=5432
DB_NAME=book
DB_USER=postgres
DB_PASSWORD=<your-password>
GEMINI_API_KEY=<your-gemini-api-key>
GCP_PROJECT_ID=<your-gcp-project-id>

pip install -r requirements.txt

uvicorn main:app --host 0.0.0.0 --port 8080 --reload

# Open: http://localhost:8080

# Enable required APIs
gcloud services enable run.googleapis.com \
  artifactregistry.googleapis.com \
  cloudbuild.googleapis.com

# Deploy
gcloud run deploy bookstore-api \
  --source . \
  --region us-central1 \
  --allow-unauthenticated \
  --port 8080 \
  --set-env-vars="DB_HOST=<ip>,DB_PORT=5432,DB_NAME=book,DB_USER=postgres,DB_PASSWORD=<pwd>,GEMINI_API_KEY=<key>" \
  --memory 512Mi \
  --cpu 1

# You will receive a public URL:
# https://bookstore-api-xxxx-uc.a.run.app

Example request:

curl -X POST http://localhost:8080/query \
  -H "Content-Type: application/json" \
  -d '{"question": "Which book has the highest rating?"}'

Example response:

{
  "question": "Which book has the highest rating?",
  "sql": "SELECT title, rating FROM books ORDER BY rating DESC LIMIT 1",
  "results": [{"title": "Atomic Habits", "rating": 4.8}],
  "answer": "Atomic Habits by James Clear has the highest rating of 4.8 out of 5!"
}

• SQL Validation Layer — retry with a corrected prompt if Gemini returns invalid SQL
• Conversation History — maintain context across multiple questions in a session
• Streaming Responses — stream Gemini output token-by-token for faster perceived response
• Query Caching — cache frequent questions to reduce API calls and latency
• Input Sanitization — validate all generated queries before execution to prevent issues

• alloydb_ai_nl Integration — migrate to native AlloyDB NL-to-SQL once the extension reaches GA
• Authentication — add Google OAuth or API key auth to secure the /query endpoint
• Larger Dataset — expand to thousands of books with reviews, categories, and inventory history
• Voice Input — add speech-to-text so users can ask questions by speaking
• Multi-language Support — handle questions in Tamil, Hindi, and other regional languages

• AlloyDB Vector Search — use pgvector for semantic book recommendations alongside NL queries
• Analytics Dashboard — charts showing sales trends, popular genres, and stock levels
• Multi-tenant SaaS — allow different bookstores to connect their own AlloyDB databases
• Fine-tuned SQL Model — fine-tune a smaller model on the bookstore schema for cheaper inference
• Mobile App — React Native frontend connecting to the same Cloud Run API

Built with ❤️ on Google Cloud — AlloyDB · Gemini AI · FastAPI · Cloud Run