Postgres Visualizer

A minimal open-source Postgres visualizer with AI-powered SQL generation, built with React, Next.js, and Tailwind CSS. Inspired by Supabase SQL Editor and Navicat.

Features

• SQL Editor: Monaco Editor with syntax highlighting and Shift+Enter execution
• Query Results: Paginated table view (50 rows/page) with JSON fallback
• AI Assistant: Natural language to SQL conversion powered by Gemini 2.0 Flash
• Schema Explorer: Auto-fetch and display database tables and columns
• Resizable Panels: Drag the divider to adjust AI panel width
• Dark Mode: Supabase-inspired dark theme
• Read-Only Mode: Blocks destructive operations (DROP, DELETE, UPDATE, INSERT)

Tech Stack

• Frontend: Next.js 14 (App Router), React 18, TypeScript
• Styling: Tailwind CSS
• Editor: Monaco Editor
• Database: PostgreSQL (pg library)
• AI: Google Gemini 2.0 Flash

Prerequisites

• Node.js 18+ and npm
• PostgreSQL database access
• Gemini API key (get from https://ai.google.dev/)

Setup Instructions

1. Install Dependencies

npm install

2. Configure Environment Variables

Create a .env.local file and add your credentials:

# PostgreSQL Database
DB_HOST=your-database-host.rds.amazonaws.com
DB_PORT=5432
DB_NAME=your-database-name
DB_USER=your-database-user
DB_PASSWORD=your-database-password
DB_SSL=true

# Gemini API
GEMINI_API_KEY=your-gemini-api-key-here

Important: Replace all placeholder values with your actual database credentials and API key. You can reference .env.example for the template.

3. Run Development Server

npm run dev

Open http://localhost:3000 in your browser.

4. Build for Production

npm run build
npm start

Usage

SQL Editor

1. Type SQL queries in the Monaco Editor (left panel)
2. Press Shift + Enter to execute
3. View results in the bottom panel

AI Assistant

1. Type a natural language request in the AI Assistant (right panel)
2. Click Send to generate SQL
3. Click Copy to Editor to insert generated SQL into the editor
4. Press Shift + Enter to run the query

Schema Explorer

• View all database tables and columns
• Expand/collapse tables to see column details
• Schema is automatically provided to AI for context-aware SQL generation

Resizable Panels

• Drag the vertical divider between the main panel and AI panel to adjust width
• Right panel width ranges from 300px to 800px
• Width preference is maintained during the session

Example Queries

Test SQL Query

-- Get first 10 rows from users table
SELECT * FROM users LIMIT 10;

-- Count total users
SELECT COUNT(*) as total_users FROM users;

-- Get users created in last 7 days
SELECT id, email, created_at
FROM users
WHERE created_at >= NOW() - INTERVAL '7 days'
ORDER BY created_at DESC;

AI Assistant Examples

Input: "Show me all users who signed up in the last month"

Output:

SELECT * FROM users
WHERE created_at >= NOW() - INTERVAL '1 month'
ORDER BY created_at DESC
LIMIT 100;

---

Input: "Count users by country"

Output:

SELECT country, COUNT(*) as user_count
FROM users
GROUP BY country
ORDER BY user_count DESC
LIMIT 100;

---

Input: "Find users with email containing 'gmail'"

Output:

SELECT id, email, created_at
FROM users
WHERE email ILIKE '%gmail%'
LIMIT 100;

Project Structure

postgres-ai/
├── src/
│   ├── app/
│   │   ├── layout.tsx          # Root layout
│   │   ├── page.tsx            # Main visualizer page
│   │   ├── globals.css         # Tailwind + custom styles
│   │   └── api/
│   │       ├── sql/route.ts    # Execute SQL queries
│   │       ├── ai/route.ts     # Generate SQL from NL
│   │       └── schema/route.ts # Fetch database schema
│   ├── components/
│   │   ├── SQLEditor.tsx       # Monaco editor wrapper
│   │   ├── ResultsPanel.tsx    # Query results display
│   │   ├── AIAssistant.tsx     # Gemini chat interface
│   │   └── SchemaExplorer.tsx  # Database schema browser
│   ├── lib/
│   │   ├── db.ts               # Postgres connection pool
│   │   └── gemini.ts           # AI integration
│   └── types/
│       └── index.ts            # TypeScript types
├── .env.local                  # Environment variables
├── package.json
├── next.config.js
├── tailwind.config.ts
├── tsconfig.json
└── README.md

Security Features

• Read-Only Mode: Blocks DROP, DELETE, UPDATE, INSERT, ALTER, CREATE operations
• Parameterized Queries: Prevents SQL injection
• Connection Pooling: Singleton pattern with max 20 connections
• SSL Support: Secure database connections
• Query Timeout: 5 seconds connection timeout
• Server-Side API: Database credentials never exposed to client

Keyboard Shortcuts

• Shift + Enter: Execute SQL query in the editor

Mouse Controls

• Drag divider: Resize the AI panel width (between main and right panel)

Troubleshooting

Connection Errors

If you see "Failed to fetch schema" or query execution errors:

1. Verify database credentials in .env.local
2. Check if database allows connections from your IP
3. Ensure PostgreSQL is running
4. Verify SSL settings (DB_SSL=true for RDS)

Gemini API Errors

If AI generation fails:

1. Verify GEMINI_API_KEY in .env.local
2. Check API quota at https://ai.google.dev/
3. Ensure you're using gemini-2.0-flash model

Monaco Editor Not Loading

1. Clear .next cache: rm -rf .next
2. Reinstall dependencies: rm -rf node_modules && npm install
3. Restart dev server

Development Notes

• Monaco Editor runs client-side ("use client" directive)
• API routes are server-side with database access
• Hot reload preserves pg Pool singleton
• Dark mode is default (no toggle needed)

License

MIT

Contributing

Contributions welcome! Open issues or submit PRs.