Schema

In Sp00ky, you define your data model using sql schema files. This approach serves as the single source of truth for your application, from which TypeScript interfaces are automatically generated.

Defining Your Schema

You start by creating .surql files in your schema directory. Sp00ky advocates for a “Schema First” approach, ensuring your data structure is explicit, typesafe, and secure.

Tables and Fields

Here is a simple example of how to define a user table. Note the use of DEFINE TABLE and DEFINE FIELD to enforce data integrity.

sql

-- Define the user table with full schema enforcement
DEFINE TABLE user SCHEMAFULL
  PERMISSIONS
    FOR select, create WHERE true
    FOR update, delete WHERE id = $auth.id; -- Only the user can modify their own record

-- Define fields with strict types and assertions
DEFINE FIELD username ON TABLE user TYPE string
ASSERT $value != NONE AND string::len($value) > 3;

DEFINE INDEX unique_username ON TABLE user FIELDS username UNIQUE;

DEFINE FIELD created_at ON TABLE user TYPE datetime
VALUE time::now();

Relationships

SurrealDB shines with its graph capabilities. Relationships are first-class citizens and can be modeled in several ways depending on your needs.

One-to-One (1:1)

For a one-to-one relationship, such as a User having a single Profile, you can simply use a record<table_name> type.

sql

DEFINE TABLE user SCHEMAFULL;
DEFINE TABLE profile SCHEMAFULL;

-- The user table has a link to a specific profile
DEFINE FIELD profile ON TABLE user TYPE record<profile>;

-- The profile can also link back to the user if needed
DEFINE FIELD user ON TABLE profile TYPE record<user>;

One-to-Many (1:N)

Models like a Thread having many Comments are classic one-to-many relationships. You define this by putting a record<parent> field on the child table.

sql

DEFINE TABLE thread SCHEMAFULL;
DEFINE TABLE comment SCHEMAFULL;

-- Child (Comment) stores a reference to Parent (Thread)
DEFINE FIELD thread ON TABLE comment TYPE record<thread>;

-- You can also optionally store an array of references on the parent (not strictly required for querying)
DEFINE FIELD comments ON TABLE thread TYPE array<record<comment>>;

Many-to-Many (N:M)

For complex relationships, like Users following other Users (graph), or Posts having Tags, use a Relation Table. This is a special table that stores the edges of the graph.

sql

-- 1. Define the component tables
DEFINE TABLE user SCHEMAFULL;
DEFINE TABLE post SCHEMAFULL;

-- 2. Define the edge (relation) table
-- It connects a 'user' (from) to a 'post' (to) via a 'liked' edge
DEFINE TABLE liked SCHEMAFULL TYPE RELATION
FROM user TO post;

-- 3. (Optional) Define properties on the edge
DEFINE FIELD created_at ON TABLE liked TYPE datetime VALUE time::now();

-- 4. Create the relationship using standard sql or Events
-- RELATE user:john->liked->post:surrealdb_is_cool;

Schema Annotations

Sp00ky recognizes special comment descriptors in your .surql files. They look like ordinary SurrealQL comments (-- @name), so SurrealDB ignores them, but the Sp00ky CLI reads them to change how a table or field is generated and synced.

Descriptor	Placement	Effect
`-- @nosync`	above a `DEFINE TABLE`	Server-only table. Excluded from sync entirely (see below).
`-- @crdt text`	above a `DEFINE FIELD`	Marks a collaborative text field backed by a Loro CRDT. Read/write it with `useCrdtField`. See CRDT.
`-- @cursor`	above a `DEFINE FIELD` (with `@crdt`)	Stores per-session cursors alongside the CRDT snapshot. See CRDT.
`-- @parent`	suffix on a `DEFINE FIELD ... TYPE record<...>`	Marks the parent side of a relationship; written automatically from the auth context, never by client code.

sql

-- @crdt text                       -- collaborative text field (Loro CRDT)
DEFINE FIELD content ON TABLE thread TYPE string;

DEFINE FIELD author ON TABLE thread TYPE record<user>; -- @parent

-- @nosync
DEFINE TABLE audit_log SCHEMALESS;

Excluding a table from sync (`@nosync`)

Put -- @nosync on the line above a DEFINE TABLE to make it server-only. This is useful for audit logs, server-side bookkeeping, or any table that should live in your database but never reach clients.

A @nosync table is:

Omitted from generated types (TypeScript / Dart / JSON Schema).
Omitted from relations — it is not listed as a relation table, and any record<...> field on another table that points at it is dropped from the generated relationships.
Never synced — no sync events are emitted for it, so no updates flow to the scheduler or SSP.
Excluded from the scheduler snapshot and from SSP bootstrap.
Still stored in the main database, and still included in backups.

sql

-- @nosync
DEFINE TABLE audit_log SCHEMALESS;
DEFINE FIELD action ON TABLE audit_log TYPE string;
DEFINE FIELD at ON TABLE audit_log TYPE datetime VALUE time::now();

Not the same as locking permissions

@nosync is distinct from PERMISSIONS FOR select WHERE false. A permission-locked table is still synced (it shows up in generated types and the snapshot, it just can’t be read); @nosync removes the table from sync altogether. Reach for @nosync when a table should never leave the server.

Generated Types

Once your schema is defined, use the Sp00ky CLI to generate TypeScript definitions:

Bash

# Recommended: drive from sp00ky.yml's `clientTypes` entries
spky generate

# One-shot variant: invoke the schema extractor directly
spky --input ./schema/src/schema.surql --output ./schema.gen.ts --format typescript

# Or emit every format (TypeScript + Dart in addition to JSON Schema)
spky --input ./schema/src/schema.surql --output ./schema --all

For example, the user table above might generate:

TypeScript

export type User = {
    id: RecordId<"user">;
    username: string;
    created_at: Date;
    // Relationships are typed as RecordIds
    profile?: RecordId<"profile">;
};

These types are then available directly in your client application, ensuring that your frontend code is always in sync with your database schema.