• ✨ Features
  • Connection Management
  • Real-Time Dashboard
  • Database Explorer
  • SQL Notebooks
  • Object Operations
• 🚀 Quick Start
• 📖 Usage Guide
• 🎓 Tutorials
• 🤝 Contributing
• 🏗️ Local Development
• 🔧 Troubleshooting
• 📫 Support

Performance Graphs:

• 📈 Connection trends over time
• 🔄 Query activity patterns
• 💾 Memory usage monitoring

Active Query Management:

• ✅ View all running queries with execution time
• ❌ Cancel long-running queries safely
• 🛑 Terminate stuck connections
• 📊 Query execution statistics

Create & Execute Queries

-- Cell 1: Data Analysis
SELECT 
  date_trunc('day', created_at) as day,
  COUNT(*) as user_count
FROM users
WHERE created_at >= NOW() - INTERVAL '30 days'
GROUP BY day
ORDER BY day;

-- Cell 2: Performance Check
EXPLAIN ANALYZE
SELECT * FROM orders 
WHERE customer_id = 123;

Key Features:

• ✅ Multi-cell execution
• ⌨️ Ctrl+Enter to run cell
• 🤖 AI-Powered Assistance: GitHub Copilot & agentic support
• 📊 Rich tabular output
• 🎨 Syntax highlighting
• 💾 Export to CSV/Excel
• 📈 Row count & execution time
• 🔄 Result sorting by column

[!TIP] AI-Powered SQL Development: The notebook interface fully supports GitHub Copilot and other agentic AI tools, providing intelligent code completion, query suggestions, and automated SQL generation directly in your notebooks!

Data Management

• ✅ View data with pagination
• ➕ Insert new rows
• ✏️ Update existing data
• 🗑️ Delete rows
• 🧹 Truncate table
• ❌ Drop table

Structure Management

• 🔍 View table properties (columns, constraints, indexes)
• ✏️ Edit table definition
• 📋 Show column details with data types
• 🔗 View foreign key relationships

Script Generation

-- Quick script generation
SELECT * FROM employees;                    -- SELECT
INSERT INTO employees (...) VALUES (...);  -- INSERT
UPDATE employees SET ... WHERE ...;        -- UPDATE
DELETE FROM employees WHERE ...;           -- DELETE
CREATE TABLE employees (...);              -- CREATE

Maintenance Tools

• 🧹 VACUUM: Clean up dead rows and reclaim space
• 📊 ANALYZE: Update table statistics for query planner
• 🔄 REINDEX: Rebuild indexes for optimal performance

Regular Views

• 📖 View definition display
• ✏️ Edit view SQL
• 🔍 Query view data
• ❌ Drop view

Materialized Views

• 🔄 Refresh cached data
• 📊 View current data
• ✏️ Edit definition
• ❌ Drop materialized view

• 📖 View function properties (signature, parameters, return type)
• ✏️ Edit definition (CREATE OR REPLACE)
• ▶️ Call function with parameters
• 📝 View source code
• ❌ Drop function

Example Function Creation:

CREATE OR REPLACE FUNCTION calculate_total(
  item_price DECIMAL,
  quantity INTEGER
)
RETURNS DECIMAL AS $$
BEGIN
  RETURN item_price * quantity;
END;
$$ LANGUAGE plpgsql;

Schema Management

• 🔍 View schema properties
• ➕ Create objects within schema
• 📜 Generate schema scripts
• 🔧 Schema-level operations

Quick Object Creation | Object Type | Shortcut | |-------------|----------| | 📊 Table | Right-click → Create Table | | 👁️ View | Right-click → Create View | | 🔄 Materialized View | Right-click → Create Materialized View | | ⚙️ Function | Right-click → Create Function | | 🏷️ Type | Right-click → Create Type | | 🔗 Foreign Table | Right-click → Create Foreign Table |

1. Right-click table → View Table Data
2. Data opens in new tab with pagination
3. Click column headers to sort
4. Use export buttons for CSV/Excel

Pro Tip: Double-click table for quick data view

1. Right-click table → Show Table Properties
2. Review columns, constraints, indexes
3. Select Edit Table Definition to modify
4. Make changes in SQL editor
5. Execute to apply

Example Modification:

ALTER TABLE employees
ADD COLUMN department VARCHAR(100),
ADD COLUMN hire_date DATE DEFAULT CURRENT_DATE;

Right-click table → Scripts → Choose type:

Goal: Create a blog database with tables, views, and functions

Step 1: Create Tables

-- Users table
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) UNIQUE NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Posts table
CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
  title VARCHAR(200) NOT NULL,
  content TEXT,
  published_at TIMESTAMP,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Comments table
