Data Models

Model row, query and write types follow the model declaration. An enum validator keeps its literal values even when a seed factory returns string, so a status validated as draft or published cannot be written as an arbitrary string. Explicit attribute types take precedence; other factory inference is preserved. Use ModelRow<typeof Model> and UpdateModelData<typeof Model> from @stacksjs/orm when declaring inputs or results rather than widening those fields in parallel interfaces.

attributes: {
  name: {
    factory: faker => faker.person.fullName(),
  },

  email: {
    factory: faker => faker.internet.email(),
  },

  status: {
    factory: faker => faker.helpers.arrayElement(['active', 'inactive', 'pending']),
  },

  // Factory with access to other attributes
  slug: {
    factory: (faker, attributes) => {
      const name = attributes?.name || faker.lorem.word()
      return name.toLowerCase().replace(/\s+/g, '-')
    },
  },
}

Relationships

Has One

Define a one-to-one relationship:

// app/Models/User.ts
export default {
  name: 'User',
  table: 'users',

  hasOne: ['Profile'],

  // Or with configuration
  hasOne: [
    {
      model: 'Profile',
      foreignKey: 'user_id',
      relationName: 'profile',
    },
  ],
} satisfies Model

Has Many

Define a one-to-many relationship:

// app/Models/User.ts
export default {
  name: 'User',
  table: 'users',

  hasMany: ['Post', 'Comment'],

  // Or with configuration
  hasMany: [
    {
      model: 'Post',
      foreignKey: 'author_id',
      relationName: 'posts',
    },
    {
      model: 'Comment',
      foreignKey: 'user_id',
    },
  ],
} satisfies Model

Belongs To

Define an inverse one-to-many relationship:

// app/Models/Post.ts
export default {
  name: 'Post',
  table: 'posts',

  belongsTo: ['User'],

  // Or with configuration
  belongsTo: [
    {
      model: 'User',
      foreignKey: 'author_id',
      relationName: 'author',
    },
  ],
} satisfies Model

Belongs To Many

Define a many-to-many relationship:

// app/Models/Post.ts
export default {
  name: 'Post',
  table: 'posts',

  belongsToMany: ['Tag'],

  // With pivot table configuration
  belongsToMany: [
    {
      model: 'Tag',
      pivotTable: 'post_tags',
      firstForeignKey: 'post_id',
      secondForeignKey: 'tag_id',
    },
  ],
} satisfies Model

Has One Through

Define a has-one-through relationship:

// app/Models/Country.ts
export default {
  name: 'Country',
  table: 'countries',

  hasOneThrough: [
    {
      model: 'Capital',
      through: 'State',
      throughForeignKey: 'country_id',
      relationName: 'capital',
    },
  ],
} satisfies Model

Polymorphic Relationships (Morph)

Define polymorphic relationships:

// app/Models/Comment.ts
export default {
  name: 'Comment',
  table: 'comments',

  morphOne: {
    model: 'Image',
    morphName: 'imageable',
    type: 'imageable_type',
    id: 'imageable_id',
  },
} satisfies Model

Traits

Available Traits

traits: {
  // Timestamps (created_at, updated_at)
  useTimestamps: true,

  // UUID column
  useUuid: true,

  // Soft deletes (deleted_at)
  useSoftDeletes: true,

  // Observable (emit events on changes)
  observe: true,

  // Searchable (Algolia/MeiliSearch integration)
  searchable: true,

  // Billable (Stripe integration)
  billable: true,

  // Auto-generate API routes
  useApi: {
    uri: 'posts',
    routes: ['index', 'store', 'show', 'update', 'destroy'],
    middleware: ['auth'],
  },

  // How many rows `buddy seed` generates from the attribute factories
  useSeeder: {
    count: 50,
  },

  // How this table is sharded, on engines that shard (Vitess).
  // Ignored on single-node dialects, so it costs nothing to declare.
  sharding: {
    column: 'tenant_id',
  },
}

API Trait Configuration

Generate RESTful API routes automatically:

traits: {
  useApi: {
    // Base URI for routes
    uri: 'posts',

    // Routes to generate
    routes: ['index', 'store', 'show', 'update', 'destroy'],

    // Or with custom action paths
    routes: {
      index: 'PostIndexAction',
      store: 'PostStoreAction',
      show: 'PostShowAction',
      update: 'PostUpdateAction',
      destroy: 'PostDestroyAction',
    },

    // Middleware for all routes
    middleware: ['auth', 'throttle:60,1'],
  },
}

