# Mono424 Sp00ky Sp00ky > Sp00ky Query Builder - Skill: `tomevault-io/mono424-sp00ky-sp00ky` (Agent Skill, multi-file: 2 files) - Install (CLI): `npx skillmds@latest add tomevault-io/mono424-sp00ky-sp00ky` - Raw SKILL.md: https://api.skillmd.com/api/skills/tomevault-io/mono424-sp00ky-sp00ky/raw - Safety review: pending (external: skill-scanner PASS, skillspector PASS) - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: Coding & Dev Tools - Author: tomevault-io (https://skillmd.com/u/tomevault-io) - Updated: 2026-09-17 - Page: https://skillmd.com/skills/tomevault-io/mono424-sp00ky-sp00ky --- # Sp00ky Query Builder `@spooky-sync/query-builder` provides the type-safe schema definition format and query builder used throughout the Sp00ky framework. ## Schema Definition Sp00ky schemas are defined as `const` objects satisfying `SchemaStructure`. They are typically generated by the Sp00ky CLI (`spooky generate`), but can be written by hand. ```typescript import type { SchemaStructure } from '@spooky-sync/query-builder'; export const schema = { tables: [ { name: 'user', columns: { id: { type: 'string', optional: false }, email: { type: 'string', optional: false }, name: { type: 'string', optional: true }, }, primaryKey: ['id'], }, { name: 'post', columns: { id: { type: 'string', optional: false }, title: { type: 'string', optional: false }, body: { type: 'string', optional: false }, author: { type: 'string', optional: false, recordId: true }, }, primaryKey: ['id'], }, ], relationships: [ { from: 'post', field: 'author', to: 'user', cardinality: 'one' }, { from: 'user', field: 'posts', to: 'post', cardinality: 'many' }, ], backends: {}, } as const satisfies SchemaStructure; ``` ### Column Types The `type` field maps to TypeScript types: | ValueType | TypeScript Type | |-------------|-----------------| | `'string'` | `string` | | `'number'` | `number` | | `'boolean'` | `boolean` | | `'null'` | `null` | | `'json'` | `unknown` | Set `optional: true` to make a field nullable (`T | null`). Set `recordId: true` to indicate a field stores a SurrealDB RecordId. ## QueryBuilder API The `QueryBuilder` class provides a fluent, chainable API for constructing type-safe queries. ```typescript import { QueryBuilder } from '@spooky-sync/query-builder'; // Create a query builder for the "post" table const query = new QueryBuilder(schema, 'post') .where({ author: 'user:alice' }) .select('id', 'title', 'body') .orderBy('title', 'desc') .limit(10) .offset(0) .related('author') .build(); ``` ### Chain Methods | Method | Signature | Description | |--------|-----------|-------------| | `where` | `.where(conditions)` | Filter by field values. String IDs are auto-parsed to RecordId. | | `select` | `.select(...fields)` | Pick specific fields. Can only be called once per query. | | `orderBy` | `.orderBy(field, 'asc' \| 'desc')` | Sort results. Default direction is `'asc'`. | | `limit` | `.limit(count)` | Limit the number of results. | | `offset` | `.offset(count)` | Skip the first N results. | | `one` | `.one()` | Return a single object instead of an array. Implicitly sets `limit(1)`. | | `related` | `.related(field, modifier?)` | Include related data via subquery. See [Relationships](#relationships). | | `build` | `.build()` | Finalize the query into a `FinalQuery` object. | ### Relationships Use `.related()` to include related tables. Relationships must be declared in the schema. ```typescript // One-to-one: post has one author new QueryBuilder(schema, 'post') .related('author') .build(); // One-to-many: user has many posts new QueryBuilder(schema, 'user') .related('posts') .build(); // Nested relationship with modifier new QueryBuilder(schema, 'user') .related('posts', (q) => q.orderBy('title', 'asc').limit(5)) .build(); ``` Cardinality is inferred from the schema. For `'one'` relationships, the result is an object. For `'many'`, it is an array. ## Backend Schema The `backends` field in `SchemaStructure` defines available HTTP backends, their outbox tables, and typed routes. This is generated by `spooky generate` from your `sp00ky.yml` config and OpenAPI spec. ### Schema Structure ```typescript export interface SchemaStructure { // ...tables, relationships... readonly backends: Record; } export interface HTTPOutboxBackendDefinition { readonly outboxTable: string; // The SurrealDB table used as the outbox (e.g., 'job') readonly routes: Record; } export interface HTTPBackendRouteDefinition { readonly args: Record; } export interface HTTPBackendRouteArgsDefinition { readonly type: ValueType; // 'string' | 'number' | 'boolean' | 'null' | 'json' readonly optional: boolean; } ``` ### Generated Example Given a `sp00ky.yml` with an `api` backend and an OpenAPI spec defining a `/spookify` route with an `id` parameter, `spooky generate` produces: ```typescript export const schema = { // ...tables, relationships... backends: { "api": { outboxTable: 'job' as const, routes: { "/spookify": { args: { "id": { type: 'string' as const, optional: false as const }, } }, } }, }, } as const satisfies SchemaStructure; ``` ### Backend Type Helpers - `BackendNames` — Union of all backend name strings (e.g., `'api'`) - `BackendRoutes` — Union of all route paths for backend `B` (e.g., `'/spookify'`) - `RoutePayload` — Typed payload object for route `R` on backend `B`. Required args become required fields, optional args become optional fields. Types are mapped from `ValueType` to TypeScript types. These types are used by `db.run()` to provide full type safety — backend name, route path, and payload are all checked at compile time. ## Type Helpers See [references/type-helpers.md](references/type-helpers.md) for a full reference of all type utilities. Key types: - `TableNames` — Union of all table name strings - `GetTable` — Extract a table definition by name - `TableModel` — Convert a table's columns to a TypeScript object type - `QueryResult` — The full result type including related fields - `BackendNames`, `BackendRoutes`, `RoutePayload` — Backend/run type helpers ## Common Pitfalls 1. **String IDs are auto-converted**: When you pass `'user:alice'` in a `where()`, it is automatically parsed into a SurrealDB `RecordId`. If the string does not contain `:`, and the field is named `id`, the table name is prepended. 2. **`select()` can only be called once**: Calling it twice throws an error. Combine all fields in one call. 3. **Schema must use `as const satisfies SchemaStructure`**: Without `as const`, TypeScript cannot infer literal types for table names and relationships, and type safety is lost. --- > Source: [mono424/sp00ky](https://github.com/mono424/sp00ky) — distributed by [TomeVault](https://tomevault.io).