SQL Query Generator

Generate safe, read-only SQL queries for databases and execute them using the Kalysta MCP server.

🚨 CRITICAL RULES

1. NEVER Start Query with Comments:

• CRITICAL: Kalysta MCP server ONLY allows whitelisted keywords as query prefix
• Whitelisted keywords: SELECT, WITH, SHOW, DESCRIBE, EXPLAIN
• NEVER use -- or /* */ comments at the start of queries
• Comments will cause "query must start with SELECT/WITH/SHOW/DESCRIBE/EXPLAIN" error
• Put comments AFTER the SELECT/WITH clause, not before

GOOD:

SELECT id, name FROM users WHERE deleted_at IS NULL;

BAD (will fail):

-- Get all users
SELECT id, name FROM users WHERE deleted_at IS NULL;

2. ALWAYS Look Up Schema First:

• MANDATORY: Before composing any SQL query, look up the database and table schemas
• Use SELECT table_name FROM information_schema.tables WHERE table_schema = 'database_name' to list tables
• Use DESCRIBE table_name or SHOW COLUMNS FROM table_name to see column definitions
• Verify column names, data types, and constraints before writing queries
• This prevents column name errors and ensures correct data types

3. ALWAYS Apply Default Filters (When Applicable):

• Check if table has is_testing column → Include is_testing = FALSE in WHERE clause
• Check if table has deleted_at column → Include deleted_at IS NULL in WHERE clause
• These filters ensure you're querying production data only and excluding soft-deleted records
• Use DESCRIBE table_name to verify which columns exist before applying filters

4. Use SELECT DISTINCT for Context:

• When exploring possible values in a column, use SELECT DISTINCT column_name frequently
• This helps understand the data domain before building complex queries
• Examples: distinct statuses, distinct types, distinct categories

5. Read-Only Queries ONLY:

• NEVER generate INSERT, UPDATE, DELETE, DROP, ALTER, or any data-modifying statements
• Only SELECT, WITH, SHOW, DESCRIBE, EXPLAIN queries are allowed
• This is a querying tool, not a data manipulation tool

6. Use the Kalysta MCP Server:

• All queries must be executed through the mcp__kalysta__executeQuery tool
• The database parameter should be set to the appropriate database name
• Parse and present results in a clear, formatted way

Overview

This skill helps you:

1. Understand and explore any database schema accessible via Kalysta MCP
2. Generate safe, optimized SQL queries with proper filters
3. Execute queries through the Kalysta MCP server
4. Analyze and present query results
5. Discover data patterns and insights

Database Discovery

Since this skill works with any database, always start by discovering the schema:

List all databases:

SHOW DATABASES;

List all tables in a database:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'your_database_name';

Show table structure:

DESCRIBE table_name;

or

SHOW COLUMNS FROM table_name;

Get detailed column information:

SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'your_database_name'
  AND table_name = 'your_table_name';

Query Generation Workflow

Step 1: Look Up Database Schema

MANDATORY FIRST STEP - Always verify schema before writing queries:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'database_name';

DESCRIBE table_name;

SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'database_name'
  AND table_name = 'target_table';

Verify:

• ✅ Table name exists and is spelled correctly
• ✅ Column names are correct
• ✅ Check if table has common filter columns (is_testing, deleted_at, etc.)
• ✅ Understand data types and constraints

Step 2: Understand the User's Intent

Analyze what the user wants to know:

• Data exploration: "What are the distinct request types?"
• Analytics: "How many requests failed last week?"
• Debugging: "Show me the error logs for request ID 12345"
• Reporting: "Top 10 users by credit usage"

Step 3: Identify Relevant Tables and Columns

Based on schema lookup, determine:

• Primary table for the query
• Related tables for JOINs
• Key columns for SELECT and WHERE clauses

Use DISTINCT for exploration:

SELECT DISTINCT column_name FROM table_name WHERE deleted_at IS NULL;

Example:

SELECT DISTINCT status FROM orders WHERE deleted_at IS NULL;
SELECT DISTINCT category FROM products WHERE deleted_at IS NULL;

Step 4: Build the Query with Mandatory Filters

Basic Query Template:

SELECT
    column1,
    column2,
    column3
FROM table_name
WHERE 1=1
    AND deleted_at IS NULL
    AND is_testing = FALSE
    AND [additional_conditions]
ORDER BY column1 DESC
LIMIT 100;

Important Considerations:

• ⚠️ NEVER start query with comments (e.g., -- comment) - Kalysta MCP will reject it
• Always check if the table has filter columns like is_testing, deleted_at using DESCRIBE
• Include appropriate filters based on what columns exist
• Use proper column names (refer to schema lookup)
• Consider performance: add LIMIT for large result sets
• Use appropriate JOINs when querying related data

Step 5: Execute Query via Kalysta MCP

Execute the query using the MCP tool:

{
  "query": "SELECT id, name, status FROM table_name WHERE deleted_at IS NULL LIMIT 10",
  "database": "your_database_name"
}

Important:

• Query MUST start with SELECT, WITH, SHOW, DESCRIBE, or EXPLAIN
• NO comments (-- or /* */) before the query
• Database name should be the actual database you want to query

Step 6: Present Results

Format and present the results clearly:

• For small result sets: Show full table
• For large result sets: Summarize with key metrics
• For data exploration: Highlight distinct values and patterns
• For analytics: Provide counts, averages, and insights

Common Query Patterns

Pattern 1: Explore Distinct Values

Use Case: Understanding possible values in a column

SELECT DISTINCT column_name, COUNT(*) as count
FROM table_name
WHERE deleted_at IS NULL
GROUP BY column_name
ORDER BY count DESC;

Examples:

