🚀 Feature: Make Query Editing & Results UX Smooth Like Snowflake (Production-Ready TUI Experience)

[muk2]

Overview

Improve the overall UX of pgrsql (Rust + ratatui SQL editor) to feel smooth, fast, and intuitive — similar to Snowflake’s worksheet experience.

The goal is to make the editor production-ready with:

• Multi-query support
• Resizable editor/results panes
• Intuitive navigation of wide result sets
• Smooth switching between editor, results, and schema/database tabs
• Fast rendering & performance optimizations

This issue tracks UX, interaction design, and performance improvements.

---

🎯 Goals

• Snowflake-like multi-query editing experience
• Seamless switching between editor/results/schema views
• Highly intuitive navigation of wide tables
• Clean, production-ready TUI ergonomics
• Zero lag when handling large result sets
• Keyboard-first UX (power user friendly)

---

📝 1. Multi-Query Support (Snowflake Worksheet Style)

Problems

• Current editor feels single-query focused.
• No smooth execution of selected query.
• No clear query boundary awareness.

Proposed Improvements

• Support multiple queries in a single editor buffer.
• Execute:
  • Current query (cursor-aware)
  • Selected query (highlighted text)
  • Entire file
• Detect query boundaries via ;.
• Highlight currently active query before execution.
• Show execution status inline (e.g., spinner, success, error).

Nice-to-Have

• Query history navigation (like ↑/↓ through executed queries)
• Query tabs (future enhancement)

---

🪟 2. Resizable Editor / Results Pane (Dynamic Layout)

Problems

• Fixed layout feels restrictive.
• Hard to focus on results or editor when needed.

Proposed Improvements

• Vertical split:
  • Top → SQL Editor
  • Bottom → Results Window
• Keyboard resizing:
  • Ctrl + Up/Down → Resize editor/results pane
• Ability to:
  • Collapse results pane
  • Restore results pane
  • Toggle full-screen editor
• Persist pane size between sessions.

---

📊 3. Intuitive Results Window Navigation (Like Snowflake)

Problems

• Hard to navigate wide tables.
• Horizontal scrolling feels clunky.
• Large result sets may feel laggy.

Proposed Improvements

• Smooth left/right horizontal scrolling.
• Sticky header row.
• Sticky first column (optional feature).
• Column-based navigation:
  • Tab → Move right
  • Shift+Tab → Move left
• Expand column width dynamically.
• Toggle wrap mode vs truncate mode.
• Show column index + row count at top.

Performance Improvements

• Virtualized row rendering (only render visible rows).
• Lazy loading for large datasets.
• Efficient diff-based redraw using ratatui.

---

🗂 4. Tabbed Navigation Between Views

Add proper UI state management for:

• 📝 Editor
• 📊 Results
• 🗄 Databases
• 📁 Tables
• 🧬 Schema Info

Proposed UX

• Top navigation bar with tabs.
• Keyboard shortcuts:
  • Ctrl + 1 → Editor
  • Ctrl + 2 → Results
  • Ctrl + 3 → Databases
  • Ctrl + 4 → Tables
  • Ctrl + 5 → Schema
• Highlight active tab clearly.
• Allow mouse support (optional enhancement).

---

⚡ 5. Performance & Production Readiness

Rendering

• Reduce full-screen redraws.
• Optimize ratatui layout recalculations.
• Avoid blocking DB calls in UI thread.
• Async query execution.

Stability

• Handle large datasets safely.
• Prevent UI freeze during heavy queries.
• Proper error rendering (non-crashing).

UX Polish

• Loading spinner during query execution.
• Clear error panel with formatted SQL errors.
• Success execution time indicator (e.g., Query OK (124ms)).

---

🧠 6. UX Design Principles

• Keyboard-first
• Minimal cognitive load
• Smooth transitions
• No flickering
• Fast feedback
• Predictable keybindings
• Clean, modern TUI aesthetic

---

📌 Implementation Suggestions

• Refactor UI state into a central AppState struct.
• Introduce:
  • ActivePane enum (Editor, Results, Schema, etc.)
  • LayoutManager for pane resizing.
• Use async runtime (tokio) for DB execution.
• Implement result virtualization (render visible window only).
• Separate:
  • Query execution engine
  • Rendering layer
  • Input handling layer

---

🏁 Definition of Done

• Multiple queries supported in one buffer.
• Resizable panes working smoothly.
• Results window supports intuitive horizontal + vertical navigation.
• Fast rendering even with 10k+ rows.
• Seamless tab switching between editor/results/schema.
• No noticeable input lag.
• Clean, production-ready experience.

---

🔥 Why This Matters

This elevates pgrsql from a basic SQL TUI tool to a serious developer-grade database client that can compete (UX-wise) with Snowflake’s worksheet while staying fully terminal-native.