Relationships

Relationships connect tables together using foreign keys. Astroladb provides a clean, type-safe API for all common relationship patterns.

belongs_to (Foreign Keys)

Creates a foreign key reference to another table. The column name is automatically generated from the relationship name by appending _id.

// In schemas/blog/post.js
export default table({
  id: col.id(),
  title: col.title(),
  author: col.belongs_to("auth.user"),
});
// Creates: author_id (UUID, NOT NULL, FK to auth.user.id)
// Auto-creates index on author_id

Custom Column Names

Use .as() to customize the column name:

// Without .as():
author: col.belongs_to("auth.user"),
// Creates: author_id

// With .as():
col.belongs_to("auth.user").as("created_by"),
// Creates: created_by_id

Optional Relationships

Make foreign keys nullable:

reviewer: col.belongs_to("auth.user").optional(),
// Creates: reviewer_id (UUID, NULL allowed, FK)

one_to_one

Creates a unique foreign key for 1:1 relationships:

profile: col.one_to_one("auth.user");
// Creates: profile_id (UUID, FK, UNIQUE)

many_to_many

Define many-to-many relationships at the table level. Astroladb automatically creates a join table.

// In schemas/blog/post.js
export default table({
  id: col.id(),
  title: col.title(),
  content: col.body(),
}).many_to_many("blog.tag");
// Auto-creates: blog_posts_tags join table
// With columns: post_id, tag_id, created_at

The join table name is automatically generated by combining both table names in alphabetical order.

Self-Referential

Reference the same table for hierarchical data by using the table’s own namespace and name:

// In schemas/catalog/category.js
export default table({
  id: col.id(),
  name: col.name(),
  parent: col.belongs_to("catalog.category").optional(),
});

This creates a nullable foreign key to the same table, enabling tree structures like nested categories or comment threads.

Polymorphic (belongs_to_any)

Reference multiple possible tables using a polymorphic relationship. This creates both type and ID columns.

// In schemas/activity/like.js
export default table({
  id: col.id(),
  user: col.belongs_to("auth.user"),
  target: col.belongs_to_any(["blog.post", "blog.comment"]),
});
// Creates:
// - target_type (string(100), stores "blog.post" or "blog.comment")
// - target_id (UUID, references the appropriate table)
// - Composite index on (target_type, target_id)

This pattern is useful for features like:

• Likes/favorites on multiple content types
• Comments on various entities
• Activity feeds tracking different event types

Referential Integrity (on_delete / on_update)

Control what happens when referenced records are deleted or updated:

// Cascade delete: remove comments when post is deleted
post: col.belongs_to("blog.post").on_delete("cascade"),

// Set null: keep user's posts when user is soft-deleted
author: col.belongs_to("auth.user").optional().on_delete("set_null"),

// Restrict: prevent category deletion if products exist
category: col.belongs_to("catalog.category").on_delete("restrict"),

on_delete Options

Option	Behavior	Use Case
"cascade"	Delete child records when parent is deleted	Comments, order items
"set_null"	Set FK to NULL when parent is deleted	Optional author after user delete
"restrict"	Prevent parent deletion if children exist	Categories with products
"no_action"	Similar to restrict (database-specific)	Custom validation scenarios

on_update Options

Control behavior when the referenced column is updated (rarely needed with UUIDs):

parent: col.belongs_to("catalog.category").on_update("cascade"),

Complete Example

schemas/blog/comment.js

export default table({
  id: col.id(),
  post: col.belongs_to("blog.post").on_delete("cascade"),
  author: col.belongs_to("auth.user").on_delete("set_null").optional(),
  parent: col.belongs_to("blog.comment").optional().on_delete("cascade"),
  body: col.text(),
}).timestamps();

This creates:

• Comments deleted when post is deleted (cascade)
• Comment author set to NULL if user is deleted (set_null)
• Nested replies deleted when parent comment is deleted (cascade)