query

Smart Database Query

Execute query: $ARGUMENTS

Using PostgreSQL MCP

1. Query Understanding

Parse and analyze the query request:

• Natural Language: "Show me all users who signed up last month"
• SQL Query: SELECT * FROM users WHERE created_at > '2024-09-01'
• Query Type: SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER

2. Safety Checks

Before Execution:

Safety Analysis:
✅ READ query - Safe to execute
⚠️  UPDATE without WHERE - DANGEROUS! Add confirmation
⚠️  DELETE without WHERE - DANGEROUS! Require explicit confirmation
⚠️  DROP TABLE - CRITICAL! Require double confirmation
⚠️  TRUNCATE - WARNING! Data loss, confirm

Confirmations Required:

• Any DELETE/UPDATE without WHERE clause
• DROP/TRUNCATE operations
• Schema changes (ALTER)
• Production database operations

3. Query Optimization

EXPLAIN Analysis:

-- Auto-run EXPLAIN before executing
EXPLAIN ANALYZE
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- Show execution plan:
Seq Scan on users  (cost=0.00..1234.56 rows=5000 width=100)
  Filter: (email ~~ '%@gmail.com'::text)
  Rows Removed by Filter: 45000

⚠️  Warning: Sequential scan detected!
Recommendation: Add index on email column

Optimization Suggestions:

-- ❌ Inefficient:
SELECT * FROM users WHERE email LIKE '%@gmail.com';
-- Sequential scan, checks all 50,000 rows

-- ✅ Better:
CREATE INDEX idx_users_email ON users(email);
SELECT * FROM users WHERE email LIKE '@gmail.com%';
-- Index scan, faster lookup

-- ✅ Even Better (if exact match):
SELECT * FROM users WHERE email = '[email protected]';
-- Index seek, optimal

4. Execute Query

Via PostgreSQL MCP:

// Execute through MCP
const result = await postgres.query(optimizedQuery);

// Return results
{
  rows: [...],
  rowCount: 42,
  executionTime: "125ms",
  fromCache: false
}

5. Format Results

Table Format:

┌──────┬─────────────┬───────────────────────┬────────────────────┐
│ id   │ username    │ email                 │ created_at         │
├──────┼─────────────┼───────────────────────┼────────────────────┤
│ 1    │ alice       │ [email protected]     │ 2024-09-15 10:30   │
│ 2    │ bob         │ [email protected]       │ 2024-09-16 14:22   │
│ 3    │ charlie     │ [email protected]   │ 2024-09-17 09:15   │
└──────┴─────────────┴───────────────────────┴────────────────────┘

Showing 3 of 42 rows (limited for display)
Query executed in: 125ms