sql-playground

# sql-playground Skill

## Overview

sql-playground lets users upload SQLite database files and run SQL queries against them through a Monaco editor in the browser. The server maintains an in-memory connection pool for open databases. All queries are executed synchronously via `better-sqlite3`. SELECT queries return paginated rows capped at the configured row limit. Mutation queries (INSERT/UPDATE/DELETE/DDL) return affected row counts. A hard 30-second timeout kills runaway queries.

## TypeScript Interfaces

```typescript
// App metadata schema types
interface DbRecord {
  id: number;
  filename: string;         // original filename e.g. northwind.sqlite
  stored_name: string;      // nanoid(12) storage filename e.g. ax9bKmPqRtUv.db
  size_bytes: number;
  table_count: number;
  uploaded_at: string;      // ISO 8601
}

interface QueryHistoryEntry {
  id: number;
  db_id: number;
  sql: string;
  query_type: 'select' | 'mutation' | 'error';
  row_count: number | null;
  changes: number | null;
  duration_ms: number;
  error_message: string | null;
  executed_at: string;
}

interface SavedQuery {
  id: number;
  db_id: number;
  name: string;
  sql: string;
  created_at: string;
  updated_at: string;
}

interface AppSettings {
  row_cap: number;          // default 1000
  timeout_seconds: number;  // default 30
  allow_mutations: number;  // 0 or 1
  idle_timeout_seconds: number; // default 300
  max_upload_mb: number;    // default 100
  editor_font_size: number; // default 13
  word_wrap: number;        // 0 or 1
  autocomplete: number;     // 0 or 1
  history_limit: number;    // default 200
}

// Query execution types
interface QueryRequest {
  sql: string;
}

interface SelectResult {
  type: 'select';
  columns: string[];
  rows: unknown[][];
  total_rows: number;       // total matched (may exceed cap)
  capped: boolean;
  row_cap: number;
  duration_ms: number;
}

interface MutationResult {
  type: 'mutation';
  changes: number;
  last_insert_rowid: number;
  duration_ms: number;
}

interface ErrorResult {
  type: 'error';
  error: string;
  duration_ms: number;
}

// Schema introspection
interface ColumnInfo {
  cid: number;
  name: string;
  type: string;
  notnull: number;
  dflt_value: string | null;
  pk: number;
}

interface IndexInfo {
  name: string;
  unique: number;
  origin: string;           // 'pk' | 'u' | 'c'
  columns: string[];
}

interface TableSchema {
  name: string;
  row_count: number;
  columns: ColumnInfo[];
  indexes: IndexInfo[];
  create_sql: string;
}
```

## API Endpoints

### Databases

**List all uploaded databases**
```bash
curl http://localhost:3000/api/databases
```
Response: `{ databases: DbRecord[] }`

**Upload a database**
```bash
curl -X POST http://localhost:3000/api/databases \
  -F "file=@northwind.sqlite"
```
Response: `{ database: DbRecord }`

Validation steps:
1. Check file extension (.sqlite, .sqlite3, .db, .s3db, .sl3)
2. Check file size against `max_upload_mb` setting
3. Validate SQLite magic bytes: `53 51 4C 69 74 65 20 66 6F 72 6D 61 74 20 33 00` (first 16 bytes)
4. Open with `better-sqlite3` in read-only mode to count tables
5. Store file with `nanoid(12)` filename, insert metadata row

**Get database metadata**
```bash
curl http://localhost:3000/api/databases/3
```
Response: `{ database: DbRecord }`

**Delete a database**
```bash
curl -X DELETE http://localhost:3000/api/databases/3
```
Closes pool connection, deletes file, deletes metadata row, preserves history/saved queries.

### Query Execution

**Execute SQL**
```bash
curl -X POST http://localhost:3000/api/databases/3/query \
  -H "Content-Type: application/json" \
  -d '{"sql":"SELECT * FROM Customers LIMIT 5"}'
```
SELECT response:
```json
{
  "type": "select",
  "columns": ["CustomerID", "CompanyName", "Country"],
  "rows": [["ALFKI", "Alfreds Futterkiste", "Germany"]],
  "total_rows": 91,
  "capped": false,
  "row_cap": 1000,
  "duration_ms": 12
}
```

Mutation response:
```json
{
  "type": "mutation",
  "changes": 8,
  "last_insert_rowid": 0,
  "duration_ms": 7
}
```

Error response:
```json
{
  "type": "error",
  "error": "no such table: Custommers",
  "duration_ms": 2
}
```

Query type detection (strips `--` line comments and `/* */` block comments, then checks first token):
- SELECT, WITH, EXPLAIN, PRAGMA (read) -> `select`
- INSERT, UPDATE, DELETE, DROP, CREATE, ALTER, ATTACH -> `mutation`

Hard timeout implementation:
```typescript
const timer = setTimeout(() => closeConnection(dbId), timeout_ms);
try {
  const result = executeQuery(db, sql);
  clearTimeout(timer);
  return result;
} catch (err) {
  clearTimeout(timer);
  throw err;
}
```

**Export all rows as CSV** (bypasses row cap)
```bash
curl "http://localhost:3000/api/databases/3/export?sql=SELECT%20*%20FROM%20Orders" \
  -o orders.csv
```
Response: `Content-Type: text/csv`, `Content-Disposition: attachment; filename="export.csv"`

Streams results using `node:stream` to avoid buffering all rows in memory.

### Schema Introspection

