Skip to content
Raindrop Developer Hub

SmartSQL

SmartSQL transforms database interactions by combining traditional SQL capabilities with AI-powered natural language processing. Execute direct SQL queries when you need precision, or use plain English when exploring data or building user-facing interfaces. Every database operation includes automatic PII detection and metadata tracking to maintain compliance and optimize future AI-generated queries.

The system processes both structured SQL commands and conversational queries like “show me users who joined last month” through the same interface. Background services handle PII scanning and schema analysis without blocking query responses, giving you immediate results while maintaining data security and improving query intelligence over time.

SmartSQL includes external API support with Connect/gRPC endpoints, making it accessible from any client that needs secure database access with AI capabilities.

Prerequisites

  • Basic understanding of SQL query syntax and database operations
  • Raindrop framework installed and configured in your project
  • Familiarity with TypeScript interfaces and async/await patterns
  • Environment variables configured for AI services (Hugging Face, SambaNova)

Creating SmartSQL

Add SmartSQL to your application manifest to enable intelligent database operations:

application "demo-app" {
    smartsql "main-db" {}
}

Run raindrop build generate to create the necessary files and service bindings.

Accessing SmartSQL

SmartSQL becomes available through environment variables following Raindrop’s standard naming pattern. The name from your manifest converts to uppercase with underscores replacing dashes.

For example, smartsql "main-db" becomes env.MAIN_DB. This single interface handles both direct SQL execution and natural language processing.

export default class extends Service<Env> {
  async fetch(request: Request): Promise<Response> {
    // Execute direct SQL query
    const sqlResult = await this.env.MAIN_DB.executeQuery({
      sqlQuery: 'SELECT * FROM users WHERE active = true',
      format: 'json'
    });


    // Execute natural language query
    const nlResult = await this.env.MAIN_DB.executeQuery({
      textQuery: 'Show me all active users from the last month',
      format: 'csv'
    });


    return new Response(JSON.stringify({ sqlResult, nlResult }));
  }
}

Core Concepts

Query Processing

SmartSQL handles two distinct input types through a unified interface. Direct SQL queries execute immediately against your database, while natural language queries convert to SQL using AI models before execution. The system validates generated SQL with safety tokens to prevent injection attacks while maintaining query flexibility.

Query results return immediately while background services handle PII detection and metadata updates. This keeps your applications responsive while building compliance documentation and improving AI query accuracy.

PII Detection Service

Automatic scanning analyzes all data modifications (INSERT, UPDATE operations) for personal information using Hugging Face models. Detection results are stored in a dedicated _pii system table with indexed lookups for efficient retrieval.

The service identifies multiple entity types including names, emails, phone numbers, and addresses. Each detection includes confidence scores and precise text positions for detailed compliance reporting and data governance workflows.

Metadata Management

Schema intelligence tracks your database structure in a _meta system table, storing column types, sample data, and relationships. This information improves AI query generation by providing context about your actual data model.

Metadata updates happen asynchronously through message queues. As your schema evolves, SmartSQL automatically adapts its understanding so AI-generated queries remain accurate and relevant to your current database structure.

Output Formats

Results support JSON format for structured data processing and CSV format for data export and analysis workflows. Both formats include the same result data with different serialization for integration with various downstream systems.

Query Operations

executeQuery()

Executes SQL queries or converts natural language to SQL with complete result information.

Parameters:

  • sqlQuery?: string - Direct SQL query to execute
  • textQuery?: string - Natural language query to convert
  • format?: 'json' | 'csv' - Output format (default: ‘json’)

Returns:

  • message: string - Operation status message
  • results?: string - Query results in specified format
  • status: number - HTTP status code
  • queryExecuted: string - SQL query that was executed
  • aiReasoning?: string - AI reasoning for natural language queries
const result = await this.env.MAIN_DB.executeQuery({
  sqlQuery: 'SELECT name, email FROM users WHERE created_at > ?',
  format: 'json'
});


console.log(`Query: ${result.queryExecuted}`);
console.log(`Status: ${result.status}`);
const users = JSON.parse(result.results || '[]');

getMetadata()

Retrieves database schema information for AI query context and application logic.

Parameters:

  • tableName?: string - Specific table to retrieve metadata for

Returns:

  • tables: TableMetadata[] - Array of table schema information
  • lastUpdated?: string - When metadata was last updated
interface TableMetadata {
  tableName: string;
  columns: Array<{
    columnName: string;
    dataType: string;
    sampleData?: string;
    nullable: boolean;
    isPrimaryKey: boolean;
  }>;
  createdAt?: string;
  updatedAt?: string;
}
const metadata = await this.env.MAIN_DB.getMetadata();


