FetchORM

A TypeScript ORM for building Dynamics365 FetchXML queries with type safety, fluent API, and validation.

🚀 Features

• Type-Safe Query Building: Full TypeScript support with type checking
• Fluent API: Chainable methods for query construction
• Comprehensive Validation: Built-in validation for attributes, operators, and query structure
• Advanced Filtering: Support for complex conditions, date ranges, and logical operators
• Entity Joins: Relationship mapping with nested query support
• Aggregation Functions: Built-in support for count, sum, avg, min, max operations
• Pagination & Performance: Top, page, and distinct clauses for queries
• Logging: Integrated Winston logging
• Error Handling: Error handling with descriptive messages

📦 Installation

npm install fetchorm

Quick Start

Define Entity Types

interface Account {
    name: string;
    emailaddress1: string;
    telephone1: string;
    revenue: number;
    accountid: string;
    statecode: number;
    createdon: Date;
    isactive: boolean;
    address1_city: string;
    address1_country: string;
}

interface Contact {
    firstname: string;
    lastname: string;
    emailaddress1: string;
    contactid: string;
    statecode: number;
    parentcustomerid: string;
    jobtitle: string;
    mobilephone: string;
    birthdate: Date;
}

Create Entities

import { BaseEntity } from 'fetchorm';

class AccountEntity extends BaseEntity<Account> {
    entityName = 'account';
}

class ContactEntity extends BaseEntity<Contact> {
    entityName = 'contact';
}

Build Queries

// Simple query
const account = new AccountEntity('account');
const query = account
    .select('name', 'emailaddress1', 'revenue')
    .where('statecode', 'eq', 0)
    .where('revenue', 'gt', 1000000)
    .orderBy('revenue', 'desc')
    .top(50)
    .build();

Examples

Basic Queries

// Select specific attributes
const query = account
    .select('name', 'emailaddress1', 'telephone1')
    .build();

// Add conditions
const query = account
    .select('name', 'revenue')
    .where('statecode', 'eq', 0)
    .where('revenue', 'gt', 100000)
    .build();

// Order results
const query = account
    .select('name', 'revenue')
    .orderBy('revenue', 'desc')
    .orderBy('name', 'asc')
    .build();

Filtering

// Date range filtering
const query = account
    .select('name', 'createdon')
    .where('createdon', 'on-or-after', '2024-01-01')
    .where('createdon', 'on-or-before', '2024-12-31')
    .build();

// Text search
const query = account
    .select('name', 'address1_city')
    .where('name', 'like', 'Acme')
    .where('address1_city', 'not-null')
    .build();

// Multiple conditions
const query = account
    .select('name', 'revenue', 'statecode')
    .where('statecode', 'eq', 0)
    .where('revenue', 'gt', 100000)
    .where('isactive', 'eq', true)
    .build();

Joins

// Join with related entities
const query = account
    .select('name', 'revenue')
    .join<Contact>('contact', 'accountid', 'parentcustomerid', 'PrimaryContact')
        .select('firstname', 'lastname', 'emailaddress1')
        .where('statecode', 'eq', 0)
        .end()
    .build();

// Multiple joins
const query = account
    .select('name', 'revenue')
    .join<Contact>('contact', 'accountid', 'parentcustomerid', 'AccountContacts')
        .select('firstname', 'lastname', 'jobtitle')
        .where('jobtitle', 'like', 'Manager')
        .end()
    .join<Opportunity>('opportunity', 'accountid', 'parentaccountid', 'AccountOpportunities')
        .select('name', 'revenue', 'probability')
        .where('probability', 'gt', 50)
        .end()
    .build();

Aggregation Functions

// Count records
const query = account
    .count('accountid', 'TotalAccounts')
    .where('statecode', 'eq', 0)
    .build();

// Sum and average
const query = account
    .select('address1_country')
    .sum('revenue', 'TotalRevenue')
    .avg('revenue', 'AverageRevenue')
    .count('accountid', 'AccountCount')
    .where('statecode', 'eq', 0)
    .groupBy('address1_country')
    .build();

// Multiple aggregates
const query = account
    .sum('revenue', 'TotalRevenue')
    .min('revenue', 'MinRevenue')
    .max('revenue', 'MaxRevenue')
    .where('revenue', 'gt', 0)
    .build();

Pagination and Performance

// Limit results
const query = account
    .select('name', 'revenue')
    .top(100)
    .build();

