joelgilbert/NL2SQL

1# Sample Database Setup Guide2 3## 📊 What's Included4 5I've created a realistic **e-commerce database** with:6 7- **6 Tables**: regions, customers, categories, products, orders, order_items8- **200+ rows** of sample data9- **Relationships**: Foreign keys connecting all tables10- **2 Views**: Pre-built analytics views11- **Indexes**: For optimal query performance12 13## 🚀 Quick Setup (3 minutes)14 15### Option 1: Using psql (Recommended)16 17```bash18# Connect to your Neon database19psql "your_neon_connection_string_here"20 21# Run the setup script22\i scripts/sample_data.sql23 24# Exit25\q26```27 28### Option 2: Using Neon Web Console29 301. Go to [console.neon.tech](https://console.neon.tech)312. Select your project323. Click on "SQL Editor"334. Copy the contents of `scripts/sample_data.sql`345. Paste and click "Run"35 36### Option 3: Using DBeaver/pgAdmin37 381. Open your database tool392. Connect to Neon using your connection string403. Open `scripts/sample_data.sql`414. Execute the script42 43## 📋 Database Schema44 45```46┌─────────────┐47│   regions   │48└──────┬──────┘49       │50       ├─────────────┐51       │             │52┌──────┴────────┐    │53│   customers   │    │54└──────┬────────┘    │55       │             │56       │   ┌─────────┴────────┐57       │   │    categories    │58       │   └─────────┬────────┘59       │             │60       │   ┌─────────┴────────┐61       │   │     products     │62       │   └─────────┬────────┘63       │             │64┌──────┴────────┐    │65│    orders     │    │66└──────┬────────┘    │67       │             │68┌──────┴────────┐────┘69│  order_items  │70└───────────────┘71```72 73## 🎯 Sample Natural Language Queries to Test74 75Once your database is set up, try these questions in the NL2SQL app:76 77### **Easy Queries**78- "How many customers do we have?"79- "Show me all active customers"80- "List all products in the Electronics category"81- "What regions do we serve?"82 83### **Medium Queries**84- "Show me total sales by category"85- "Who are our top 5 customers by total spending?"86- "How many orders were placed last month?"87- "What's the average order value?"88 89### **Advanced Queries**90- "Show me monthly revenue for the last 3 months"91- "Which products are low in stock (less than 50 units)?"92- "List customers who haven't ordered anything"93- "What's the total revenue by region?"94 95### **Time-Based Queries**96- "Show me orders from last week"97- "How many sales did we have today?"98- "What's our revenue for November 2024?"99 100### **Analytical Queries**101- "Which category generates the most revenue?"102- "Show me the top 3 best-selling products"103- "How many orders are currently being shipped?"104- "What's the average customer loyalty points?"105 106## 📊 Database Statistics107 108After running the script, you'll have:109 110| Table | Rows | Description |111|-------|------|-------------|112| **regions** | 5 | Geographic regions (NA, Europe, Asia, etc.) |113| **customers** | 15 | Customer profiles with loyalty points |114| **categories** | 6 | Product categories |115| **products** | 25 | Products with prices and stock |116| **orders** | 20 | Order history (last 3 months) |117| **order_items** | 35+ | Individual items in each order |118| **Views** | 2 | Pre-built analytics queries |119 120## 🔍 Verify Installation121 122Check if everything loaded correctly:123 124```sql125-- Count rows in each table126SELECT 'regions' as table_name, COUNT(*) as rows FROM regions127UNION ALL128SELECT 'customers', COUNT(*) FROM customers129UNION ALL130SELECT 'products', COUNT(*) FROM products131UNION ALL132SELECT 'orders', COUNT(*) FROM orders;133 134-- Test a query135SELECT * FROM sales_summary;136```137 138Expected output:139- regions: 5 rows140- customers: 15 rows  141- products: 25 rows142- orders: 20 rows143 144## 🎨 Sample Data Highlights145 146### Customers147- 15 customers across 5 regions148- Mix of active (13) and inactive (2) customers149- Loyalty points ranging from 50 to 4,100150 151### Products152- 25 products across 6 categories153- Prices from $14.99 to $299.99154- Stock quantities from 28 to 250 units155- 8 featured products156 157### Orders158- 20 orders spanning 3 months159- Status types: delivered, shipped, processing, cancelled160- Order values from $44.99 to $559.96161- Most recent orders from last week162 163## 🔐 Setting Up Read-Only User (Important!)164 165After loading the data, create a read-only user for the NL2SQL app:166 167```sql168-- Create readonly user169CREATE USER nl2sql_readonly WITH PASSWORD 'your_secure_password';170 171-- Grant permissions172GRANT CONNECT ON DATABASE your_database TO nl2sql_readonly;173GRANT USAGE ON SCHEMA public TO nl2sql_readonly;174GRANT SELECT ON ALL TABLES IN SCHEMA public TO nl2sql_readonly;175GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO nl2sql_readonly;176 177-- For future tables178ALTER DEFAULT PRIVILEGES IN SCHEMA public 179GRANT SELECT ON TABLES TO nl2sql_readonly;180```181 182Then update your `.env` file:183```env184NEON_READONLY_CONNECTION_STRING=postgresql://nl2sql_readonly:your_secure_password@host/db?sslmode=require185```186 187## 🚦 Next Steps188 1891. ✅ **Load the sample data** (using one of the methods above)1902. ✅ **Verify the data** (run verification queries)1913. ✅ **Create read-only user** (for security)1924. ✅ **Update .env file** (with readonly connection string)1935. ✅ **Initialize vector store** (`python scripts/init_vector_store.py`)1946. ✅ **Test the app** (`streamlit run app.py`)195 196## 💡 Pro Tips197 198- **Keep the DBA connection** for your main admin account199- **Use readonly connection** for the NL2SQL app in production200- **The sample data is realistic** - it mimics a real e-commerce business201- **Try complex queries** - the system handles joins, aggregations, and time-based filters202- **Check audit logs** - See what SQL was generated for each question203 204## 🔄 Reset Database205 206To start fresh:207 208```sql209-- Drop all tables210DROP TABLE IF EXISTS order_items CASCADE;211DROP TABLE IF EXISTS orders CASCADE;212DROP TABLE IF EXISTS products CASCADE;213DROP TABLE IF EXISTS categories CASCADE;214DROP TABLE IF EXISTS customers CASCADE;215DROP TABLE IF EXISTS regions CASCADE;216 217-- Then re-run the sample_data.sql script218```219 220## ❓ Troubleshooting221 222### "Permission denied" error223- Make sure you're connected as the database owner224- Check that your user has CREATE TABLE permissions225 226### "Database doesn't exist"227- Create a database first in Neon console228- Use the connection string for that specific database229 230### Tables already exist231- Either drop them first (see Reset Database above)232- Or use a different database name233 234---235 236**Your database is now ready for testing! 🎉**237 238Try asking questions in natural language and watch the AI translate them to SQL!239