Skip to content

API.md

Latest commit

 

History

History
520 lines (389 loc) · 9.53 KB

API.md

File metadata and controls

520 lines (389 loc) · 9.53 KB
Raw

API Documentation

Overview

The AI to DB API provides endpoints for database connection management and natural language query execution.

Base URL: http://localhost:8000/api

Authentication: Not required (for development)

Content-Type: application/json

Endpoints

Health Check

GET /

Returns application health status.

Response:

{
  "message": "AI to DB API is running",
  "version": "1.0.0",
  "status": "healthy"
}

GET /health

Detailed health check for monitoring.

Response:

{
  "status": "healthy",
  "app_name": "AI to DB",
  "version": "1.0.0"
}

Connection Endpoints

Test Database Connection

POST /api/connection/test

Test a database connection and retrieve basic schema information.

Request Body:

{
  "connection_string": "postgresql://user:password@host:5432/database",
  "alias": "My Database" // Optional
}

Response (Success):

{
  "success": true,
  "message": "Connection successful",
  "database_type": "postgresql",
  "schema_info": {
    "total_tables": 10,
    "tables": ["users", "products", "orders"],
    "has_more": true
  }
}

Response (Error):

{
  "success": false,
  "message": "Database error: connection refused",
  "database_type": null,
  "schema_info": null
}

Status Codes:

  • 200 OK: Connection successful
  • 400 Bad Request: Invalid connection string
  • 500 Internal Server Error: Server error

Get Database Schema

POST /api/connection/schema

Extract complete database schema information.

Request Body:

{
  "connection_string": "postgresql://user:password@host:5432/database"
}

Response:

{
  "success": true,
  "tables": [
    {
      "table_name": "users",
      "columns": [
        {
          "name": "user_id",
          "type": "INTEGER",
          "nullable": false,
          "default": null,
          "primary_key": true
        },
        {
          "name": "username",
          "type": "VARCHAR(50)",
          "nullable": false,
          "default": null,
          "primary_key": false
        }
      ],
      "primary_keys": ["user_id"],
      "foreign_keys": [],
      "indexes": [
        {
          "name": "idx_username",
          "columns": ["username"],
          "unique": true
        }
      ]
    }
  ],
  "total_tables": 4,
  "database_type": "postgresql"
}

Status Codes:

  • 200 OK: Schema extracted successfully
  • 400 Bad Request: Invalid connection or extraction failed
  • 500 Internal Server Error: Server error

Query Endpoints

Execute Natural Language Query

POST /api/query/execute

Convert natural language question to SQL and execute it.

Request Body:

{
  "connection_string": "postgresql://user:password@host:5432/database",
  "question": "Show me all users who signed up last month"
}

Response (Success):

{
  "success": true,
  "question": "Show me all users who signed up last month",
  "generated_sql": "SELECT * FROM users WHERE created_at >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month') AND created_at < DATE_TRUNC('month', CURRENT_DATE)",
  "results": [
    {
      "user_id": 1,
      "username": "john_doe",
      "email": "john@example.com",
      "created_at": "2026-01-15T10:30:00"
    },
    {
      "user_id": 2,
      "username": "jane_smith",
      "email": "jane@example.com",
      "created_at": "2026-01-20T14:15:00"
    }
  ],
  "row_count": 2,
  "error": null,
  "execution_time": 0.234
}

Response (Error):

{
  "success": false,
  "question": "Show me all users",
  "generated_sql": "DROP TABLE users",
  "results": null,
  "row_count": null,
  "error": "Security validation failed: Dangerous keyword detected: DROP",
  "execution_time": 0.123
}

Status Codes:

  • 200 OK: Query processed (check success field for execution result)
  • 400 Bad Request: Invalid request
  • 500 Internal Server Error: Server error

Processing Steps:

  1. Extract database schema
  2. Generate SQL from natural language using GPT-4
  3. Validate SQL for security (read-only check)
  4. Execute query and return results

Limitations:

  • Maximum 1000 rows returned
  • Only SELECT queries allowed
  • Timeout after 60 seconds

Validate SQL Query

POST /api/query/validate-sql

Validate a SQL query for security without executing it.

Query Parameters:

  • sql (string, required): SQL query to validate

Request:

POST /api/query/validate-sql?sql=SELECT%20*%20FROM%20users

Response (Valid):

{
  "valid": true,
  "message": "Query is safe to execute",
  "sql": "SELECT * FROM users"
}

