sp00ky-query-builder

name	sp00ky-query-builder
description	Type-safe query builder for SurrealDB used by the Sp00ky framework. Use when defining schemas (SchemaStructure), building queries with QueryBuilder, handling relationships (one/many cardinality), or working with Sp00ky type helpers like TableNames, GetTable, TableModel, and QueryResult.
metadata	{"author":"sp00ky-sync","version":"0.0.1"}

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.

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.

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.
build	.build()	Finalize the query into a FinalQuery object.

Relationships

Use .related() to include related tables. Relationships must be declared in the schema.

// 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

export interface SchemaStructure {
  // ...tables, relationships...
  readonly backends: Record<string, HTTPOutboxBackendDefinition>;
}

export interface HTTPOutboxBackendDefinition {
  readonly outboxTable: string;  // The SurrealDB table used as the outbox (e.g., 'job')
  readonly routes: Record<string, HTTPBackendRouteDefinition>;
}

export interface HTTPBackendRouteDefinition {
  readonly args: Record<string, HTTPBackendRouteArgsDefinition>;
}

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:

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<S> — Union of all backend name strings (e.g., 'api')
• BackendRoutes<S, B> — Union of all route paths for backend B (e.g., '/spookify')
• RoutePayload<S, B, R> — 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 for a full reference of all type utilities.

Key types:

• TableNames<S> — Union of all table name strings
• GetTable<S, Name> — Extract a table definition by name
• TableModel<T> — Convert a table's columns to a TypeScript object type
• QueryResult<S, TableName, RelatedFields, IsOne> — The full result type including related fields
• BackendNames<S>, BackendRoutes<S, B>, RoutePayload<S, B, R> — 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.