SELECT DISTINCT status, COUNT(*) as count
FROM orders
WHERE deleted_at IS NULL
GROUP BY status;

SELECT DISTINCT category, COUNT(*) as usage_count
FROM products
WHERE deleted_at IS NULL
GROUP BY category;

Pattern 2: Time-Based Analytics

Use Case: Analyzing trends over time

SELECT
    DATE(created_at) as date,
    COUNT(*) as record_count,
    COUNT(CASE WHEN status = 'completed' THEN 1 END) as completed_count,
    COUNT(CASE WHEN status = 'failed' THEN 1 END) as failed_count
FROM table_name
WHERE deleted_at IS NULL
    AND created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY DATE(created_at)
ORDER BY date DESC;

Pattern 3: Aggregation by Category

Use Case: Per-category statistics

SELECT
    category_column,
    COUNT(*) as total_count,
    COUNT(CASE WHEN status = 'completed' THEN 1 END) as completed,
    COUNT(CASE WHEN status = 'failed' THEN 1 END) as failed,
    MAX(created_at) as last_record_at
FROM table_name
WHERE deleted_at IS NULL
GROUP BY category_column
ORDER BY total_count DESC
LIMIT 10;

Pattern 4: Join Queries for Related Data

Use Case: Combining data from multiple tables

SELECT
    t1.id,
    t1.status,
    t2.related_field,
    t3.another_field
FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.foreign_key AND t2.deleted_at IS NULL
LEFT JOIN table3 t3 ON t2.id = t3.foreign_key AND t3.deleted_at IS NULL
WHERE t1.deleted_at IS NULL
    AND t1.id = 12345;

Pattern 5: Error Investigation

Use Case: Debugging failures

SELECT
    id,
    user_id,
    error_message,
    created_at
FROM table_name
WHERE deleted_at IS NULL
    AND status = 'failed'
ORDER BY created_at DESC
LIMIT 50;

Best Practices

Query Construction

• 🔴 NEVER start queries with comments (-- or /* */) - Kalysta MCP will reject them
• ✅ Look up schema first (DESCRIBE table_name)
• ✅ Start with DISTINCT to explore data
• ✅ Use LIMIT for all exploratory queries
• ✅ Test queries incrementally
• ✅ Avoid SELECT * - specify needed columns
• ✅ Use indexed columns in WHERE (typically: id, foreign keys, created_at)
• ✅ Verify filter columns exist before using them (use DESCRIBE)

Common Filter Columns

When working with databases, check for these common filter columns using DESCRIBE table_name:

Common soft-delete column:

• deleted_at - Filter with WHERE deleted_at IS NULL

Common environment/testing columns:

• is_testing - Filter with WHERE is_testing = FALSE (production data)
• is_active - Filter with WHERE is_active = TRUE
• status - Filter based on specific status values

Always verify column existence before applying filters:

DESCRIBE table_name;

Examples

Example 1: Daily Statistics

User Request: "Show me daily statistics for the last 7 days"

Step 1 - Verify Schema:

DESCRIBE orders;

Step 2 - Build Query:

SELECT
    DATE(created_at) as date,
    status,
    COUNT(*) as total_orders,
    SUM(amount) as total_amount
FROM orders
WHERE deleted_at IS NULL
    AND created_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
GROUP BY DATE(created_at), status
ORDER BY date DESC, status;

Example 2: Top Users Report

User Request: "Which users have the most activity?"

Step 1 - Verify Schema:

DESCRIBE users;
DESCRIBE activity_logs;

Step 2 - Build Query:

SELECT
    u.id,
    u.email,
    COUNT(a.id) as activity_count,
    MAX(a.created_at) as last_activity
FROM users u
LEFT JOIN activity_logs a
    ON u.id = a.user_id
    AND a.deleted_at IS NULL
WHERE u.deleted_at IS NULL
GROUP BY u.id, u.email
ORDER BY activity_count DESC
LIMIT 20;

Troubleshooting

Issue	Solution
Query Rejected by Kalysta	Remove comments from query start. Query MUST begin with SELECT/WITH/SHOW/DESCRIBE/EXPLAIN
Column Not Found	Run DESCRIBE table_name first to verify columns exist
No Results	Check filters, verify data exists: SELECT COUNT(*) FROM table WHERE deleted_at IS NULL
Query Too Slow	Add LIMIT, use indexed columns, narrow date range
Unexpected Data	Use SELECT DISTINCT column to explore actual values first

Quick Workflow

For any query request:

1. Look up schema → DESCRIBE table_name to see all columns
2. Verify columns exist → Confirm column names and check for filter columns (deleted_at, is_testing, etc.)
3. Explore data first → Use SELECT DISTINCT to understand values
4. Build query → Include appropriate filters, NEVER start with comments
5. Test with LIMIT → Run with LIMIT 10 first
6. Execute via MCP → Use mcp__kalysta__executeQuery tool with database name
7. Present results → Format clearly with insights

Key Reminders

Priority	Rule
🔴 CRITICAL	NEVER start queries with comments (-- or /* */) - Kalysta will reject
🔴 CRITICAL	Query MUST start with: SELECT, WITH, SHOW, DESCRIBE, or EXPLAIN
🔴 CRITICAL	Look up schema first: DESCRIBE table_name
🔴 CRITICAL	Verify filter columns exist before using them
🟡 IMPORTANT	Use SELECT DISTINCT to explore data first
🟡 IMPORTANT	Test with LIMIT before running full queries
🟢 BEST PRACTICE	Only SELECT queries - never modify data

Common filter columns to check for:

• deleted_at → Use WHERE deleted_at IS NULL
• is_testing → Use WHERE is_testing = FALSE
• is_active → Use WHERE is_active = TRUE