SQL Query Analyzer

A modern, full-featured web application for running SQL queries and visualizing results with interactive charts.

📚 Detailed Documentation

• Features - Comprehensive guide to all features and their rationale
• Technical Decisions - Architecture and technology choices

Quick Reference

The Features document provides detailed explanations of:

• Core functionality (Query Editor, Tabs, Templates, History, Saved Queries)
• Results management (Table, Charts, Export)
• User experience (Themes, Keyboard Shortcuts, Error Handling)
• Performance features (Virtual Scrolling, State Management)

The Technical Decisions document explains:

• Why we chose Zustand, Tailwind, Shadcn UI, Monaco Editor
• Architecture patterns and component organization
• Performance considerations and optimization strategies

🚀 Features

Core Functionality

• SQL Query Editor with Monaco Editor - VS Code editor with full SQL syntax highlighting
• Multiple Query Tabs - Execute and compare multiple queries simultaneously (up to 10 tabs)
• Query Templates - 3 predefined queries with descriptions
• Query History - Last 20 queries with timestamps and localStorage persistence
• Mock Query Execution - 3 predefined datasets with simulated loading (500ms-1s)

Results & Visualization

• Results Table - TanStack Table with sorting, filtering, and pagination
• Interactive Charts - Bar, Line, Pie, and Area charts using Recharts
• Manual Axis Selection - Choose X and Y axes for custom visualizations
• Smart Data Display - Numeric formatting, date handling, and type-aware rendering

Query Management

• Saved Queries - Bookmark queries with custom names and descriptions
• Import/Export - Save and restore queries as JSON files
• Query History - Access recently executed queries
• Template Library - Pre-built analytics queries

Export & Actions

• CSV Export - Download results as CSV files
• JSON Export - Export data in JSON format
• Clipboard Copy - Copy table data to clipboard
• Quick Actions - Export all data, clear filters, reset view

User Experience

• Dark/Light Mode - System-aware theme switching with persistence
• Responsive Design - Works on desktop and tablet screens
• Keyboard Shortcuts - Power-user productivity features
• Toast Notifications - Success/error feedback for all actions
• Tab Persistence - Restore your workspace after reload

🛠️ Tech Stack

Core Framework

• React 18 with TypeScript
• Vite for fast builds and hot module replacement

UI Libraries

• Shadcn UI (Radix UI primitives) - Modern, accessible components
• Tailwind CSS - Utility-first CSS framework
• Lucide React - Beautiful icon library

Specialized Libraries

• Monaco Editor (@monaco-editor/react) - VS Code editor with SQL syntax highlighting
• TanStack Table - Headless table with sorting, filtering, and pagination
• TanStack Virtual - Row virtualization for handling large datasets
• Recharts - Modern, composable charting library
• Zustand - Lightweight state management
• date-fns - Date formatting and manipulation
• xlsx - Excel export functionality

Deployment

• Surge.sh - Static web publishing for front-end developers

📦 Installation

Prerequisites

• Node.js 18+
• npm or yarn

Steps

1. Clone the repository

git clone <repository-url>
cd sql-query-analyzer

2. Install dependencies

npm install

3. Start development server

npm run dev

4. Build for production

npm run build

5. Preview production build

npm run preview

🏃 Running the App

After installation, the app will be available at http://localhost:5173

Getting Started

1. Select a Template - Click on any query template from the sidebar to load it into the editor
2. Execute Query - Press Ctrl/Cmd + Enter or click the "Execute" button
3. View Results - Results appear in the table below the editor
4. Create Charts - Select X and Y axes in the Charts panel to visualize data
5. Save Query - Click "Save Current Query" to bookmark it for later use
6. Multiple Tabs - Use Ctrl/Cmd + T to open new tabs and compare queries

📊 Demo Video

https://www.loom.com/share/984b2ade28fa4612afef4d79efe55db7 A concise walkthrough video (< 3 minutes) demonstrating:

• Query execution
• Results exploration
• Chart creation
• Tab management
• Export functionality

⚡ Performance

Page Load Time

Measured Load Time: ~800ms (0.8 seconds)

How We Measured:

We use the browser's Performance Navigation Timing API to measure actual load times. Check the browser console when the app loads - you'll see detailed metrics logged automatically.

