Skip to content
Sapporta

Relationships and lookup behavior

View as Markdown

A relationship has three distinct meanings: the foreign key preserves stored integrity, rowLabelColumns names the referenced row, and children exposes the reverse path on a parent record.

Define the forward relationship

Section titled “Define the forward relationship”

The database relationship starts with a Drizzle foreign key. Import the target’s raw table and reference its primary key:

import { integer, sqliteTable } from "drizzle-orm/sqlite-core";
import { projectsTable } from "./projects.js";


export const tasksTable = sqliteTable("tasks", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  project_id: integer("project_id")
    .notNull()
    .references(() => projectsTable.id, { onDelete: "cascade" }),
  // Other task columns...
});

The foreign key lets generated forms treat project_id as a lookup. The target table still needs a human label:

export const projects = sapportaTable({
  drizzle: projectsTable,
  meta: {
    label: "Projects",
    rowScope: "workspaceGlobal",
    rowLabelColumns: ["name"],
    children: [
      {
        table: "tasks",
        foreignKey: "project_id",
        label: "Tasks",
        columns: ["title", "status", "due_date"],
        defaultSort: "due_date",
      },
    ],
    search: {
      children: {
        tasks: "allColumns",
      },
    },
  },
});

rowLabelColumns: ["name"] makes lookup results display the project name while preserving the project primary key as the value. Keep that key type through pickers, caches, filters, and API calls: numeric keys remain numbers and text keys remain strings. The lookup route returns entries, not an ID-to-label map:

{
  "entries": [
    {
      "value": 1,
      "label": "Website Relaunch",
      "meta": { "id": 1, "name": "Website Relaunch" }
    }
  ]
}

meta contains ordinary visible fields from the source row. Consumers may use those fields for presentation, but they should not assume the object is empty or that it has one universal shape.

Two rows can share a label, such as two accounts named “Fuel” under different parents. meta.rowCaption names a column or computed field whose text pickers show under the label, such as the account’s parent path; see Computed fields and row captions. A custom form can also narrow a picker’s choices, such as to accounts of one type, or leave out a row and its descendants when choosing its parent; see Pick a related row.

children describes the reverse path. Expanding a project row in /tables/projects filters tasks by project_id, shows the chosen columns, and applies the stable due-date sort. This is a table-row expansion and child collection, not a generated /tables/projects/:id detail route. Declare child collections only when the reverse path is part of the record workflow. A join table may appear under both parents. A self-reference, such as a parent account, forms a tree: declare it with meta.tree as described in Show a table as a tree.

The search.children.tasks entry lets a visible task make its project appear in the parent result. Search configuration is separate from the child grid’s display columns, and it can continue through further declared children when the domain needs it. Child matching uses the Tasks read ability and row scope; it cannot make an inaccessible task reveal its project.

Expanding the matching project runs the child grid’s own query and shows all visible tasks. The parent term is not inherited as a hidden child filter. Use an explicit task-table link or an application result when the workflow needs to show only the matching children.

Exercise both directions

Section titled “Exercise both directions”

Create the parent before the child. Run the app and create a project named Website Relaunch at /tables/projects/new. If you use the API or CLI, capture the returned project key instead of assuming a fresh database assigned 1. Create a task at /tables/tasks/new and choose that project from the lookup. Return to /tables/projects and expand the project row to find the task under Tasks.

Lookup options, child rows, and child-assisted search all use the active read ability and row scope. Relationship metadata never widens access.

Section titled “Related reference”