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.
Table of contents
⚙️ Install
npm install open-soql
NOTICE:
Use withwebpack >= 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: falseOn
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
;; // See `src/types.ts` > `QueryBuilderInfo`;
Query
;// result is [{...}, ...]
Pre-compiled query
- Non-parameterized query.
(Template literal parameters will be interpreted before compiling.)
;;
- Named parameterized query.
;;
You can use parameters on the right side of the conditional expression, function arguments, limit, and offset.
Aggregate
;// aggregationResult is [{...}, ...]
DML (bulk)
;// inserted is [{ Id: 'Contact/1', Name: 'foo' }] ;// updated is [{ Id: 'Contact/1', Name: 'foo' }] await remove'Contact', updated; ;;
DML (single record)
;// inserted is { Id: 'Contact/1', Name: 'foo' } ;// updated is { Id: 'Contact/1', Name: 'foo' } await remove'Contact', updated;
Execute commands within a transaction
await transaction;
Publish / Subscribe messaging
Without a transaction
; // 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
; // 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;// (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
Select
field list- detail-master relationship name
- resolver (relationship) alias name
- field alias name
- function call (aggregate | scalar | immediate_scalar)
- nested function call (call functions in actual parameters of functions)
- functions
- Aggregate functions
-
count()
,count(field)
-
count_distinct(field)
-
sum(field)
-
avg(field)
-
min(field)
-
max(field)
-
grouping(field)
-
- Scalar functions
- String functions
-
format(field | literal | function call)
-
concat(field | literal | function call, ...)
-
- Cast functions
-
cast_to_string(field | literal | function call)
-
cast_to_number(field | literal | function call)
-
cast_to_boolean(field | literal | function call)
-
- Calc functions
-
add(field | literal | function call, ...)
-
sub(field | literal | function call, ...)
-
mul(field | literal | function call, ...)
-
div(field | literal | function call, ...)
-
mod(field | literal | function call, ...)
-
- Date and datetime functions (UTC)
-
convertTimezone(field | literal | function call)
-
calendar_month(field | literal | convertTimezone(field) | function call)
-
calendar_quarter(field | literal | convertTimezone(field) | function call)
-
calendar_year(field | literal | convertTimezone(field) | function call)
-
day_in_month(field | literal | convertTimezone(field) | function call)
-
day_in_week(field | literal | convertTimezone(field) | function call)
-
day_in_year(field | literal | convertTimezone(field) | function call)
-
day_only(field | literal | convertTimezone(field) | function call)
-
fiscal_month(field | literal | convertTimezone(field) | function call)
-
fiscal_quarter(field | literal | convertTimezone(field) | function call)
-
fiscal_year(field | literal | convertTimezone(field) | function call)
-
hour_in_day(field | literal | convertTimezone(field) | function call)
-
week_in_month(field | literal | convertTimezone(field) | function call)
-
week_in_year(field | literal | convertTimezone(field) | function call)
-
- Date and datetime functions (local timezone)
-
calendar_month_lc(field | literal | function call)
-
calendar_quarter_lc(field | literal | function call)
-
calendar_year_lc(field | literal | function call)
-
day_in_month_lc(field | literal | function call)
-
day_in_week_lc(field | literal | function call)
-
day_in_year_lc(field | literal | function call)
-
day_only_lc(field | literal | function call)
-
fiscal_month_lc(field | literal | function call)
-
fiscal_quarter_lc(field | literal | function call)
-
fiscal_year_lc(field | literal | function call)
-
hour_in_day_lc(field | literal | function call)
-
week_in_month_lc(field | literal | function call)
-
week_in_year_lc(field | literal | function call)
-
- String functions
- Aggregate functions
-
TYPEOF
expression
- field expressions
- field
- field alias name
- data types
- string
- number
- date
- datetime
- null
From
clause- resolver (relationship name) alias
Where
clause- field
- data types
- string
- number
- date
- datetime
- null
- op1 function call (scalar | immediate_scalar)
- op2 function call (immediate_scalar)
- date literals (e.g.:
TODAY
) - logical operators
-
and
-
or
-
not
-
- comparison operators
-
=
-
!=
-
<
-
<=
-
>
-
>=
-
like
-
not_like
-
in
-
not_in
-
includes
-
excludes
-
Having
clause- field
- data types
- string
- number
- date
- datetime
- null
- op1 function call (immediate_scalar | aggregate)
- op2 function call (immediate_scalar)
- date literals (e.g.:
TODAY
) - logical operators
-
and
-
or
-
not
-
- comparison operators
-
=
-
!=
-
<
-
<=
-
>
-
>=
-
like
-
not_like
-
in
-
not_in
-
includes
-
excludes
-
Group by
clause- fields
- field alias name
-
ROLLUP
-
CUBE
Order by
clause- fields
- resolver (relationship) alias name
- field alias name
- asc/desc
- nulls first/last
-
Using scope
clause -
Limit
clause -
Offset
clause -
With
clause -
For
clause
Other features
- prepared query (pre-compiled query)
- named parameterized query
- standard query resolvers
- JSON string
- CSV string
- Array of object
- DML
-
insert
-
update
-
remove
-
- Publish / Subscribe messaging
- transaction scope
- template string
📖 Usage
open-soql/modules/builder
📦 Module build()
🟢 ; ;
- Set up the resolvers.
parameters:
builder
: Resolvers and configurations.
NOTICE:
Theimmediate-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 parameterconditions
will be[]
.
To get complete conditions, use parameterctx.conditions
.
You can get transformed conditions that include only the fields you specified by usinggetIndexFieldConditions()
.
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
: Queuesupdate
events for subscribers. (to notify remote changes)notifyRemoved
: Queuesremove
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.
open-soql/modules/sort
📦 Module sortRecords()
🟢 ;
- Sort records.
parameters:
query
: Prepared query object.records
: Records to sort.
returns:
- Sorted records.
open-soql/modules/filters
📦 Module applyWhereConditions()
🟢 ;
- 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()
🟢 ;
- 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()
🟢 ;
- 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()
🟢 ;
- 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()
🟢 ;
- Escape the standard SQL string literal. (pass to
SqlDialect
)
parameters:
s
: string literal.
returns:
- Escaped string.
escapeSqlStringLiteral_MySql()
🟢 ;
- Escape the MySQL string literal. (pass to
SqlDialect
)
parameters:
s
: string literal.
returns:
- Escaped string.
open-soql/modules/resolvers
📦 Module staticJsonResolverBuilder()
🟢 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()
🟢 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()
🟢 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 forSOQL is Object Query Language
. - 👉 In original SOQL,
SOQL
stands forSalesforce Object Query Language
.
- 👉 In
⚖️ License
ISC
Copyright (c) 2020 Shellyl_N and Authors