↓ Skip to main content
Miles Wallace

PostgreSQL Query Visualizer

·1235 words
Miles Wallace
Author
Miles Wallace

Introduction
#

Understanding SQL query performance can be challenging, especially when you’re learning database concepts or debugging slow queries. The PostgreSQL Query Visualizer is an interactive, browser-based tool that lets you write, execute, and analyze SQL queries against sample datasets—no database installation required.

Built with React, Monaco Editor (the same editor powering VS Code), and sql.js (SQLite compiled to WebAssembly), this tool brings the power of SQL execution directly to your browser.

Launch PostgreSQL Query Visualizer

Features
#

1. Interactive SQL Editor
#

  • Monaco Editor Integration: Full-featured code editor with SQL syntax highlighting
  • Keyboard Shortcuts: Execute queries with Cmd/Ctrl + Enter
  • Syntax Support: PostgreSQL-style SQL syntax (powered by SQLite)
  • Auto-formatting: Clean, readable query formatting

2. Real Query Execution
#

  • sql.js Engine: Actual SQL execution powered by SQLite compiled to WebAssembly
  • Performance Metrics: View query execution time
  • Error Handling: Detailed error messages to help debug your queries
  • Result Tables: Clean, formatted table display with row counts

3. Sample Datasets
#

Two production-ready datasets to explore:

E-commerce Store:

  • Tables: users, products, orders, order_items
  • Demonstrates: JOINs, aggregations, subqueries
  • ~200+ sample rows

Blog Platform:

  • Tables: authors, posts, comments, tags, post_tags
  • Demonstrates: Many-to-many relationships, GROUP BY operations
  • ~150+ sample rows

4. Schema Visualization
#

  • Table Information: View all tables in the loaded dataset
  • Column Details: See column names, types, and constraints
  • Primary Keys: Identify primary key columns at a glance

5. Example Queries
#

Pre-built queries for each dataset:

  • Simple SELECT statements
  • Complex JOINs with aggregations
  • Subqueries and CTEs
  • GROUP BY and ORDER BY operations

Live Demo
#

Visit the live application: Launch PostgreSQL Query Visualizer

How It Works
#

Architecture
#

┌─────────────────┐      ┌──────────────┐      ┌─────────────┐
│  Monaco Editor  │──────│  React App   │──────│   sql.js    │
│  (VS Code)      │      │  (State Mgmt)│      │  (SQLite)   │
└─────────────────┘      └──────────────┘      └─────────────┘
        │                        │                      │
        │                        │                      │
        ▼                        ▼                      ▼
  SQL Syntax             Query Execution         WASM Engine
  Highlighting           & Results Display       (Browser)

Technology Stack
#

Frontend Framework: React 18 Build Tool: Vite Code Editor: Monaco Editor (@monaco-editor/react) SQL Engine: sql.js (SQLite via WebAssembly) Styling: Tailwind CSS

Why sql.js?
#

sql.js compiles SQLite to WebAssembly, enabling a fully functional SQL database to run entirely in your browser. This means:

  • No backend server required
  • No database installation needed
  • Instant query execution
  • Complete data privacy (all data stays in your browser)

While sql.js uses SQLite syntax, it’s ~95% compatible with PostgreSQL for standard queries, making it perfect for learning and experimentation.

Getting Started
#

Step 1: Load a Dataset
#

  1. Visit the application
  2. On the right panel, select a dataset from the dropdown
  3. Click “Load Dataset”
  4. View the schema information that appears

Step 2: Explore Example Queries
#

Click any example query in the right panel to:

  • Auto-populate the editor
  • Execute the query automatically
  • See results immediately

Step 3: Write Your Own Queries
#

Try these sample queries:

Simple SELECT:

SELECT * FROM users LIMIT 5;

JOIN with Aggregation:

SELECT
  u.name,
  COUNT(o.id) as order_count,
  SUM(o.total) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
ORDER BY total_spent DESC;

Subquery:

SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

Many-to-Many Relationship (Blog dataset):

SELECT
  p.title,
  GROUP_CONCAT(t.name, ', ') as tags
FROM posts p
LEFT JOIN post_tags pt ON p.id = pt.post_id
LEFT JOIN tags t ON pt.tag_id = t.id
GROUP BY p.id, p.title;

Sample Datasets
#

E-commerce Store
#

Tables:

  • users - Customer information
  • products - Product catalog with prices and stock
  • orders - Order history with totals and status
  • order_items - Line items for each order

Schema Example:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  created_at TEXT NOT NULL
);

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  category TEXT NOT NULL,
  price REAL NOT NULL,
  stock INTEGER NOT NULL
);

