ORM

The ORM module provides an Active Record implementation with model decorators, relationship loading, and a fluent query builder.

BaseModel

import { BaseModel, primary } from '@strav/database'

class User extends BaseModel {
  static override softDeletes = true

  @primary
  declare pid: string

  declare username: string | null
  declare createdAt: DateTime
}

Conventions

• Table name: derived from the class name — User becomes user, OrderItem becomes order_item.
• Primary key: marked with @primary (defaults to id if no decorator).
• Column mapping: camelCase properties map to snake_case columns automatically (createdAt <-> created_at).
• Timestamps: Date values from the database are hydrated as Luxon DateTime objects.

The declare keyword

Properties with schema defaults use initializers instead:

role: UserRole = UserRole.User   // included in INSERT
declare createdAt: DateTime      // NOT included — DB generates it

CRUD operations

Creating records

// Using create()
const user = await User.create({ username: 'alice' })

// Using save()
const user = new User()
user.username = 'alice'
await user.save()   // INSERT ... RETURNING *

// Using merge() + save()
const user = new User()
user.merge({ username: 'alice', role: 'admin' })
await user.save()

// Inside a transaction (all CRUD methods accept an optional trx parameter)
await transaction(async (trx) => {
  const user = await User.create({ username: 'alice' }, trx)
  await user.save(trx)
  await user.delete(trx)
})

Reading records

const user = await User.find(userId)            // returns null if not found
const user = await User.findOrFail(userId)      // throws if not found
const users = await User.all()                  // all non-deleted records

All static read methods respect soft deletes — soft-deleted records are excluded automatically.

Merging data

const user = await User.findOrFail(userId)
user.merge({ username: 'bob', role: 'admin' }).save()

Updating records

const user = await User.findOrFail(userId)
user.username = 'bob'
await user.save()   // UPDATE — auto-sets updatedAt

Deleting records

// Soft delete (if model supports it) - sets deletedAt timestamp
await user.delete()

// Hard delete - permanently removes from database, bypasses soft deletes
await user.forceDelete()

// Both methods accept a transaction parameter
await transaction(async (trx) => {
  await user.delete(trx)
  await user.forceDelete(trx)
})

For models with softDeletes = true, delete() sets the deletedAt timestamp. Use forceDelete() to permanently remove records regardless of soft delete settings.

Decorators

@primary

Marks the primary key property:

@primary
declare id: number

@ulid

Marks a field as a ULID that should be auto-generated on insert if not provided:

import { ulid } from '@strav/database'

@ulid
@primary
declare id: string  // Auto-generates ULID on insert

ULIDs (Universally Unique Lexicographically Sortable Identifiers) are 26-character strings that are:

• Lexicographically sortable (time-ordered)
• Cryptographically secure
• Compatible with distributed systems

@reference (belongs-to)

Defines a belongs-to relationship. The decorated property is excluded from persistence and can be loaded on demand:

@reference({ model: 'User', foreignKey: 'userId', targetPK: 'id' })
declare user: User

@associate (many-to-many)

Defines a many-to-many relationship through a pivot table:

@associate({
  through: 'team_user',
  foreignKey: 'team_id',
  otherKey: 'user_pid',
  model: 'User',
  targetPK: 'pid',
})
declare members: User[]

@cast (type casting)

Defines automatic type casting between database and application values. Transforms are applied during hydration (DB → model) and dehydration (model → DB).

import { cast } from '@strav/database'

// Bare — defaults to JSON parsing/serialization
@cast
declare state: CanvasState

// Named built-in type
@cast('boolean')
declare isActive: boolean

// Custom get/set functions
@cast({ get: (v) => new Money(v as number), set: (v: Money) => v.toCents() })
declare price: Money

@cast('bigint')
declare viewCount: number

@encrypt (field-level encryption)

import { encrypt } from '@strav/database'

@encrypt
declare ssn: string

Serialization & BigInt safety

Relationship loading

const profile = await Profile.findOrFail(1)
await profile.load('user', 'reviewer')   // loads @reference relations

const team = await Team.findOrFail(1)
await team.load('members')              // loads @associate relation

// Chaining
const user = await User.findOrFail(userId)
await user.load('teams')
console.log(user.teams)                 // Team[]

QueryBuilder

import { query, transaction } from '@strav/database'

Basic queries

const users = await query(User)
  .where('role', UserRole.Admin)
  .all()

const user = await query(User)
  .where('email', 'alice@example.com')
  .first()

const user = await query(User)
  .where('email', 'alice@example.com')
  .firstOrFail()  // throws if not found

Where clauses

query(User).where('age', '>=', 18)
query(User).whereIn('role', [UserRole.Admin, UserRole.Staff])
query(User).whereNotIn('status', ['banned'])
query(User).whereNull('deletedAt')
query(User).whereNotNull('username')
query(User).whereBetween('age', 18, 65)
query(User).whereRaw('"email" ILIKE $1', ['%@example.com'])

Joins

const results = await query(User)
  .innerJoin(Profile).on('User.pid', '=', 'Profile.userPid')
  .select('User.username', 'Profile.name')
  .all()

Ordering, pagination, grouping

query(User)
  .orderBy('createdAt', 'desc')
  .limit(20)
  .offset(40)
  .all()

query(User)
  .groupBy('role')
  .select('role', 'COUNT(*) AS count')
  .all()

query(User).distinct().all()

Soft delete control

query(User).withTrashed().all()    // include soft-deleted records
query(User).onlyTrashed().all()   // only soft-deleted records

Aggregates and inspection

const count = await query(User).where('role', 'admin').count()
const exists = await query(User).where('email', 'x@y.com').exists()

// Inspect generated SQL without executing
const { sql, params } = query(User).where('role', 'admin').toSQL()

Pagination

import { query, type PaginationResult } from '@strav/database'

const result: PaginationResult = await query(User)
  .where('organizationId', orgId)
  .orderBy('createdAt', 'desc')
  .paginate(page, 20)

result.data       // User[]
result.meta.page      // current page (1-based)
result.meta.perPage   // items per page
result.meta.total     // total matching records
result.meta.lastPage  // last available page number
result.meta.from      // 1-based index of first item (0 when empty)
result.meta.to        // 1-based index of last item (0 when empty)

Column resolution

Column references are automatically resolved:

• 'email' resolves to "user"."email" (primary table + snake_case).
• 'User.email' resolves to "user"."email" (explicit model reference).
• 'Profile.userId' resolves to "profile"."user_id" (cross-table with case conversion).

Transactions

import { query, transaction } from '@strav/database'

const user = await transaction(async (trx) => {
  const u = await User.create({ name: 'Alice', email: 'alice@example.com' }, trx)
  await Profile.create({ userId: u.id, bio: 'Hello' }, trx)
  return u
})

QueryBuilder operations also accept the transaction handle:

await transaction(async (trx) => {
  // Query and update within transaction
  const user = await query(User, trx).where('email', 'alice@example.com').firstOrFail()
  user.role = 'admin'
  await user.save(trx)

  // Bulk operations within same transaction
  await query(AuditLog, trx).where('userId', user.id).delete()
  await query(LoginAttempt, trx).where('userId', user.id).where('success', false).delete()
})