Open SOQL

Table of contents

⚙️ Install

npm install open-soql

🚀 Getting started

Set up the resolvers

import { build } from 'open-soql/modules/builder';
import { staticJsonResolverBuilder,
 staticCsvResolverBuilder,
 passThroughResolverBuilder } from 'open-soql/modules/resolvers';
// See `src/types.ts` > `QueryBuilderInfo`
const { compile, soql,
 insert, update, remove, touch, notifyRemoved,
 transaction,
 subscribe, unsubscribe, unsubscribeAllBySubscriber } = build({
 functions: [{ // optional: For defining custom functions.
 type: 'scalar',
 name: 'string',
 fn: (ctx, args, records) => {
 return String(args[0]);
 },
 }, {
 type: 'scalar',
 name: 'number',
 fn: (ctx, args, records) => {
 return Number(args[0]);
 },
 }, {
 type: 'immediate-scalar',
 name: 'cast_string',
 fn: (ctx, args) => {
 return String(args[0]);
 },
 }, {
 type: 'immediate-scalar',
 name: 'cast_number',
 fn: (ctx, args) => {
 return Number(args[0]);
 },
 }, {
 type: 'aggregate',
 name: 'count_twice',
 fn: (ctx, args, records) => {
 return records.length * 2;
 },
 }],
 events: { // optional: For resolving transaction and N+1 query problem.
 beginTransaction: (evt) => Promise.resolve(),
 endTransaction: (evt, err) => Promise.resolve(),
 beginExecute: (evt) => Promise.resolve(),
 endExecute: (evt, err) => Promise.resolve(),
 beforeMasterSubQueries: (evt) => Promise.resolve(),
 afterMasterSubQueries: (evt) => Promise.resolve(),
 beforeDetailSubQueries: (evt) => Promise.resolve(),
 afterDetailSubQueries: (evt) => Promise.resolve(),
 },
 resolvers: {
 query: {
 Account: (fields, conditions, limit, offset, ctx) => {
 // Fetch the `Account` object data.
 ctx.resolverCapabilities.filtering = true; // True if the resolver can filter records.
 return Promise.resolve([{ ... }, ... ]);
 },
 Contact: (fields, conditions, limit, offset, ctx) => {
 // Fetch the `Contact` object data.
 // `ctx.parent` is a parent record.
 ctx.resolverCapabilities.filtering = true; // True if the resolver can filter records.
 return Promise.resolve([{ ... }, ... ]);
 },
 Opportunity: (fields, conditions, limit, offset, ctx) => {
 // Fetch the `Opportunity` object data.
 // `ctx.parent` is a parent record.
 ctx.resolverCapabilities.filtering = true; // True if the resolver can filter records.
 return Promise.resolve([{ ... }, ... ]);
 },
 Event: staticCsvResolverBuilder( // (CSV string)
 // "staticJsonResolverBuilder"(JSON string) and
 // "passThroughResolverBuilder"(array of object)
 // are also available.
 'Event', () => Promise.resolve(`
 Id, Subject, WhatId
 Event/1, Email, Account/1
 Event/2, Phone, Contact/1
 `)
 ),
 },
 insert: { // optional: For DML
 Contact: (records, ctx) => {
 return Promise.resolve(records.map((x, i) => ({...x, id: `Contact/${i}`})));
 },
 },
 update: { // optional: For DML
 Contact: (records, ctx) => {
 return Promise.resolve(records);
 },
 },
 remove: { // optional: For DML
 Contact: (records, ctx) => {
 return Promise.resolve();
 },
 },
 },
 relationships: { // optional: For relationship query
 /**
 * detailResolverName
 * e.g.: Contact: { account: 'Account' }
 * Contact: { account: { resolver: 'Account', id: 'accountId' }}
 *
 * NOTE: 'Account' is `masterResolverName`.
 * 'account' is `masterObjectFieldName`.
 * 'accountId' is `masterIdName`. (foreign key field name)
 * `Contact (resolver) -> account (field name)` direction is `Detail to Master`.
 * 
 * masterResolverName
 * e.g.: Account: { contacts: ['Contact'] }
 * Account: { contacts: ['Contact', 'account'] }
 *
 * NOTE: 'contacts' is details relationship name.
 * 'Contact' is `detailResolverName` and 'account' is Contact's `masterObjectFieldName`.
 * Default masterObjectFieldName is `MasterResolverName`.
 * `Account (resolver) -> contacts (relationship name)` direction is `Master to Details`.
 */
 Account: {
 Contacts: ['Contact'], // master->details relationship
 Opportunities: ['Opportunity', 'Account'], // master->details relationship
 }, // (Explicitly specify relationship item)
 Contact: {
 Account: 'Account', // detail->master relationship
 },
 Opportunity: {
 Account: 'Account', // detail->master relationship
 },
 Event: {
 Account: { resolver: 'Account', id: 'WhatId' }, // detail->master relationship
 Contact: { resolver: 'Contact', id: 'WhatId' }, // (Explicitly specify Id item)
 Opportunity: { resolver: 'Opportunity', id: 'WhatId' },
 },
 },
});

