ObjectQL Query Language

ObjectQL is HotCRM's type-safe query language that replaces traditional SQL with a more intuitive, flexible, and developer-friendly syntax.

Why ObjectQL?

Traditional ORMs and SQL have limitations:

Not type-safe: Easy to make mistakes
Complex joins: Difficult to work with relationships
Vendor lock-in: SQL dialects vary across databases
Hard to test: Requires database connection

ObjectQL solves these problems:

✅ Fully type-safe with TypeScript
✅ Intuitive syntax for relationships
✅ Database agnostic - works with any backend
✅ Easy to mock for testing

Basic Operations

Import the Database Client

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

Find Records

Find all records of an object:

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

Find with filters:

// Array-based filters (recommended)
const techAccounts = await db.find('Account', {
  filters: [['Industry', '=', 'Technology']]
});

// Multiple filters (AND)
const results = await db.find('Account', {
  filters: [
    ['Industry', '=', 'Technology'],
    ['AnnualRevenue', '>', 1000000]
  ]
});

Find One Record

const account = await db.findOne('Account', '123');
// Returns single record or null

Create Records

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

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

Update Records

await db.doc.update('Account', '123', {
  Phone: '+1-555-9999',
  Status: 'Active Customer'
});

Delete Records

await db.doc.delete('Account', '123');

Advanced Queries

Complex Filters

Operators

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

// Not equal
[['Status', '!=', 'Closed']]

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

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

// Not in list
[['Status', 'NOT IN', ['Closed Lost', 'Dead']]]

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

// Between
[['CreatedAt', 'BETWEEN', ['2026-01-01', '2026-12-31']]]

// Is null / Is not null
[['ParentAccountId', 'IS NULL', null]]
[['Email', 'IS NOT NULL', null]]

Combining Filters (AND)

// All conditions must match
const results = await db.find('Opportunity', {
  filters: [
    ['Stage', '!=', 'Closed Lost'],
    ['Amount', '>', 5000],
    ['CloseDate', '>=', '2026-01-01']
  ]
});

OR Conditions

// Using object-based syntax
const results = await db.query({
  object: 'Lead',
  filters: {
    $or: [
      { Status: 'New' },
      { Status: 'Working' }
    ]
  }
});

Sorting

// Sort ascending
const accounts = await db.find('Account', {
  filters: [],
  orderBy: { field: 'Name', direction: 'asc' }
});

// Sort descending
const opportunities = await db.find('Opportunity', {
  filters: [],
  orderBy: { field: 'Amount', direction: 'desc' }
});

Pagination

// Limit results
const first10 = await db.find('Lead', {
  filters: [],
  limit: 10
});

// Skip and limit (for pagination)
const page2 = await db.find('Lead', {
  filters: [],
  skip: 10,  // Skip first 10
  limit: 10  // Return next 10
});

Field Selection

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

Working with Relationships

// Get account with all related contacts
const account = await db.query({
  object: 'Account',
  fields: ['Name', 'Industry'],
  filters: { Id: '123' },
  related: {
    Contacts: {
      fields: ['FirstName', 'LastName', 'Email'],
      filters: { IsActive: true }
    }
  }
});

console.log(account.Name);
account.Contacts.forEach(contact => {
  console.log(contact.FirstName, contact.Email);
});

Multiple Relationships

const account = await db.query({
  object: 'Account',
  fields: ['Name'],
  filters: { Id: '123' },
  related: {
    Contacts: {
      fields: ['FirstName', 'LastName']
    },
    Opportunities: {
      fields: ['Name', 'Amount', 'Stage'],
      filters: { IsClosed: false }
    },
    Cases: {
      fields: ['Subject', 'Status', 'Priority']
    }
  }
});

Nested Relationships

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

Batch Operations

Bulk Create

const newContacts = await db.bulk.create('Contact', [
  {
    FirstName: 'John',
    LastName: 'Doe',
    Email: 'john.doe@acme.com'
  },
  {
    FirstName: 'Jane',
    LastName: 'Smith',
    Email: 'jane.smith@acme.com'
  }
]);

console.log(`Created ${newContacts.length} contacts`);

Bulk Update

await db.bulk.update('Lead', [
  { Id: 'lead_1', Status: 'Working' },
  { Id: 'lead_2', Status: 'Converted' },
  { Id: 'lead_3', Status: 'Dead' }
]);

Bulk Delete

await db.bulk.delete('Lead', ['lead_1', 'lead_2', 'lead_3']);

Aggregations

Count

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

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

Sum

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

Average

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

Min / Max

const minAmount = await db.min('Opportunity', 'Amount', {});
const maxAmount = await db.max('Opportunity', 'Amount', {});

Group By

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

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

Transactions

Execute multiple operations atomically:

await db.transaction(async (trx) => {
  // Create account
  const account = await trx.doc.create('Account', {
    Name: 'New Customer Inc.'
  });
  
  // Create contact
  const contact = await trx.doc.create('Contact', {
    FirstName: 'John',
    LastName: 'Doe',
    AccountId: account.Id
  });
  
  // Create opportunity
  await trx.doc.create('Opportunity', {
    Name: 'Initial Deal',
    AccountId: account.Id,
    Amount: 50000,
    Stage: 'Prospecting'
  });
  
  // All or nothing - if any operation fails, all are rolled back
});

Query Builder Pattern

For complex dynamic queries:

const queryBuilder = db.query('Opportunity')
  .fields(['Name', 'Amount', 'Stage'])
  .where('Amount', '>', 10000);

// Add conditions dynamically
if (stage) {
  queryBuilder.where('Stage', '=', stage);
}

if (accountId) {
  queryBuilder.where('AccountId', '=', accountId);
}

// Execute query
const results = await queryBuilder.execute();

Performance Optimization

Indexing

// Define indexes in metadata
{
  indexes: [
    { fields: ['Email'], unique: true },
    { fields: ['Industry', 'Status'] },
    { fields: ['CreatedAt'] }
  ]
}

Lazy Loading

// Don't load related records unless needed
const account = await db.findOne('Account', '123');

// Later, load relationships on demand
const contacts = await db.find('Contact', {
  filters: [['AccountId', '=', account.Id]]
});

Select Only Needed Fields

// ✅ Good - Select only what you need
const accounts = await db.query({
  object: 'Account',
  fields: ['Name', 'Industry']
});

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

Type Safety

ObjectQL is fully type-safe when used with TypeScript:

// Type inference
const account = await db.findOne('Account', '123');
// account is typed as Account | null

// Type checking
account.Name; // ✅ OK
account.InvalidField; // ❌ TypeScript error

// Filter type safety
await db.find('Account', {
  filters: [
    ['Name', '=', 'Acme'], // ✅ OK
    ['InvalidField', '=', 'value'] // ❌ TypeScript error
  ]
});

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');
  } else {
    console.error('Unexpected error:', error.message);
  }
}

Best Practices

1. Use Batch Operations for Multiple Records

// ✅ Good - Single batch operation
await db.bulk.create('Contact', contacts);

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

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

// ❌ Bad - Separate operations
await db.doc.create('Account', {...});
await db.doc.create('Contact', {...}); // Might fail after account created

3. Filter at Database Level

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

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

4. Use Pagination for Large Results

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

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

ObjectQL Query Language

On this page