@roostjs/orm Guides

Task-oriented instructions for models, querying, relationships, factories, and testing.

How to define a model

Extend Model, set tableName, and configure optional flags for timestamps and soft deletes.

import { Model } from '@roostjs/orm';

export class Post extends Model {
  static tableName = 'posts';
  static primaryKey = 'id';    // default
  static timestamps = true;    // adds created_at / updated_at
  static softDeletes = false;  // set true to use deleted_at instead of DELETE
}

Access attributes via the attributes proxy: post.attributes.title. Do not add TypeScript instance properties — the proxy handles attribute access dynamically.

How to query with the QueryBuilder

Chain query methods on model static calls. All queries are lazy — execute with .all(), .first(), or .count().

import { Post } from '../models/Post';

// Simple equality
const published = await Post.where('status', 'published').all();

// Operators
const recent = await Post.where('created_at', '>', lastWeek).all();

// Multiple conditions (AND)
const userPosts = await Post
  .where('author_id', userId)
  .where('status', 'published')
  .orderBy('created_at', 'desc')
  .limit(10)
  .all();

// OR conditions
const visible = await Post
  .where('status', 'published')
  .orWhere('author_id', currentUserId)
  .all();

// IN clause
const archived = await Post.whereIn('status', ['archived', 'deleted']).all();

// NULL checks
const drafts = await Post.whereNull('published_at').all();

// Single record
const post = await Post.where('slug', 'hello-world').firstOrFail();

// Count
const total = await Post.where('author_id', userId).count();

How to define relationships between models

Declare relationships as static properties using the relation classes. Call load(instance) on the relation to fetch related records for a single model, or loadMany(instances) to batch-load for a collection.

import { Model, HasManyRelation, HasOneRelation } from '@roostjs/orm';
import { Post } from './Post';
import { Profile } from './Profile';

export class User extends Model {
  static tableName = 'users';

  // One user has many posts
  static posts = new HasManyRelation(Post, 'author_id', 'id');

  // One user has one profile
  static profile = new HasOneRelation(Profile, 'user_id', 'id');
}

import { Model, BelongsToRelation } from '@roostjs/orm';
import { User } from './User';

export class Post extends Model {
  static tableName = 'posts';

  // Each post belongs to one user
  static author = new BelongsToRelation(User, 'author_id', 'id');
}

// Usage
const post = await Post.findOrFail(1);
const author = await Post.author.load(post); // User | null

const user = await User.findOrFail(1);
const posts = await User.posts.load(user); // Post[]

How to use lifecycle hooks

Register hooks via Model.on(event, callback). Return false from a creating or updating hook to abort the operation.

import { Model } from '@roostjs/orm';

export class Post extends Model {
  static tableName = 'posts';
}

// Generate slug before insert
Post.on('creating', (post) => {
  if (!post.attributes.slug) {
    post.attributes.slug = post.attributes.title
      .toLowerCase()
      .replace(/[^a-z0-9]+/g, '-')
      .replace(/^-|-$/g, '');
  }
});

// Abort if title is missing
Post.on('creating', (post) => {
  if (!post.attributes.title?.trim()) {
    return false; // Aborts the insert
  }
});

// Log updates for audit trail
Post.on('updated', (post) => {
  console.log(`Post ${post.attributes.id} updated at ${new Date().toISOString()}`);
});

How to use factories in tests

Extend Factory, implement the definition() method, and use .count() / .state() to customise what gets created.

import { Factory } from '@roostjs/orm';
import { Post } from '../../src/models/Post';

export class PostFactory extends Factory<typeof Post> {
  definition() {
    return {
      title: 'Test Post ' + Math.random().toString(36).slice(2),
      slug: 'test-post-' + Math.random().toString(36).slice(2),
      body: 'Lorem ipsum dolor sit amet.',
      author_id: 1,
      status: 'published',
    };
  }
}

import { PostFactory } from '../database/factories/PostFactory';

const factory = new PostFactory(Post);

it('lists published posts', async () => {
  await factory.createOne();
  await factory.state((a) => ({ ...a, status: 'draft' })).createOne();

  const published = await Post.where('status', 'published').all();
  expect(published.length).toBe(1);
});

How to paginate query results

Call .paginate(page, perPage) on any query chain. The result includes the data and metadata needed for pagination UI.

const page = Number(new URL(request.url).searchParams.get('page') || '1');

const result = await Post
  .where('status', 'published')
  .orderBy('created_at', 'desc')
  .paginate(page, 20);