Query

const result = await soql<Partial<Contact>>`
 Select
 acc.id aid
 , acc.Region reg
 , acc.Category cat
 , (
 Select id, Name
 from acc.Opportunities
 where Amount > ${10000}
 -- It can be number, string, boolean or null.
 order by DueDate desc limit 5
 )
 , string(id)
 , string(foo)
 , string(reg)
 , string(acc.qux)
 from Contact con, con.Account acc
 where
 (
 number(acc.numOfEmployees) = 5
 and acc.created > ${{type: 'date', value: '2020-01-01'}}
 -- It can be 'date' or 'datetime'.
 and acc.updated > 2020年01月01日
 ) or (
 acc.foo = 1
 and acc.bar = 2
 and acc.baz = 2
 ) or not (
 acc.qux = 1
 and acc.quux = 2
 and acc.corge in (Select id from Event)
 )
 order by aid, reg, cat
 limit 10 offset 2
 -- line comment
 /* block comment */
`;
// result is [{...}, ...]

Pre-compiled query

const query = compile`Select id from account where id > ${'100'}`;
const result = await query.execute<Partial<Account>>();

const query = compile`Select id from account where id > :idGreaterThan`;
const result = await query.execute<Partial<Account>>({ idGreaterThan: '100' });

Aggregate

const aggregationResult = await soql<ContactAgg>`
 Select
 count()
 , count(id) cnt
 , sum(bar) sum
 , cast_string(12345) str
 , cast_number('2234') num
 from
 Contact
 where
 foo > ''
 group by Region
 having count(id) > 0
`;
// aggregationResult is [{...}, ...]

DML (bulk)

const inserted = await insert('Contact', [{
 Name: 'foo',
}]);
// inserted is [{ Id: 'Contact/1', Name: 'foo' }]
const updated = await update('Contact', inserted);
// updated is [{ Id: 'Contact/1', Name: 'foo' }]
await remove('Contact', updated);
const selected = await soql<Partial<Contact>>`Select Id, Name from Contact`;
const updated2 = await update('Contact', selected);

DML (single record)

const inserted = await insert('Contact', {
 Name: 'foo',
});
// inserted is { Id: 'Contact/1', Name: 'foo' }
const updated = await update('Contact', inserted);
// updated is { Id: 'Contact/1', Name: 'foo' }
await remove('Contact', updated);

Execute commands within a transaction

await transaction(async (commands, tr) => {
 const { compile, soql, insert, update, remove, touch, notifyRemoved } = commands;
 const inserted = await insert('Contact', [{
 Name: 'foo',
 }]);
 const selected = await soql<Partial<Contact>>`Select Id, Name from Contact`;
 const updated = await update('Contact', selected);
 await remove('Contact', updated);
 const query = compile`Select id from account where id > ${'100'}`;
 const selectedAccounts = await query.execute<Partial<Account>>();
});

