# DSQL Editor
URL: /docs/web/docyrus/dsql-editor

A two-pane workbench for writing and running DSQL (security-scoped SQL) over Docyrus data sources, with a result grid and an embedded AI agent.

**DSQL** (Docyrus Structured Query Language) is a security-scoped SQL surface restricted to the
tenant's data sources. The `DsqlEditor` is a two-pane workbench: a top **DSQL Query** editor (SQL
syntax highlighting, Format + Run) and a bottom **Result** panel that renders the returned rows in a
paginated data grid. It carries the same embedded **AI agent** panel as the JSONata / Handlebars /
HTML Template editors.

Queries reference tables as `appSlug.dataSourceSlug` (e.g. `base.contact`), **not** raw schemas —
the backend rewrites them. Statements are SELECT-only / read-only and run through
`PUT /v1/dsql/query`.

**Demo:**

```tsx
'use client';

import { Button } from '@docyrus/ui/primitives/ui/button';

export function DsqlEditorDemo() {
  return (
    <div className="flex w-full flex-col gap-3 rounded-lg border border-dashed border-border p-6 text-sm text-muted-foreground">
      <p>
        <code>{''}</code> requires an authenticated{' '}
        <code>RestApiClient</code> and executes queries via{' '}
        <code>PUT /v1/dsql/query</code> against your Docyrus tenant — wire it up
        to see it in action. Tables are referenced as{' '}
        <code>app.dataSource</code> (e.g. <code>base.contact</code>). Results
        render in a paginated data grid, and an embedded AI agent can author
        DSQL for you.
      </p>
      <div>
        
      </div>
    </div>
  );
}
```

## Installation

```bash
pnpm dlx @docyrus/cli add @docyrus/ui-dsql-editor
```

