ObjectQL API Reference

ObjectQL is HotCRM's type-safe query language for data operations. This reference covers all available methods and syntax.

Database Client

Import the database client:

import { db } from '@hotcrm/core';

Query Operations

db.find()

Find records using array-based filter syntax.

Signature:

async find<T>(
  objectName: string,
  options: ObjectQLFindOptions
): Promise<T[]>

Parameters:

objectName: Object name (e.g., 'Account', 'Opportunity')
options.filters: Array of filter conditions
options.fields: Array of field names to return
options.sort: Sort order
options.limit: Maximum number of records
options.skip: Number of records to skip (pagination)

Examples:

// Find all accounts
const accounts = await db.find('Account', {});

// Find with filters
const techAccounts = await db.find('Account', {
  filters: [['Industry', '=', 'Technology']]
});

// Multiple filters (AND)
const results = await db.find('Opportunity', {
  filters: [
    ['Stage', '!=', 'Closed Lost'],
    ['Amount', '>', 10000],
    ['CloseDate', '>=', '2026-01-01']
  ]
});

// Select specific fields
const accounts = await db.find('Account', {
  fields: ['Name', 'Industry', 'Phone'],
  filters: [['Status', '=', 'Active']]
});

// Sort results
const opportunities = await db.find('Opportunity', {
  filters: [],
  sort: [['Amount', 'desc']],
  limit: 10
});

// Pagination
const page2 = await db.find('Lead', {
  filters: [['Status', '=', 'New']],
  skip: 20,
  limit: 20
});

db.query()

Query records using object-based filter syntax with relationship support.

Signature:

async query<T>(
  query: ObjectQLQuery
): Promise<ObjectQLResult<T>>

Parameters:

query.object: Object name
query.fields: Array of field names
query.filters: Object-based filter conditions
query.related: Related object queries
query.orderBy: Sort configuration
query.limit: Maximum records
query.offset: Skip records

Examples:

// Basic query
const result = await db.query({
  object: 'Account',
  fields: ['Name', 'Industry'],
  filters: {
    Industry: { $in: ['Technology', 'Finance'] },
    AnnualRevenue: { $gt: 1000000 }
  },
  limit: 50
});

console.log(result.records);
console.log(result.totalCount);
console.log(result.hasMore);

// Query with relationships
const account = await db.query({
  object: 'Account',
  fields: ['Name', 'Industry'],
  filters: { Id: 'acc_123' },
  related: {
    Opportunities: {
      fields: ['Name', 'Amount', 'Stage'],
      filters: {
        Stage: { $ne: 'Closed Lost' }
      }
    },
    Contacts: {
      fields: ['FirstName', 'LastName', 'Email']
    }
  }
});

// Nested relationships
const opportunity = await db.query({
  object: 'Opportunity',
  fields: ['Name', 'Amount'],
  filters: { Id: 'opp_123' },
  related: {
    Account: {
      fields: ['Name'],
      related: {
        Contacts: {
          fields: ['FirstName', 'LastName']
        }
      }
    }
  }
});

Document Operations

db.doc.create()

Create a new record.

Signature:

async create(
  objectName: string,
  data: Record<string, any>
): Promise<any>

Examples:

// Create account
const account = await db.doc.create('Account', {
  Name: 'Acme Corporation',
  Industry: 'Technology',
  AnnualRevenue: 5000000,
  Phone: '+1-555-0123',
  Website: 'https://acme.com'
});

console.log('Created account:', account.Id);

// Create with lookup relationship
const contact = await db.doc.create('Contact', {
  FirstName: 'John',
  LastName: 'Doe',
  Email: 'john.doe@acme.com',
  AccountId: account.Id  // Lookup relationship
});

// Create with default values
const opportunity = await db.doc.create('Opportunity', {
  Name: 'Enterprise Deal',
  AccountId: account.Id,
  Stage: 'Prospecting',     // Default if not provided
  CloseDate: '2026-12-31',
  Amount: 100000
});

db.doc.update()

Update an existing record.

Signature:

async update(
  objectName: string,
  id: string,
  data: Record<string, any>
): Promise<void>

Examples:

// Update single field
await db.doc.update('Account', accountId, {
  Phone: '+1-555-9999'
});

// Update multiple fields
await db.doc.update('Opportunity', oppId, {
  Stage: 'Negotiation',
  Amount: 120000,
  NextStep: 'Schedule contract review'
});

// Update with null (clear field)
await db.doc.update('Lead', leadId, {
  ConvertedDate: null
});