To Measure Yourself:

Option 1: Browser Console

1. Open the application
2. Open browser DevTools (F12)
3. Check the console for performance output
4. Look for metrics like:
   • DNS Lookup
   • TCP Connection
   • Time to First Byte
   • DOM Content Loaded
   • Full Page Load

Option 2: DevTools Network Tab

1. Open DevTools (F12)
2. Go to Network tab
3. Refresh the page
4. Look at the "DOMContentLoaded" and "Load" events at the bottom

Option 3: Performance API in Console

// Run this in browser console
const perfData = performance.getEntriesByType('navigation')[0];
console.log(`Full Page Load: ${(perfData.loadEventEnd - perfData.fetchStart).toFixed(2)}ms`);
console.log(`DOM Content Loaded: ${(perfData.domContentLoadedEventEnd - perfData.fetchStart).toFixed(2)}ms`);

Query Execution Performance

100,000 Rows: ~1-2 seconds to generate and render first view

Why It's Fast with Large Datasets:

• Virtual Scrolling: Only renders visible rows (~18 at a time)
• Efficient State Management: Zustand minimizes re-renders
• No Unnecessary Work: Skips hidden elements, lazy evaluation

Performance Optimizations Implemented

1. Code Splitting

   • Monaco Editor: Lazy-loaded with React.lazy (heaviest component)
   • Chart Libraries: Dynamic imports reduce initial bundle
   • Tree-shaking: Vite removes unused code automatically
   • Minification: Terser minifier for production builds
   • Result: Initial load only downloads necessary code

2. Virtual Scrolling

   • Library: TanStack Virtual (@tanstack/react-virtual)
   • Implementation: Only renders visible rows + buffer
   • Fixed Row Height: 50px for precise calculations
   • Overscan: Renders extra rows above/below viewport for smooth scrolling
   • Result: Handles 100,000+ rows smoothly (~18 DOM nodes at once)

3. State Management

   • Library: Zustand (lightweight, fast)
   • Minimal Re-renders: Selectors prevent unnecessary updates
   • No Provider Overhead: Direct state access, no wrapper components
   • Efficient Updates: Only affected components re-render
   • Result: Fast state updates without lag

4. Memoization

   • React.memo: Expensive components (table rows, charts, filters)
   • useMemo: Filtered data, chart transformations, computed values
   • useCallback: Event handlers, function props
   • Result: Prevents recalculation on every render

5. Bundle Optimization

   • Tree-shaking: Unused code eliminated by Vite
   • CSS Purging: Tailwind removes unused styles
   • Code Splitting: Dynamic imports for route/code chunks
   • Compression: Gzip compression for production
   • Result: ~250KB gzipped total bundle

6. Efficient Data Structures

   • Column-based Type Detection: Optimized sorting/filtering per type
   • Debounced Inputs: 300ms delay on search/filter inputs
   • Lazy Filter Evaluation: Filters only run when user pauses typing
   • Result: Responsive UI even with complex operations

Performance Test Template

We've included a "Performance Test" template that generates 100,000 rows to stress test the application and demonstrate these optimizations.

To Use:

1. Click "Performance Test" in the Templates sidebar (under "Testing" category)
2. Click Execute or press Ctrl/Cmd + Enter
3. Watch the execution time in the bottom navbar
4. Scroll smoothly through 100,000 rows

Expected Results:

• Generation Time: ~1-2 seconds (data creation + execution timing)
• First Render: <100ms (virtualized view)
• Scroll Performance: 60 FPS (smooth scrolling)
• Memory Usage: Stable, no memory leaks
• UI Responsiveness: Remains responsive during execution

What This Demonstrates:

• Virtual scrolling handles massive datasets efficiently
• State management prevents UI freezing
• Memory doesn't grow unbounded
• User experience remains smooth

📝 Usage Guide

Query Editor

• SQL Syntax Highlighting - Automatic highlighting for SQL keywords
• Auto-completion - SQL keywords with Tab completion
• Keyboard Shortcuts - Ctrl/Cmd + Enter to execute

Query Templates

1. Click on any template in the sidebar
2. SQL loads into the editor
3. Click "Execute" to run