Use Cases:

  • Customer analytics
  • Product sales analysis
  • Order reporting
  • Inventory management

Blog Platform
#

Tables:

  • authors - Blog authors with bios
  • posts - Blog posts with content and view counts
  • comments - User comments on posts
  • tags - Content tags
  • post_tags - Many-to-many relationship between posts and tags

Schema Example:

CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  author_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  published_at TEXT NOT NULL,
  views INTEGER DEFAULT 0,
  FOREIGN KEY (author_id) REFERENCES authors(id)
);

CREATE TABLE post_tags (
  post_id INTEGER NOT NULL,
  tag_id INTEGER NOT NULL,
  PRIMARY KEY (post_id, tag_id),
  FOREIGN KEY (post_id) REFERENCES posts(id),
  FOREIGN KEY (tag_id) REFERENCES tags(id)
);

Use Cases:

  • Content analytics
  • Author performance metrics
  • Tag-based filtering
  • Comment engagement analysis

Advanced Features (Coming Soon)
#

The current version is an MVP with core functionality. Future enhancements include:

Phase 2: Execution Plan Visualization
#

  • D3.js tree diagrams showing query execution steps
  • Visual indicators for scan types and join methods
  • Query cost estimation

Phase 3: Schema Designer
#

  • Drag-and-drop table builder
  • Visual relationship mapping
  • Auto-generate CREATE TABLE statements

Phase 4: Query Optimization
#

  • Performance comparison tool
  • Index suggestion engine
  • Anti-pattern detection
  • Query history and saved queries

Phase 5: PostgreSQL-Specific Features
#

  • JSONB query builder
  • Window functions playground
  • Full-text search examples
  • CTE (Common Table Expression) visualizer

Use Cases
#

For Learners
#

  • Practice SQL without installing a database
  • Experiment with different query patterns
  • Learn JOINs, aggregations, and subqueries
  • Understand database relationships

For Developers
#

  • Prototype queries before production implementation
  • Test complex JOIN logic
  • Visualize data relationships
  • Share query examples with teammates

For Educators
#

  • Teaching SQL fundamentals
  • Demonstrating query optimization
  • Live coding sessions
  • Creating interactive tutorials

Technical Details
#

Browser Compatibility
#

  • Chrome/Edge: Full support
  • Firefox: Full support
  • Safari: Full support
  • Mobile browsers: Responsive design (best on tablet/desktop)

Performance
#

  • Dataset loading: < 100ms
  • Simple queries: < 10ms
  • Complex JOINs: < 50ms
  • No network latency (everything runs in-browser)

Privacy & Security
#

  • All data processing happens in your browser
  • No data sent to any server
  • No tracking or analytics
  • Safe for sensitive query prototyping

Source Code
#

The PostgreSQL Query Visualizer is part of my portfolio showcasing modern web development with React and WebAssembly.

Key Implementation Highlights:

SQL Executor Singleton:

// src/utils/sqlExecutor.js
import initSqlJs from 'sql.js';

export class SQLExecutor {
  async initialize() {
    this.SQL = await initSqlJs({
      locateFile: file => `https://sql.js.org/dist/${file}`
    });
    this.db = new this.SQL.Database();
  }

  async executeQuery(sql) {
    const start = performance.now();
    const results = this.db.exec(sql);
    const executionTime = (performance.now() - start).toFixed(2);

    return {
      success: true,
      results,
      executionTime: `${executionTime}ms`
    };
  }
}

React Component Architecture:

  • App.jsx - Main application with state management
  • SQLEditor.jsx - Monaco Editor wrapper with keyboard shortcuts
  • ResultsPanel.jsx - Dynamic result table rendering
  • SampleDatasets.jsx - Dataset management and schema display

Roadmap
#

Q1 2026:

  • Add query execution plan visualization
  • Implement dark mode
  • Add CSV export functionality

Q2 2026:

  • Schema designer with drag-and-drop
  • Query performance comparison tool
  • Share queries via URL parameters

Q3 2026:

  • PostgreSQL-specific features (JSONB, window functions)
  • Query history and saved queries
  • Index suggestion engine

Resources
#

PostgreSQL Documentation
#

sql.js
#

Monaco Editor
#

Related Projects#

Conclusion
#

The PostgreSQL Query Visualizer demonstrates the power of modern web technologies—bringing database execution directly to the browser without sacrificing performance or functionality. Whether you’re learning SQL, prototyping queries, or teaching database concepts, this tool provides an accessible, interactive experience.

Launch the Application and start exploring SQL today!

Built with: React, Vite, Monaco Editor, sql.js, Tailwind CSS Part of: mileswallace.com portfolio