Skip to content

NgoHuuLoc0612/SQL-Query-Visualizer

BranchesTags

Repository files navigation

SQL Query Visualizer

A full-stack web application that parses, visualizes, and AI-analyzes SQL queries. Write any SQL query and instantly see it as an interactive flow diagram, get AI-powered performance suggestions, convert natural language to SQL, and save your query history — all in one place.

Features

  • Interactive Flow Canvas — Visualizes SQL queries as node-based flow diagrams using React Flow, showing tables, joins, filters, aggregates, CTEs, and subqueries as connected nodes.
  • SQL Editor — Monaco Editor (the engine behind VS Code) with syntax highlighting, keyboard shortcuts, and one-click formatting via sql-formatter.
  • Multi-dialect Parser — Automatically tries PostgreSQL, MySQL, TransactSQL, SQLite, BigQuery, MariaDB, Hive, and FlinkSQL dialects using node-sql-parser until one succeeds.
  • AI Analysis — Sends parsed query structure to Groq (Llama 3.3 70B) for deep analysis: complexity breakdown, performance estimation, index recommendations, anti-pattern detection, and a fully optimized rewrite.
  • AI Chat — Conversational assistant that stays in context of your current query, powered by Groq.
  • Natural Language to SQL (NL2SQL) — Describe what you want in plain English and get a ready-to-use SQL query back.
  • Query History — Every analyzed query is saved to Supabase with its full analysis and AI results. Supports favorites, tags, and search.
  • Schema Context — Define your database schema so the AI can generate more accurate, schema-aware SQL and suggestions.
  • Authentication — Supabase Auth with Row Level Security — each user only sees their own data.
  • Resizable Panel — Drag to resize the sidebar panel that houses Analysis, AI Chat, History, and Schema tabs.

Tech Stack

Layer Technology
Framework Next.js 14 (App Router)
Language TypeScript
Styling Tailwind CSS + Radix UI
Editor Monaco Editor
Flow Diagram React Flow + Dagre (auto-layout)
SQL Parsing node-sql-parser
SQL Formatting sql-formatter
AI / LLM Groq SDK (Llama 3.3 70B)
Auth + Database Supabase (Auth + PostgreSQL + RLS)
State Management Zustand
Animations Framer Motion

Project Structure

SQL-Query-Visualizer/
├── app/
│   ├── page.tsx                  # Root redirect (login ↔ dashboard)
│   ├── layout.tsx
│   ├── globals.css
│   ├── login/page.tsx            # Supabase Auth UI
│   ├── dashboard/page.tsx        # Main app shell
│   ├── auth/
│   │   ├── callback/route.ts     # OAuth callback handler
│   │   └── session/page.tsx
│   └── api/
│       ├── ai/
│       │   ├── analyze/route.ts  # POST → AI analysis via Groq
│       │   ├── chat/route.ts     # POST → AI chat via Groq
│       │   └── nl2sql/route.ts   # POST → NL to SQL via Groq
│       ├── analyze/route.ts      # POST → SQL parse + structural analysis
│       ├── format/route.ts       # POST → SQL formatting
│       ├── validate/route.ts     # POST → SQL syntax validation
│       └── query/history/route.ts
├── components/
│   ├── ai/
│   │   ├── AiPanel.tsx           # AI chat interface
│   │   ├── AnalysisPanel.tsx     # Structural analysis display
│   │   ├── HistoryPanel.tsx      # Saved query history
│   │   └── SchemaPanel.tsx       # Schema context editor
│   ├── editor/
│   │   ├── SqlEditor.tsx         # Monaco Editor wrapper
│   │   └── NL2SqlBar.tsx         # Natural language input bar
│   ├── flow/
│   │   ├── FlowCanvas.tsx        # React Flow canvas
│   │   └── SqlNodes.tsx          # Custom node components
│   └── ui/                       # Reusable UI primitives (Radix-based)
├── lib/
│   ├── sql-parser.ts             # SERVER ONLY — node-sql-parser + sql-formatter
│   ├── sql-client.ts             # Client-side wrappers (calls API routes)
│   ├── flow-builder.ts           # SqlAnalysis → React Flow nodes/edges
│   ├── groq.ts                   # Groq AI functions (analyze, chat, nl2sql)
│   ├── supabase.ts               # Supabase client (browser + server)
│   ├── store.ts                  # Zustand global state
│   └── utils.ts
├── types/index.ts                # Shared TypeScript types
├── supabase/migrations/
│   └── 001_initial_schema.sql    # DB schema + RLS policies
└── .env.local.example

Authentication

The app supports three ways to log in or create an account, all powered by Supabase Auth.

Option 1 — Email & Password

On the login page, enter your email and password and click Sign in. To create a new account, switch to the Sign up tab, fill in your email and a password (minimum 8 characters), and click Create account. If email confirmation is enabled in your Supabase project, you will receive a confirmation link before being redirected to the dashboard.

Forgot your password? Click Forgot password?, enter your email, and a reset link will be sent to your inbox. The link expires in 1 hour.

Option 2 — Sign in with GitHub

  1. Click the GitHub button on the login page.
  2. You are redirected to GitHub to authorize the app. The OAuth app requests read:user and user:email scopes.
  3. After authorizing, GitHub redirects back to {APP_URL}/auth/callback with a code parameter.
  4. The callback route (app/auth/callback/route.ts) exchanges the code for a Supabase session and passes the access and refresh tokens to /auth/session.
  5. The browser Supabase client picks up the tokens and you are redirected to /dashboard.

Required setup in Supabase:

  • Go to Authentication → Providers → GitHub and enable it.
  • Create a GitHub OAuth App at github.com/settings/developers.
  • Set the Authorization callback URL to: https://<your-supabase-project>.supabase.co/auth/v1/callback
  • Copy the Client ID and Client Secret into Supabase.