Publish / Subscribe messaging

Without a transaction

const subscriber: Subscriber = ({on, resolver, id}) => {
 switch (on) {
 case 'insert':
 ...
 break;
 case 'update':
 ...
 break;
 case 'remove':
 ...
 break;
 }
};
// Subscribe to all changes of the resolver `Contact`.
subscribe('Contact', null, subscriber);
// Subscribe to all changes of the record `Contact(id='Contact/z2')`.
subscribe('Contact', 'Contact/z2', subscriber);
await update('Contact', [ ... ]); // or insert(), remove(), touch()
// (Fire events on next event loop.)
await update('Contact', [ ... ]);
// (Fire events on next event loop.)
await update('Contact', [ ... ]);
// (Fire events on next event loop.)
...
// Unsubscribe to all changes of the resolver `Contact`.
unsubscribe('Contact', null, subscriber);
// Unsubscribe to all changes of the record `Contact(id='Contact/z2')`.
unsubscribe('Contact', 'Contact/z2', subscriber);

Within a transaction

const subscriber: Subscriber = ({on, resolver, id}) => { ... };
// Subscribe to all changes of the resolver `Contact`.
subscribe('Contact', null, subscriber);
// Subscribe to all changes of the record `Contact(id='Contact/z2')`.
subscribe('Contact', 'Contact/z2', subscriber);
await transaction(async (commands, tr) => {
 const { compile, soql, insert, update, remove, touch } = commands;
 await update('Contact', [ ... ]); // or insert(), remove(), touch()
 await update('Contact', [ ... ]);
 await update('Contact', [ ... ]);
});
// (Fire events on next event loop.)
...
// Unsubscribe to all changes of the resolver `Contact`.
unsubscribe('Contact', null, subscriber);
// Unsubscribe to all changes of the record `Contact(id='Contact/z2')`.
unsubscribe('Contact', 'Contact/z2', subscriber);

💎 Features

Syntax

Other features

📖 Usage

📦 Module open-soql/modules/builder

🟢 build()

export interface QueryBuilderInfo {
 functions?: QueryFuncInfo[];
 // QueryFuncInfo[i].type is 'aggregate' | 'scalar' | 'immediate-scalar'
 rules?: {
 idFieldName?: (resolverName: string) => string;
 foreignIdFieldName?: (masterResolverName: string | undefined) => string | undefined;
 };
 events?: {
 beginTransaction?: (evt: ResolverEvent) => Promise<void>;
 endTransaction?: (evt: ResolverEvent, err: Error | null) => Promise<void>;
 beginExecute?: (evt: ResolverEvent) => Promise<void>;
 endExecute?: (evt: ResolverEvent, err: Error | null) => Promise<void>;
 beforeMasterSubQueries?: (evt: ResolverEvent) => Promise<void>;
 afterMasterSubQueries?: (evt: ResolverEvent) => Promise<void>;
 beforeDetailSubQueries?: (evt: ResolverEvent) => Promise<void>;
 afterDetailSubQueries?: (evt: ResolverEvent) => Promise<void>;
 };
 resolvers: {
 query: {
 [resolverNames: string]: QueryResolverFn;
 };
 insert?: {
 [resolverNames: string]: InsertResolverFn;
 };
 update?: {
 [resolverNames: string]: UpdateResolverFn;
 };
 remove?: {
 [resolverNames: string]: RemoveResolverFn;
 };
 };
 relationships?: {
 [detailOrMasterResolverNames: string]: {
 [fieldOrRelNames: string]:
 string | { resolver: string, id: string } | [string, string?];
 };
 };
}
export interface IQuery {
 public execute<R>(
 params?: {
 [paramNames: string]:
 number | string | null |
 Array<number | string | null>
 }): Promise<R[]>;
}
export interface SubscriberParams {
 on: 'insert' | 'update' | 'remove';
 resolver: string;
 id: any | null;
}
export type Subscriber = (params: SubscriberParams) => void;
export function build(builder: QueryBuilderInfo): {
 compile: (strings: TemplateStringsArray | string,
 ...values: Array<PreparedAtomValue | Array<PreparedAtomValue>>) => IQuery;
 soql: (strings: TemplateStringsArray | string,
 ...values: Array<PreparedAtomValue | Array<PreparedAtomValue>>) => Promise<R[]>;
 insert: (resolver: string, obj: T) => Promise<T extends (infer R)[] ? R[] : T>;
 update: (resolver: string, obj: T) => Promise<T extends (infer R)[] ? R[] : T>;
 remove: (resolver: string, obj: T) => Promise<void>;
 touch: (resolver: string, obj: T) => Promise<void>;
 notifyRemoved: (resolver: string, obj: T) => Promise<void>;
 subscribe: (resolver: string, id: any, fn: Subscriber) => void,
 unsubscribe: (resolver: string, id: any, fn: Subscriber) => void,
 unsubscribeAllBySubscriber: (resolver: string, fn: Subscriber) => void,
 transaction: (
 callback: (commands: {
 compile, soql, insert, update, remove, touch
 }, tr: any) => Primise<void>,
 trOptions?: any,
 ) => Primise<void>;
};

