Relationships

Relationships connect tables together using foreign keys.

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.

junction

Mark an explicit junction table for many-to-many relationships. Unlike .many_to_many() which auto-generates a junction table, .junction() lets you define the junction table yourself with custom fields, constraints, and cascade rules.

Auto-detect (Simple Case)

For tables with exactly 2 foreign keys, .junction() auto-detects the relationship:

schemas/blog/posts_tags.js

export default table({
  post: col.belongs_to("blog.post"),
  tag: col.belongs_to("blog.tag"),
}).junction();
// Registers: blog.post ↔ blog.tag many-to-many
// Creates: post_id, tag_id columns with indexes

Explicit Parameters (Complex Case)

For tables with 3+ foreign keys, specify which 2 tables form the many-to-many relationship:

schemas/blog/posts_tags.js

export default table({
  post: col.belongs_to("blog.post"),
  tag: col.belongs_to("blog.tag"),
  created_by: col.belongs_to("auth.user"), // Extra metadata FK
  position: col.integer(),
}).junction("blog.post", "blog.tag");
// Explicitly marks post ↔ tag as the M2M relationship
// created_by is just an audit field

With Custom Fields

Add any fields you need to the junction table:

export default table({
  post: col.belongs_to("blog.post").on_delete("cascade"),
  tag: col.belongs_to("blog.tag").on_delete("cascade"),
  order: col.integer().default(0), // Display order
  created_at: col.datetime().default("now"), // When tagged
  created_by: col.belongs_to("auth.user").optional(), // Who tagged it
})
  .unique("post", "tag") // Prevent duplicate associations
  .junction("blog.post", "blog.tag");

Result in Exports

Both .many_to_many() and .junction() produce identical metadata, so alab export --relations generates relationship fields for both:

// From: alab export typescript --relations
interface PostWithRelations extends Post {
  tag?: Tag[]; // Relationship field added
}

interface TagWithRelations extends Tag {
  post?: Post[]; // Relationship field added
}

Tip

Use .many_to_many() for simple relationships. Use .junction() when you need custom fields, audit columns, or per-FK cascade rules.

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

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

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

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

Note

on_update is rarely needed when using UUIDs as primary keys, since UUIDs don’t change.

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