--- title: Data Models description: Learn how to define and use ORM models in Stacks applications --- # 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` and `UpdateModelData` from `@stacksjs/orm` when declaring inputs or results rather than widening those fields in parallel interfaces. ```typescript 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: ```typescript // 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: ```typescript // 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: ```typescript // 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: ```typescript // 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: ```typescript // 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: ```typescript // 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 ```typescript 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: ```typescript 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](/guide/database-scaling#sharding-with-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. ```typescript 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: ```typescript traits: { sharding: { unsharded: true }, } ``` | Option | Meaning | | --- | --- | | `column` | Column whose value decides the shard. | | `vindex` | Mapping from that column to a shard. Defaults to `hash`. | | `unsharded` | Replicate this table to every shard instead of splitting it. | | `sequence` | Sequence table backing an integer primary key, for models not using `useUuid`. | ## Querying Models A read returns its value where it is written, on SQLite, MySQL and Postgres. `await` is optional on a read, because `await` on a value is that value: ```typescript const user = User.where('email', 'like', '%@example.com').orderByDesc('created_at').first() user?.email // the row is already here const same = await User.where('email', 'like', '%@example.com').orderByDesc('created_at').first() ``` A write (`create`, `update`, `save`, `delete`) answers in place too, with the model's lifecycle around it. Event listeners that answer in place have run by the time the row is back, and a `creating` listener that returns `false` makes the write throw where it stands. The search-index update and the broadcast start with the write and finish after it. ```typescript const order = Order.create({ total: 1200 }) // Order declares observe and useSearch order.update({ total: 1500 }) ``` A write is a promise, and is typed as one, only on a model that declares something it has to wait for: `useSoftDeletes`, `useAudit`, `useActivityLog`, or a `set:` hook or `hooks` member written `async`. When in doubt, `await` the write: that is correct on every model. ```typescript const note = await Note.create({ title: 'Draft' }) // Note declares useSoftDeletes ``` Keep setters synchronous so a model's writes stay in place. The default `User` hashes its password with `makeHashSync`, which holds the thread for the length of the hash (about a quarter of a second for bcrypt at 12 rounds). An app that would rather wait than pause writes the setter `async` with `makeHash` and awaits its `User` writes. A server needs nothing more. A script that calls `process.exit()` straight after a write waits for the index update and the broadcast first: ```typescript import { modelWorkSettled } from '@stacksjs/orm' Product.create({ name: 'Widget' }) await modelWorkSettled() process.exit(0) ``` A call that fails in place, such as `findOrFail` on a missing row, throws where it stands, so use `try`/`catch` and not `.catch()`. SQLite runs a query in place. MySQL and Postgres run it on a worker thread that the caller waits on, so a server process handles one query at a time. On [stacksjs/bun](https://github.com/stacksjs/bun), a Bun whose MySQL and Postgres drivers can wait for a statement themselves, the connection stays on the calling thread and there is no worker; the framework looks at the runtime and uses what it has. With pantry that runtime is `stacksjs.com/bun: ^1.4.4` in `deps.yaml`, in place of `bun.com` (macOS arm64 and Linux x64). An app that needs the event loop free during queries can set `models: { execution: 'async' }` in `config/query-builder.ts` and await every model call. The `db` query builder answers the same way: `db.selectFrom('posts').get()` is the rows, and a transaction is done when it returns. ```typescript const postId = transaction((tx) => { const post = tx.insertInto('posts').values({ title: 'Hello' }).returning('id').first() tx.insertInto('post_revisions').values({ post_id: post.id, body: '' }).execute() return post.id }) // Committed. A callback that throws has rolled everything back and rethrown. ``` A callback that is `async` is waited for, and the transaction is then a promise to `await`, as before. While such a transaction is open it holds its row locks, so a statement elsewhere in the process that needs one of them answers with a promise that resolves once the transaction has finished. Any statement that needs nothing held is still its value. Inside `db.transaction()`, write through `tx` and not through a model. A model call runs on its own connection, and one that needs a row the open transaction has locked can never finish. It throws a `StatementTimeoutError` after `models.timeoutMs` (30 seconds by default) instead of stopping the process. ### Basic Queries ```typescript 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 ```typescript // 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 ```typescript // 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 ```typescript // Select specific columns const posts = await Post.select(['id', 'title', 'created_at']).get() // Distinct values const statuses = await Post.distinct('status').get() ``` ### Aggregates ```typescript // 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 ```typescript 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 ```typescript // 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: ```typescript await Post.chunk(100, async (posts) => { for (const post of posts) { await processPost(post) } }) ``` ## Creating and Updating ### Creating Records ```typescript // 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 ```typescript // 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 ```typescript // 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: ```typescript // 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 ```typescript 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: ```typescript traits: { observe: true, } ``` This emits events on model changes: - `{model}:created` - After creation - `{model}:updated` - After update - `{model}:deleted` - After deletion ### Listening to Events ```typescript 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 ```typescript traits: { useSoftDeletes: true, } ``` This adds a `deleted_at` column and modifies query behavior. ### Querying with Soft Deletes ```typescript // 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 ```typescript attributes: { password: { hidden: true, // Never included in JSON fillable: true, validation: { rule: schema.string().min(8), }, }, } ``` ### Converting to JSON ```typescript 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: ```typescript // 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: ```typescript // 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 ```typescript 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 | Method | Description | |--------|-------------| | `save()` | Save changes to database | | `update(data)` | Update with new data | | `forceUpdate(data)` | Update bypassing guarded | | `delete()` | Delete the record | | `toJSON()` | Convert to JSON object | | `toSearchableObject()` | Convert for search indexing | ### Static Query Methods | Method | Description | |--------|-------------| | `find(id)` | Find by primary key | | `findOrFail(id)` | Find or throw error | | `findMany(ids)` | Find multiple by IDs | | `all()` | Get all records | | `first()` | Get first record | | `last()` | Get last record | | `create(data)` | Create new record | | `createMany(data[])` | Create multiple records | | `where(column, value)` | Add where clause | | `with(relations)` | Eager load relationships | | `paginate(options)` | Paginate results | ## Related Documentation - [Database](/packages/database) - Database configuration - [Migrations](/guide/buddy/migrate) - Database migrations - [Validation](/packages/validation) - Validation rules - [Actions](/basics/actions) - Using models in actions ## Generic dashboard exposure A model can remain available to application services while being excluded from generic dashboard model access: ```ts 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. ### Scope a database to selected models An app with separate local and hosted runtimes can keep every definition in `app/Models/` while selecting the models each database owns: ```ts // config/database.ts export default { models: { include: ['User', 'Subscriber', 'EmailList', 'EmailListSubscriber'] }, } ``` Names are case insensitive. Empty selections and unknown names are rejected before generating migrations. Models outside the selection are protected from table drops. A native backend can continue to pass its explicit local registry to `openNativeDatabase` independently of this web database selection. Use `security.api.models` to select the corresponding generated web APIs. Schema selection does not change API authentication or grant access to other models.