Text2SQL.ai logo

API Integration

Published on

Our public API is useful to build your own integrations, create your applications, white-label, etc.

Important: The API is only available for Pro plan users and above.

Each API request consumes credits from your plan's quota in the same way as using the application interface would

Quick Start

Want to test the API quickly? Here's the simplest example with just the essential parameters:

curl -X POST "https://api.text2sql.ai/api/external/generate-sql" \
-H "Authorization: Bearer YOUR_API_KEY" \
-H "Content-Type: application/json" \
-d '{
      "prompt": "Show me all users created in the last 30 days",
      "connectionID": "your-connection-id"
    }'

This will return:

{
  "output": "SELECT * FROM users WHERE created_at >= CURRENT_DATE - INTERVAL '30 days';",
  "explanation": "This query retrieves all users created within the last 30 days.",
  "conversationID": "550e8400-e29b-41d4-a716-446655440000"
}

That's it! The API will use intelligent defaults for all optional parameters. For more control, see the detailed documentation below.

Authentication

All API requests require authentication using your API key in the Authorization header:

Authorization: Bearer YOUR_API_KEY

Connection ID

To use your database connections with the API, you'll need the Connection ID. You can find this on each connection's settings page in your dashboard.

The Connection ID allows the API to:

  • Access your database schema for more accurate SQL generation
  • Execute queries directly against your database (when using runQuery)
  • Provide context-aware suggestions based on your actual tables and columns

Auto-Fix Technology

Our Generate SQL endpoint includes intelligent auto-fix capabilities. When you set runQuery: true, the system will automatically attempt to fix any SQL errors up to 3 times using AI. This ensures you get working queries even if the initial generation has issues.

If a query cannot be fixed after 3 attempts, the response will include a runError field with the error details, while still returning the final SQL attempt.

Conversation Memory

The Generate SQL endpoint automatically maintains conversation context, allowing you to build upon previous queries naturally. Every request creates or continues a conversation, and the AI remembers your previous prompts and generated queries.

To continue an existing conversation, include the conversationID from a previous response. Without a conversationID, a new conversation is automatically created. This enables iterative query refinement without repeating context.

Mode Parameter: One-Shot vs Conversational

The Generate SQL endpoint supports two modes of operation:

one-shot mode (default): Always attempts to generate SQL immediately, even with ambiguous requests. This mode interpolates missing information and makes reasonable assumptions to produce a working query. Best for automated systems or when you need a query regardless of ambiguity.

conversational mode: May ask clarifying questions when requests are ambiguous or lack critical details. Returns null for the output field and uses the explanation field for questions. Best for interactive applications where accuracy is more important than immediate generation.

Both modes maintain conversation history and can reference previous context. The main difference is how they handle ambiguous or incomplete requests.

Generate SQL

Convert natural language to SQL queries with automatic error fixing, conversation memory, and optional query execution

Parameters

  • prompt (Required): The natural language description of the query you want to generate
  • connectionID (Optional): ID of your database connection (found on the connection's settings page)
  • runQuery (Optional): Set to true to execute the query and return results. Requires connectionID
  • limit (Optional): Maximum number of rows to return when runQuery is true. Defaults to 100 rows
  • conversationID (Optional): UUID of an existing conversation to continue. If omitted, a new conversation is created automatically
  • mode (Optional): Either "one-shot" (default) for immediate SQL generation, or "conversational" to allow clarifying questions when the request is ambiguous

Example Request

curl -X POST "https://api.text2sql.ai/api/external/generate-sql" \
-H "Authorization: Bearer YOUR_API_KEY" \
-H "Content-Type: application/json" \
-d '{
      "prompt": "Find all items with id 1",
      "connectionID": "your-connection-id",
      "runQuery": true,
      "limit": 100,
      "conversationID": "optional-conversation-id",
      "mode": "one-shot"
    }'

Success Response

{
  "output": "SELECT * FROM items WHERE id = 1;",
  "explanation": "This query retrieves all columns from the items table where the id column equals 1, returning a single row that matches this condition.",
  "results": [{ "id": 1, "name": "Sample Item" }],
  "conversationID": "550e8400-e29b-41d4-a716-446655440000"
}

Error Response (when query execution fails)

{
  "output": "SELECT * FROM non_existent_table WHERE id = 1;",
  "explanation": "This query attempts to retrieve data from a table that doesn't exist in the database.",
  "runError": "relation \"non_existent_table\" does not exist",
  "conversationID": "550e8400-e29b-41d4-a716-446655440000"
}

Conversation Example

