Sql Connections

3. Explore Schema

-- PostgreSQL
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'users';

-- MySQL
SHOW TABLES;
DESCRIBE users;

Security

• Connections are pre-configured by user (credentials never exposed)
• Queries run through secure proxy
• Read-only access recommended for analytics

Common Patterns

Get table structure

SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'orders'
ORDER BY ordinal_position;

Sample data

SELECT * FROM orders ORDER BY created_at DESC LIMIT 5;

Aggregations

SELECT DATE(created_at) as date, COUNT(*) as count, SUM(total) as revenue
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY DATE(created_at)
ORDER BY date;

Querying from Web Applications

When building web apps that query external databases via the Spikefrost SQL API, use this pattern:

export async function queryDatabase(env: Env, alias: string, sql: string): Promise<any[]> {
  const response = await fetch('https://mcp.spikefrost.com/api/v1/execute', {
    method: 'POST',
    headers: {
      'x-api-key': env.SPIKE_TENANT_API_KEY,
      'Content-Type': 'application/json',
    },
    body: JSON.stringify({
      provider: 'sql',
      capability: 'sql_query',
      params: {
        alias: alias,
        query: sql,
      },
    }),
  });

  if (!response.ok) {
    console.error(`SQL query failed: HTTP ${response.status}`);
    return [];
  }

  const data = await response.json();

  // ⛔ CRITICAL: Check for 'status: completed' - NOT 'success: true'
  if (data.status === 'completed' && data.result?.rows) {
    return data.result.rows;
  }

  if (data.error || data.status === 'failed') {
    console.error('SQL query error:', data.error);
  }

  return [];
}

API Response Format

The Spikefrost SQL API returns responses in this format:

{
  "jobId": "...",
  "status": "completed",
  "result": {
    "rows": [...],
    "row_count": 1,
    "truncated": false,
    "execution_time_ms": 148,
    "connection": { "alias": "mydb", "name": "mydb", "db_type": "postgresql", "database": "mydb" }
  }
}

⛔ CRITICAL: Always check data.status === 'completed' to verify success. The API does NOT use data.success. Using data.success will cause queries to return empty results even when data exists.

Integration with Other Skills

• data-analytics: Use SQL connections in scheduled workflows
• chart-generation: Visualize query results
• website-builder: Build dashboards that query external data