MicroSQL Authoring Guide

MicroSQL is a restricted SQL dialect for querying print domain reporting tables. Tenant isolation is enforced automatically — you never write WHERE tenant_id = '...'.

Quick Start

Discover the schema — call describe_sql_schema to see all tables, columns, and descriptions
Write a query — use standard SELECT syntax (see below)
Validate — call validate_sql to check syntax and see inferred column types
Execute — call execute_sql to run the query and get tabular results
Save as Data Clip — use the upsertDataClip GraphQL mutation to persist the query

MicroSQL Syntax

SELECT [columns | aggregates]
FROM table
[JOIN table ON condition]
[WHERE conditions]
[GROUP BY columns]
[ORDER BY column ASC|DESC, ...]
[LIMIT n]

Supported Features

SELECT: Column references, which MUST be qualified as table.column — a bare column is rejected with "Invalid SELECT column: ", even in a single-table query. Also aggregate functions and aliases (AS name). Qualification applies in WHERE, GROUP BY and ORDER BY too.
FROM: Single table or multiple tables via JOIN
JOIN: JOIN table ON left.col = right.col (INNER JOIN)
WHERE: Comparisons (=, !=, <, >, <=, >=), AND, OR, NOT, IS NULL, IS NOT NULL, LIKE, IN (...)
GROUP BY: Group results by one or more columns
ORDER BY: Sort by columns; each target requires explicit ASC or DESC
LIMIT: Cap the number of returned rows (server enforces max 1000; results report truncation and next OFFSET)

Aggregate Functions

COUNT(column) or COUNT(*) — count rows
SUM(column) — sum numeric values
AVG(column) — average numeric values
MIN(column) — minimum value
MAX(column) — maximum value

Not Supported

Subqueries
UNION / INTERSECT / EXCEPT
Window functions (OVER, PARTITION BY)
INSERT / UPDATE / DELETE (read-only)
Raw SQL functions (COALESCE, CAST, etc.)
CTEs (WITH ... AS)

Data Clips

Data clips are saved MicroSQL queries with a persistent share token for public CSV/JSON export.

List Data Clips

query {
  listDataClips(first: 20) {
    edges {
      node {
        id
        name
        description
        queryString
        shareToken
        createdAt
        updatedAt
      }
    }
    totalCount
  }
}

Get a Data Clip

query GetClip($id: DataClipID!) {
  getDataClip(id: $id) {
    id name description queryString shareToken
    creatorId createdAt updatedAt
  }
}

Create or Update a Data Clip

mutation UpsertClip($input: UpsertDataClipInput!) {
  upsertDataClip(input: $input) {
    dataClip {
      id name description queryString shareToken
    }
    errors { path message }
  }
}

Variables (create):

{
  "input": {
    "name": "Completed Jobs This Month",
    "description": "All jobs with status COMPLETED, ordered by completion date",
    "queryString": "SELECT jobs.name, jobs.quantity, jobs.actual_end_at FROM jobs WHERE jobs.status = 'COMPLETED' ORDER BY jobs.actual_end_at DESC LIMIT 500"
  }
}

Variables (update — include id):

{
  "input": {
    "id": "dcl_...",
    "name": "Updated Name",
    "queryString": "SELECT jobs.name, jobs.quantity FROM jobs LIMIT 100"
  }
}

Preview a Query (Without Saving)

query Preview($input: PreviewDataClipInput!) {
  previewDataClip(input: $input) {
    headers
    rows
    rowCount
    error
  }
}

Variables:

{ "input": { "query": "SELECT jobs.name, jobs.status FROM jobs LIMIT 5" } }

Delete a Data Clip

mutation DeleteClip($id: DataClipID!) {
  deleteDataClip(id: $id) {
    success
    errors { path message }
  }
}

Invalidates old share links and generates a new token:

mutation RollToken($input: RollShareTokenInput!) {
  rollDataClipShareToken(input: $input) {
    dataClip { id shareToken }
    errors { path message }
  }
}

Variables:

{ "input": { "id": "dcl_..." } }

Data clips can be exported publicly via share token — no authentication required:

GET /api/v1/clips/{shareToken}.csv
GET /api/v1/clips/{shareToken}.json
GET /api/v1/clips/{shareToken}.ndjson

The share token is generated when a data clip is created. Use rollDataClipShareToken to regenerate it and invalidate old links.

Lua Integration

In custom app server scripts (Lua), use ctx.sql.query():

local ok, result = ctx.sql.query([[
  SELECT jobs.name, jobs.quantity, jobs.status
  FROM jobs
  WHERE jobs.status = 'READY'
  ORDER BY jobs.due_at ASC
  LIMIT 50
]])
if not ok then error(result) end

-- result.headers = {"name", "quantity", "status"}
-- result.rows = {{...}, {...}, ...}
-- result.rowCount = 50

ML Integration

ML model configs accept MicroSQL via the dataQuerySQL field as an alternative to Lua data scripts. The SQL query provides the training data directly.

Worked Examples

Example 1: Simple Status Report

SELECT jobs.status, COUNT(*) AS job_count
FROM jobs
GROUP BY jobs.status
ORDER BY job_count DESC

Example 2: Jobs with Component Details

SELECT jobs.name, jobs.status,
       components.name AS component_name,
       components.kind, components.quantity
FROM jobs
JOIN components ON components.job_id = jobs.id
WHERE jobs.status = 'READY'
ORDER BY jobs.due_at ASC
LIMIT 100

Example 3: Overdue Jobs

SELECT jobs.name, jobs.job_number, jobs.due_at, jobs.status
FROM jobs
WHERE jobs.status != 'COMPLETED'
  AND jobs.status != 'CANCELLED'
  AND jobs.due_at < '2026-02-22'
ORDER BY jobs.due_at ASC

Example 4: Component Mix by Kind

SELECT components.kind,
       COUNT(*) AS count,
       AVG(components.quantity) AS avg_quantity
FROM components
GROUP BY components.kind
ORDER BY count DESC

Example 5: Full Data Clip Workflow

Discover the schema — what tables and columns exist
Draft the query and check it parses
Preview the results before saving anything
Save as clip with the upsertDataClip mutation
Share the public URL: /api/v1/clips/{shareToken}.csv