AskPostgreSQL

A powerful web application that enables users to interact with a PostgreSQL database through natural language queries, powered by AI (GPT-5 Mini), with instant visualizations and query history.

Features

• Natural Language Queries: Ask questions in plain English
• Interactive Visualizations: Automatic chart generation with Recharts
• Query History: Save and reload previous queries with all data
• User Authentication: Secure login system with NextAuth.js
• Modern UI: Beautiful interface with Tailwind CSS and shadcn/ui
• Real-time Results: Instant SQL generation and execution
• Responsive Design: Works perfectly on desktop and mobile

Architecture

Technology Stack

• Frontend: Next.js 15.5.2 with App Router, React 19, TypeScript
• Styling: Tailwind CSS with shadcn/ui components
• Database: PostgreSQL with Drizzle ORM
• Authentication: NextAuth.js with JWT sessions
• AI Integration: OpenAI GPT-5 Mini API
• Visualization: Recharts for interactive charts
• Animations: Framer Motion for smooth transitions

Quick Start Guide

Prerequisites

Before you begin, ensure you have the following installed:

• Node.js (v18 or higher) - Download here
• PostgreSQL (v12 or higher) - Download here
• Git - Download here
• OpenAI API Key - Get one here

Step 1: Clone the Repository

git clone https://github.com/DebojyotiMishra/AskPostgreSQL
cd askpostgresql

Step 2: Install Dependencies

pnpm install

Step 3: Set Up PostgreSQL Database

1. Start PostgreSQL service (if not already running):

   # On macOS with Homebrew
   brew services start postgresql
   
   # On Ubuntu/Debian
   sudo systemctl start postgresql
   
   # On Windows
   # Start PostgreSQL service from Services or pgAdmin

2. Create a database:

   # Connect to PostgreSQL
   psql -U postgres
   
   # Create database
   CREATE DATABASE unicorns;
   
   # Create user (optional, you can use postgres user)
   CREATE USER your_username WITH PASSWORD 'your_password';
   GRANT ALL PRIVILEGES ON DATABASE unicorns TO your_username;
   
   # Exit psql
   \q

Step 4: Configure Environment Variables

Create a .env file in the root directory:

# Database Configuration
POSTGRES_URL="postgresql://username:password@localhost:5432/unicorns"
POSTGRES_PRISMA_URL="postgresql://username:password@localhost:5432/unicorns"
POSTGRES_URL_NO_SSL="postgresql://username:password@localhost:5432/unicorns"
POSTGRES_URL_NON_POOLING="postgresql://username:password@localhost:5432/unicorns"
POSTGRES_USER="username"
POSTGRES_HOST="localhost"
POSTGRES_PASSWORD="password"
POSTGRES_DATABASE="unicorns"

# OpenAI API Key
OPENAI_API_KEY="sk-your-openai-api-key-here"

# Table Name (optional, defaults to 'unicorn_companies')
TABLE_NAME="unicorn_companies"

# NextAuth Configuration
NEXTAUTH_SECRET="your-super-secret-key-here-change-in-production"
NEXTAUTH_URL="http://localhost:3000"

Important: Replace the placeholder values with your actual database credentials and OpenAI API key.

Step 5: Set Up the Database

Run the migration and seed scripts to create the database schema and populate it with sample data:

# Create database tables
pnpm run migrate

# Populate with sample data
pnpm run seed

# Verify everything is working
pnpm run check-db

You should see output like:

Checking database tables and data...

Available tables:
  users
  query_history
  unicorn_companies

Users: 1 records
Query History: 0 records
Unicorns: 1277 records

Database check completed!

Step 6: Start the Development Server

pnpm run dev

The application will be available at http://localhost:3000

Step 7: Create Your First User Account

1. Go to http://localhost:3000/signup
2. Create a new account with your desired username and password
3. You'll be redirected to the login page
4. Log in with your new credentials

Step 8: Start Querying!

1. Once logged in, you'll see the main interface
2. Try asking questions like:
   • "What are the top 5 companies by valuation?"
   • "Show me all companies from the United States"
   • "Which companies are in the healthcare industry?"
   • "What's the average valuation by country?"

Database Schema

The application uses three main tables:

users Table

• id - Primary key (serial)
• username - Unique username (varchar)
• password_hash - Hashed password (varchar)
• created_at - Account creation timestamp

query_history Table

• id - Primary key (serial)
• user_id - Foreign key to users table
• question - Original user question (text)
• sql_query - Generated SQL query (text)
• results - Query results as JSON (text)
• chart_config - Chart configuration as JSON (text)
• explanation - AI-generated explanation as JSON (text)
• created_at - Query timestamp

unicorn_companies Table

• id - Primary key (serial)
• company - Company name (varchar)
• valuation - Valuation in billions (decimal)
• date_joined - Date when company joined (date)
• country - Company country (varchar)
• city - Company city (varchar)
• industry - Industry category (varchar)
• select_investors - Comma-separated list of investors (text)

Available Scripts

Command	Description
pnpm run dev	Start development server with Turbo
pnpm run build	Build the application for production
pnpm run start	Start the production server
pnpm run lint	Run ESLint for code quality
pnpm run seed	Populate database with sample data
pnpm run migrate	Run database migrations
pnpm run check-db	Check database status and data

Troubleshooting

Common Issues

1. Database Connection Error

Error: POSTGRES_URL is not set in the environment

• Solution: Make sure your .env file exists and contains the correct POSTGRES_URL

2. JWT Session Error

[next-auth][error][JWT_SESSION_ERROR] decryption operation failed

• Solution: Clear your browser cookies and restart the server

3. OpenAI API Error

AI service quota exceeded

• Solution: Check your OpenAI API key and billing status

4. Port Already in Use

Port 3000 is in use

• Solution: Kill the process using port 3000 or use a different port

Reset Everything

If you encounter persistent issues, you can reset everything:

# Stop the server
# Kill any processes using port 3000

# Clear database
psql "postgresql://username:password@localhost:5432/unicorns" -c "DROP SCHEMA public CASCADE; CREATE SCHEMA public;"

# Reinstall dependencies
rm -rf node_modules pnpm-lock.yaml
pnpm install

# Recreate database
pnpm run migrate
pnpm run seed

# Start fresh
pnpm run dev

Production Deployment

Environment Variables for Production

# Database (use your production database URL)
POSTGRES_URL="postgresql://username:password@your-production-db:5432/unicorns"

# OpenAI API
OPENAI_API_KEY="sk-your-production-openai-key"

# NextAuth (use a secure secret)
NEXTAUTH_SECRET="your-super-secure-production-secret"
NEXTAUTH_URL="https://your-domain.com"

# Table name
TABLE_NAME="unicorn_companies"

Build for Production

pnpm run build
pnpm run start