Relationships
Most apps have tables that connect to each other: an order belongs to a user, a product has many reviews, a project has many members. These connections are called foreign keys — a column in one table that references a row in another.
Creating a foreign key
The visual editor has a column type called Reference that sets up the foreign key for you.
Add a column
In the table that will hold the reference (e.g. orders), add a new column. Name it after what it points to: user_id, product_id.
Pick the type
Match the primary key type of the target table — usually uuid. Exception: if it references a user, use text, see Tables & Schema.
Set the reference
Select the target table and column (usually id). The editor adds the FOREIGN KEY constraint for you.
Pick ON DELETE behavior
What should happen when the referenced row is deleted? See the options below.
ON DELETE behavior
When you delete a row that others reference, Postgres needs to know what to do with the referrers:
| Option | What it does | Use for |
|---|---|---|
| CASCADE | Delete the referring rows too | A user's tasks, a post's comments, a session's messages |
| SET NULL | Keep referrers, set the FK to NULL | An author on a post — if the author leaves, keep the post |
| RESTRICT | Block the delete entirely | Products referenced by orders — never delete a product if it's in someone's order history |
| NO ACTION | Same as RESTRICT in most cases | Rarely used |
The safest default for user-owned data is CASCADE. When the user is deleted, their tasks go with them.
One-to-many
The common case: one user has many tasks, one post has many comments. Put the foreign key on the many side:
CREATE TABLE public.posts (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
title TEXT NOT NULL,
user_id TEXT NOT NULL REFERENCES system.users(id) ON DELETE CASCADE
);
CREATE TABLE public.comments (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
post_id UUID NOT NULL REFERENCES public.posts(id) ON DELETE CASCADE,
content TEXT NOT NULL
);
One post has many comments. The post_id column lives on comments.
Many-to-many
When both sides can have many of the other — a user can be in many projects, a project has many users — use a pivot table (also called a join table):
CREATE TABLE public.project_members (
project_id UUID NOT NULL REFERENCES public.projects(id) ON DELETE CASCADE,
user_id TEXT NOT NULL REFERENCES system.users(id) ON DELETE CASCADE,
role TEXT NOT NULL DEFAULT 'member',
joined_at TIMESTAMPTZ NOT NULL DEFAULT now(),
PRIMARY KEY (project_id, user_id)
);
The primary key is the combination of both columns — a user can only be in a project once. Add extra columns (role, joined_at) on the pivot table itself when you need metadata about the relationship.
Querying across relationships
To get data from related tables in one request, use a SQL JOIN in your workflow's database node:
SELECT
posts.id,
posts.title,
COUNT(comments.id) AS comment_count
FROM public.posts
LEFT JOIN public.comments ON comments.post_id = posts.id
WHERE posts.user_id = ${current_user_id}
GROUP BY posts.id
ORDER BY posts.created_at DESC;
See API Builder for how to use custom SQL inside a workflow.