ORM Package

A powerful and elegant Object-Relational Mapping (ORM) for TypeScript applications, providing intuitive model definitions, relationships, and query building capabilities.

Installation

bun add @stacksjs/orm

Basic Usage

import { User, Post, Comment } from '@stacksjs/orm'

// Find a single record
const user = await User.find(1)

// Find with conditions
const activeUsers = await User.where('status', '=', 'active').get()

// Create a new record
const newUser = await User.create({
  name: 'John Doe',
  email: 'john@example.com',
  password: 'hashed_password'
})

// Update a record
await User.where('id', '=', 1).update({ name: 'Jane Doe' })

// Delete a record
await User.where('id', '=', 1).delete()

Defining Models

Models are defined in the app/Models directory using a declarative schema:

// app/Models/User.ts
import { defineModel } from '@stacksjs/orm'

export default defineModel({
  name: 'User',
  table: 'users',

  attributes: {
    name: {
      type: 'string',
      required: true,
      maxLength: 255,
    },
    email: {
      type: 'string',
      required: true,
      unique: true,
      validation: 'email',
    },
    password: {
      type: 'string',
      required: true,
      hidden: true,
    },
    status: {
      type: 'string',
      default: 'active',
    },
  },

  traits: {
    useTimestamps: true,
    useSoftDeletes: true,
    useUuid: false,
  },
})

Model Attributes

Attribute Types

attributes: {
  // String types
  name: { type: 'string', maxLength: 255 },
  bio: { type: 'text' },

  // Numeric types
  age: { type: 'integer' },
  price: { type: 'decimal', precision: 10, scale: 2 },
  quantity: { type: 'bigint' },

  // Boolean
  isActive: { type: 'boolean', default: false },

  // Date/Time
  publishedAt: { type: 'datetime' },
  birthDate: { type: 'date' },

  // JSON
  metadata: { type: 'json' },
}

Attribute Options

attributes: {
  email: {
    type: 'string',
    required: true,      // Cannot be null
    unique: true,        // Must be unique in table
    default: 'default',  // Default value
    hidden: false,       // Hide from JSON serialization
    fillable: true,      // Can be mass-assigned
    guarded: false,      // Protected from mass-assignment
  }
}

Relationships

Has One

export default defineModel({
  name: 'User',

  hasOne: ['Profile'],

  // Or with configuration
  hasOne: [
    {
      model: 'Profile',
      foreignKey: 'user_id',
      relationName: 'userProfile',
    }
  ],
})

// Usage
const user = await User.with('profile').find(1)
console.log(user.profile)

Has Many

export default defineModel({
  name: 'User',

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

  // Or with configuration
  hasMany: [
    {
      model: 'Post',
      foreignKey: 'author_id',
    }
  ],
})

// Usage
const user = await User.with('posts').find(1)
console.log(user.posts) // Array of posts

Belongs To

export default defineModel({
  name: 'Post',

  belongsTo: ['User'],

  // Or with configuration
  belongsTo: [
    {
      model: 'User',
      foreignKey: 'user_id',
      relationName: 'author',
    }
  ],
})

// Usage
const post = await Post.with('author').find(1)
console.log(post.author.name)

Belongs To Many (Many-to-Many)

export default defineModel({
  name: 'User',

  belongsToMany: [
    {
      model: 'Role',
      pivotTable: 'user_roles',
      firstForeignKey: 'user_id',
      secondForeignKey: 'role_id',
    }
  ],
})

// Usage
const user = await User.with('roles').find(1)
console.log(user.roles) // Array of roles

Has One Through

export default defineModel({
  name: 'Country',

  hasOneThrough: [
    {
      model: 'PostalCode',
      through: 'User',
      throughForeignKey: 'country_id',
    }
  ],
})

Query Building

Basic Queries

// Select all
const users = await User.all()

// Find by ID
const user = await User.find(1)

// Find or fail
const user = await User.findOrFail(1)

// First record
const user = await User.first()

// Get specific columns
const users = await User.select('id', 'name', 'email').get()

Where Clauses

// Simple where
const users = await User.where('status', '=', 'active').get()

// Multiple conditions
const users = await User
  .where('status', '=', 'active')
  .where('age', '>=', 18)
  .get()

// Or where
const users = await User
  .where('role', '=', 'admin')
  .orWhere('role', '=', 'moderator')
  .get()

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

// Where not in
const users = await User.whereNotIn('status', ['banned', 'suspended']).get()

// Where null
const users = await User.whereNull('deleted_at').get()

// Where not null
const users = await User.whereNotNull('email_verified_at').get()

// Where between
const users = await User.whereBetween('age', 18, 65).get()

Ordering and Pagination

// Order by
const users = await User.orderBy('created_at', 'desc').get()

// Multiple order by
const users = await User
  .orderBy('status', 'asc')
  .orderBy('name', 'asc')
  .get()

// Random order
const users = await User.inRandomOrder().get()

// Limit and offset
const users = await User.limit(10).offset(20).get()

