Schema

In Spooky, 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. Spooky 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;

Generated Types

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

Bash

# Generate TypeScript types
spooky --input src/schema.surql --output schema.gen.ts --format typescript

# Or generate all formats at once
spooky --input 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.