Tables

Basic Table Syntax

Define tables using the table({}) function with column definitions:

export default table({
  id: col.id(),
  title: col.string(),
  body: col.text(),
  published: col.boolean().default(false),
  author: col.belongs_to("auth.user"),
});

Table Modifiers

Chain modifiers to add common functionality:

export default table({
  id: col.id(),
  title: col.title(),
  content: col.body(),
})
  .timestamps()
  .soft_delete()
  .searchable(["title", "content"]);

Available Modifiers

Modifier	What it does
`.timestamps()`	Adds `created_at` and `updated_at` columns (auto-managed)
`.auditable()`	Adds `created_by` and `updated_by` UUID columns
`.soft_delete()`	Adds nullable `deleted_at` column for soft deletes
`.sortable()`	Adds `position` integer column for manual ordering
`.sort_by([...])`	Specifies default ORDER BY columns for queries
`.searchable([...])`	Marks columns for full-text search indexing
`.filterable([...])`	Specifies which columns can be filtered in queries
`.unique("col1", "col2")`	Creates composite unique constraint
`.index("col1", "col2")`	Creates composite index
`.many_to_many("other.table")`	Defines many-to-many relationship (auto-creates join table)

Modifier Details

Timestamps

Adds automatic timestamp tracking:

.timestamps()
// Adds:
// created_at: datetime, default NOW()
// updated_at: datetime, default NOW()

Soft Delete

Enables soft deletion pattern:

.soft_delete()
// Adds:
// deleted_at: datetime, nullable
// Records are marked as deleted instead of being removed

Sortable

Adds manual ordering support:

.sortable()
// Adds:
// position: integer, default 0
// Useful for drag-and-drop ordering in admin interfaces

Composite Constraints

Create multi-column uniqueness or indexes:

.unique("user", "product")  // Only one review per user per product
.index("status", "created_at")  // Optimize queries filtering by status and date

Note: For relationship columns, you can use the alias name - the _id suffix is automatically appended:

// Both work the same:
.unique("user", "product")
.unique("user_id", "product_id")

Composite Constraints Example

export default table({
  id: col.id(),
  order: col.belongs_to("sales.order"),
  product: col.belongs_to("catalog.product"),
  quantity: col.integer(),
})
  .unique("order", "product")
  .index("product", "quantity");