metadata.tables.forEach(table => {
  console.log(`Table: ${table.tableName}`);
  table.columns.forEach(column => {
    console.log(`  ${column.columnName}: ${column.dataType}`);
    if (column.sampleData) {
      console.log(`    Sample: ${column.sampleData}`);
    }
  });
});

updateMetadata()

Updates database schema metadata to improve AI query generation accuracy.

Parameters:

  • tables: TableMetadata[] - Array of table metadata to update
  • mode?: 'replace' | 'merge' | 'append' - Update strategy (default: ‘replace’)

Returns:

  • success: boolean - Whether the update succeeded
  • tablesUpdated: number - Number of tables updated
  • message: string - Status message
const updateResult = await this.env.MAIN_DB.updateMetadata([
  {
    tableName: 'products',
    columns: [
      {
        columnName: 'id',
        dataType: 'INTEGER',
        sampleData: '1001',
        nullable: false,
        isPrimaryKey: true
      },
      {
        columnName: 'name',
        dataType: 'TEXT',
        sampleData: 'Wireless Headphones',
        nullable: false,
        isPrimaryKey: false
      }
    ]
  }
], 'merge');


if (updateResult.success) {
  console.log(`Updated ${updateResult.tablesUpdated} tables`);
}
// Completely overwrites existing metadata
await this.env.MAIN_DB.updateMetadata(tables, 'replace');

getPiiData()

Retrieves PII detection results for compliance reporting and data governance.

Parameters:

  • tableName: string - Table name to retrieve PII data from
  • recordId?: string - Specific record identifier (row signature)

Returns:

  • piiDetections: PiiDetection[] - Array of PII detection results
interface PiiDetection {
  detectionId: string;
  tableName: string;
  recordId: string;
  entities: Array<{
    entityType: string;        // 'EMAIL', 'PERSON', 'PHONE', etc.
    confidenceScore: number;   // 0-1 confidence level
    detectedText: string;      // The actual PII text found
    startPosition: number;     // Start position in text
    endPosition: number;       // End position in text
    tokenIndex: number;        // Token position
  }>;
  detectedAt: string;          // When PII was detected
}
const allPii = await this.env.MAIN_DB.getPiiData('users');


allPii.piiDetections.forEach(detection => {
  console.log(`Record ${detection.recordId}:`);
  detection.entities.forEach(entity => {
    console.log(`  ${entity.entityType}: ${entity.detectedText} (${entity.confidenceScore})`);
  });
});

External API Access

SmartSQL provides Connect/gRPC endpoints for external clients with JWT authentication and request validation.

API Configuration

Protocol: Connect/gRPC over HTTP
Content-Type: application/connect+json
Authentication: Bearer token (JWT) with user and organization claims
Base URL: Your deployed SmartSQL service endpoint

Authentication Requirements

All API requests require valid JWT tokens with matching identity claims.

  • JWT Token - Valid token in Authorization header
  • User Validation - userId in request must match JWT claims
  • Organization Validation - organizationId in request must match JWT claims
  • Module Access - Permission to access the specified SmartSQL module

SmartSQL Location

API requests identify target modules using smartSqlLocation:

{
  "smartSqlLocation": {
    "moduleId": "your-module-id"
  }
}

API Endpoints

ExecuteQuery

Endpoint: smartsql.SmartSqlService/ExecuteQuery

Execute SQL or natural language queries through the external API.

{
  "smartSqlLocation": {
    "moduleId": "prod-database-module"
  },
  "sqlQuery": "SELECT * FROM orders WHERE status = 'pending'",
  "format": "JSON",
  "userId": "user_01ABC123DEF456",
  "organizationId": "org_01XYZ789GHI012"
}

GetMetadata

Endpoint: smartsql.SmartSqlService/GetMetadata

Retrieve database schema information through the API.

{
  "smartSqlLocation": {
    "moduleId": "prod-database-module"
  },
  "tableName": "products",
  "userId": "user_01ABC123DEF456",
  "organizationId": "org_01XYZ789GHI012"
}

UpdateMetadata

Endpoint: smartsql.SmartSqlService/UpdateMetadata

Update database schema metadata through the API.

{
  "smartSqlLocation": {
    "moduleId": "prod-database-module"
  },
  "tables": [
    {
      "tableName": "inventory",
      "columns": [
        {
          "columnName": "sku",
          "dataType": "TEXT",
          "sampleData": "WH-1000XM4",
          "nullable": false,
          "isPrimaryKey": true
        }
      ]
    }
  ],
  "userId": "user_01ABC123DEF456",
  "organizationId": "org_01XYZ789GHI012"
}

