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.

Live Demo

Want to see the API in action? Check out our open-source chat demo on GitHub that showcases how to integrate the Text2SQL.ai API into a real application. The demo includes:

  • Complete working implementation with conversation memory
  • React-based chat interface
  • Best practices for API integration
  • Ready-to-run example code

This is a great starting point for building your own integrations!

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."
}

Create Connection

Programmatically create a new database connection. This endpoint auto-tests the connection and extracts the schema before creation. Requires team permission.

Parameters

  • name (Required): Connection name
  • databaseType (Required): Database type: "postgres", "mysql", "mssql", or "plsql"
  • host (Required): Database hostname or IP address (no localhost in production)
  • port (Required): Port number (1-65535)
  • database (Required): Database name
  • username (Required): Database username
  • password (Required): Database password
  • ssl (Optional): Enable SSL (default: false)
  • sslCA (Optional): SSL certificate
  • selectedHost (Optional): Host provider: "aws-rds", "google-cloud-sql", "azure", "local", or "other"
  • customHostName (Optional): Custom host name (required if selectedHost is "other")

Example Request

curl -X POST "https://api.text2sql.ai/api/external/connections" \
-H "Authorization: Bearer YOUR_API_KEY" \
-H "Content-Type: application/json" \
-d '{
      "name": "Production PostgreSQL",
      "databaseType": "postgres",
      "host": "my-db.us-east-1.rds.amazonaws.com",
      "port": 5432,
      "database": "production_db",
      "username": "api_user",
      "password": "secure_password",
      "ssl": true,
      "selectedHost": "aws-rds"
    }'

Success Response

{
  "id": "550e8400-e29b-41d4-a716-446655440000"
}

Error Response

{
  "error": "API connection creation not enabled",
  "message": "Your team does not have permission to create connections via API. Please contact support to enable this feature."
}

⚠️ Restricted Feature

The Create Connection endpoint requires special permission. Contact support to enable allow_api_connection_creation for your team. This feature allows you to programmatically create database connections via the API.

Create Connection - Error Responses

403 Forbidden - Feature Not Enabled

{
  "error": "API connection creation not enabled",
  "message": "Your team does not have permission to create connections via API. Please contact support to enable this feature."
}

403 Forbidden - Insufficient Credits

{
  "error": "Insufficient API credits",
  "message": "Your team has run out of API credits. Please purchase more credits to continue."
}

400 Bad Request - Localhost Blocked

{
  "error": "Cannot connect to localhost or local IP addresses in production. Please provide a publicly accessible database host. Need to connect to a local database? Try our desktop app for secure local connections."
}

400 Bad Request - Connection Test Failed

{
  "error": "Connection test failed",
  "message": "Could not connect to database: Connection timeout",
  "details": {
    "code": "ETIMEDOUT",
    "suggestion": "Check that the host and port are correct and that your firewall allows connections from IP: 165.227.187.107"
  }
}

500 Internal Server Error - Schema Extraction Failed

{
  "error": "Schema extraction failed",
  "message": "Could not extract database schema",
  "details": {
    "reason": "Insufficient privileges to query information_schema"
  }
}

Usage

Get current API usage statistics

Parameters

  • breakdownConnections (Optional): Set to true to include usage breakdown by connection ID
  • startDate (Optional): Unix timestamp (milliseconds) for start of date range. Must be used with endDate
  • endDate (Optional): Unix timestamp (milliseconds) for end of date range. Must be used with startDate

Example Request

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

Response

{
  "remaining_credits": 950,
  "billing_period_start": "2024-03-01T00:00:00.000Z",
  "billing_period_end": "2024-04-01T00:00:00.000Z",
  "used_credits": 50
}

Advanced Usage Example

Get detailed usage statistics with connection breakdown and date filtering:

# Example 1: Get usage with connection breakdown
curl "https://api.text2sql.ai/api/external/usage?breakdownConnections=true" \
-H "Authorization: Bearer YOUR_API_KEY"

# Example 2: Get usage for a specific date range with connection breakdown
curl "https://api.text2sql.ai/api/external/usage?breakdownConnections=true&startDate=1704067200000&endDate=1706745600000" \
-H "Authorization: Bearer YOUR_API_KEY"

Response with Connection Breakdown

{
  "remaining_credits": 950,
  "billing_period_start": "2024-03-01T00:00:00.000Z",
  "billing_period_end": "2024-04-01T00:00:00.000Z",
  "used_credits": 50,
  "used_credits_by_connection": {
    "connection-uuid-1": 30,
    "connection-uuid-2": 15,
    "no_connection": 5
  }
}