---
title: "Schema Generation API"
description: "Generate a CREATE TABLE statement from JSON samples with the HeliosDB Lite REST API, what the inference does today, and which schema endpoints are planned."
---

# Schema Generation API

> Draft a `CREATE TABLE` statement from JSON samples, for AI agents and rapid prototyping

---

## Overview

The schema inference endpoints read JSON sample objects and return:
- the inferred columns, each with a SQL type and a nullable flag
- a `CREATE TABLE` statement you can review and run

The inference is a fast heuristic. Treat its DDL as a draft and review it before you run it (see [Current Behaviour](#current-behaviour)).

The endpoints are served by `heliosdb-lite start` under `/v1` (and `/api/v1`) on the HTTP port (default 8080). They require a JWT signed with `HELIOSDB_JWT_SECRET`; see [Authentication](https://www.heliosdb.com/docs/lite/website/getting-started/authentication/).

---

## Quick Start

### 1. Infer a Schema from JSON

```bash
curl -X POST http://127.0.0.1:8080/v1/schema/infer \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "samples": [
      {"id": 1, "name": "Widget", "price": 29.99, "in_stock": true, "tags": ["electronics", "gadget"]},
      {"id": 2, "name": "Gadget Pro", "price": 49.99, "in_stock": false, "tags": ["electronics", "premium"]}
    ],
    "table_name": "products"
  }'
```

### 2. Review the Draft DDL

The response is wrapped in `{"success": ..., "data": ...}`. Abbreviated:

```json
{
  "success": true,
  "data": {
    "table_name": "products",
    "columns": [
      {"name": "id", "sql_type": "INTEGER", "nullable": false},
      {"name": "name", "sql_type": "VARCHAR(12)", "nullable": false},
      {"name": "price", "sql_type": "NUMERIC", "nullable": false},
      {"name": "in_stock", "sql_type": "BOOLEAN", "nullable": false},
      {"name": "tags", "sql_type": "JSON", "nullable": false}
    ],
    "primary_key": ["id"],
    "ddl": "CREATE TABLE products (\n    id INTEGER NOT NULL,\n    ...\n    PRIMARY KEY (id)\n);",
    "confidence": 0.85,
    "warnings": ["Add 'id' column explicitly if needed for primary key"]
  }
}
```

Column order in the response is not guaranteed to follow the sample order.

### 3. Run the DDL

DDL and DML go to the `execute` endpoint of a branch:

```bash
curl -X POST http://127.0.0.1:8080/v1/branches/main/execute \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"sql": "CREATE TABLE products (id INTEGER NOT NULL, name VARCHAR(12) NOT NULL, price NUMERIC NOT NULL, in_stock BOOLEAN NOT NULL, tags JSON NOT NULL, PRIMARY KEY (id))"}'
```

---

## API Endpoints

### Infer Schema

```
POST /v1/schema/infer
```

**Request Body:**

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| `samples` | array | Yes | JSON objects; at least one is required |
| `table_name` | string | No | Table name (default `inferred_table`) |
| `options` | object | No | Inference options |

**Options that change the result:**

| Option | Type | Default | Effect |
|--------|------|---------|--------|
| `prefer_narrow_types` | boolean | true | Integers become `INTEGER` (true) or `BIGINT` (false) |
| `max_varchar_length` | number | 255 | Strings longer than this become `TEXT` |
| `detect_vectors` | boolean | true | Arrays of numbers become `VECTOR(n)` |
| `detect_json` | boolean | true | Objects become `JSONB` (true) or `JSON` (false) |
| `detect_unique` | boolean | true | Sets the `unique` flag on columns present in every sample |

The request also accepts `detect_nullable`, `detect_primary_key`, `detect_foreign_keys` and `suggest_indexes`. They do not change the result yet.

### Batch Inference

```
POST /v1/schema/infer/batch
```

Infer schemas for several tables in one call:

```json
{
  "tables": [
    {"name": "users", "samples": [{"id": 1, "name": "Alice", "email": "alice@example.com"}]},
    {"name": "orders", "samples": [{"id": 1, "user_id": 1, "total": 99.99}]}
  ]
}
```

The response has `schemas` (one entry per table) and `combined_ddl` (all `CREATE TABLE` statements). Batch DDL does not add `NOT NULL` or a primary key. The request also accepts `detect_relationships`. Relationship detection is planned; `relationships` is always empty today.

### Planned Schema Endpoints

These routes are mounted today but return placeholder results. Their full behaviour is planned:

| Endpoint | Planned behaviour |
|----------|-------------------|
| `POST /v1/schema/infer/file` | Infer a schema from an uploaded file |
| `POST /v1/schema/validate` | Validate DDL and suggest fixes |
| `POST /v1/schema/compare` | Diff two schemas and generate migrations |
| `POST /v1/schema/optimize` | Suggest schema optimizations |
| `POST /v1/schema/generate` | Generate a schema from a natural-language description |
| `GET /v1/schema/templates`, `POST /v1/schema/templates/instantiate` | Schema templates |

---

## Current Behaviour

| JSON value | SQL type |
|------------|----------|
| `true` / `false` | `BOOLEAN` |
| Integer | `INTEGER` (or `BIGINT` with `prefer_narrow_types: false`) |
| Decimal | `NUMERIC` |
| String up to `max_varchar_length` | `VARCHAR(n)`, with n set to twice the sample length and capped at `max_varchar_length` |
| Longer string | `TEXT` |
| Array of numbers | `VECTOR(n)` |
| Other array | `JSON` |
| Object | `JSONB` |
| `null` | Marks the column nullable in `columns` |

Review these points before you run the DDL:

- When samples disagree, the column takes the type of the first non-null sample value. Pass representative samples.
- Date and timestamp strings are typed as `VARCHAR`. Change them to `DATE` or `TIMESTAMP` yourself.
- The single-table DDL marks every column `NOT NULL`, including columns flagged nullable, and always adds `PRIMARY KEY (id)`. Edit the statement if your data has no `id` column or has optional fields.
- `confidence` is a fixed 0.85, and `indexes` and `constraints` are empty.

---

## For AI Agents

An agent can infer a draft, review it, and run it on a branch so that a bad guess never touches `main`:

```python
import requests

BASE = "http://127.0.0.1:8080/v1"
HEADERS = {"Authorization": f"Bearer {TOKEN}"}

def create_table_from_samples(samples, table_name, branch="main"):
    schema = requests.post(f"{BASE}/schema/infer", headers=HEADERS,
                           json={"samples": samples, "table_name": table_name}).json()["data"]
    ddl = schema["ddl"]
    # Review step: adjust NOT NULL, the primary key and date columns before running
    requests.post(f"{BASE}/branches/{branch}/execute", headers=HEADERS,
                  json={"sql": ddl}).raise_for_status()
    return schema
```

---

## Error Handling

Errors use the REST error format: an HTTP status plus `{"error": "<kind>", "message": "<text>"}`.

| Status | `error` | Cause |
|--------|---------|-------|
| 400 | `BadRequest` | `samples` is empty (`"At least one sample is required"`) |
| 401 | `Unauthorized` | Missing, expired or invalid token |

---

## Next Steps

- [REST API Reference](https://www.heliosdb.com/docs/lite/website/api/readme/)
- [Vector Search](https://www.heliosdb.com/docs/lite/website/guides/vector-search/)

---

*Questions? [API Reference](https://www.heliosdb.com/docs/lite/website/api/readme/) | [Join Discord](https://discord.gg/yTykuUrFXc)*