generate

The /generate_sql endpoint converts natural language questions into SQL queries. It allows you to interact with your database using natural language.

Basic Usage

To generate a SQL query, send a request with your question:

// Request
{
  "projectId: 1,
  "question": "Show me all customers"
}

// Response
{
  "id": "1fbc0d64-1c58-45b2-a990-9183bbbcf913",
  "sql": "SELECT * FROM \"olist_customers_dataset\"",
  "threadId": "9c537507-9cec-46ed-b877-07bfa6322bed"
}

Getting Both Wren SQL and Dialect SQL

Use the returnBothSqlDialect parameter to receive both the original Wren SQL and the database-specific dialect SQL:

// Request
{
  "projectId": 1,
  "question": "Show me top 10 customers with their details",
  "returnBothSqlDialect": true
}

// Response
{
  "id": "1fbc0d64-1c58-45b2-a990-9183bbbcf913",
  "sql": "SELECT \"customer_id\" AS \"Customer_ID\", \"customer_unique_id\" AS \"Customer_Unique_ID\", \"customer_city\" AS \"Customer_City\", \"customer_state\" AS \"Customer_State\" FROM \"olist_customers_dataset\" LIMIT 10",
  "dialectSql": "SELECT olist_customers_dataset.customer_id AS `Customer_ID`, olist_customers_dataset.customer_unique_id AS `Customer_Unique_ID`, olist_customers_dataset.customer_city AS `Customer_City`, olist_customers_dataset.customer_state AS `Customer_State` FROM (SELECT __source.customer_city AS customer_city, __source.customer_id AS customer_id, __source.customer_state AS customer_state, __source.customer_unique_id AS customer_unique_id FROM `wrenai-saas-staging`.sample_dataset_ecommerce.olist_customers_dataset AS __source) AS olist_customers_dataset LIMIT 10",
  "threadId": "9c537507-9cec-46ed-b877-07bfa6322bed"
}

Understanding the Response Fields

sql: Clean, readable Wren SQL that's optimized for understanding
dialectSql: Database-specific SQL that will actually be executed, including schema prefixes, table references, and database-specific syntax

When to Use `returnBothSqlDialect`

Debugging: Compare the generated Wren SQL with the actual executed SQL
Transparency: See exactly what SQL will run against your database
Integration: Use the dialectSql directly in your database tools if needed

SQL Dialect Compatibility

The returnSqlDialect parameter returns only the dialect SQL:

// Request
{
  "projectId": 1,
  "question": "Show me all customers",
  "returnSqlDialect": true
}

// Response  
{
  "id": "1fbc0d64-1c58-45b2-a990-9183bbbcf913",
  "sql": "SELECT olist_customers_dataset.customer_id, olist_customers_dataset.customer_unique_id FROM `project.dataset.olist_customers_dataset`",
  "threadId": "9c537507-9cec-46ed-b877-07bfa6322bed"
}

Conversation Context

You can use the threadId returned in the response to ask follow-up questions while maintaining context:

// Follow-up question
{
  "projectId: 1,
  "question": "list top 10 only",
  "threadId": "9c537507-9cec-46ed-b877-07bfa6322bed"
}

// Response
{
  "id": "2589615b-abbd-48b0-926f-128faa96a87e",
  "sql": "SELECT * FROM \"olist_customers_dataset\" ORDER BY \"customer_id\" ASC LIMIT 10",
  "threadId": "9c537507-9cec-46ed-b877-07bfa6322bed"
}

Executing Queries

Once you have the SQL, you can:

Pass the SQL to the /run_sql endpoint to execute the query and get results
Use the returnSqlDialect: true parameter to get SQL in your database's native dialect and run it directly in your database

Non-SQL Query Handling

If your question can't be converted to SQL, you'll receive an error response with the following fields:

id: A unique identifier for the error response
code: An error code indicating the type of issue encountered:
- NON_SQL_QUERY: The question cannot be translated to SQL because it's unrelated to data querying
- NO_DEPLOYMENT_FOUND: No active deployment was found for the project
- POLLING_TIMEOUT: The operation timed out while waiting for a response from the AI service
error: A human-readable description explaining why the query couldn't be translated to SQL
explanationQueryId: An identifier you can use to get a more detailed explanation

// Example 1: Asking question "hello"
{
  "id": "e593369b-e222-4435-b874-dc10edb12a96",
  "code": "NON_SQL_QUERY",
  "error": "Vague greeting unrelated to schema, SQL, or user guide; no specific intent identified.",
  "explanationQueryId": "475afc1f-7950-4bc7-a248-a7da394d137a"
}

// Example 2: Asking question "what could you do"
{
  "id": "75c13d09-6f86-4e79-a00e-a4f85f73f2d7",
  "code": "NON_SQL_QUERY",
  "error": "User asks about Wren AI's features and capabilities, unrelated to database schema.",
  "explanationQueryId": "71b016c5-42bb-4897-82d6-46f9b0bf7d94"
}

Use the explanationQueryId with the /stream_explanation endpoint to receive a detailed explanation response streamed as events.