Skip to main content

Text-to-SQL Datasource Plugin

Link to Plugin Source Code

Overview​

A Godspeed plugin that enables natural language to SQL conversion and multi-database query execution with built-in caching support. This plugin leverages Gemini models to convert natural language queries into SQL statements and executes them across various database types.

Features​

  • Natural language to SQL conversion using Google Gemini
  • Multi-database support
    • PostgreSQL
    • MySQL
    • MongoDB
    • Oracle
  • Automatic query caching with Redis
  • Query validation
  • Schema management
  • Error handling and logging
  • Connection pooling
  • Resource cleanup

1. Install the Plugin​

First, add the Text-to-SQL plugin using

godspeed plugin add @godspeedsystems/plugins-text-to-sql-as-datasource

Setup Environment​

Create or update your .env file with the following:

# Gemini API Configuration
GEMINI_API_KEY=your_gemini_api_key

# PostgreSQL Configuration
PG_USER=your_postgres_user
PG_HOST=localhost
PG_DB=your_database
PG_PASSWORD=your_password
PG_PORT=5432

# MySQL Configuration
MYSQL_HOST=localhost
MYSQL_USER=your_mysql_user
MYSQL_PASSWORD=your_password
MYSQL_DB=your_database

# MongoDB Configuration
MONGODB_URL=mongodb://localhost:27017
MONGODB_DB=your_database

# Oracle Configuration
ORACLE_USER=your_oracle_user
ORACLE_PASSWORD=your_password
ORACLE_CONNECT_STRING=localhost:1521/XEPDB1

# Redis Configuration
REDIS_URL=redis://localhost:6379

DataSource Configuration​

Check your auto generated YAML configuration file src/datasources/text-to-sql.yaml:

type: text-to-sql
config:
  gemini:
    apiKey: ${GEMINI_API_KEY}
  databases:
    postgres:
      enabled: true
      config:
        user: ${PG_USER}
        host: ${PG_HOST}
        database: ${PG_DB}
        password: ${PG_PASSWORD}
        port: ${PG_PORT}
  cache:
    enabled: true
    url: ${REDIS_URL}

Define the Event (API Endpoint)​

Create src/events/sql-query.yaml:

http.post./sql-query:
  summary: "Convert natural language to SQL and execute"
  description: "Endpoint to convert natural language to SQL query and execute it"
  fn: execute-query
  authn: false
  body:
    content:
      application/json:
        schema:
          type: object
          properties:
            query:
              type: string
              description: "Natural language query to be converted to SQL"
            dbType:
              type: string
              description: "Target database type (postgres, mysql, etc.)"
              default: "postgres"
          required:
            - query
  responses:
    200:
      description: "Successful query execution"
      content:
        application/json:
          schema:
            type: object

3. Write Event Handler Function​

Create src/functions/execute-query.ts:

import { GSContext, PlainObject } from "@godspeedsystems/core";

async function handler(ctx: GSContext): Promise<PlainObject> {
  try {
    const { body } = ctx.inputs.data;
    
    if (!body.query) {
      throw new Error("Query is required");
    }

    // Execute the query using the text-to-sql datasource
    const result = await ctx.datasources['text-to-sql'].execute(ctx, {
      query: body.query,
      dbType: body.dbType || 'postgres',
      validateOnly: false,
      cache: true
    });

    return {
      success: true,
      data: result
    };

  } catch (error: any) {
    ctx.logger.error('Query execution failed:', error);
    return {
      success: false,
      code: 500,
      message: error.message,
      data: {
        message: "Query execution failed"
      }
    };
  }
}
export default handler;

Database Setup​

PostgreSQL Container Setup​

# Run PostgreSQL container
sudo docker run --name test-postgres \
  -e POSTGRES_USER=testuser \
  -e POSTGRES_PASSWORD=testpass \
  -e POSTGRES_DB=testdb \
  -p 5434:5432 \
  -d postgres:latest

# Create test data
psql -h localhost -p 5434 -U testuser -d testdb

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    status VARCHAR(20)
);

INSERT INTO users (name, email, status) VALUES 
    ('John Doe', 'john@example.com', 'active'),
    ('Jane Smith', 'jane@example.com', 'inactive');

Redis Cache Setup​

# Run Redis container
sudo docker run --name test-redis \
  -p 6380:6379 \
  -d redis:latest

Test API​

POST /sql-query

Request Body​

{
  "query": "find all active users",
  "dbType": "postgres"
}

Response​

{
  "success": true,
  "data": {
    "rows": [
      {
        "id": 1,
        "name": "John Doe",
        "email": "john@example.com",
        "status": "active"
      }
    ],
    "metadata": {
      "rowCount": 1,
      "executionTime": "0.123s"
    }
  }
}

Key Benefits​

  1. Developer Productivity

    • No need to write SQL queries manually
    • Rapid prototyping and development
    • Focus on business logic instead of query syntax

  2. End User Experience

    • Natural language interface to databases
    • Faster query results with caching
    • Reduced learning curve

  3. Enterprise Ready

    • Production-grade security
    • Scalable architecture
    • Comprehensive logging
    • Performance optimization

Advanced Examples​

Complex Queries​

{
  "query": "Show me total sales by category for active products in last quarter",
  "dbType": "postgres"
}

Analytical Queries​

{
  "query": "Calculate monthly growth rate of user signups",
  "dbType": "postgres"
}

Security Features​

  • Query validation before execution
  • SQL injection prevention
  • Rate limiting support
  • Error handling and sanitization
  • Secure database connections

Performance​

  • Redis caching reduces database load
  • Query optimization suggestions
  • Connection pooling
  • Execution time monitoring
  • Cache hit ratio tracking

Example Usage​

Basic Query Event Configuration​

Create a new file src/events/query.yaml:

'http.post./query':
  fn: execute-query
  body:
    content:
      application/json:
        schema:
          type: object
          properties:
            query:
              type: string
            dbType:
              type: string
              default: postgres
          required: ['query']

Query Execution Workflow​

Define your function src/functions/execute-query.yaml:

id: execute-query
summary: Execute natural language query
tasks:
  - id: convert-and-execute
    fn: datasource.text-to-sql.execute
    args:
      query: <% inputs.body.query %>
      dbType: <% inputs.body.dbType || 'postgres' %>

Example Requests​

  1. Simple Query Request:
curl -X POST http://localhost:3000/query \
  -H "Content-Type: application/json" \
  -d '{"query": "Find all active users"}'
  1. Complex Query Request:
curl -X POST http://localhost:3000/query \
  -H "Content-Type: application/json" \
  -d '{
    "query": "Show total sales by category for the last quarter",
    "dbType": "mysql"
  }'

Example Responses​

{
  "sql": "SELECT category, SUM(amount) AS total_sales FROM sales WHERE date >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH)",
  "data": [
    {
      "category": "Electronics",
      "total_sales": 105000
    }
  ],
  "metadata": {
    "rowCount": 1,
    "executionTime": 48
  }
}

Error Handling​

{
  "error": "QUERY_ERROR",
  "message": "Failed to execute query",
  "details": "Syntax error at or near 'total'"
}

Limitations​

  1. Natural language processing depends on Google Gemini
  2. Redis required for caching functionality
  3. Database-specific features may vary

Troubleshooting​

Common Issues​

  1. Connection Failures

    • Verify database credentials
    • Check network connectivity
    • Ensure services are running

  2. Query Generation Issues

    • Validate Google gemini API key
    • Check query format
    • Review database schema

  3. Cache Problems

    • Verify Redis connection
    • Check cache configuration
    • Monitor memory usage