Transforming Natural Language into SQL Queries with Defog SQLCoder

21 Apr 2025
Transforming Natural Language into SQL Queries with Defog SQLCoder

In the realm of database management and data analysis, the ability to transform natural language queries into SQL statements is invaluable. The Defog SQLCoder, part of the gregwdata/defog-sqlcoder-q8 spec, leverages a powerful 15B parameter LLM fine-tuned on the StarCoder model to convert user questions into SQL queries seamlessly. This capability allows developers to streamline interactions with databases, enabling more natural and intuitive data retrieval without needing deep SQL expertise.

Prerequisites

Before diving into the integration of the Defog SQLCoder, ensure you have the following:

  • An API key for accessing the Cognitive Actions platform.
  • Familiarity with sending HTTP requests and handling JSON data.
  • Basic understanding of SQL and database schemas.

For authentication, you typically pass your API key in the headers of your requests.

Cognitive Actions Overview

Convert Natural Language to SQL

The Convert Natural Language to SQL action utilizes the Defog SQLCoder to interpret natural language queries and generate corresponding SQL statements. This action falls under the category of Natural Language Processing (NLP).

Input

The input for this action requires the following schema:

  • prompt (required): The natural language question you want to convert to SQL.
  • seed (optional): An integer for reproducible outputs. Default is -1 for random seed.
  • debug (optional): Enables debug logging if set to true.
  • maximumLength (optional): Maximum number of tokens in the generated SQL output (default is 1200).
  • numberOfBeams (optional): Number of beams for beam search to enhance output diversity (default is 5).
  • promptTemplate (optional): Template guiding the SQL generation, must include placeholders for user questions and schema metadata.
  • schemaMetadata (optional): A string representing the database schema to be used in SQL generation.

Example Input:

{
  "seed": -1,
  "debug": false,
  "prompt": "Which salesperson made the most sales?",
  "maximumLength": 1200,
  "numberOfBeams": 5,
  "promptTemplate": "### Instructions:\nYour task is convert a question into a SQL query, given a Postgres database schema.\nAdhere to these rules:\n- **Deliberately go through the question and database schema word by word** to appropriately answer the question...",
  "schemaMetadata": "CREATE TABLE products (\n  product_id INTEGER PRIMARY KEY, -- Unique ID for each product\n  name VARCHAR(50), ...\n);"
}

Output

The output of this action is a generated SQL query based on the provided prompt. The output can vary in format but generally returns a SQL statement that answers the question posed.

Example Output:

SELECT s.name
FROM sales s
JOIN (
    SELECT salesperson_id, COUNT(sale_id) as cnt
    FROM sales
    GROUP BY salesperson_id
    ORDER BY cnt DESC
    LIMIT 1
) tmp ON s.salesperson_id = tmp.salesperson_id;

Conceptual Usage Example (Python)

To utilize the Convert Natural Language to SQL action, you can make a request to a hypothetical Cognitive Actions API endpoint. Below is a conceptual example of how to do this using Python:

import requests
import json

# Replace with your Cognitive Actions API key and endpoint
COGNITIVE_ACTIONS_API_KEY = "YOUR_COGNITIVE_ACTIONS_API_KEY"
COGNITIVE_ACTIONS_EXECUTE_URL = "https://api.cognitiveactions.com/actions/execute"  # Hypothetical endpoint

action_id = "b815ac39-d3a8-4de9-96ef-d4154bdc929e"  # Action ID for Convert Natural Language to SQL

# Construct the input payload based on the action's requirements
payload = {
    "seed": -1,
    "debug": False,
    "prompt": "Which salesperson made the most sales?",
    "maximumLength": 1200,
    "numberOfBeams": 5,
    "promptTemplate": "### Instructions:\nYour task is convert a question into a SQL query...",
    "schemaMetadata": "CREATE TABLE products (...);"
}

headers = {
    "Authorization": f"Bearer {COGNITIVE_ACTIONS_API_KEY}",
    "Content-Type": "application/json"
}

try:
    response = requests.post(
        COGNITIVE_ACTIONS_EXECUTE_URL,
        headers=headers,
        json={"action_id": action_id, "inputs": payload}  # Hypothetical structure
    )
    response.raise_for_status()  # Raise an exception for bad status codes (4xx or 5xx)

    result = response.json()
    print("Action executed successfully:")
    print(json.dumps(result, indent=2))

except requests.exceptions.RequestException as e:
    print(f"Error executing action {action_id}: {e}")
    if e.response is not None:
        print(f"Response status: {e.response.status_code}")
        try:
            print(f"Response body: {e.response.json()}")
        except json.JSONDecodeError:
            print(f"Response body: {e.response.text}")

In this code snippet, replace the placeholders with your actual API key and adjust the endpoint as necessary. The action_id corresponds to the Convert Natural Language to SQL action, and the payload is structured according to the input schema.

Conclusion

Integrating the Defog SQLCoder's capability to convert natural language into SQL queries can significantly enhance user interaction with databases. By leveraging this action, developers can create applications that allow users to query databases using simple, intuitive language. As you explore this tool, consider how it can streamline your data retrieval processes and improve user experience. Happy coding!

← Back to All Articles