Query History

• Shows last 20 executed queries
• Click "Load" to restore a query
• Clear history with one click

Saved Queries

1. Write or load a query
2. Click "Save Current Query"
3. Enter name and description
4. Import/Export saved queries as JSON

Results Table

• Sort - Click column headers (toggle asc/desc)
• Filter - Use the search box to filter all columns
• Paginate - Navigate through results 50 rows at a time
• Select Rows - Click row checkboxes

Charts Panel

1. Select chart type (Bar, Line, Pie, Area)
2. Choose X-axis column (categorical data)
3. Choose Y-axis column (numeric data)
4. Chart auto-generates with aggregated values

Export Data

• CSV - Download as comma-separated values
• JSON - Export as structured JSON
• Clipboard - Copy to system clipboard
• All Data - Export entire dataset via Quick Actions

Tabs

• New Tab - Ctrl/Cmd + T (max 10 tabs)
• Close Tab - Click X button or Ctrl/Cmd + W
• Switch Tab - Click on tab or use Ctrl/Cmd + Tab

⌨️ Keyboard Shortcuts

Shortcut	Action
Ctrl/Cmd + Enter	Execute query
Ctrl/Cmd + T	New tab
Ctrl/Cmd + W	Close current tab
Ctrl/Cmd + S	Save current query
Ctrl/Cmd + H	Toggle history (not implemented)
Ctrl/Cmd + K	Focus search filter
Ctrl/Cmd + /	Show shortcuts help (not implemented)
Ctrl/Cmd + E	Export current results
Esc	Close dialogs/modals
Tab	Next input/element
Shift + Tab	Previous input/element

🌐 Deployment

The application is deployed on Surge.sh: https://sql-query-analyzer.surge.sh

Deployment Steps

1. Build the application:

npm run build

2. Install Surge CLI (if not already installed):

npm install -g surge

3. Deploy to Surge.sh:

surge dist --domain sql-query-analyzer.surge.sh

Or use a custom domain:

surge dist --domain your-custom-domain.surge.sh

📄 Project Structure

sql-query-analyzer/
├── src/
│   ├── components/
│   │   ├── query/          # Query editor and templates
│   │   ├── results/        # Results table and charts
│   │   ├── tabs/           # Tab management
│   │   ├── saved/          # Saved queries
│   │   ├── ui/             # Shadcn UI components
│   │   └── common/         # Shared components
│   ├── services/            # Query execution, export services
│   ├── hooks/              # Custom React hooks
│   ├── context/            # React Context providers
│   ├── store/              # Zustand stores
│   ├── types/              # TypeScript definitions
│   ├── utils/              # Utility functions
│   ├── data/               # Mock data and templates
│   └── App.tsx             # Main application
├── public/                 # Static assets
├── package.json
├── tsconfig.json
├── vite.config.ts
└── README.md

🔧 Configuration

Theme Configuration

Themes are stored in localStorage with key sql-query-analyzer-theme

Query Storage

• History: sql-query-analyzer-history
• Saved: sql-query-analyzer-saved
• Tabs: sql-query-analyzer-tabs

🐛 Known Limitations

This is a demonstration application with the following limitations:

1. No Real Database - Uses mock data generators, no actual SQL execution
2. No Query Validation - SQL syntax is not validated (only basic checks)
3. No Authentication - No user authentication or authorization
4. Limited Chart Types - Basic chart types only (no advanced visualizations)
5. No Real-time Updates - Static data only, no live streaming
6. Mock Execution Time - Fixed 500ms-1s delay, not realistic

🚧 Future Enhancements

• Real database connectivity (PostgreSQL, MySQL, etc.)
• Advanced SQL validation and error handling
• More chart types (scatter, heatmap, treemap)
• Chart export as PNG/SVG
• Query builder UI (drag-and-drop)
• Collaboration features (share queries with team)
• Query scheduling and automation
• Advanced filtering (date ranges, multi-select)
• Row-level actions (edit, delete)
• SQL formatting and beautification

📄 License

MIT License - feel free to use this project for learning and development.

🤝 Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

📧 Support

For questions or issues, please open an issue on the GitHub repository.

---

Built with ❤️ using React, TypeScript, and Shadcn UI