parameters:

returns:

📦 Module open-soql/modules/sort

🟢 sortRecords()

export function sortRecords(query: PreparedQuery, records: any[]): any[];

parameters:

returns:

📦 Module open-soql/modules/filters

🟢 applyWhereConditions()

export function applyWhereConditions(
 Omit<ResolverContext, 'resolverCapabilities'>,
 conds: PreparedCondition[], records: any[]): any[];

parameters:

returns:

🟢 applyHavingConditions()

export function applyHavingConditions(
 Omit<ResolverContext, 'resolverCapabilities'>,
 conds: PreparedCondition[], groupedRecsArray: any[][]): any[];

parameters:

returns:

🟢 getIndexFieldConditions()

export function getIndexFieldConditions(
 ctx: Pick<ResolverContext, 'params'>,
 conds: PreparedCondition[], indexFieldNames: string[]): PreparedCondition[];

parameters:

returns:

🟢 getSqlConditionString()

export interface SqlDialect {
 fieldName: (name: string) => string;
 escapeString: (s: string) => string;
}
export function getSqlConditionString(
 ctx: Pick<ResolverContext, 'params'>,
 conds: PreparedCondition[], dialect: SqlDialect): string;

parameters:

returns:

🟢 escapeSqlStringLiteral_Std()

export function escapeSqlStringLiteral_Std(s: string): string;

parameters:

returns:

🟢 escapeSqlStringLiteral_MySql()

export function escapeSqlStringLiteral_MySql(s: string): string;

parameters:

returns:

📦 Module open-soql/modules/resolvers

🟢 staticJsonResolverBuilder()

export interface StaticResolverConfig {
 noCache?: boolean;
 noFiltering?: boolean;
 noSorting?: boolean;
}
export const staticJsonResolverBuilder:
 (resolverName: string, fetcher: () => Promise<string>,
 config?: StaticResolverConfig) => QueryResolverFn;

parameters:

returns:

🟢 staticCsvResolverBuilder()

export const staticCsvResolverBuilder:
 (resolverName: string, fetcher: () => Promise<string>,
 config?: StaticResolverConfig) => QueryResolverFn;

parameters:

returns:

🟢 passThroughResolverBuilder()

export const passThroughResolverBuilder:
 (resolverName: string, fetcher: () => Promise<any[]>,
 config?: StaticResolverConfig) => QueryResolverFn;

parameters:

returns:

🙋 FAQ

⚖️ License