GetPiiData

Endpoint: smartsql.SmartSqlService/GetPiiData

Retrieve PII detection results through the API.

{
  "smartSqlLocation": {
    "moduleId": "prod-database-module"
  },
  "tableName": "customer_data",
  "recordId": "cust_456",
  "userId": "user_01ABC123DEF456",
  "organizationId": "org_01XYZ789GHI012"
}

JavaScript Client Example

class SmartSqlClient {
  constructor(baseUrl, token, userId, organizationId) {
    this.baseUrl = baseUrl;
    this.token = token;
    this.userId = userId;
    this.organizationId = organizationId;
  }


  async executeQuery(moduleId, options) {
    const response = await fetch(`${this.baseUrl}smartsql.SmartSqlService/ExecuteQuery`, {
      method: 'POST',
      headers: {
        'Content-Type': 'application/connect+json',
        'Authorization': `Bearer ${this.token}`
      },
      body: JSON.stringify({
        smartSqlLocation: { moduleId },
        sqlQuery: options.sqlQuery,
        textQuery: options.textQuery,
        format: options.format || 'JSON',
        userId: this.userId,
        organizationId: this.organizationId
      })
    });


    if (!response.ok) {
      throw new Error(`API request failed: ${response.status}`);
    }


    return await response.json();
  }


  async getMetadata(moduleId, tableName) {
    const response = await fetch(`${this.baseUrl}smartsql.SmartSqlService/GetMetadata`, {
      method: 'POST',
      headers: {
        'Content-Type': 'application/connect+json',
        'Authorization': `Bearer ${this.token}`
      },
      body: JSON.stringify({
        smartSqlLocation: { moduleId },
        tableName,
        userId: this.userId,
        organizationId: this.organizationId
      })
    });


    if (!response.ok) {
      throw new Error(`Metadata request failed: ${response.status}`);
    }


    return await response.json();
  }
}


// Usage
const client = new SmartSqlClient(
  'https://your-smartsql-endpoint.com/',
  'your-jwt-token',
  'user_01ABC123DEF456',
  'org_01XYZ789GHI012'
);


// Execute a natural language query
const result = await client.executeQuery('my-module-id', {
  textQuery: 'Show me top selling products this month',
  format: 'JSON'
});


console.log(`AI generated: ${result.queryExecuted}`);
console.log(`Results: ${result.results.jsonResults}`);

Error Handling

API responses use standard Connect/gRPC error codes:

  • INVALID_ARGUMENT: Missing or invalid request parameters
  • PERMISSION_DENIED: Authentication failed or insufficient permissions
  • INTERNAL: Server-side processing errors
  • UNAUTHENTICATED: Invalid or missing JWT token

All requests validate using Zod schemas with detailed error messages for validation failures.

System Tables

SmartSQL creates and manages two system tables for PII detection and metadata storage:

_pii Table

Stores PII detection results with indexed lookups for efficient compliance reporting:

CREATE TABLE _pii (
  id TEXT PRIMARY KEY,
  table_name TEXT NOT NULL,
  row_signature TEXT NOT NULL,
  pii_data TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_pii_table_signature ON _pii(table_name, row_signature);

_meta Table

Maintains database schema information for AI query generation:

CREATE TABLE _meta (
  id TEXT PRIMARY KEY,
  table_name TEXT NOT NULL,
  column_name TEXT NOT NULL,
  data_type TEXT NOT NULL,
  sample_data TEXT,
  UNIQUE(table_name, column_name)
);

Configuration

Required environment variables for AI services:

  • HF_API_TOKEN: Hugging Face API token for PII detection models
  • HF_ENDPOINT: Hugging Face model endpoint URL
  • SAMBA_KEY: SambaNova API key for natural language processing
  • RAINBOW_MGMT_API_TOKEN: Rainbow management API token

Code Examples

Complete Application Example

import { Service } from '@liquidmetal-ai/raindrop-framework';
import { Env } from './raindrop.gen';


export default class extends Service<Env> {
  async fetch(request: Request): Promise<Response> {
    const url = new URL(request.url);
    const path = url.pathname;


    try {
      switch (path) {
        case '/search':
          return this.handleSearch(request);
        case '/metadata':
          return this.handleMetadata();
        case '/pii-report':
          return this.handlePiiReport();
        default:
          return new Response('Not found', { status: 404 });
      }
    } catch (error) {
      return new Response(
        JSON.stringify({ error: error instanceof Error ? error.message : 'Unknown error' }),
        { status: 500, headers: { 'Content-Type': 'application/json' } }
      );
    }
  }


  private async handleSearch(request: Request): Promise<Response> {
    const { query, format = 'json' } = await request.json();


    // Try natural language first, fallback to direct SQL
    const result = await this.env.MAIN_DB.executeQuery({
      textQuery: query,
      format: format as 'json' | 'csv'
    });


    if (result.status !== 200) {
      // Fallback to treating as direct SQL
      const sqlResult = await this.env.MAIN_DB.executeQuery({
        sqlQuery: query,
        format: format as 'json' | 'csv'
      });


      return new Response(JSON.stringify(sqlResult), {
        headers: { 'Content-Type': 'application/json' }
      });
    }


    return new Response(JSON.stringify({
      ...result,
      wasNaturalLanguage: true
    }), {
      headers: { 'Content-Type': 'application/json' }
    });
  }


  private async handleMetadata(): Promise<Response> {
    const metadata = await this.env.MAIN_DB.getMetadata();


    // Enrich with table statistics
    const enrichedTables = await Promise.all(
      metadata.tables.map(async (table) => {
        const countResult = await this.env.MAIN_DB.executeQuery({
          sqlQuery: `SELECT COUNT(*) as row_count FROM ${table.tableName}`,
          format: 'json'
        });


        const rowCount = countResult.results ?
          JSON.parse(countResult.results)[0]?.row_count || 0 : 0;


        return {
          ...table,
          rowCount
        };
      })
    );


    return new Response(JSON.stringify({
      tables: enrichedTables,
      totalTables: enrichedTables.length,
      lastUpdated: metadata.lastUpdated
    }), {
      headers: { 'Content-Type': 'application/json' }
    });
  }


  private async handlePiiReport(): Promise<Response> {
    // Get all tables from metadata
    const metadata = await this.env.MAIN_DB.getMetadata();


    // Check each table for PII
    const piiReport = await Promise.all(
      metadata.tables.map(async (table) => {
        const piiData = await this.env.MAIN_DB.getPiiData(table.tableName);


        return {
          tableName: table.tableName,
          piiDetections: piiData.piiDetections.length,
          entities: piiData.piiDetections.flatMap(d =>
            d.entities.map(e => e.entityType)
          ).filter((v, i, a) => a.indexOf(v) === i) // unique
        };
      })
    );


    const totalDetections = piiReport.reduce((sum, table) =>
      sum + table.piiDetections, 0);


    return new Response(JSON.stringify({
      summary: {
        totalTables: piiReport.length,
        totalDetections,
        tablesWithPii: piiReport.filter(t => t.piiDetections > 0).length
      },
      details: piiReport
    }), {
      headers: { 'Content-Type': 'application/json' }
    });
  }
}

Metadata Health Check System

class MetadataHealthChecker {
  constructor(private smartSql: any) {}


  async performHealthCheck(tableName?: string): Promise<void> {
    const metadata = await this.smartSql.getMetadata(tableName);


    for (const table of metadata.tables) {
      const columnsWithoutSamples = table.columns.filter(col => !col.sampleData);


      if (columnsWithoutSamples.length > 0) {
        console.log(`Updating samples for ${table.tableName}`);
        await this.updateSampleData(table.tableName, columnsWithoutSamples);
      }
    }
  }


  private async updateSampleData(tableName: string, columns: any[]): Promise<void> {
    // Get sample data for columns
    const sampleQuery = `SELECT ${columns.map(c => c.columnName).join(', ')} FROM ${tableName} LIMIT 5`;
    const sampleResult = await this.smartSql.executeQuery({
      sqlQuery: sampleQuery,
      format: 'json'
    });


    if (sampleResult.status === 200 && sampleResult.results) {
      const samples = JSON.parse(sampleResult.results);
      if (samples.length > 0) {
        const firstRow = samples[0];


        // Update metadata with sample data
        const updatedColumns = columns.map(column => ({
          ...column,
          sampleData: String(firstRow[column.columnName] || '')
        }));


        await this.smartSql.updateMetadata([{
          tableName,
          columns: updatedColumns
        }], 'merge');
      }
    }
  }
}


// Usage in your service
const healthChecker = new MetadataHealthChecker(this.env.MAIN_DB);
await healthChecker.performHealthCheck(); // Check all tables
await healthChecker.performHealthCheck('users'); // Check specific table