This generates:

• GET /posts - List posts
• POST /posts - Create post
• GET /posts/{id} - Show post
• PATCH /posts/{id} - Update post
• DELETE /posts/{id} - Delete post

Sharding Trait Configuration

Only relevant on engines that shard, currently Vitess. Single-node dialects ignore it, so declaring it early costs nothing.

You usually do not need it. buddy generate:vschema derives a sharding column from your relationship graph: a table with a belongsTo shards by the key of its parent, so the two live on the same shard and a join between them does not scatter across the cluster. Set this only when that derived choice is wrong.

traits: {
  // A sharded keyspace cannot use AUTO_INCREMENT, because every shard
  // would hand out the same values. Generate keys in the application.
  useUuid: true,

  sharding: {
    // Column whose value decides the shard. Rows sharing a value land together.
    column: 'tenant_id',

    // How that column maps to a shard. Defaults to 'hash'.
    // 'hash' | 'xxhash' | 'binary' | 'binary_md5' | 'unicode_loose_md5'
    vindex: 'hash',
  },
}

For a small lookup table that is joined against constantly, copy it to every shard instead of splitting it. Splitting would make every such join a scatter-gather; duplicating a few hundred rows costs nothing:

traits: {
  sharding: { unsharded: true },
}

Querying Models

Basic Queries

import { Post } from '@stacksjs/orm'

// Find by ID
const post = await Post.find(1)

// Find or fail
const post = await Post.findOrFail(1)

// Get all records
const posts = await Post.all()

// First record
const post = await Post.first()

// Last record
const post = await Post.last()

Where Clauses

// Simple where
const posts = await Post.where('status', 'published').get()

// With operator
const posts = await Post.where('views', '>', 100).get()

// Multiple conditions
const posts = await Post
  .where('status', 'published')
  .where('author_id', 1)
  .get()

// Or where
const posts = await Post
  .where('status', 'published')
  .orWhere([['status', 'draft'], ['author_id', 1]])
  .get()

// Where in
const posts = await Post.whereIn('id', [1, 2, 3]).get()

// Where not in
const posts = await Post.whereNotIn('status', ['archived', 'deleted']).get()

// Where null
const posts = await Post.whereNull('deleted_at').get()

// Where not null
const posts = await Post.whereNotNull('published_at').get()

// Where between
const posts = await Post.whereBetween('created_at', ['2024-01-01', '2024-12-31']).get()

// Where like
const posts = await Post.whereLike('title', '%TypeScript%').get()

Ordering and Limiting

// Order by
const posts = await Post.orderBy('created_at', 'desc').get()

// Order by shortcuts
const posts = await Post.orderByDesc('created_at').get()
const posts = await Post.orderByAsc('title').get()

// Latest/Oldest
const latestPost = await Post.latest('created_at')
const oldestPost = await Post.oldest('created_at')

// Random order
const posts = await Post.inRandomOrder().get()

// Limit and offset
const posts = await Post.take(10).skip(20).get()

Selecting Columns

// Select specific columns
const posts = await Post.select(['id', 'title', 'created_at']).get()

// Distinct values
const statuses = await Post.distinct('status').get()

Aggregates

// Count
const total = await Post.count()

// Max
const maxViews = await Post.max('views')

// Min
const minViews = await Post.min('views')

// Average
const avgViews = await Post.avg('views')

// Sum
const totalViews = await Post.sum('views')

Pagination

const result = await Post.paginate({
  page: 1,
  limit: 15,
})

// Result shape:
// {
//   data: Post[],
//   paging: {
//     total_records: number,
//     page: number,
//     total_pages: number,
//   },
//   next_cursor: number | null,
// }

Eager Loading

// Load relationships
const posts = await Post.with(['author', 'comments']).get()

// Nested relationships
const posts = await Post.with(['author', 'comments.user']).get()

Chunking

Process large datasets efficiently:

await Post.chunk(100, async (posts) => {
  for (const post of posts) {
    await processPost(post)
  }
})

Creating and Updating

Creating Records

// Create single record
const post = await Post.create({
  title: 'Hello World',
  content: 'This is my first post',
  status: 'draft',
})

// Create multiple records
await Post.createMany([
  { title: 'Post 1', content: 'Content 1' },
  { title: 'Post 2', content: 'Content 2' },
])

// First or create
const post = await Post.firstOrCreate(
  { email: 'user@example.com' },  // Search criteria
  { name: 'New User' },            // Values to create with
)