// result.data       — Post[] for this page
// result.total      — total matching records
// result.perPage    — 20
// result.currentPage — current page number
// result.lastPage   — total pages

return Response.json({
  posts: result.data.map((p) => p.attributes),
  pagination: {
    current: result.currentPage,
    total: result.lastPage,
    count: result.total,
  },
});

How to enable row-level tenant isolation

Row-level isolation keeps all tenants in a single database and filters every query by a tenantColumn. This is the default strategy and requires the least infrastructure.

1. Add `org_id` to your tables

ALTER TABLE posts ADD COLUMN org_id TEXT NOT NULL DEFAULT '';
CREATE INDEX idx_posts_org_id ON posts (org_id);

2. Set `tenantColumn` on the model

import { Model } from '@roostjs/orm';

export class Post extends Model {
  static tableName = 'posts';
  static tenantColumn = 'org_id';
}

3. Register TenantScopeMiddleware

TenantScopeMiddleware reads the org from the request, looks up its ID, and sets it on the shared TenantContext. All subsequent model queries in the same request are automatically scoped to that org.

import { TenantScopeMiddleware } from '@roostjs/orm';
import { OrgResolver } from '@roostjs/auth';
import { TenantContext } from '@roostjs/orm';
import { Organization } from '../models/Organization';

const resolver = new OrgResolver(['subdomain']);

export function makeTenantMiddleware(ctx: TenantContext) {
  return new TenantScopeMiddleware(
    resolver,
    async (slug) => Organization.withoutTenantScope(() =>
      Organization.where('slug', slug).first(),
    ),
    ctx,
  );
}

import { OrmServiceProvider } from '@roostjs/orm';

app.register(
  new OrmServiceProvider()
    .withModels([Post, Organization])
);

// Apply middleware before route handlers
app.useMiddleware(makeTenantMiddleware(app.container.resolve(TenantContext)));

4. Use the escape hatch for cross-tenant queries

Administrative routes that must see all tenants use withoutTenantScope:

const allPosts = await Post.withoutTenantScope(() => Post.all());

How to set up DB-per-tenant routing

DB-per-tenant gives each organization its own D1 database binding, with no data co-mingling at the row level.

1. Add per-tenant D1 bindings to wrangler.jsonc

{
  "d1_databases": [
    { "binding": "DB", "database_name": "shared", "database_id": "..." },
    { "binding": "DB_TENANT_ACME", "database_name": "tenant-acme", "database_id": "..." },
    { "binding": "DB_TENANT_GLOBEX", "database_name": "tenant-globex", "database_id": "..." }
  ]
}

2. Configure the database strategy

export default {
  tenantStrategy: 'database',
  tenantBindingPattern: 'DB_TENANT_{SLUG}',
  d1Binding: 'DB', // fallback for unauthenticated or admin requests
};

OrmServiceProvider reads tenantStrategy at boot. When set to 'database', it resolves the active org slug from TenantContext and uses TenantDatabaseResolver to pick the right D1 binding. If no per-tenant binding is found, it falls back to the shared DB.

3. Register providers in the correct order

TenantScopeMiddleware must run before OrmServiceProvider.boot() so the context is populated before the resolver runs. Ensure AuthServiceProvider (which populates the session) boots before OrmServiceProvider.

app.register(AuthServiceProvider);
app.register(
  new OrmServiceProvider().withModels([Post])
);

How to use D1 Sessions for read-your-writes consistency

D1 replicates across Cloudflare's network. A write committed on one replica may not be immediately visible on a different replica that serves the next read. Enable useSession to enforce that reads in the same request always see the most recent write.

export default {
  tenantStrategy: 'row',
  useSession: true,
};

With useSession: true, OrmServiceProvider wraps the D1 binding in a D1SessionHandle. The handle calls db.withSession() automatically once markWritten() has been called.

Call markWritten() after any write that must be visible to subsequent reads in the same request lifecycle:

import { D1SessionHandle } from '@roostjs/orm';

const sessionHandle = app.container.resolve(D1SessionHandle);

const post = await Post.create({ title, body, org_id: orgId });
sessionHandle.markWritten(); // subsequent reads will see this post

const updated = await Post.find(post.attributes.id); // guaranteed to see the new row

If withSession() is unavailable (e.g. local dev with Miniflare), the handle falls back to the plain binding and logs a warning.

How to write and run migrations

Create a migration file in database/migrations/ using roost make:migration, then run it with roost migrate.

roost make:migration create_posts_table
roost migrate

Migrations are raw SQL files executed against D1. See the migrations guide for the full column reference and rollback patterns.