SmartSQL

This content is for the 0.6.3 version. Switch to the latest version for up-to-date documentation.

SmartSQL changes how you interact with databases. It runs regular SQL queries and converts plain English into SQL. It finds sensitive data automatically and tracks your database structure for better AI queries.

This component uses SQLite as the underlying database system. You can execute standard SQL queries or use natural language that gets translated by AI. The AI translation takes longer than direct SQL but makes database interactions more accessible.

SmartSQL automatically detects personally identifiable information (PII) in your data. It tracks database metadata to improve AI query translation over time. This makes it perfect for applications that need intelligent data access with privacy awareness.

Creating

Configure SmartSQL in your raindrop.manifest file:

application "demo-app" {
  smartsql "ssql-demo" {}
}

Accessing

Access your SmartSQL database through environment bindings:

// Execute a SQL query
const result = await env.SSQL_DEMO.executeQuery({
  sqlQuery: "SELECT * FROM users WHERE age > 21"
});

Database Setup and Seeding

SmartSQL requires direct SQL queries for table creation and data seeding. Unlike traditional database migrations, you execute these queries directly through the executeQuery interface.

Creating Tables

Use CREATE IF NOT EXISTS statements to ensure idempotent table creation:

// Create users table with idempotent statement
await env.SSQL_DEMO.executeQuery({
  sqlQuery: `
    CREATE TABLE IF NOT EXISTS users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      email TEXT UNIQUE NOT NULL,
      name TEXT NOT NULL,
      age INTEGER,
      status TEXT DEFAULT 'active',
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )
  `
});

// Create products table
await env.SSQL_DEMO.executeQuery({
  sqlQuery: `
    CREATE TABLE IF NOT EXISTS products (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      price DECIMAL(10, 2) NOT NULL,
      category TEXT,
      stock_quantity INTEGER DEFAULT 0,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )
  `
});

Seeding Data

Use idempotent seeding patterns to avoid duplicate data:

// Seed initial users with conflict resolution
await env.SSQL_DEMO.executeQuery({
  sqlQuery: `
    INSERT OR IGNORE INTO users (email, name, age, status)
    VALUES
      ('admin@example.com', 'Admin User', 35, 'active'),
      ('john@example.com', 'John Doe', 28, 'active'),
      ('jane@example.com', 'Jane Smith', 32, 'active')
  `
});

// Alternative: Use INSERT OR REPLACE for updates
await env.SSQL_DEMO.executeQuery({
  sqlQuery: `
    INSERT OR REPLACE INTO products (id, name, price, category)
    VALUES
      (1, 'Laptop', 999.99, 'Electronics'),
      (2, 'Coffee Maker', 79.99, 'Appliances'),
      (3, 'Desk Chair', 249.99, 'Furniture')
  `
});

SmartSQL uses SQLite syntax for all operations. Remember to use idempotent patterns (CREATE IF NOT EXISTS, INSERT OR IGNORE) to ensure your setup scripts can run multiple times safely.

Core Concepts

SmartSQL provides these main interfaces:

executeQuery - Run SQL queries or natural language queries
getMetadata - Retrieve database schema information
updateMetadata - Update database schema metadata
getPiiData - Get PII detection results for specific tables

QueryOptions

Configuration for database queries:

interface QueryOptions {
  textQuery?: string;    // Natural language query (AI translated)
  sqlQuery?: string;     // Direct SQL query
  format?: 'json' | 'csv'; // Response format
}

TableMetadata

Database schema information structure:

interface TableMetadata {
  tableName: string;     // Name of the database table
  columns: Array<{
    columnName: string;  // Column identifier
    dataType: string;    // SQL data type
    sampleData?: string; // Example value for AI context
    nullable: boolean;   // Whether column allows NULL
    isPrimaryKey: boolean; // Primary key indicator
  }>;
  createdAt?: string;    // Table creation timestamp
  updatedAt?: string;    // Last modification timestamp
}

PiiDetection

PII detection results for sensitive data:

interface PiiDetection {
  detectionId: string;   // Unique detection identifier
  tableName: string;     // Source table name
  recordId: string;      // Specific record identifier
  entities: Array<{
    entityType: string;  // PII type (EMAIL, CREDITCARDNUMBER, etc.)
    confidenceScore: number; // Detection confidence (0-1)
    detectedText: string;    // Actual sensitive text found
    startPosition: number;   // Text start position
    endPosition: number;     // Text end position
    tokenIndex: number;      // Token position in text
  }>;
  detectedAt: string;    // Detection timestamp
}

{
  textQuery?: string;      // "Find all users older than 25"
  sqlQuery?: string;       // "SELECT * FROM users WHERE age > 25"
  format?: 'json' | 'csv'  // Response format preference
}

{
  message: string;         // Operation status message
  results?: string;        // Query results data
  status: number;          // HTTP-style status code
  queryExecuted: string;   // Actual SQL query that ran
  aiReasoning?: string;    // AI translation explanation
}

Example

Execute a natural language query to find active users:

const result = await env.SSQL_DEMO.executeQuery({
  textQuery: "Show me all active users from the last month",
  format: "json"
});

// AI translates to SQL and executes
console.log(result.queryExecuted); // "SELECT * FROM users WHERE status = 'active' AND created_at > ..."
console.log(result.results);       // JSON string with user data

getMetadata

Input
Output

string?  // Optional table name filter

{
  tables: Array<{
    tableName: string;
    columns: Array<{
      columnName: string;
      dataType: string;
      sampleData?: string;
      nullable: boolean;
      isPrimaryKey: boolean;
    }>;
    createdAt?: string;
    updatedAt?: string;
  }>;
  lastUpdated?: string;
}

Example

Retrieve schema information for the users table:

const metadata = await env.SSQL_DEMO.getMetadata("users");

// Returns complete schema information
console.log(metadata.tables[0].columns);
// Shows column names, types, and sample data for AI context

updateMetadata

Input
Output

{
  tables: Array<{
    tableName: string;
    columns: Array<{
      columnName: string;
      dataType: string;
      sampleData?: string;
      nullable: boolean;
      isPrimaryKey: boolean;
    }>;
  }>;
  mode?: 'replace' | 'merge' | 'append'  // Default: 'replace'
}

{
  success: boolean;        // Update operation success
  tablesUpdated: number;   // Number of tables modified
  message: string;         // Operation result message
}

Example

Update metadata for better AI query translation:

const result = await env.SSQL_DEMO.updateMetadata([{
  tableName: "products",
  columns: [{
    columnName: "price",
    dataType: "DECIMAL",
    sampleData: "29.99",  // Helps AI understand price format
    nullable: false,
    isPrimaryKey: false
  }]
}], "merge");

// Merges new metadata with existing information
console.log(result.tablesUpdated); // 1

getPiiData

Input
Output

{
  tableName: string;   // Target table to analyze
  recordId?: string;   // Optional specific record filter
}

{
  piiDetections: Array<{
    detectionId: string;
    tableName: string;
    recordId: string;
    entities: Array<{
      entityType: string;
      confidenceScore: number;
      detectedText: string;
      startPosition: number;
      endPosition: number;
      tokenIndex: number;
    }>;
    detectedAt: string;
  }>;
}

Example

Detect PII in customer data for compliance:

const piiData = await env.SSQL_DEMO.getPiiData("customers");

// Returns detected sensitive information
piiData.piiDetections.forEach(detection => {
  detection.entities.forEach(entity => {
    console.log(`Found ${entity.entityType}: ${entity.detectedText}`);
    // Might show: "Found EMAIL: john@example.com"
  });
});