**Get full schema for a database**
```bash
curl http://localhost:3000/api/databases/3/schema
```
Response: `{ tables: TableSchema[] }`

**Get schema for one table**
```bash
curl http://localhost:3000/api/databases/3/schema/Orders
```
Response: `{ table: TableSchema }`

Introspection queries:
```sql
-- Table list with row counts
SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;
SELECT COUNT(*) FROM "${tableName}";

-- Column info
PRAGMA table_info("${tableName}");

-- Index list
PRAGMA index_list("${tableName}");

-- Index columns
PRAGMA index_info("${indexName}");

-- DDL
SELECT sql FROM sqlite_master WHERE type='table' AND name=?;
```

### Query History

**List history for a database**
```bash
curl "http://localhost:3000/api/databases/3/history?limit=50&offset=0"
```
Response: `{ history: QueryHistoryEntry[], total: number }`

History is written as fire-and-forget (non-blocking) after each query execution. Entries exceeding `history_limit` are pruned with `DELETE FROM query_history WHERE id IN (SELECT id FROM query_history ORDER BY id ASC LIMIT ?)`.

**Delete all history for a database**
```bash
curl -X DELETE http://localhost:3000/api/databases/3/history
```

### Saved Queries

**List saved queries**
```bash
curl http://localhost:3000/api/saved-queries
curl "http://localhost:3000/api/saved-queries?db_id=3"
```
Response: `{ queries: SavedQuery[] }`

**Create saved query**
```bash
curl -X POST http://localhost:3000/api/saved-queries \
  -H "Content-Type: application/json" \
  -d '{"db_id":3,"name":"Top customers","sql":"SELECT..."}'
```
Response: `{ query: SavedQuery }`

**Update saved query**
```bash
curl -X PATCH http://localhost:3000/api/saved-queries/7 \
  -H "Content-Type: application/json" \
  -d '{"name":"Top 20 customers","sql":"SELECT..."}'
```

**Delete saved query**
```bash
curl -X DELETE http://localhost:3000/api/saved-queries/7
```

### Settings

**Get settings**
```bash
curl http://localhost:3000/api/settings
```
Response: `{ settings: AppSettings }`

**Update settings**
```bash
curl -X PATCH http://localhost:3000/api/settings \
  -H "Content-Type: application/json" \
  -d '{"row_cap":500,"timeout_seconds":60}'
```
Response: `{ settings: AppSettings }`

Only provided keys are updated (partial update). Zod validates ranges:
- `row_cap`: 100-50000
- `timeout_seconds`: 5-120
- `idle_timeout_seconds`: 60-3600
- `max_upload_mb`: 1-2048
- `editor_font_size`: 10-24
- `history_limit`: 10-1000

## Connection Pool

```typescript
// lib/connections.ts
const pool = new Map<number, Database>();

export function getConnection(dbId: number, storedPath: string): Database {
  if (!pool.has(dbId)) {
    const db = new Database(storedPath, { readonly: false });
    db.pragma('journal_mode = WAL');
    pool.set(dbId, db);
    scheduleIdleClose(dbId);
  }
  resetIdleTimer(dbId);
  return pool.get(dbId)!;
}

export function closeConnection(dbId: number): void {
  const db = pool.get(dbId);
  if (db) {
    try { db.close(); } catch (_) {}
    pool.delete(dbId);
    clearIdleTimer(dbId);
  }
}
```

## Error Codes

| HTTP | Code | Meaning |
|------|------|---------|
| 400 | INVALID_SQL | SQL is empty or fails basic parse |
| 400 | MUTATIONS_DISABLED | Mutation attempted when allow_mutations=0 |
| 404 | DB_NOT_FOUND | No database with that id |
| 404 | TABLE_NOT_FOUND | No table with that name |
| 413 | FILE_TOO_LARGE | Upload exceeds max_upload_mb |
| 422 | INVALID_FILE | Bad extension or magic bytes |
| 422 | VALIDATION_ERROR | Request body fails Zod schema |
| 408 | QUERY_TIMEOUT | Query killed by 30s timeout |
| 500 | INTERNAL_ERROR | Unexpected server error |

## Monaco Autocomplete Provider

Registered via `monaco.languages.registerCompletionItemProvider('sql', provider)`:

1. On database open, fetch `/api/databases/:id/schema`
2. Store table names and column names per table in a ref
3. The provider inspects the token before the cursor:
   - After `FROM`, `JOIN`, `UPDATE`, `INTO` -> suggest table names
   - After `<table>.` -> suggest columns of that table
   - Anywhere else -> suggest SQL keywords + all table names + all column names
4. Completion items include `detail` (type affinity) and `documentation` (nullable, default value)

## Environment Variables

| Variable | Default | Description |
|----------|---------|-------------|
| `PORT` | `3000` | HTTP server port |
| `DATA_DIR` | `./data` | Directory for app.db and uploaded files |
| `NODE_ENV` | `development` | `development` or `production` |

## Docker

```yaml
# docker-compose.yml
services:
  app:
    build: .
    ports:
      - "3000:3000"
    volumes:
      - sql_data:/app/data
    environment:
      NODE_ENV: production
      DATA_DIR: /app/data

volumes:
  sql_data:
```

Multi-stage Dockerfile: stage 1 installs all deps and builds client (`pnpm build`), stage 2 copies `dist/`, `server/`, `node_modules/` (prod only), sets `CMD ["node", "server/index.js"]`.