db.doc.delete()

Delete a record.

Signature:

async delete(
  objectName: string,
  id: string
): Promise<void>

Examples:

// Delete record
await db.doc.delete('Lead', leadId);

// Delete with error handling
try {
  await db.doc.delete('Account', accountId);
} catch (error) {
  if (error.code === 'REFERENCE_ERROR') {
    console.error('Cannot delete: Account has related records');
  }
}

db.doc.get()

Get a single record by ID.

Signature:

async get(
  objectName: string,
  id: string,
  options?: { fields?: string[] }
): Promise<any>

Examples:

// Get all fields
const account = await db.doc.get('Account', accountId);

// Get specific fields only
const account = await db.doc.get('Account', accountId, {
  fields: ['Name', 'Industry', 'Phone']
});

// Get with error handling
const account = await db.doc.get('Account', accountId);
if (!account) {
  console.error('Account not found');
}

Batch Operations

db.batch.create()

Create multiple records in a single transaction.

Signature:

async create(
  objectName: string,
  records: Record<string, any>[]
): Promise<any[]>

Examples:

// Create multiple contacts
const contacts = await db.batch.create('Contact', [
  {
    FirstName: 'John',
    LastName: 'Doe',
    Email: 'john@example.com'
  },
  {
    FirstName: 'Jane',
    LastName: 'Smith',
    Email: 'jane@example.com'
  },
  {
    FirstName: 'Bob',
    LastName: 'Johnson',
    Email: 'bob@example.com'
  }
]);

console.log(`Created ${contacts.length} contacts`);
contacts.forEach(c => console.log(c.Id));

db.batch.update()

Update multiple records in a single transaction.

Signature:

async update(
  objectName: string,
  updates: Array<{ id: string; data: Record<string, any> }>
): Promise<void>

Examples:

// Update multiple leads
await db.batch.update('Lead', [
  { id: 'lead_1', data: { Status: 'Working' } },
  { id: 'lead_2', data: { Status: 'Converted' } },
  { id: 'lead_3', data: { Status: 'Nurturing' } }
]);

// Update with different fields
await db.batch.update('Opportunity', [
  { id: 'opp_1', data: { Stage: 'Closed Won', CloseDate: '2026-01-15' } },
  { id: 'opp_2', data: { Amount: 50000 } },
  { id: 'opp_3', data: { NextStep: 'Schedule demo' } }
]);

db.batch.delete()

Delete multiple records in a single transaction.

Signature:

async delete(
  objectName: string,
  ids: string[]
): Promise<void>

Examples:

// Delete multiple records
await db.batch.delete('Lead', [
  'lead_1',
  'lead_2',
  'lead_3'
]);

// Delete with confirmation
const idsToDelete = ['acc_1', 'acc_2'];
if (confirm(`Delete ${idsToDelete.length} accounts?`)) {
  await db.batch.delete('Account', idsToDelete);
}

Aggregation Operations

db.count()

Count records matching filters.

Signature:

async count(
  objectName: string,
  options: { filters?: ObjectQLArrayFilter }
): Promise<number>

Examples:

// Count all records
const total = await db.count('Account', {});

// Count with filter
const techCount = await db.count('Account', {
  filters: [['Industry', '=', 'Technology']]
});

// Count multiple conditions
const activeCount = await db.count('Opportunity', {
  filters: [
    ['IsClosed', '=', false],
    ['Amount', '>', 10000]
  ]
});

console.log(`${activeCount} active opportunities over $10K`);

db.sum()

Calculate sum of a numeric field.

Signature:

async sum(
  objectName: string,
  field: string,
  options: { filters?: ObjectQLArrayFilter }
): Promise<number>

Examples:

// Total revenue
const totalRevenue = await db.sum('Opportunity', 'Amount', {
  filters: [['Stage', '=', 'Closed Won']]
});

console.log(`Total revenue: $${totalRevenue.toLocaleString()}`);

// Pipeline value
const pipelineValue = await db.sum('Opportunity', 'Amount', {
  filters: [['IsClosed', '=', false]]
});

db.avg()

Calculate average of a numeric field.

Signature:

async avg(
  objectName: string,
  field: string,
  options: { filters?: ObjectQLArrayFilter }
): Promise<number>

Examples:

// Average deal size
const avgDealSize = await db.avg('Opportunity', 'Amount', {
  filters: [['Stage', '=', 'Closed Won']]
});

console.log(`Average deal size: $${avgDealSize.toLocaleString()}`);