Here's how to use conversations to iteratively refine queries:

# First request - starts a new conversation
curl -X POST "https://api.text2sql.ai/api/external/generate-sql" \
-H "Authorization: Bearer YOUR_API_KEY" \
-H "Content-Type: application/json" \
-d '{
      "prompt": "Show me all users",
      "connectionID": "your-connection-id",
      "mode": "one-shot"
    }'

# Response includes conversationID
{
  "output": "SELECT * FROM users;",
  "explanation": "This query retrieves all columns and rows from the users table.",
  "conversationID": "550e8400-e29b-41d4-a716-446655440000"
}

# Second request - continues the conversation
curl -X POST "https://api.text2sql.ai/api/external/generate-sql" \
-H "Authorization: Bearer YOUR_API_KEY" \
-H "Content-Type: application/json" \
-d '{
      "prompt": "Now filter by active status",
      "connectionID": "your-connection-id",
      "conversationID": "550e8400-e29b-41d4-a716-446655440000",
      "mode": "one-shot"
    }'

# Response builds on previous context
{
  "output": "SELECT * FROM users WHERE active = true;",
  "explanation": "Building on the previous query, this filters the users table to show only active users.",
  "conversationID": "550e8400-e29b-41d4-a716-446655440000"
}

Conversational Mode Examples

When using conversational mode, the API can ask clarifying questions for ambiguous requests:

# Example 1: Ambiguous request triggers clarification
curl -X POST "https://api.text2sql.ai/api/external/generate-sql" \
-H "Authorization: Bearer YOUR_API_KEY" \
-H "Content-Type: application/json" \
-d '{
      "prompt": "Show me the best customers",
      "connectionID": "your-connection-id",
      "mode": "conversational"
    }'

# Response asks for clarification
{
  "output": null,
  "explanation": "I need more information to generate an accurate query. How would you like to define 'best customers'? For example: 
    - By total purchase amount?
    - By number of orders?
    - By frequency of purchases?
    - By most recent activity?
    Also, would you like to see a specific number of top customers (e.g., top 10)?",
  "conversationID": "550e8400-e29b-41d4-a716-446655440000"
}

# Example 2: Clear request generates SQL immediately
curl -X POST "https://api.text2sql.ai/api/external/generate-sql" \
-H "Authorization: Bearer YOUR_API_KEY" \
-H "Content-Type: application/json" \
-d '{
      "prompt": "Show me all customers who made purchases over $1000",
      "connectionID": "your-connection-id",
      "mode": "conversational"
    }'

# Response generates SQL directly
{
  "output": "SELECT DISTINCT c.* FROM customers c JOIN orders o ON c.id = o.customer_id WHERE o.total_amount > 1000;",
  "explanation": "This query retrieves all distinct customers who have made at least one purchase over $1000, joining the customers and orders tables.",
  "conversationID": "550e8400-e29b-41d4-a716-446655440001"
}

Fix SQL

Fix SQL query errors or improve query efficiency

Parameters

  • query (Required): The SQL query to fix
  • error (Optional): The error message received
  • connectionID (Optional): ID of your database connection for schema context

Example Request

curl -X POST "https://api.text2sql.ai/api/external/fix-sql" \
-H "Authorization: Bearer YOUR_API_KEY" \
-H "Content-Type: application/json" \
-d '{
      "query": "SELECT * FROM users WHERE id = 1;",
      "error": "relation \"users\" does not exist",
      "connectionID": "your-connection-id"
    }'

Response

{
  "output": "SELECT * FROM items WHERE id = 1;"
}

Explain SQL

Get natural language explanations of SQL queries

Parameters

  • query (Required): The SQL query to explain
  • connectionID (Optional): ID of your database connection for context-aware explanations

Example Request

curl -X POST "https://api.text2sql.ai/api/external/explain-sql" \
-H "Authorization: Bearer YOUR_API_KEY" \
-H "Content-Type: application/json" \
-d '{
      "query": "SELECT * FROM items WHERE id = 1;",
      "connectionID": "your-connection-id"
    }'

Response

{
  "output": "This query retrieves all columns from the items table where the id equals 1."
}

Usage

Get current API usage statistics

Example Request

curl "https://api.text2sql.ai/api/external/usage" \
-H "Authorization: Bearer YOUR_API_KEY"

Response

{
  "used": 50,
  "remaining": 950,
  "limit": 1000,
  "billing_period_start": "2024-03-01T00:00:00.000Z",
  "billing_period_end": "2024-04-01T00:00:00.000Z"
}