// Pagination
const query = account
    .select('name', 'revenue')
    .page(2, 25) // Page 2, 25 records per page
    .build();

// Distinct results
const query = account
    .select('address1_city')
    .distinct()
    .build();

Complex Scenarios

const query = account
    .select('name', 'revenue', 'address1_city', 'address1_country')
    .sum('revenue', 'TotalRevenue')
    .where('statecode', 'eq', 0)
    .where('revenue', 'gt', 100000)
    .where('isactive', 'eq', true)
    .join<Contact>('contact', 'accountid', 'parentcustomerid', 'PrimaryContact')
        .select('firstname', 'lastname', 'emailaddress1', 'jobtitle')
        .where('statecode', 'eq', 0)
        .where('jobtitle', 'like', 'Manager')
        .end()
    .join<Opportunity>('opportunity', 'accountid', 'parentaccountid', 'AccountOpportunities')
        .select('name', 'revenue', 'probability', 'estimatedclosedate')
        .where('statecode', 'eq', 0)
        .where('probability', 'ge', 75)
        .where('estimatedclosedate', 'this-month')
        .end()
    .orderBy('revenue', 'desc')
    .orderBy('name', 'asc')
    .distinct()
    .top(50)
    .build();

🔧 API

BaseEntity Methods

Selection

• select(...attributes: (keyof T)[]): this - Select specific attributes
• selectAs(attribute: keyof T, alias: string): this - Select attribute with alias

Filtering

• where(attribute: keyof T, operator: FilterOperator, value?: any): this - Add filter condition

Aggregation

• count(attribute?: keyof T, alias?: string): this - Count records
• sum(attribute: keyof T, alias?: string): this - Sum values
• avg(attribute: keyof T, alias?: string): this - Average values
• min(attribute: keyof T, alias?: string): this - Minimum value
• max(attribute: keyof T, alias?: string): this - Maximum value

Sorting

• orderBy(attribute: keyof T, order: OrderType = 'asc'): this - Order results

Performance

• top(count: number): this - Limit number of results
• page(pageNumber: number, pageSize: number = 50): this - Add pagination
• distinct(): this - Return distinct results
• groupBy(attribute: keyof T): this - Group by attribute

Joins

• join<U>(entityName: string, fromAttribute: keyof T, toAttribute: string, alias?: string, linkType?: 'inner' | 'outer'): JoinBuilder<U> - Join with related entity

Output

• build(): string - Generate FetchXML string

Filter Operators

Operator	Description	Example
eq	Equal to	where('statecode', 'eq', 0)
ne	Not equal to	where('statecode', 'ne', 1)
gt	Greater than	where('revenue', 'gt', 100000)
ge	Greater than or equal	where('revenue', 'ge', 100000)
lt	Less than	where('revenue', 'lt', 100000)
le	Less than or equal	where('revenue', 'le', 100000)
like	Contains	where('name', 'like', 'Acme')
not-like	Does not contain	where('name', 'not-like', 'Test')
in	In list	where('statecode', 'in', [0, 1])
not-in	Not in list	where('statecode', 'not-in', [2, 3])
null	Is null	where('emailaddress1', 'null')
not-null	Is not null	where('emailaddress1', 'not-null')
today	Today	where('createdon', 'today')
yesterday	Yesterday	where('createdon', 'yesterday')
this-month	This month	where('createdon', 'this-month')
last-month	Last month	where('createdon', 'last-month')
this-year	This year	where('createdon', 'this-year')

JoinBuilder Methods

• select(...attributes: (keyof T)[]): this
• selectAs(attribute: keyof T, alias: string): this
• where(attribute: keyof T, operator: FilterOperator, value?: any): this
• end(): BaseEntity - Return to parent entity builder

Configuration

Logging

FetchORM uses Winston for logging.

import { Logger } from 'fetchorm';

// Configure logging
Logger.getInstance().setLevel('debug'); // 'error', 'warn', 'info', 'debug'

Validation

All queries are validated automatically. Invalid queries will throw descriptive errors:

try {
    const query = account
        .select('invalid_attribute')
        .where('invalid_operator', 'invalid_op', 'value')
        .build();
} catch (error) {
    console.error('Validation error:', error.message);
}

License

This library is licensed under a CC BY-NC-ND 4.0 License.

• No commercial use allowed
• No modifications or forks
• Attribution required

For more details, see the LICENSE file.