// Average by industry
const avgRevenue = await db.avg('Account', 'AnnualRevenue', {
  filters: [['Industry', '=', 'Technology']]
});

db.min() / db.max()

Get minimum or maximum value of a field.

Signature:

async min(
  objectName: string,
  field: string,
  options: { filters?: ObjectQLArrayFilter }
): Promise<number>

async max(
  objectName: string,
  field: string,
  options: { filters?: ObjectQLArrayFilter }
): Promise<number>

Examples:

// Smallest and largest deals
const minDeal = await db.min('Opportunity', 'Amount', {
  filters: [['Stage', '=', 'Closed Won']]
});

const maxDeal = await db.max('Opportunity', 'Amount', {
  filters: [['Stage', '=', 'Closed Won']]
});

console.log(`Deal range: $${minDeal} - $${maxDeal}`);

db.groupBy()

Group records and perform aggregations.

Signature:

async groupBy(
  objectName: string,
  options: {
    groupBy: string;
    aggregations: Record<string, { function: string; field: string }>;
    filters?: ObjectQLArrayFilter;
  }
): Promise<any[]>

Examples:

// Count by stage
const stageCount = await db.groupBy('Opportunity', {
  groupBy: 'Stage',
  aggregations: {
    count: { function: 'COUNT', field: 'Id' },
    totalAmount: { function: 'SUM', field: 'Amount' },
    avgAmount: { function: 'AVG', field: 'Amount' }
  }
});

stageCount.forEach(group => {
  console.log(`${group.Stage}: ${group.count} opps, $${group.totalAmount}`);
});

// Revenue by month
const monthlyRevenue = await db.groupBy('Opportunity', {
  groupBy: 'CloseDate.month',
  aggregations: {
    revenue: { function: 'SUM', field: 'Amount' },
    deals: { function: 'COUNT', field: 'Id' }
  },
  filters: [
    ['Stage', '=', 'Closed Won'],
    ['CloseDate', 'this_year']
  ]
});

Transaction Operations

db.transaction()

Execute multiple operations atomically.

Signature:

async transaction(
  callback: (trx: ObjectQLEngine) => Promise<void>
): Promise<void>

Examples:

// Create related records atomically
await db.transaction(async (trx) => {
  // Create account
  const account = await trx.doc.create('Account', {
    Name: 'New Customer Inc.',
    Industry: 'Technology'
  });
  
  // Create contact
  const contact = await trx.doc.create('Contact', {
    FirstName: 'John',
    LastName: 'Doe',
    AccountId: account.Id,
    Email: 'john@newcustomer.com'
  });
  
  // Create opportunity
  await trx.doc.create('Opportunity', {
    Name: 'Initial Deal',
    AccountId: account.Id,
    ContactId: contact.Id,
    Amount: 50000,
    Stage: 'Prospecting',
    CloseDate: '2026-12-31'
  });
  
  // If any operation fails, all are rolled back
});

// Transfer ownership atomically
await db.transaction(async (trx) => {
  const newOwnerId = 'user_456';
  
  // Update account
  await trx.doc.update('Account', accountId, {
    OwnerId: newOwnerId
  });
  
  // Update all related opportunities
  const opps = await trx.find('Opportunity', {
    filters: [
      ['AccountId', '=', accountId],
      ['IsClosed', '=', false]
    ]
  });
  
  for (const opp of opps) {
    await trx.doc.update('Opportunity', opp.Id, {
      OwnerId: newOwnerId
    });
  }
});

Filter Operators

Comparison Operators

// Equal
[['Status', '=', 'Active']]

// Not equal
[['Status', '!=', 'Closed']]
[['Status', '<>', 'Closed']]  // Alternative syntax

// Greater than / Less than
[['Amount', '>', 10000]]
[['Amount', '>=', 10000]]
[['Amount', '<', 100000]]
[['Amount', '<=', 100000]]

// Between
[['Amount', 'between', [10000, 50000]]]

List Operators

// In list
[['Industry', 'in', ['Technology', 'Finance', 'Healthcare']]]

// Not in list
[['Status', 'not in', ['Closed Won', 'Closed Lost']]]

String Operators

// Like (contains)
[['Name', 'like', '%Corp%']]

// Starts with
[['Name', 'like', 'Acme%']]

// Ends with
[['Name', 'like', '%Inc']]

// Case-insensitive
[['Email', 'ilike', '%@ACME.COM']]

Null Operators

// Is null
[['ParentAccountId', 'is null']]
[['Email', '=', null]]

// Is not null
[['Email', 'is not null']]
[['Email', '!=', null]]