// Update or create
const post = await Post.updateOrCreate(
  { email: 'user@example.com' },  // Search criteria
  { name: 'Updated Name' },        // Values to update/create with
)

Updating Records

// Update single record
const post = await Post.find(1)
await post.update({ title: 'Updated Title' })

// Save changes
post.title = 'New Title'
await post.save()

// Force update (bypass guarded)
await post.forceUpdate({ admin_only_field: 'value' })

Deleting Records

// Delete single record
const post = await Post.find(1)
await post.delete()

// Delete by ID
await Post.remove(1)

Scopes

Local Scopes

Define reusable query constraints:

// In your model usage
const publishedPosts = await Post
  .where('status', 'published')
  .where('published_at', '<=', new Date())
  .get()

// Create a helper function
async function getPublishedPosts() {
  return Post
    .where('status', 'published')
    .whereNotNull('published_at')
    .orderByDesc('published_at')
    .get()
}

Conditional Queries

const posts = await Post
  .when(request.has('category'), (query) => {
    return query.where('category_id', request.get('category'))
  })
  .when(request.has('search'), (query) => {
    return query.whereLike('title', `%${request.get('search')}%`)
  })
  .get()

Events

Model Observers

Enable model events with the observe trait:

traits: {
  observe: true,
}

This emits events on model changes:

• {model}:created - After creation
• {model}:updated - After update
• {model}:deleted - After deletion

Listening to Events

import { events } from '@stacksjs/events'

events.on('post:created', async (post) => {
  console.log('New post created:', post.title)
  await notifySubscribers(post)
})

events.on('post:updated', async (post) => {
  await invalidateCache(`post:${post.id}`)
})

events.on('post:deleted', async (post) => {
  await cleanupRelatedData(post.id)
})

Soft Deletes

Enabling Soft Deletes

traits: {
  useSoftDeletes: true,
}

This adds a deleted_at column and modifies query behavior.

Querying with Soft Deletes

// Normal queries exclude soft-deleted records
const posts = await Post.all()

// Include soft-deleted records
const allPosts = await Post.withTrashed().get()

// Only soft-deleted records
const trashedPosts = await Post.onlyTrashed().get()

// Restore soft-deleted record
const post = await Post.withTrashed().find(1)
await post.restore()

// Permanently delete
await post.forceDelete()

JSON Serialization

Hiding Attributes

attributes: {
  password: {
    hidden: true,  // Never included in JSON
    fillable: true,
    validation: {
      rule: schema.string().min(8),
    },
  },
}

Converting to JSON

const post = await Post.find(1)

// Get JSON representation
const json = post.toJSON()

// Includes all visible attributes and loaded relationships

Edge Cases and Gotchas

Mass Assignment Protection

Attributes must be marked as fillable for mass assignment:

// This works
const post = await Post.create({
  title: 'Hello',      // fillable: true
  content: 'World',    // fillable: true
})

// This field is ignored
const post = await Post.create({
  admin_only: true,    // fillable: false (ignored)
})

// Use forceCreate to bypass
const post = await Post.forceCreate({
  admin_only: true,    // Works with forceCreate
})

Relationship Loading

Always use .with() for eager loading to avoid N+1 queries:

// Bad: N+1 queries
const posts = await Post.all()
for (const post of posts) {
  console.log(post.author.name)  // Query per post!
}

// Good: Eager loading
const posts = await Post.with(['author']).get()
for (const post of posts) {
  console.log(post.author.name)  // Already loaded
}

Transaction Support

import { transaction } from '@stacksjs/orm'

await transaction(async (trx) => {
  const user = await User.create({ name: 'John' }, { transaction: trx })
  await Post.create({ title: 'Hello', author_id: user.id }, { transaction: trx })
  // Both operations commit or rollback together
})

API Reference

Model Instance Methods

Static Query Methods

Related Documentation

• Database - Database configuration
• Migrations - Database migrations
• Validation - Validation rules
• Actions - Using models in actions

Generic dashboard exposure

A model can remain available to application services while being excluded from generic dashboard model access:

dashboard: { access: false },

This server-side boundary excludes the model from generic catalogs and counts, global model search, and model-browser reads and writes. Direct generic model requests return 404 before querying its rows. Omission defaults to access. dashboard.enabled: false still controls sidebar presentation only.

This option does not disable direct ORM use, named domain Actions, or the separate routes generated by useApi. Configure model API selection, middleware, ownership and policies for those routes. It also does not add row-level authorization to models that remain exposed in the dashboard.