Option 3 — Sign in with Google

  1. Click the Google button on the login page.
  2. You are redirected to Google's OAuth consent screen. The app requests offline access with prompt: consent to obtain a refresh token.
  3. After granting access, Google redirects back to {APP_URL}/auth/callback with a code parameter.
  4. The same callback route exchanges the code for a Supabase session and redirects you to /dashboard.

Required setup in Supabase:

  • Go to Authentication → Providers → Google and enable it.
  • Create OAuth 2.0 credentials in Google Cloud Console under APIs & Services → Credentials.
  • Set the Authorized redirect URI to: https://<your-supabase-project>.supabase.co/auth/v1/callback
  • Copy the Client ID and Client Secret into Supabase.

OAuth Callback Flow (both providers)

User clicks GitHub / Google
        ↓
Supabase redirects user to provider consent screen
        ↓
Provider redirects to /auth/callback?code=...
        ↓
callback/route.ts exchanges code → Supabase session
        ↓
Redirects to /auth/session?access_token=...&refresh_token=...
        ↓
Browser client sets session → redirect to /dashboard

Local development: Add http://localhost:3000/auth/callback to the Allowed Redirect URLs list in Supabase under Authentication → URL Configuration.

Getting Started

Prerequisites

1. Clone and Install

git clone https://github.com/NgoHuuLoc0612/SQL-Query-Visualizer.git
cd SQL-Query-Visualizer
npm install

2. Configure Environment Variables

Copy the example file and fill in your credentials:

cp .env.local.example .env.local

Edit .env.local:

# Supabase
NEXT_PUBLIC_SUPABASE_URL=https://your-project.supabase.co
NEXT_PUBLIC_SUPABASE_ANON_KEY=your-anon-key-here
SUPABASE_SERVICE_ROLE_KEY=your-service-role-key-here

# Groq
GROQ_API_KEY=your-groq-api-key-here

# App
NEXT_PUBLIC_APP_URL=http://localhost:3000

Where to find these:

  • Supabase keys: Project Settings → API
  • Groq key: console.groq.com → API Keys

3. Set Up the Database

In your Supabase project, open the SQL Editor and run the contents of:

supabase/migrations/001_initial_schema.sql

This creates the query_history, saved_queries, and schema_definitions tables with proper indexes and Row Level Security policies.

4. Run the Development Server

npm run dev

Open http://localhost:3000. You will be redirected to the login page. Sign up or log in with Supabase Auth.

Available Scripts

Command Description
npm run dev Start development server
npm run build Build for production
npm start Run production build
npm run lint Run ESLint

Database Schema

query_history

Stores every analyzed query per user.

Column Type Description
id UUID Primary key
user_id UUID FK to auth.users
sql TEXT Raw SQL query
name TEXT Optional user-given name
tags TEXT[] Tag array (GIN indexed)
analysis JSONB Structural parse result
ai_analysis JSONB Groq AI analysis result
is_favorite BOOLEAN Starred/favorited flag
created_at TIMESTAMPTZ

saved_queries

Named queries explicitly saved by the user (distinct from auto-saved history).

schema_definitions

Per-user database schema context, stored as JSONB. Used to give the AI accurate table/column information.

All tables have Row Level Security enabled — users can only read and write their own rows.

API Routes

Route Method Description
/api/analyze POST Parse SQL and return SqlAnalysis
/api/format POST Format SQL using sql-formatter
/api/validate POST Validate SQL syntax across dialects
/api/ai/analyze POST Run Groq AI analysis on a query
/api/ai/chat POST Send a chat message in query context
/api/ai/nl2sql POST Convert natural language to SQL
/api/query/history GET/POST Fetch or save query history

How It Works

  1. Write SQL in the Monaco editor (or use NL2SQL to generate it).
  2. Click Visualize — the query is sent to /api/analyze, parsed by node-sql-parser using the first dialect that succeeds, and the resulting SqlAnalysis is stored in Zustand state.
  3. flow-builder.ts converts the SqlAnalysis into React Flow nodes and edges. Dagre handles automatic layout.
  4. The Flow Canvas renders the diagram. Each node type (table, join, filter, aggregate, CTE, etc.) has its own styled component defined in SqlNodes.tsx.
  5. Clicking AI Analyze sends the SQL and pre-parsed analysis to /api/ai/analyze, which calls Groq and returns a structured JSON report with suggestions, an optimized rewrite, and index recommendations.
  6. The query and its analysis are automatically saved to Supabase for the authenticated user.

Complexity Scoring

The parser calculates a complexity score used for both the flow diagram and AI analysis:

Factor Weight
Each table +1
Each JOIN +2
Each subquery +3
Each aggregate +1
Each GROUP BY column +1
Each CTE +2
Each UNION +2
Each WHERE filter +1
Score Label
≤ 3 Simple
4–8 Moderate
9–15 Complex
> 15 Very Complex

Deployment

Vercel (recommended)

  1. Push your repo to GitHub.
  2. Import the project in vercel.com.
  3. Add all environment variables from .env.local in the Vercel project settings.
  4. Deploy. Vercel automatically handles the Next.js build.

Make sure to update NEXT_PUBLIC_APP_URL to your production domain and add your production URL to Supabase's Allowed Redirect URLs (Authentication → URL Configuration).

License

MIT

About

First Next.js project. It's kinda terrible

Resources

Readme

License

MIT license
Activity

Stars

2 stars

Watchers

0 watching

Forks

0 forks
Report repository

Releases

No releases published

Packages

 
 
 

Contributors

Languages