npm package: @rip-lang/data
Description: DuckDB SQL Server — Simple HTTP API for DuckDB queries
Author: Steve Shreeve
Latest version: 0.5.0

<img src="https://raw.githubusercontent.com/shreeve/rip-lang/main/docs/rip.svg" style="width:50px" /> <br>

# Rip Data - @rip-lang/data
A simple HTTP server for DuckDB queries. Run any SQL against a DuckDB database via REST API.

Uses custom Zig-based DuckDB bindings (`lib/`) for fast, type-safe database access.

## Quick Start

```bash
# Install globally
bun add -g @rip-lang/data

# Run with in-memory database
rip-data

# Run with file-based database
rip-data mydb.duckdb

# Specify port
rip-data mydb.duckdb --port=8080
```

Or run directly with Rip:

```bash
rip data.rip :memory: --port=4000
```

## API Endpoints

### POST /sql

Execute any SQL statement (queries or mutations).

**Request:**
```json
{
  "sql": "SELECT * FROM users WHERE id = ?",
  "params": [1]
}
```

**Response (JSON-Compact format):**
```json
{
  "ok": true,
  "columns": ["id", "name", "email"],
  "types": ["INTEGER", "VARCHAR", "VARCHAR"],
  "rows": [
    [1, "Alice", "alice@example.com"]
  ],
  "count": 1,
  "time": 0.001
}
```

### GET /health

Simple health check (no database query).

```json
{ "ok": true }
```

### GET /status

Database info including table list.

```json
{
  "ok": true,
  "database": "mydb.duckdb",
  "tables": ["users", "orders", "products"],
  "time": "2026-02-02T14:30:00.000Z"
}
```

### GET /tables

List all tables in the database.

```json
{
  "ok": true,
  "tables": ["users", "orders", "products"]
}
```

### GET /schema/:table

Get schema for a specific table.

```json
{
  "ok": true,
  "table": "users",
  "columns": [
    { "column_name": "id", "data_type": "INTEGER", "is_nullable": "NO" },
    { "column_name": "name", "data_type": "VARCHAR", "is_nullable": "YES" },
    { "column_name": "email", "data_type": "VARCHAR", "is_nullable": "YES" }
  ]
}
```

## Examples

### Create a table

```bash
curl -X POST http://localhost:4000/sql \
  -H "Content-Type: application/json" \
  -d '{"sql": "CREATE TABLE users (id INTEGER PRIMARY KEY, name VARCHAR, email VARCHAR)"}'
```

### Insert data

```bash
curl -X POST http://localhost:4000/sql \
  -H "Content-Type: application/json" \
  -d '{"sql": "INSERT INTO users VALUES (?, ?, ?)", "params": [1, "Alice", "alice@example.com"]}'
```

### Query with parameters

```bash
curl -X POST http://localhost:4000/sql \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT * FROM users WHERE name LIKE ?", "params": ["%Ali%"]}'
```

### Aggregations

```bash
curl -X POST http://localhost:4000/sql \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT COUNT(*) as total, AVG(age) as avg_age FROM users"}'
```

## Response Format

All responses use a consistent JSON-Compact format:

| Field | Type | Description |
|-------|------|-------------|
| `ok` | boolean | Whether the request succeeded |
| `columns` | string[] | Column names (for queries) |
| `types` | string[] | Column types (INTEGER, VARCHAR, etc.) |
| `rows` | any[][] | Row data as arrays (not objects) |
| `count` | number | Number of rows returned |
| `time` | number | Execution time in seconds |
| `error` | string | Error message (when `ok: false`) |

The JSON-Compact format uses arrays instead of objects for row data, reducing payload size by ~40% compared to traditional JSON row-object format.

## Performance

Our custom Zig-based DuckDB bindings deliver exceptional performance:

| Operation | Latency | Throughput |
|-----------|---------|------------|
| Point lookup (WHERE id=?) | 0.09ms | 11,000 qps |
| Range scan (LIMIT 100) | 0.20ms | 5,000 qps |
| Aggregation (COUNT/AVG/MAX) | 0.12ms | 8,400 qps |
| JOIN + GROUP BY | 0.25ms | 4,000 qps |
| INSERT (single row) | 0.13ms | 7,700 qps |

### Comparison to MySQL/PostgreSQL

| Operation | Our DuckDB | MySQL/PG (localhost) | Speedup |
|-----------|------------|----------------------|---------|
| Point lookup | **0.09ms** | 0.3-1ms | 3-10x faster |
| Range scan | **0.20ms** | 1-5ms | 5-25x faster |
| Aggregation | **0.12ms** | 2-20ms | 17-170x faster |
| JOIN + GROUP BY | **0.25ms** | 5-50ms | 20-200x faster |
| INSERT | **0.13ms** | 0.5-2ms | 4-15x faster |

**Why so fast?**
- **Zero network latency** — DuckDB runs in-process
- **No connection overhead** — No auth, handshake, or protocol parsing
- **Columnar engine** — DuckDB is optimized for analytical queries
- **Direct FFI** — Zig bindings call DuckDB's C API directly

## Concurrency

DuckDB handles concurrent reads efficiently using MVCC. This server creates a new connection per request, allowing multiple simultaneous queries. Writes are serialized internally by DuckDB.

## Requirements

- Bun 1.0+
- rip-lang 2.0+
- @rip-lang/api 0.5+

## Building from Source

To rebuild the native DuckDB bindings (requires Zig 0.15+ and DuckDB):

```bash
./src/build.sh
```

This creates `lib/{platform}-{arch}/duckdb.node` for your platform.

## License

MIT