crm-orm

Typed Node ORM for CRMs with Zod models, a $-operator where DSL, and a Salesforce dialect using an Ampersand transport. v0.1 focuses on typed read queries and SOQL rendering.

Status (v0.1)

• Salesforce: typed SELECT with projections, where DSL, order, limit/offset. SOQL renderer included.
• Core: model registry (Zod + provider field mapping), query AST + builder, $-DSL normalization.
• HubSpot: stubs only.

Planned next: pagination helpers (cursor/unbounded), IN chunking, CRUD + Composite Graph, telemetry, error mapping polish, HubSpot search builder.

Requirements

• Node 18+ (package enforces Node >=20)
• TypeScript 5+
• Zod for model schemas

Install

npm install crm-orm zod

Quick start

import { createORM, sf, AmpersandAdapter } from 'crm-orm';
import type { AmpersandTransportRequest, AmpersandTransportResponse } from 'crm-orm';
import { z } from 'zod';

// Implement or wrap your Ampersand client (must have a .request method)
class AmpersandPassthroughAPI {
  async request<T = unknown>(req: AmpersandTransportRequest): Promise<AmpersandTransportResponse<T>> {
    // delegate to your Ampersand instance
    throw new Error('wire me to Ampersand');
  }
}

const transport = new AmpersandAdapter(new AmpersandPassthroughAPI());

const orm = createORM({
  transport,
  salesforce: { version: '59.0', providerName: 'salesforce' },
});

// Define a Salesforce model (Zod schema + provider field mapping)
const Opportunity = sf.model({
  name: 'Opportunity',
  object: 'Opportunity',
  schema: z.object({
    id: z.string(),
    name: z.string(),
    amount: z.number().nullable(),
    createdDate: z.string(),
  }),
  fields: {
    id: { path: 'Id' },
    name: { path: 'Name' },
    amount: { path: 'Amount' },
    createdDate: { path: 'CreatedDate' },
  },
});

// Typed query
const opps = await orm
  .from(Opportunity)
  .select({ id: true, name: true, amount: true, createdDate: true })
  .where({ name: { $like: 'Acme%' }, amount: { $gte: 25000 } })
  .orderBy({ createdDate: 'desc' })
  .limit(100)
  .getMany();

$ Where DSL

Each model field is type-aware. Supported operators (by primitive):

• Strings: $eq, $ne, $in, $nin, $like, $startsWith, $endsWith, $exists
• Numbers: $eq, $ne, $in, $nin, $gt, $gte, $lt, $lte, $between, $exists
• Booleans: $eq (or bare boolean), $exists
• Dates/strings-as-dates: $gt, $gte, $lt, $lte, $between, $exists
• Nulls: field: null => IS NULL; { field: { $ne: null } } => IS NOT NULL
• Logic: $and, $or, $not
• Raw escape: $raw: string (SOQL passthrough)

Examples:

// range
.where({ createdDate: { $between: ['2024-01-01', '2024-12-31'] } })

// nulls / exists
.where({ ownerId: null, amount: { $ne: null } })

// OR / AND
.where({ $or: [ { name: { $startsWith: 'Strategic ' } }, { amount: { $gte: 100000 } } ] })

// raw
.where({ $raw: "CALENDAR_YEAR(CreatedDate) = 2024" })

Note: IN chunking and nested relation filters are planned for v0.2+. For relation paths today, map the related field into your model via fields (e.g. Account.Name) and filter that mapped key.

Sorting, limit/offset

.orderBy({ createdDate: 'desc', name: 'asc' })
.offset(0)
.limit(200)

Cursor/unbounded pagination helpers will arrive in v0.2.

What gets sent to Salesforce (SOQL)

The ORM renders SOQL using your mapped fields.path and normalized filters.

• Input:

.select({ id: true, name: true, amount: true, createdDate: true })
.where({ name: { $like: 'Acme%' }, amount: { $gte: 25000 } })
.orderBy({ createdDate: 'desc' })
.limit(100)

• SOQL:

SELECT Id, Name, Amount, CreatedDate FROM Opportunity WHERE (Name LIKE 'Acme%' AND Amount >= 25000) ORDER BY CreatedDate DESC LIMIT 100

• Relation path + null/exists:

// fields: { ownerId: 'OwnerId', amount: 'Amount', accountName: 'Account.Name' }
.select({ ownerId: true, amount: true, accountName: true })
.where({ ownerId: null, amount: { $ne: null } })

SELECT OwnerId, Amount, Account.Name FROM Opportunity WHERE (OwnerId = null AND Amount != null)

• IN / NOT IN:

.where({ id: { $in: ['1', '2', '3'] } })
.where({ id: { $nin: ['x'] } })

SELECT Id, Name, Amount, CreatedDate FROM Opportunity WHERE Id IN ('1', '2', '3')
SELECT Id, Name, Amount, CreatedDate FROM Opportunity WHERE Id NOT IN ('x')

Ampersand transport

All HTTP calls go through your Ampersand instance via the adapter:

import { AmpersandAdapter } from 'crm-orm';

const transport = new AmpersandAdapter({
  async request(req) {
    // forward to Ampersand and return { status, headers, data }
    return { status: 200, headers: {}, data: {} };
  },
});

Interface:

interface AmpersandTransportRequest {
  providerName: 'salesforce' | 'hubspot' | (string & {});
  method: 'GET' | 'POST' | 'PUT' | 'PATCH' | 'DELETE';
  url: string;
  headers?: Record<string, string>;
  query?: Record<string, string | number | boolean | undefined>;
  body?: unknown;
  metadata?: { requestName?: string; userId?: string; organizationId?: string };
}

API surface (v0.1)

// Create ORM
createORM({ transport, salesforce?: { version?: string; providerName?: 'salesforce' } })

// Define models
sf.model({ name, object, schema, fields, relations? })
hs.model(...) // stub

// Query
orm.from(Model)
  .select({ ... })
  .where({ ... })
  .orderBy({ ... })
  .limit(n)
  .offset(n)
  .getMany()
  .getOne()

Roadmap

• M2: Pagination helpers (cursor/unbounded), IN chunking with concurrency.
• M3: Typed CRUD (insert/update/delete) + Composite Graph batching and references.
• M4: Telemetry hooks and normalized error mapping; introspect validators.
• M5: HubSpot search builder MVP.

Development

npm run build
npm test

License

MIT