Date Operators

// Specific date
[['CloseDate', '=', '2026-01-15']]

// Date range
[['CloseDate', 'between', ['2026-01-01', '2026-12-31']]]

// Relative dates
[['CreatedDate', 'last_n_days', 7]]
[['CreatedDate', 'next_n_days', 30]]

// Date literals
[['CloseDate', 'today']]
[['CloseDate', 'yesterday']]
[['CloseDate', 'tomorrow']]
[['CloseDate', 'this_week']]
[['CloseDate', 'this_month']]
[['CloseDate', 'this_quarter']]
[['CloseDate', 'this_year']]
[['CloseDate', 'last_week']]
[['CloseDate', 'last_month']]
[['CloseDate', 'last_quarter']]
[['CloseDate', 'last_year']]

Special Variables

// Current user
[['OwnerId', '=', '$currentUser']]

// Current date/time
[['CreatedDate', '=', '$now']]
[['CloseDate', '=', '$today']]

Sort Options

// Single field ascending
sort: [['Name', 'asc']]

// Single field descending
sort: [['Amount', 'desc']]

// Multiple fields
sort: [
  ['Priority', 'desc'],
  ['DueDate', 'asc']
]

// Shorthand string syntax
sort: 'Name'           // asc by default
sort: '-Amount'        // desc with minus prefix

Type Definitions

ObjectQLFindOptions

interface ObjectQLFindOptions {
  filters?: ObjectQLArrayFilter;
  fields?: string[];
  sort?: string | string[];
  limit?: number;
  skip?: number;
}

ObjectQLArrayFilter

type ObjectQLArrayFilter = [string, string, any][];

// Examples
[['Status', '=', 'Active']]
[['Amount', '>', 10000], ['Stage', '!=', 'Lost']]

ObjectQLQuery

interface ObjectQLQuery {
  object: string;
  fields?: string[];
  filters?: ObjectQLFilter;
  related?: {
    [relationName: string]: {
      fields?: string[];
      filters?: ObjectQLFilter;
      limit?: number;
    };
  };
  orderBy?: string | { field: string; direction: 'asc' | 'desc' }[];
  limit?: number;
  offset?: number;
}

ObjectQLResult

interface ObjectQLResult<T = any> {
  records: T[];
  totalCount: number;
  hasMore: boolean;
}

Error Handling

try {
  const account = await db.doc.create('Account', {
    Name: 'Test Account'
  });
} catch (error) {
  if (error.code === 'VALIDATION_ERROR') {
    console.error('Validation failed:', error.details);
  } else if (error.code === 'DUPLICATE_ERROR') {
    console.error('Record already exists:', error.field);
  } else if (error.code === 'PERMISSION_ERROR') {
    console.error('Access denied:', error.message);
  } else if (error.code === 'NOT_FOUND') {
    console.error('Record not found');
  } else {
    console.error('Unexpected error:', error.message);
  }
}

Error Codes

VALIDATION_ERROR: Field validation failed
DUPLICATE_ERROR: Unique constraint violation
PERMISSION_ERROR: Insufficient permissions
NOT_FOUND: Record not found
REFERENCE_ERROR: Foreign key constraint violation
REQUIRED_ERROR: Required field missing

Performance Tips

1. Select Only Needed Fields

// ✅ Good
const accounts = await db.find('Account', {
  fields: ['Name', 'Industry']
});

// ❌ Bad
const accounts = await db.find('Account', {});

2. Use Batch Operations

// ✅ Good
await db.batch.create('Contact', contacts);

// ❌ Bad
for (const contact of contacts) {
  await db.doc.create('Contact', contact);
}

3. Filter at Database Level

// ✅ Good
const active = await db.find('Account', {
  filters: [['Status', '=', 'Active']]
});

// ❌ Bad
const all = await db.find('Account', {});
const active = all.filter(a => a.Status === 'Active');

4. Use Pagination

// ✅ Good
const page = await db.find('Lead', {
  limit: 50,
  skip: pageNumber * 50
});

// ❌ Bad
const all = await db.find('Lead', {});

// ✅ Good
await db.transaction(async (trx) => {
  await trx.doc.create('Account', {...});
  await trx.doc.create('Contact', {...});
});

// ❌ Bad
await db.doc.create('Account', {...});
await db.doc.create('Contact', {...});

Next Steps

Creating Objects - Define objects to query
Business Logic - Use ObjectQL in hooks
REST API - HTTP endpoints

ObjectQL API Reference

On this page