Response (Invalid):

{
  "valid": false,
  "message": "Dangerous keyword detected: DELETE",
  "sql": "DELETE FROM users"
}

Security Checks:

  • Query must start with SELECT or WITH
  • No destructive keywords (INSERT, UPDATE, DELETE, DROP, etc.)
  • No SQL injection patterns
  • No access to system tables

Data Models

ConnectionRequest

{
  connection_string: string;  // Required
  alias?: string;             // Optional
}

ConnectionResponse

{
  success: boolean;
  message: string;
  database_type?: string;
  schema_info?: {
    total_tables: number;
    tables: string[];
    has_more: boolean;
  };
}

QueryRequest

{
  connection_string: string; // Required
  question: string; // Required
}

QueryResponse

{
  success: boolean;
  question: string;
  generated_sql?: string;
  results?: Array<Record<string, any>>;
  row_count?: number;
  error?: string;
  execution_time?: number;
}

SchemaResponse

{
  success: boolean;
  tables: Array<{
    table_name: string;
    columns: Array<{
      name: string;
      type: string;
      nullable: boolean;
      default: string | null;
      primary_key: boolean;
    }>;
    primary_keys: string[];
    foreign_keys: Array<{
      constrained_columns: string[];
      referred_table: string;
      referred_columns: string[];
    }>;
    indexes: Array<{
      name: string;
      columns: string[];
      unique: boolean;
    }>;
  }>;
  total_tables: number;
  database_type: string;
}

Error Handling

Error Response Format

{
  "detail": "Error message describing what went wrong"
}

Common Error Codes

  • 400 Bad Request: Invalid input data

    • Invalid connection string format
    • Missing required fields
    • Validation errors

  • 500 Internal Server Error: Server-side errors

    • Database connection failures
    • LLM API errors
    • Unexpected exceptions

Rate Limiting

Currently, no rate limiting is implemented. For production:

  • Implement API key authentication
  • Add rate limiting middleware
  • Set per-user query limits

Best Practices

Connection Strings

  • Always use secure connections (SSL/TLS) in production
  • Don't expose credentials in logs or error messages
  • Use environment variables for sensitive data

Queries

  • Be specific with natural language questions
  • Reference table and column names when possible
  • Start with simple queries and iterate

Performance

  • Use LIMIT clauses for large datasets
  • Create appropriate indexes on your database
  • Monitor query execution times

Examples

Example 1: Connect and Query

# 1. Test connection
curl -X POST http://localhost:8000/api/connection/test \
  -H "Content-Type: application/json" \
  -d '{
    "connection_string": "postgresql://demo:demo123@localhost:5432/demo_db"
  }'

# 2. Execute query
curl -X POST http://localhost:8000/api/query/execute \
  -H "Content-Type: application/json" \
  -d '{
    "connection_string": "postgresql://demo:demo123@localhost:5432/demo_db",
    "question": "How many users are there?"
  }'

Example 2: Get Schema

curl -X POST http://localhost:8000/api/connection/schema \
  -H "Content-Type: application/json" \
  -d '{
    "connection_string": "sqlite:///./demo.db"
  }'

Example 3: Validate SQL

curl -X POST "http://localhost:8000/api/query/validate-sql?sql=SELECT%20*%20FROM%20users%20LIMIT%2010"

Interactive Documentation

For interactive API testing, visit:

These provide:

  • Interactive API testing interface
  • Request/response examples
  • Schema documentation
  • Try-it-out functionality

Supported Database Types

Database Connection String Format Driver
PostgreSQL postgresql://user:pass@host:5432/db psycopg2
MySQL mysql+pymysql://user:pass@host:3306/db PyMySQL
SQLite sqlite:///./database.db Built-in
Oracle oracle+cx_oracle://user:pass@host:1521/service cx_Oracle
MSSQL mssql+pyodbc://user:pass@host:1433/db pyodbc

Security Considerations

Read-Only Operations

All queries are validated to ensure they:

  • Only perform SELECT operations
  • Don't modify data or schema
  • Don't execute stored procedures
  • Don't access system tables maliciously

SQL Injection Prevention

  • Pattern detection for common injection techniques
  • Keyword blacklisting
  • Query structure validation
  • Parameterized query execution

Best Practices

  • Always validate user input
  • Use prepared statements
  • Limit query complexity
  • Monitor for suspicious activity
  • Keep dependencies updated

For more information, see the main README.md file.