SQL Generator

You are a SQL query generation expert. The user wants to convert natural language descriptions into syntactically correct SQL queries for various databases.

What to check first

• Identify the target database system (PostgreSQL, MySQL, SQLite, SQL Server, etc.) — different databases have different syntax for functions and types
• Examine the existing schema or table structure if provided — query accuracy depends on knowing actual column names and types
• Determine if the query needs to handle NULL values, aggregations, or complex joins

Steps

1. Parse the natural language request to identify the main operation (SELECT, INSERT, UPDATE, DELETE, JOIN, aggregation)
2. Extract key entities: table names, column names, conditions, filters, and sorting requirements
3. Map natural language operators ("greater than" → >, "contains" → LIKE or ILIKE) to SQL syntax
4. Determine if JOINs are needed by looking for relationships between mentioned tables
5. Add WHERE clauses for any filtering conditions mentioned in the request
6. Include GROUP BY and aggregation functions (COUNT, SUM, AVG) if the request asks for summaries
7. Apply ORDER BY and LIMIT clauses for sorting and result constraints
8. Validate the generated SQL against the target database dialect and schema

Code

import re
from typing import Optional

class SQLGenerator:
    def __init__(self, db_type: str = "postgresql"):
        self.db_type = db_type.lower()
        self.operator_map = {
            "greater than": ">",
            "less than": "<",
            "equals": "=",
            "not equal": "!=",
            "contains": "ILIKE" if db_type == "postgresql" else "LIKE",
            "starts with": "LIKE",
            "ends with": "LIKE",
        }
    
    def generate(self, natural_query: str, schema: Optional[dict] = None) -> str:
        """
        Generate SQL from natural language.
        
        Args:
            natural_query: Natural language request (e.g., "Get all users over 18")
            schema: Optional dict mapping table names to column lists
        
        Returns:
            SQL query string
        """
        query = natural_query.lower().strip()
        
        # Detect operation type
        operation = self._detect_operation(query)
        
        if operation == "select":
            return self._generate_select(query, schema)
        elif operation == "insert":
            return self._generate_insert(query, schema)
        elif operation == "update":
            return self._generate_update(query, schema)
        elif operation == "delete":
            return self._generate_delete(query, schema)
        else:
            raise ValueError(f"Cannot determine SQL operation from: {natural_query}")
    
    def _detect_operation(self, query: str) -> str:
        """Identify SELECT, INSERT, UPDATE, or DELETE operation."""
        if any(word in query for word in ["get",

Note: this example was truncated in the source. See the GitHub repo for the latest full version.

Common Pitfalls

• Treating this skill as a one-shot solution — most workflows need iteration and verification
• Skipping the verification steps — you don't know it worked until you measure
• Applying this skill without understanding the underlying problem — read the related docs first

When NOT to Use This Skill

• When a simpler manual approach would take less than 10 minutes
• On critical production systems without testing in staging first
• When you don't have permission or authorization to make these changes

How to Verify It Worked

• Run the verification steps documented above
• Compare the output against your expected baseline
• Check logs for any warnings or errors — silent failures are the worst kind

Production Considerations

• Test in staging before deploying to production
• Have a rollback plan — every change should be reversible
• Monitor the affected systems for at least 24 hours after the change