CREATE TABLE comments (
  id SERIAL PRIMARY KEY,
  post_id INTEGER REFERENCES posts(id) ON DELETE CASCADE,
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
  content TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

Step 2: Create View

CREATE VIEW recent_posts AS
SELECT 
  p.id,
  p.title,
  u.username as author,
  p.published_at,
  COUNT(c.id) as comment_count
FROM posts p
JOIN users u ON p.user_id = u.id
LEFT JOIN comments c ON p.post_id = c.id
WHERE p.published_at IS NOT NULL
GROUP BY p.id, p.title, u.username, p.published_at
ORDER BY p.published_at DESC;

Step 3: Create Function

CREATE OR REPLACE FUNCTION get_user_post_count(user_id_param INTEGER)
RETURNS INTEGER AS $$
BEGIN
  RETURN (
    SELECT COUNT(*) 
    FROM posts 
    WHERE user_id = user_id_param 
      AND published_at IS NOT NULL
  );
END;
$$ LANGUAGE plpgsql;

Step 4: Use the Extension

1. Refresh schema in explorer
2. View table data
3. Query the view
4. Call the function with test data

Goal: Optimize database performance

1. Check Table Bloat

SELECT 
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

2. Vacuum Large Tables

• Right-click large table → Maintenance → VACUUM
• Choose VACUUM FULL for maximum space reclamation

3. Update Statistics

• Right-click table → Maintenance → ANALYZE
• Improves query planning

4. Rebuild Indexes

• Right-click table → Maintenance → REINDEX
• Fixes index bloat and fragmentation

5. Monitor Results

• View dashboard for cache hit ratio improvement
• Check query performance

Goal: Export data from one database and import to another

1. Export Source Data

-- In source database notebook
SELECT * FROM users WHERE active = true;

• Execute query
• Export to CSV

2. Backup Source Database

• Right-click database → Backup
• Choose location and format
• Save backup file

3. Create Target Schema

• Connect to target database
• Run CREATE TABLE scripts

4. Import Data

-- Use COPY command or insert from CSV
COPY users FROM '/path/to/users.csv' 
WITH (FORMAT csv, HEADER true);

5. Verify Migration

SELECT COUNT(*) FROM users;
SELECT * FROM users LIMIT 10;

• ✅ Follow TypeScript best practices
• ✅ Use meaningful variable/function names
• ✅ Add comments for complex logic
• ✅ Keep functions small and focused
• ✅ Use async/await for async operations

• ✅ Test changes thoroughly
• ✅ Include test cases for new features
• ✅ Ensure existing tests pass
• ✅ Test with different PostgreSQL versions
• ✅ Test edge cases

• ✅ Update README for new features
• ✅ Add JSDoc comments to functions
• ✅ Update CHANGELOG.md
• ✅ Include usage examples
• ✅ Document breaking changes

1. Clone Repository

git clone https://github.com/dev-asterix/yape-postgres-ext-vsc.git
cd yape-postgres-ext-vsc

2. Install Dependencies

npm install

3. Compile TypeScript

# One-time compilation
npm run compile

# Watch mode (auto-recompile)
npm run watch

4. Open in VS Code

code .

5. Run Extension

• Press F5 to launch Extension Development Host
• Or use Run and Debug panel (Ctrl+Shift+D)
• Select "Run Extension" configuration
• Click green play button ▶️

6. Debug Extension

• Set breakpoints in .ts files
• Press F5 to start debugging
• Use Debug Console for inspection
• Step through code with toolbar

Quick Start:

docker run --name postgres-test \
  -e POSTGRES_PASSWORD=test123 \
  -e POSTGRES_DB=testdb \
  -p 5432:5432 \
  -d postgres:latest

Create Test Data:

CREATE TABLE test_users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO test_users (username, email)
SELECT 
  'user' || generate_series,
  'user' || generate_series || '@example.com'
FROM generate_series(1, 100);

Cleanup:

docker stop postgres-test
docker rm postgres-test

1. Open Output panel: Ctrl+Shift+U
2. Select "PostgreSQL Explorer" from dropdown
3. View real-time extension logs
4. Look for errors and warnings

In Extension Development Host:

1. Press Ctrl+Shift+I to open DevTools
2. Check Console tab for errors
3. Use Network tab for requests
4. Inspect Elements tab for UI issues

1. Right-click in webview panel
2. Select "Inspect"
3. Use Chrome DevTools
4. Debug HTML/CSS/JavaScript

# Clear compiled output
rm -rf out/

# Reinstall dependencies
rm -rf node_modules/ package-lock.json
npm install

# Recompile
npm run compile

Check:

• ✅ package.json syntax is valid
• ✅ Activation events are correct
• ✅ Extension host output for errors
• ✅ All dependencies are installed

Solutions:

• Reload window: Ctrl+R
• Restart VS Code
• Check Extension Host logs

Symptoms:

• Connection timeout
• Authentication failed
• Server not found

Solutions:

1. Verify Connection Details

   # Test with psql
   psql -h localhost -p 5432 -U postgres -d mydb
   

2. Check PostgreSQL Status

   # Linux
   sudo service postgresql status
   sudo service postgresql start
   
   # MacOS
   brew services list
   brew services start postgresql
   
   # Windows
   # Check Services → PostgreSQL
   

3. Verify pg_hba.conf

   • Allow connections from your client IP
   • Check authentication method
   • Reload PostgreSQL after changes

4. Firewall Settings

   # Linux - allow PostgreSQL port
   sudo ufw allow 5432/tcp
   

5. Test Connectivity

   # Check if port is open
   telnet localhost 5432
   nc -zv localhost 5432
   

[!TIP] Enable logging in PostgreSQL to see connection attempts.

Causes:

• Network latency
• Server overload
• Firewall blocking

Solutions:

• Increase timeout in VS Code settings
• Check network connectivity
• Verify server is accepting connections
• Check max_connections setting

Optimization Steps:

1. Add WHERE Clauses

   -- Instead of
   SELECT * FROM large_table;
   
   -- Use
   SELECT * FROM large_table 
   WHERE created_at >= NOW() - INTERVAL '7 days'
   LIMIT 1000;
   

2. Create Indexes

   CREATE INDEX idx_users_email ON users(email);
   CREATE INDEX idx_orders_date ON orders(created_at);
   

3. Use EXPLAIN

   EXPLAIN ANALYZE
   SELECT * FROM orders WHERE customer_id = 123;
   

4. Adjust Settings

   • Increase statement_timeout in PostgreSQL
   • Increase query timeout in extension settings

Solutions:

• Reduce result set size with LIMIT
• Use pagination for large datasets
• Export data in chunks
• Increase VS Code memory: --max-memory=4096