// Pagination
const users = await User.paginate(15, 1) // 15 per page, page 1

Aggregates

// Count
const count = await User.count()

// Max
const maxAge = await User.max('age')

// Min
const minAge = await User.min('age')

// Average
const avgAge = await User.avg('age')

// Sum
const totalBalance = await User.sum('balance')

Eager Loading

// Single relationship
const users = await User.with('posts').get()

// Multiple relationships
const users = await User.with(['posts', 'profile', 'comments']).get()

// Nested relationships
const users = await User.with('posts.comments').get()

// Conditional eager loading
const users = await User.with({
  posts: (query) => query.where('published', '=', true)
}).get()

Existence Queries

// Has relationship
const usersWithPosts = await User.has('posts').get()

// Where has with conditions
const usersWithPublishedPosts = await User.whereHas('posts', (query) => {
  query.where('status', '=', 'published')
}).get()

// Doesn't have relationship
const usersWithoutPosts = await User.doesntHave('posts').get()

// Where doesn't have
const users = await User.whereDoesntHave('posts', (query) => {
  query.where('status', '=', 'draft')
}).get()

Model Traits

Timestamps

traits: {
  useTimestamps: true, // Adds created_at and updated_at
}

Soft Deletes

traits: {
  useSoftDeletes: true, // Adds deleted_at column
}

// Usage
await User.find(1).delete() // Soft delete
await User.withTrashed().get() // Include soft deleted
await User.onlyTrashed().get() // Only soft deleted
await User.find(1).restore() // Restore soft deleted
await User.find(1).forceDelete() // Permanently delete

UUID

traits: {
  useUuid: true, // Use UUID instead of auto-increment
}

Observable

traits: {
  observe: true, // Enable model events
  // Or specify which events to observe
  observe: ['create', 'update', 'delete'],
}

Custom Accessors and Mutators

export default defineModel({
  name: 'User',

  // Custom getters
  get: {
    fullName: (attributes) => `${attributes.firstName} ${attributes.lastName}`,
    isAdmin: (attributes) => attributes.role === 'admin',
  },

  // Custom setters
  set: {
    password: async (value) => await hashPassword(value),
    email: (value) => value.toLowerCase(),
  },
})

// Usage
const user = await User.find(1)
console.log(user.fullName) // "John Doe"

Scopes

Define reusable query constraints:

export default defineModel({
  name: 'User',

  scopes: {
    active: (query) => query.where('status', '=', 'active'),
    admins: (query) => query.where('role', '=', 'admin'),
    recent: (query) => query.orderBy('created_at', 'desc').limit(10),
  },
})

// Usage
const activeAdmins = await User.active().admins().get()
const recentUsers = await User.recent().get()

Mass Assignment

Fillable Attributes

export default defineModel({
  name: 'User',

  fillable: ['name', 'email', 'password'],
})

// Only these fields can be mass-assigned
await User.create({ name: 'John', email: 'john@test.com', role: 'admin' })
// 'role' will be ignored

Guarded Attributes

export default defineModel({
  name: 'User',

  guarded: ['role', 'permissions'],
})

// These fields cannot be mass-assigned

Serialization

Hidden Attributes

export default defineModel({
  name: 'User',

  hidden: ['password', 'remember_token'],
})

// These fields won't appear in JSON output
const user = await User.find(1)
console.log(JSON.stringify(user)) // password not included

Appending Computed Attributes

export default defineModel({
  name: 'User',

  appends: ['fullName', 'isAdmin'],
})

// Computed attributes included in JSON

Transactions

import { transaction } from '@stacksjs/orm'

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

Raw Queries

import { db } from '@stacksjs/database'

// Raw select
const users = await db.selectFrom('users')
  .where('status', '=', 'active')
  .selectAll()
  .execute()

// Raw insert
await db.insertInto('users')
  .values({ name: 'John', email: 'john@test.com' })
  .execute()

Model Events

import { dispatch, listen } from '@stacksjs/events'

// Listen for model events
listen('user:created', (user) => {
  console.log('New user created:', user.name)
})

listen('user:updated', (user) => {
  console.log('User updated:', user.id)
})

listen('user:deleted', (user) => {
  console.log('User deleted:', user.id)
})

Edge Cases

Handling Null Values

// Where null with soft deletes
const activeUsers = await User
  .whereNull('deleted_at')
  .get()

// Coalescing null values
const users = await User.select('name', 'nickname')
  .get()
  .map(u => ({
    displayName: u.nickname ?? u.name
  }))

Large Result Sets

// Use chunking for large datasets
await User.chunk(100, async (users) => {
  for (const user of users) {
    await processUser(user)
  }
})

// Cursor for memory efficiency
for await (const user of User.cursor()) {
  await processUser(user)
}

Concurrent Updates

// Use transactions for concurrent updates
await transaction(async (trx) => {
  const user = await User.find(1, trx)
  user.balance += 100
  await user.save(trx)
})

API Reference

Model Static Methods

Model Instance Methods