🚀 DAY 3 OF LAUNCH WEEK: Introducing Webhook Events for Pull Request Scans.Learn more →

Book a Demo Install Sign in

open-soql

Package Overview

Advanced tools

Install Socket

Detect and block malicious and high-risk dependencies

Install

open-soql

Open source implementation of the SOQL.

latest

Source

npm

Version: 0.6.0

Version published: 5 years ago

Weekly downloads: 13

Maintainers: 1

Weekly downloads

Created: 5 years ago

Source

Open SOQL

Open source implementation of the SOQL.
You can query everything you want by defining the resolvers.

SOQL is an object-oriented query language that allows you to query related data based on an object graph.

⚙️ Install

npm install open-soql

NOTICE:
Use with webpack >= 5

If you get the error:
Module not found: Error: Can't resolve '(importing/path/to/filename)'
in '(path/to/node_modules/path/to/dirname)'
Did you mean '(filename).js'?`
Add following setting to your webpack.config.js.
{
    test: /\.m?js/,
    resolve: {
        fullySpecified: false,
    },
},
On webpack >= 5, the extension in the request is mandatory for it to be fully specified if the origin is a '.mjs' file or a '.js' file where the package.json contains '"type": "module"'.

🚀 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

Non-parameterized query.
(Template literal parameters will be interpreted before compiling.)

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

Named parameterized query.

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

You can use parameters on the right side of the conditional expression, function arguments, limit, and offset.

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>>();
});

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);

See also the following usage example repositories:

💎 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>;
};

Set up the resolvers.

parameters:

builder: Resolvers and configurations.

NOTICE:
The immediate-scalar function does not refer to the fields of a record and must be referentially transparent.

NOTICE:
If query conditions includes computed fields, QueryResolverFn's parameter conditions will be [].
To get complete conditions, use parameter ctx.conditions.
You can get transformed conditions that include only the fields you specified by using getIndexFieldConditions().

returns:

Functions that execute select queries and DML
- compile: Compile the query.
- soql: Select records.
- insert: Insert record(s).
- update: Update record(s).
- remove: Remove record(s).
- touch: Queues update events for subscribers. (to notify remote changes)
- notifyRemoved: Queues remove events for subscribers. (to notify remote changes)
- subscribe: Subscribe to publishing events.
- unsubscribe: Unsubscribe to publishing events.
- unsubscribeAllBySubscriber: Unsubscribe to publishing events.
- transaction: Execute commands within a transaction.

📦 Module `open-soql/modules/sort`

🟢 `sortRecords()`

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

Sort records.

parameters:

query: Prepared query object.
records: Records to sort.

returns:

Sorted records.

📦 Module `open-soql/modules/filters`

🟢 `applyWhereConditions()`

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

Filter records by where conditions.

parameters:

ctx: Context object.
conds: where conditions.
records: Records to apply the filter.

returns:

Records that the filter applied.

🟢 `applyHavingConditions()`

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

Filter groups by having conditions.

parameters:

ctx: Context object.
conds: having conditions.
records: Groups to apply the filter.

returns:

Groups that the filter applied.

🟢 `getIndexFieldConditions()`

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

Gets the transformed conditions that include only the fields specified in indexFieldNames.

parameters:

ctx: Context object.
conds: Original conditions.
indexFieldNames: Index fields.

returns:

Transformed conditions.

🟢 `getSqlConditionString()`

export interface SqlDialect {
    fieldName: (name: string) => string;
    escapeString: (s: string) => string;
}

export function getSqlConditionString(
    ctx: Pick<ResolverContext, 'params'>,
    conds: PreparedCondition[], dialect: SqlDialect): string;

Get the SQL condition string.

parameters:

ctx: Context object.
conds: Conditions for converting to SQL conditions.
dialect: SQL dialect.

returns:

SQL condition string (where clause excludes the where keyword).

🟢 `escapeSqlStringLiteral_Std()`

export function escapeSqlStringLiteral_Std(s: string): string;

Escape the standard SQL string literal. (pass to SqlDialect)

parameters:

s: string literal.

returns:

Escaped string.

🟢 `escapeSqlStringLiteral_MySql()`

export function escapeSqlStringLiteral_MySql(s: string): string;

Escape the MySQL string literal. (pass to SqlDialect)

parameters:

s: string literal.

returns:

Escaped string.

📦 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;

Generate the query resolver for static JSON data.

parameters:

resolverName: Resolver name.
fetcher: The function that returns promise of data.

returns:

Query resolver.

🟢 `staticCsvResolverBuilder()`

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

Generate the query resolver for static CSV data.

parameters:

resolverName: Resolver name.
fetcher: The function that returns promise of data.

returns:

Query resolver.

🟢 `passThroughResolverBuilder()`

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

Generate the query resolver for static object array data.

parameters:

resolverName: Resolver name.
fetcher: The function that returns promise of data.

returns:

Query resolver.

🙋 FAQ

What does SOQL stand for?
- 👉 In Open SOQL, SOQL stands for SOQL is Object Query Language.
- 👉 In original SOQL, SOQL stands for Salesforce Object Query Language.

⚖️ License

Keywords

SOQL

query-engine

Graph query language

Object query language

graph-query

object-query

FAQs

What is open-soql?

Is open-soql popular?

Is open-soql well maintained?

Package last updated on 25 Oct 2020

Did you know?

Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.

Install

open-soql

Open SOQL

Table of contents

⚙️ Install

🚀 Getting started

Set up the resolvers

Query

Pre-compiled query

Aggregate

DML (bulk)

DML (single record)

Execute commands within a transaction

Publish / Subscribe messaging

Without a transaction

Within a transaction

💎 Features

Syntax

Other features

📖 Usage

📦 Module open-soql/modules/builder

🟢 build()

parameters:

returns:

📦 Module open-soql/modules/sort

🟢 sortRecords()

parameters:

returns:

📦 Module open-soql/modules/filters

🟢 applyWhereConditions()

parameters:

returns:

🟢 applyHavingConditions()

parameters:

returns:

🟢 getIndexFieldConditions()

parameters:

returns:

🟢 getSqlConditionString()

parameters:

returns:

🟢 escapeSqlStringLiteral_Std()

parameters:

returns:

🟢 escapeSqlStringLiteral_MySql()

parameters:

returns:

📦 Module open-soql/modules/resolvers

🟢 staticJsonResolverBuilder()

parameters:

returns:

🟢 staticCsvResolverBuilder()

parameters:

returns:

🟢 passThroughResolverBuilder()

parameters:

returns:

🙋 FAQ

⚖️ License

v0.6.0

Keywords

Related posts

Malicious NuGet Packages Typosquat Nethereum to Exfiltrate Wallet Keys

Unify Your Security Stack with Socket Basics

📦 Module `open-soql/modules/builder`

🟢 `build()`

📦 Module `open-soql/modules/sort`

🟢 `sortRecords()`

📦 Module `open-soql/modules/filters`

🟢 `applyWhereConditions()`

🟢 `applyHavingConditions()`

🟢 `getIndexFieldConditions()`

🟢 `getSqlConditionString()`

🟢 `escapeSqlStringLiteral_Std()`

🟢 `escapeSqlStringLiteral_MySql()`

📦 Module `open-soql/modules/resolvers`

🟢 `staticJsonResolverBuilder()`

🟢 `staticCsvResolverBuilder()`

🟢 `passThroughResolverBuilder()`