📺 Video Guides
1. Setup
2. More Settings
3. AI Assistant
4. AI Copilot
5. Dashboard
6. DB Operations
7. Power Editor
8. More Features
✨ Key Features
- 🔌 Secure Connections — VS Code SecretStorage encryption
- 🛡️ Connection Safety — Environment tagging (🔴 PROD, 🟡 STAGING, 🟢 DEV), read-only mode, query safety analyzer
- ⏱️ Performance Tracking — Historical query execution monitoring with degradation alerts
- 📊 Live Dashboard — Real-time metrics & query monitoring
- 🚦 Dashboard Health Signals — Status badges, lock/wait indicators, and performance-focused telemetry cards
- 📓 SQL Notebooks — Interactive notebooks with AI assistance
- 🗂️ Notebook Manager — Open/create notebooks with searchable picker and improved metadata context
- 💾 Saved Queries — Tag-based organization, connection context restoration, AI metadata generation, edit & reuse
- 🌳 Database Explorer — Browse tables, views, functions, types, FDWs
- 🛠️ Object Operations — CRUD, scripts, VACUUM, ANALYZE, REINDEX, plus triggers/sequences/domains/rules/partitions/publications/event triggers/tablespaces/aggregates
- 🏗️ Visual Table Designer — Create/Edit tables with a robust GUI
- 🧭 Definition Viewer (SQL Preview) — Open object DDL with toggleable SQL preview, copy/edit workflows, and routine scaffolding
- 🔑 Index & Constraint Manager — Visual management of DB constraints
- 🧠 Schema Intelligence — Schema search, index advisor, and migration generator workflows
- 📋 Smart Paste — Context-aware clipboard actions (SQL/CSV/JSON)
- 📊 Table Intelligence — Profile, activity monitor, index usage, definition viewer
- 🔍 EXPLAIN CodeLens — One-click query analysis directly in notebooks
- 🎛️ Advanced Result UX — Column stats, transpose view, enhanced filtering, sliding-window streaming for large
SELECTs, configurable bytea display, and structured in-grid editing with explicit commit confirmation
- 🛡️ Auto-LIMIT — Intelligent query protection (configurable, default 1000 rows)
- 🌍 Foreign Data Wrappers — Manage foreign servers, user mappings & tables
- 🤖 AI-Powered — Generate, Optimize, Explain & Analyze with guided follow-ups; regenerate or branch the conversation from a prior user message (GitHub Models, OpenAI, Anthropic, Gemini, VS Code LM)
- 🧩 Flexible SQL Assistant Layout — Open SQL Assistant in editor tabs and keep multiple assistant tabs open simultaneously
- 🖼️ Vision AI — Paste or upload images directly in the SQL Assistant; sent to vision-capable AI providers
- 📎 File Preview — Click attached file chips to open them as preview tabs in the editor
- 📤 Export Data — Export results to CSV, JSON, or Excel
🎯 Why PgStudio?
🎨 Modern Interface
- Beautiful, intuitive UI designed for developers
- Real-time dashboard with live metrics
- Context-aware operations
- Seamless VS Code integration
|
⚡ Powerful Features
- Interactive SQL notebooks
- 🤖 AI-powered Copilot & agentic support
- Table intelligence & performance insights
- Complete CRUD operations
- EXPLAIN CodeLens for query analysis
|
🛡️ Production-Ready Safety
- Environment tagging (Production/Staging/Dev)
- Read-only mode enforcement
- Query safety analyzer with risk scoring
- Auto-LIMIT for SELECT queries
- Status bar risk indicators
|
- Table profile with size & statistics
- Real-time activity monitoring
- Index usage analytics
- Bloat detection & warnings
- Query performance history & alerts
- Complete table definitions
|
📋 Feature Matrix
| Area |
PgStudio v1.2.1 |
Notes |
| Core PostgreSQL object operations |
✅ |
Tables, views, mat views, functions, roles, extensions, FDWs, and more |
| AI-assisted SQL workflows |
✅ |
Generate, optimize, explain, and analyze with notebook-first execution |
| Production safety controls |
✅ |
Read-only mode, risk scoring, confirmation prompts, Auto-LIMIT |
| Real-time monitoring dashboard |
✅ |
Activity and health views in VS Code |
| Interactive SQL notebooks |
✅ |
Native .pgsql notebook execution with completions |
| In-grid result editing parity with desktop IDEs |
⚠️ Partial |
Stronger commit flow and tooling in v1.2.x; full parity still evolving |
| ERD/schema visualization parity |
⚠️ Partial |
Schema designer exists; ERD depth still evolving |
| Advanced replication administration |
⚠️ Partial |
Additional publication/subscription depth planned |
🚀 Quick Start
# Install from VS Code
ext install ric-v.postgres-explorer
# Or via command line
code --install-extension ric-v.postgres-explorer
Then: PostgreSQL icon → Add Connection → Enter details → Connect!
Telemetry Setup
PgStudio ships with a privacy-first telemetry client designed for anonymous product analytics.
- No SQL text, schema/object names, hostnames, database names, usernames, or credentials are collected.
- VS Code global telemetry setting is a hard gate; when disabled globally, PgStudio telemetry is disabled.
- Event payloads are allowlisted and bucketed (durations/result sizes) to avoid raw sensitive values.
Set these in VS Code settings:
postgresExplorer.telemetry.mode: off | basic | detailed (default basic)postgresExplorer.telemetry.allowUsage: allow anonymous usage counters (default true)postgresExplorer.telemetry.allowPerformance: allow anonymized performance buckets (default false)
Configure PostHog sink (optional)
For remote telemetry ingestion:
postgresExplorer.telemetry.posthogHost: default https://us.i.posthog.compostgresExplorer.telemetry.posthogApiKey: your PostHog project key
If posthogApiKey is empty, telemetry stays local (debug sink only).
What to expect from telemetry
- Lifecycle: extension/session start and end events
- Activity: command and feature usage counters
- Connections: opened/closed/error with coarse error category
- Query execution: success/failure with duration/result-size buckets (detailed/performance-enabled mode)
- AI usage: provider-level success/failure counters
📚 Documentation Map
README.md - Product overview, installation, development, and troubleshootingdocs/ARCHITECTURE.md - System architecture and component/data-flow detailsdocs/STYLING_GUIDE.md - Centralized styling/templates and UI refactoring patternsdocs/WEBSITE_CONTEXT.md - Website architecture and content contextdocs/API_STABILITY.md - v1.x API stability and deprecation policydocs/SECURITY_REVIEW.md - v1.0 security controls and release checklistdocs/RELEASE_NOTES_v1.0.0.md - v1.0 highlights and release notesdocs/MIGRATION_GUIDE_0.x_to_1.0.0.md - upgrade path from 0.9.x to 1.0.0SECURITY.md - Security policy and vulnerability reporting guidanceCHANGELOG.md - Release notes and what changed across versions
Stable: v1.2.1 | Nightly: v1.0.0-nightly+ — Latest stable adds cursor-based result streaming, bytea formatting controls, richer result-grid tooling, export correctness with Auto-LIMIT, and SQL Assistant regenerate/resend. See CHANGELOG.md; v1.0 launch materials remain in Release Notes and Migration Guide.
🏗️ Project Structure
PgStudio/
├── src/
│ ├── extension.ts # Extension entry point
│ ├── commands/ # Command implementations
│ │ ├── tables.ts # Table operations
│ │ ├── views.ts # View operations
│ │ ├── functions.ts # Function operations
│ │ ├── connection.ts # Connection commands
│ │ ├── notebook.ts # Notebook commands
│ │ ├── helper.ts # Shared helper utilities
│ │ ├── sql/ # SQL template modules
│ │ │ ├── tables.ts # Table SQL templates
│ │ │ ├── views.ts # View SQL templates
│ │ │ ├── functions.ts # Function SQL templates
│ │ │ ├── indexes.ts # Index SQL templates
│ │ │ └── ... # Other SQL templates
│ │ └── ...
│ ├── providers/ # VS Code providers
│ │ ├── DatabaseTreeProvider.ts # Tree view provider
│ │ ├── NotebookKernel.ts # Notebook kernel
│ │ ├── ChatViewProvider.ts # AI chat provider
│ │ ├── SqlCompletionProvider.ts # IntelliSense
│ │ └── ...
│ ├── services/ # Business logic
│ │ ├── ConnectionManager.ts # Connection handling
│ │ └── SecretStorageService.ts # Credential storage
│ ├── dashboard/ # Dashboard webview
│ ├── common/ # Shared utilities
│ └── test/ # Unit tests
├── resources/ # Icons & screenshots
├── docs/ # Documentation & landing page
├── dist/ # Compiled output (bundled)
├── out/ # Compiled output (tsc)
├── package.json # Extension manifest
├── tsconfig.json # TypeScript config
└── webpack.config.js # Webpack config
💾 Saved Queries Library
Organize, manage, and reuse your most important queries with intelligent tagging and context preservation.
Features
- 🏷️ Tag-Based Organization — Group queries by topic (e.g., "analytics", "maintenance", "daily-reports")
- 🔗 Connection Context — Queries remember their original connection, database, and schema
- 📓 Quick Reopening — Click "Open in Notebook" to restore the query with full context in a new notebook
- ✏️ Edit Anytime — Modify title, description, tags, and SQL without creating duplicates
- 🤖 AI Metadata — Auto-generate titles, descriptions, and tags using AI
- 📊 Rich Metadata Display — Hover to see creation date, last used, database, and schema
Usage
- Save Query: Click "Save Query" CodeLens button on any SQL cell in a notebook
- Add Metadata: Enter title, description, and tags (AI can help auto-generate)
- Organize: Use tags to group related queries
- Reuse: Click a saved query → "Open in Notebook" to restore with original context
- Edit: Right-click any saved query → "Edit Query" to modify it
🤖 AI-Powered Operations
PgStudio integrates advanced AI capabilities directly into your workflow, but keeps YOU in control.
🔐 GitHub Models via GitHub Sign-In
Use GitHub Models without manually managing a PAT in normal VS Code authentication flows.
- Native Sign-In: Connect with your GitHub account from AI Settings.
- Model Catalog Access: List and select available GitHub-hosted models.
- Session-Based Auth: Uses VS Code GitHub authentication sessions instead of storing provider tokens.
🧩 SQL Assistant Tabs
Use SQL Assistant where you work, not only in the sidebar.
- Open in Editor Tab: Run
SQL Assistant: Open in Editor Tab from Command Palette.
- Parallel Assistants: Open multiple SQL Assistant tabs for separate tasks (e.g., optimization, migration, and schema exploration).
🪄 Generate Query (Natural Language → SQL)
Describe what you need in plain English (e.g., "Show me top 10 users by order count"), and PgStudio will generate the SQL for you using your schema context.
- Command Palette:
AI: Generate Query
- Context-Aware: The AI understands your table schemas, columns, and relationships.
Click the Optimize button on any successful query result.
- Explain Scripts: Generates
EXPLAIN ANALYZE commands for deeper profiling.
- Static Analysis: Suggests missing indexes, query rewrites, or schema improvements.
📊 Data Analysis
Click the Analyze Data button in result tables.
- Clean Workflow: Automatically exports data to a temporary CSV and attaches it to the chat.
- Actionable Insights: AI summarizes patterns, trends, and outliers in your result sets.
✨ Error Handling (Explain & Fix)
When a query fails, get instant help directly in the error cell.
- Explain Error: Translates cryptic Postgres errors into plain English.
- Fix Query: Suggests corrected SQL to resolve the error.
🛡️ Safe Execution Model (Notebook-First)
We believe AI should assist, not take over. No query is ever executed automatically.
- Ask/Trigger: You use one of the AI features.
- Review: The AI generates SQL or suggestions in the chat.
- Insert: You click "Open in Notebook" to place code into a cell.
- Execute: You review the code and click "Run" when you are ready.
📊 Advanced Visualizations
Turn any query result into beautiful, interactive charts in seconds.
- One-Click Charting: Instantly visualize your data directly from the notebook results.
- Customizable: Toggle between Bar, Line, Pie, Doughnut, and Scatter charts.
- Rich Data Display:
- Log Scale: Easily analyze data with wide variances.
- Blur/Glow Effects: Modern, high-fidelity chart aesthetics.
- Zoom & Pan: Inspect detailed data points interactively.
🛠️ Local Development
Prerequisites
- Node.js ≥ 18.0.0
- VS Code ≥ 1.90.0
- PostgreSQL (for testing)
Setup
# Clone the repository
git clone https://github.com/dev-asterix/PgStudio.git
cd PgStudio
# Install dependencies
npm install
# Compile TypeScript
npm run compile
Development Commands
| Command |
Description |
npm run watch |
Watch mode (auto-recompile) |
npm run compile |
One-time TypeScript compilation |
npm run esbuild |
Bundle with esbuild (with sourcemaps) |
npm run esbuild-watch |
Bundle in watch mode |
npm run test |
Run unit tests |
npm run coverage |
Run tests with coverage |
npm run vscode:prepublish |
Build for production |
Running the Extension
- Open the project in VS Code
- Press
F5 to launch Extension Development Host
- Or use Run and Debug (
Ctrl+Shift+D) → "Run Extension"
Debugging Tips
- Output Panel:
Ctrl+Shift+U → Select "PostgreSQL Explorer"
- DevTools:
Ctrl+Shift+I in Extension Development Host
- Webview Debug: Right-click in webview → "Inspect"
🧪 Testing
Quick Start
# Install dependencies
npm ci
# Run all tests
npm run test:all
# Run tests with coverage
npm run coverage
# Run specific test types
npm run test:unit # Unit tests
npm run test:integration # Integration tests with Docker
npm run test:renderer # Renderer component tests
Docker-Based Integration Tests
# Start PostgreSQL containers (12-17)
make docker-up
# Run integration tests
npm run test:integration
# Stop containers
make docker-down
Using Make
make test-unit # Unit tests
make test-integration # Integration tests
make test-renderer # Renderer component tests
make test-all # All tests
make coverage # Coverage report
make test-full # Full suite with Docker
Using Test Scripts
Linux/macOS:
./scripts/test.sh --unit
./scripts/test.sh --integration --pg 16
./scripts/test.sh --coverage
Windows:
scripts\test.bat --unit
scripts\test.bat --integration --pg 16
scripts\test.bat --coverage
Testing Infrastructure
PgStudio includes comprehensive testing infrastructure:
- Unit Tests (50%+ coverage): Mocha + Chai + Sinon
- Integration Tests: Connection lifecycle, SSL, pool exhaustion, version compatibility
- Component Tests: Renderer with jsdom, tree views, forms, dashboards
- Docker Containers: PostgreSQL 12, 14, 15, 16, 17 for compatibility testing
- CI/CD Pipeline: GitHub Actions with Matrix testing (Node 18-22, PostgreSQL 12-17)
📖 Testing docs: Use the scripts listed above and CI workflow in .github/workflows/test.yml.
🤝 Contributing
Commit Convention
We follow Conventional Commits:
feat: add new feature
fix: resolve bug
docs: update documentation
refactor: code restructuring
test: add/update tests
chore: maintenance tasks
📦 Building & Publishing
# Build VSIX package
npx vsce package
# Publish to VS Code Marketplace
npx vsce publish
# Publish to Open VSX
npx ovsx publish
Stable And Nightly Channels
- Stable releases are published from version tags (
v*) via .github/workflows/publish.yml.
- Nightly releases are published on every merge to
main via .github/workflows/publish-nightly.yml.
VS Code Marketplace channel behavior:
- Stable extension ID:
ric-v.postgres-explorer
- Nightly is published as a pre-release of the same extension ID.
- Users can opt in/out directly from the extension page with:
Switch to Pre-Release VersionSwitch to Release Version
Open VSX channel behavior:
- Stable extension ID:
ric-v.postgres-explorer
- Nightly companion extension ID:
ric-v.postgres-explorer-nightly
- To opt out of nightly on Open VSX-based editors, uninstall the nightly companion extension and install stable.
Nightly versioning policy:
- Nightly builds use an odd minor stream and CI run number as patch.
- Example format:
0.9.<run_number>
- This keeps nightly versions monotonically increasing for reliable updates.
📝 License
MIT License
Made with ❤️ for the PostgreSQL Community
Also on Open VSX
🔧 Troubleshooting
Connection Issues
SSL Connection Failures
Problem: SSL connection failed or certificate verify failed
Solutions:
- Disable SSL (development only): Set SSL Mode to
disable
- Use
prefer mode (tries SSL, falls back to non-SSL)
- Provide CA certificate: SSL Mode
verify-ca + CA Certificate path
Connection Timeout
Problem: Connection timeout or ETIMEDOUT
Solutions:
- Increase connection timeout in settings
- Check firewall rules
- Verify PostgreSQL
pg_hba.conf allows remote connections
- Ensure PostgreSQL is listening on correct interface
SSH Tunnel Issues
Problem: SSH tunnel failed to establish
Solutions:
- Verify SSH credentials and host
- Test SSH connection manually:
ssh user@host -p port
- Check SSH key permissions:
chmod 600 ~/.ssh/id_rsa
- Ensure SSH server allows port forwarding
Large Result Sets
Problem: Querying large tables causes freezes
Solution: Results are automatically limited to 10,000 rows. Use LIMIT clause for specific row counts.
Slow Tree View
Problem: Database tree takes long to load
Solutions:
- Use search filter to narrow objects
- Collapse unused schemas
- Disable object count badges in settings
Common Error Messages
| Error |
Cause |
Solution |
password authentication failed |
Wrong credentials |
Verify username/password |
database does not exist |
Database name typo |
Check database name |
permission denied |
Insufficient privileges |
Grant SELECT permission |
too many connections |
Pool exhausted |
Close unused connections |
no pg_hba.conf entry |
Access control |
Add entry to pg_hba.conf |
🙈 Feature Comparison
| Feature |
PgStudio |
pgAdmin |
DBeaver |
TablePlus |
| VS Code Integration |
✅ Native |
❌ |
❌ |
❌ |
| SQL Notebooks |
✅ Interactive |
❌ |
❌ |
❌ |
| AI Assistant |
✅ Built-in |
❌ |
❌ |
❌ |
| Real-time Dashboard |
✅ |
✅ |
⚠️ Limited |
⚠️ Limited |
| Inline Cell Editing |
✅ |
✅ |
✅ |
✅ |
| Export Formats |
CSV, JSON, Excel |
CSV, JSON |
CSV, JSON, Excel |
CSV, JSON, SQL |
| SSH Tunneling |
✅ |
✅ |
✅ |
✅ |
| Foreign Data Wrappers |
✅ Full |
✅ |
⚠️ Limited |
❌ |
| License |
MIT (Free) |
PostgreSQL (Free) |
Apache 2.0 (Free) |
Proprietary (Paid) |
Unique to PgStudio
- 🤖 AI-powered query generation and optimization
- 📓 Interactive SQL notebooks with persistent state
- 🔄 Infinite scrolling for large result sets (10k rows)
- 🎨 Modern UI integrated into VS Code
- 🚀 Hybrid connection pooling for performance