**Dependencies:**
- [sql-formatter](https://www.npmjs.com/package/sql-formatter)
- [@uiw/react-codemirror](https://www.npmjs.com/package/@uiw/react-codemirror)
- [@uiw/codemirror-extensions-langs](https://www.npmjs.com/package/@uiw/codemirror-extensions-langs)
- [lucide-react](https://www.npmjs.com/package/lucide-react)

## Usage

```tsx
import { useState } from 'react';
import { useDocyrusAuth } from '@docyrus/signin';
import { DsqlEditor, useApplyDsql } from '@docyrus/ui/components/dsql-editor';
import { EditorAgentDrawer } from '@/components/editor-agent-drawer';

const DSQL_AGENT_ID = '019ecf41-2c43-7b0e-8d2d-71f218952e14';

function MyDsqlWorkbench() {
  const { client } = useDocyrusAuth();
  const dsql = useApplyDsql('select id, email from base.contact limit 100');
  const [aiOpen, setAiOpen] = useState(false);

  if (!client) return null;

  return (
    <DsqlEditor
      client={client}
      query={dsql.query}
      onQueryChange={dsql.setQuery}
      height="100%"
      aiAssistantOpen={aiOpen}
      onAiAssistantOpenChange={setAiOpen}
      renderAiAssistant={({ open, onClose, query }) => (
        <EditorAgentDrawer
          agentId={DSQL_AGENT_ID}
          open={open}
          onClose={onClose}
          clientTools={dsql.tools}
          editorContext={() => `Current DSQL query:\n${query || '(empty)'}`}
        />
      )}
    />
  );
}
```

For tests or mock data, supply `onRun` instead of `client`:

```tsx
<DsqlEditor onRun={async (query) => ({
  rows: [{ id: 1, email: 'a@b.com' }],
  columns: [{ name: 'id', type: 'number' }, { name: 'email', type: 'string' }],
  rowCount: 1
})} />
```

## Schema-aware autocomplete

Pass a `schema` namespace to get table + column completion: `from base.` completes data-source
names and `base.contact.` completes columns. The `useDsqlSchema` hook fetches it from
`GET /v1/dsql/schema/apps/:appSlug` (returns compact `create table appSlug.dataSourceSlug ( … )`
DDL per data source), parses it, and synthesizes the referenced `tenant.user` / `tenant.enum` system
tables. The same fetched DDL is ideal to feed into the agent's `editorContext` so it authors valid
DSQL.

```tsx
import { DsqlEditor, useApplyDsql, useDsqlSchema } from '@docyrus/ui/components/dsql-editor';

const dsql = useApplyDsql();
const { schema, tables } = useDsqlSchema(client, ['base']);

;
```

Building blocks are exported for custom wiring: `fetchDsqlAppSchema(client, appSlug)`,
`fetchDsqlSchemaNamespace(client, apps)`, `dsqlSchemasToNamespace(tables)`, and
`parseDsqlColumns(ddl)`. You can also pass a hand-built `SQLNamespace` directly to `schema` (or to
the standalone `DsqlCodeEditor` / `sqlExtensions({ schema })`).

## Auto-run when the agent writes a query

`useApplyDsql` takes an `onApply(query)` callback that fires right after the agent's `applyDsqlQuery`
tool writes a (formatted) query into the editor. Combine it with the imperative `ref` to close the
agent panel and run the query in one step:

```tsx
const editorRef = useRef<DsqlEditorHandle>(null);
const dsql = useApplyDsql('', {
  onApply: (query) => {
    setAiOpen(false);             // close the agent panel
    editorRef.current?.run(query); // run immediately (pass query to avoid stale state)
  }
});

;
```

The `DsqlEditorHandle` exposes `run(query?)` (runs the current text, or an explicit query) and
`getQuery()`.

## Row caps

The backend returns a single page — there is no server-side pagination. The effective row limit is
`min(yourLimit ?? 100, maxLimit)`, where `maxLimit` is **1000** for delegated user sessions and
**100** for API / client-credentials tokens. The editor surfaces the cap next to the result count so
a truncated result isn't mistaken for the full set. Add `LIMIT` / filters to narrow large queries.

## API Reference

| Prop | Type | Default |
|------|------|---------|
| `client` | `DsqlEditorClient \| null` | — |
| `onRun` | `(query: string) => Promise<DsqlRunResult>` | — |
| `query` | `string` | — |
| `defaultQuery` | `string` | — |
| `onQueryChange` | `(next: string) => void` | — |
| `onResult` | `(result: DsqlRunResult) => void` | — |
| `onError` | `(message: string) => void` | — |
| `schema` | `SQLNamespace` | — |
| `resultPageSize` | `number` | `25` |
| `height` | `number \| string` | `'100%'` |
| `className` | `string` | — |
| `aiAssistantOpen` | `boolean` | — |
| `onAiAssistantOpenChange` | `(open: boolean) => void` | — |
| `renderAiAssistant` | `(ctx: DsqlAiAssistantRenderProps) => ReactNode` | — |

### DsqlCodeEditor

The standalone SQL editor pane (embed it in form fields or automation-node configs).

| Prop | Type | Default |
|------|------|---------|
| `value` | `string` | — |
| `onChange` | `(value: string) => void` | — |
| `readOnly` | `boolean` | `false` |
| `placeholder` | `string` | — |
| `autoFocus` | `boolean` | `false` |
| `minHeight` | `string` | `'2.5rem'` |
| `maxHeight` | `string` | `'12rem'` |
| `height` | `string` | — |
| `className` | `string` | — |
| `lineNumbers` | `boolean` | `true` |
| `schema` | `SQLNamespace` | — |
| `basicSetup` | `BasicSetupOptions` | — |
| `extensions` | `Extension[]` | — |

## Components

| Component | Description |
|-----------|-------------|
| `DsqlEditor` | Two-pane query + result workbench |
| `DsqlCodeEditor` | Standalone SQL CodeMirror pane |
| `useApplyDsql` | Owns query state + the `applyDsqlQuery` agent client tool |
| `useDsqlSchema` | Fetches + builds the autocomplete `SQLNamespace` for given apps |
| `runDsqlQuery` | `PUT /v1/dsql/query` caller |
| `normalizeDsqlResponse` | Parses the API envelope into a `DsqlRunResult` |
| `extractDsqlError` | Pulls a human message out of a rejected request |
| `fetchDsqlAppSchema` / `fetchDsqlSchemaNamespace` | Fetch DSQL schema from the schema endpoints |
| `dsqlSchemasToNamespace` / `parseDsqlColumns` | Build a `SQLNamespace` from DDL schema text |
| `formatSql` | PostgreSQL-dialect formatter (never throws) |
| `sqlExtensions` | CodeMirror SQL highlight + completion extensions factory |

## Type Exports

| Type | Description |
|------|-------------|
| `DsqlEditorProps` | Props for `DsqlEditor` |
| `DsqlCodeEditorProps` | Props for `DsqlCodeEditor` |
| `DsqlEditorClient` | Minimal `{ put }` client contract |
| `DsqlRunResult` | `{ rows, columns, rowCount, durationMs? }` |
| `DsqlRunState` | Discriminated result-pane state |
| `DsqlColumnMeta` | `{ name, type?, label? }` resolved column |
| `DsqlTableSchema` | Raw schema row from the schema endpoints (`{ appSlug, slug, schema, … }`) |
| `DsqlAiAssistantRenderProps` | Context passed to `renderAiAssistant` |

## Type Reference

### DsqlRunResult

| Field | Type | Description |
|-------|------|-------------|
| `rows` | `Array<Record<string, unknown>>` | Returned rows |
| `columns` | `DsqlColumnMeta[]` | Columns inferred from row keys (SELECT order) |
| `rowCount` | `number` | Row count from the server (`meta.count`) |
| `durationMs` | `number` | Client-measured round-trip duration |

### DsqlColumnMeta

| Field | Type | Description |
|-------|------|-------------|
| `name` | `string` | Column key |
| `type` | `'number' \| 'boolean' \| 'date' \| 'datetime' \| 'uuid' \| 'string'` | Coarse inferred type (drives the cell variant) |
| `label` | `string` | Optional human label (defaults to `name`) |