odata-sequelize

!npm
!npm


Objective

This library is intended to take an OData query string as a parameter and transform it on a
sequelize-compliant query.

🚀 Latest Features (v2.0)

- Navigation Properties: Filter on related entity properties (Customer/CompanyName eq 'Acme')
- Lambda Expressions: Query child tables with any/all operators (Orders/any(o: o/Amount gt 100))
- Complex Mixed Logic: Advanced AND/OR combinations with deep parentheses nesting
- Smart Include Merging: Automatic merging of $expand and navigation filters
- 0 deps: Now the library has no dependencies!

Requirements

- Node.JS
- NPM
- Sequelize.JS

Installing

Simply run a npm command to install it in your project:

npm install odata-sequelize

How does it work?

The OData query string is parsed using a custom PEG.js grammar that handles the complete OData specification. The resulting abstract syntax tree (AST) is then transformed using a visitor pattern to build a sequelize-compliant query object.

Roadmap

$3

All planned features have been implemented!

$3

- [x] AND
- [x] OR
- [x] NOT

$3

- [x] Equal (eq)
- [x] Not Equal (ne)
- [x] Greater Than (gt)
- [x] Greater Than or Equal (ge)
- [x] Less Than (lt)
- [x] Less Than or Equal (le)

$3

1. String Functions

- [x] substringof
- [x] endswith
- [x] startswith
- [x] tolower
- [x] toupper
- [x] trim
- [x] concat
- [x] substring
- [x] replace
- [x] indexof

2. Date Functions

- [x] day
- [x] hour
- [x] minute
- [x] month
- [x] second
- [x] year

$3

- [x] $expand - Eager loading associations with nested support
- [x] Lambda expressions - any/all operators for child table queries
- [x] Navigation properties - Filter on related entity properties (e.g., Customer/CompanyName)
- [x] Mixed logical operators - Complex AND/OR combinations with parentheses
- [x] Function integration - Functions combined with navigation and lambda expressions
- [x] Include merging - Smart merging of $expand and navigation filters
- [x] Precedence handling - Proper parentheses and operator precedence support

$3

- [x] $filter - Complex filtering with all operators and functions
- [x] $select - Choose specific fields to return
- [x] $expand - Eager load related entities
- [x] $top - Limit number of results
- [x] $skip - Pagination offset
- [x] $orderby - Sorting with multiple fields

$3

- [x] Test (Jest) - Thanks to @remcohaszing
- [x] Lint & Prettier - Thanks to @remcohaszing
- [x] 86 comprehensive tests - Including complex integration scenarios
- [x] 76% code coverage - High-quality test coverage

How to Use

You just need to pass an OData query string as parameter with your sequelize object instance, and
automagically it is converted to a sequelize query.

Usage Example:

``javascript
var parseOData = require("odata-sequelize");
var sequelize = require("sequelize");
var query = parseOData(
"$top=5&$skip=1&$select=Foo,Bar&$filter=Foo eq 'Test' or Bar eq 'Test'&$orderby=Foo desc",
sequelize
);

// Supposing you have your sequelize model
Model.findAll(query);
`

See the examples below to checkout what's created under the hood:

1) Simple Query with Top, Skip, Select, Filter and OrderBy

`javascript
var parseOData = require("odata-sequelize");
var sequelize = require("sequelize");
var query = parseOData(
"$top=5&$skip=1&$select=Foo,Bar&$filter=Foo eq 'Test' or Bar eq 'Test'&$orderby=Foo desc",
sequelize
);
`

query becomes...

`javascript
{
attributes: ['Foo', 'Bar'],
limit: 5,
offset: 1,
order: [
['Foo', 'DESC']
],
where: {
[Op.or]: [
{
Foo: {
[Op.eq]: "Test"
}
},
{
Bar: {
[Op.eq]: "Test"
}
}
]
}
}
`

2) Complex Query with Precedence

`javascript
var parseOData = require("odata-sequelize");
var sequelize = require("sequelize");
var query = parseOData(
"$filter=(Foo eq 'Test' or Bar eq 'Test') and ((Foo ne 'Lorem' or Bar ne 'Ipsum') and (Year gt 2017))",
sequelize
);
`

query becomes...

`javascript
{
where: {
[Op.and]: [
{
[Op.or]: [
{
Foo: {
[Op.eq]: "Test"
}
},
{
Bar: {
[Op.eq]: "Test"
}
}
]
},
{
[Op.and]: [
{
[Op.or]: [
{
Foo: {
[Op.ne]: "Lorem"
},
},
{
Bar: {
[Op.ne]: "Ipsum"
}
}
]
},
{
Year: {
[Op.gt]: 2017
}
}
]
}
]
}
}
`

3) Using Date

`javascript
var parseOData = require("odata-sequelize");
var sequelize = require("sequelize");
var query = parseOData(
"$filter=Foo eq 'Test' and Date gt datetime'2012-09-27T21:12:59'",
sequelize
);
`

query becomes...

`javascript
{
where: {
[Op.and]: [
{
Foo: {
[Op.eq]: "Test"
}
},
{
Date: {
[Op.gt]: new Date("2012-09-27T21:12:59")
}
}
]
}
}
`

4) startswith function

`javascript
var parseOData = require("odata-sequelize");
var sequelize = require("sequelize");
var query = parseOData("$filter=startswith('lorem', Foo) and Bar eq 'Test'", sequelize);
`

query becomes...

`javascript
{
where: {
[Op.and]: [
{
Foo: {
[Op.like]: "lorem%"
}
},
{
Bar: {
[Op.eq]: "Test"
}
}
]
}
}
`

5) substringof function

`javascript
var parseOData = require("odata-sequelize");
var sequelize = require("sequelize");
var query = parseOData("$filter=substringof('lorem', Foo) and Bar eq 'Test'", sequelize);
`

query becomes...

`javascript
{
where: {
[Op.and]: [
{
Foo: {
[Op.like]: "%lorem%"
}
},
{
Bar: {
[Op.eq]: "Test"
}
}
]
}
}
`

6) startswith function

`javascript
var parseOData = require("odata-sequelize");
var sequelize = require("sequelize");
var query = parseOData("$filter=startswith('Foo', Name) and Bar eq 'Test'", sequelize);
`

query becomes...

`javascript
{
where: {
[Op.and]: [
{
Name: {
[Op.like]: "Foo%"
}
},
{
Bar: {
[Op.eq]: "Test"
}
}
]
}
}
`

7) trim function

`javascript
var parseOData = require("odata-sequelize");
var sequelize = require("sequelize");
var query = parseOData("$filter=trim(Name) eq 'Foo' and Bar eq 'Test'", sequelize);
`

query becomes...

`javascript
{
where: {
[Op.and]: [
{
Name: {
comparator: [Op.eq],
logic: "Foo",
attribute: {
fn: "trim",
args: [
{
col: "Name"
}
]
}
}
},
{
Bar: {
[Op.eq]: "Test"
}
}
]
}
}
`

8) tolower function

`javascript
var parseOData = require("odata-sequelize");
var sequelize = require("sequelize");
var query = parseOData("$filter=tolower(Name) eq 'foobaz' and Name eq 'bar'", sequelize);
`

query becomes...

`javascript
{
where: {
[Op.and]: [
{
Name: {
comparator: [Op.eq],
logic: "foobaz",
attribute: {
fn: "lower",
args: [
{
col: "Name"
}
]
}
}
},
{
Bar: {
[Op.eq]: "Test"
}
}
]
}
}
`

9) toupper function

`javascript
var parseOData = require("odata-sequelize");
var sequelize = require("sequelize");
var query = parseOData("$filter=toupper(Name) eq 'FOOBAZ' and Name eq 'bar'", sequelize);
`

query becomes...

`javascript
{
where: {
[Op.and]: [
{
Name: {
comparator: [Op.eq],
logic: "FOOBAZ",
attribute: {
fn: "upper",
args: [
{
col: "Name"
}
]
}
}
},
{
Bar: {
[Op.eq]: "Test"
}
}
]
}
}
`

10) year, month, day, hour, minute, second function

- The same logic applies to all 6 date functions. The only difference resides in attribute object,
whose "fn" property reflects the called function.

`javascript
var parseOData = require("odata-sequelize");
var sequelize = require("sequelize");
var query = parseOData("$filter=year(StartDate) gt 2017", sequelize);
`

becomes...

`javascript
{
where: {
{
StartDate: {
comparator: [Op.gt],
logic: 2017,
attribute: {
fn: "year",
args: [
{
col: "StartDate"
}
]
}
}
}
}
}
`

11) $expand for eager loading associations

`javascript
var parseOData = require("odata-sequelize");
var sequelize = require("sequelize");
var query = parseOData("$expand=Orders", sequelize);
`

becomes...

`javascript
{
include: [
{
association: "Orders"
}
]
}
`

12) Multiple $expand

`javascript
var parseOData = require("odata-sequelize");
var sequelize = require("sequelize");
var query = parseOData("$expand=Orders,Customer", sequelize);
`

becomes...

`javascript
{
include: [
{
association: "Orders"
},
{
association: "Customer"
}
]
}
`

13) Nested $expand

`javascript
var parseOData = require("odata-sequelize");
var sequelize = require("sequelize");
var query = parseOData("$expand=Orders/OrderItems", sequelize);
`

becomes...

`javascript
{
include: [
{
association: "Orders",
include: [
{
association: "OrderItems"
}
]
}
]
}
`

14) Complex query with $expand

`javascript
var parseOData = require("odata-sequelize");
var sequelize = require("sequelize");
var query = parseOData(
"$select=Name,Id&$expand=Orders&$top=10&$filter=Active eq true",
sequelize
);
`

becomes...

`javascript
{
attributes: ["Name", "Id"],
limit: 10,
include: [
{
association: "Orders"
}
],
where: {
Active: {
[Op.eq]: true
}
}
}
`

15) Query in children tables (Lambda expressions)

The library fully supports OData lambda expressions (any and all operators) for filtering parent entities based on child entity properties. This powerful feature allows you to query related data with complex conditions.

`javascript
// Filter customers who have orders with amount > 100
var query = parseOData("$filter=Orders/any(o: o/Amount gt 100)", sequelize);

// Filter customers where all orders are shipped
var query = parseOData("$filter=Orders/all(o: o/Status eq 'Shipped')", sequelize);
`

Expected output format:

`javascript
{
include: [
{
association: "Orders",
where: {
Amount: {
[Op.gt]: 100
}
},
required: true // 'any' uses INNER JOIN, 'all' uses different logic
}
]
}
`

Key Features of Lambda Expressions:
- any operator: Returns parent records if at least one child matches the condition (uses required: true for INNER JOIN)
- all operator: Returns parent records if all children match the condition
- Variable scoping: Supports variable names in lambda expressions (e.g., o: o/Amount)
- Complex conditions: Supports nested property access and multiple comparison operators
- Sequelize integration: Converts to appropriate include structures with where clauses

16) Navigation Properties - Filtering on Related Entity Properties

You can filter parent entities based on properties of related entities using navigation syntax:

`javascript
var parseOData = require("odata-sequelize");
var sequelize = require("sequelize");
var query = parseOData("$filter=Customer/CompanyName eq 'Acme Corp'", sequelize);
`

becomes...

`javascript
{
include: [
{
association: "Customer",
where: {
CompanyName: {
[Op.eq]: "Acme Corp"
}
},
required: true
}
]
}
`

17) Navigation Properties with $expand

Navigation filters automatically merge with $expand when targeting the same association:

`javascript
var query = parseOData(
"$filter=Customer/Country ne 'USA'&$expand=Customer",
sequelize
);
`

becomes...

`javascript
{
include: [
{
association: "Customer",
where: {
Country: {
[Op.ne]: "USA"
}
},
required: true
}
]
}
`

18) Complex Mixed AND/OR with Parentheses

The parser handles deeply nested logical expressions with proper precedence:

`javascript
var query = parseOData(
"$filter=((Type eq 'A' or Type eq 'B') and Status eq 'Active') or (Category eq 'Premium' and Year gt 2020)",
sequelize
);
`

becomes...

`javascript
{
where: {
[Op.or]: [
{
[Op.and]: [
{
[Op.or]: [
{
Type: {
[Op.eq]: "A"
}
},
{
Type: {
[Op.eq]: "B"
}
}
]
},
{
Status: {
[Op.eq]: "Active"
}
}
]
},
{
[Op.and]: [
{
Category: {
[Op.eq]: "Premium"
}
},
{
Year: {
[Op.gt]: 2020
}
}
]
}
]
}
}
`

19) Mixed Function and Navigation Filters

Complex queries combining function calls, navigation properties, and $expand:

`javascript
var query = parseOData(
"$filter=tolower(CompanyName) eq 'acme corp' and Customer/Country ne 'USA'&$expand=Customer&$orderby=OrderDate desc",
sequelize
);
`

becomes...

`javascript
{
order: [["OrderDate", "DESC"]],
where: {
[Op.and]: [
{
CompanyName: {
attribute: {
fn: "tolower",
args: [{ col: "CompanyName" }]
},
comparator: [Op.eq],
logic: "acme corp"
}
}
]
},
include: [
{
association: "Customer",
where: {
Country: {
[Op.ne]: "USA"
}
},
required: true
}
]
}
`

20) Complete Integration Example

Real-world complex query combining all features:

`javascript
var query = parseOData(
"$select=Name,Status,Priority&$expand=Customer,Orders&$top=20&$skip=10&$orderby=Priority desc,Name asc&$filter=((Status eq 'Active' and Priority ge 3) or (Customer/Country eq 'USA' and Orders/any(o: o/Amount gt 500))) and year(CreatedDate) ge 2023",
sequelize
);
`

This generates a comprehensive Sequelize query with:
- Attributes selection ($select)
- Eager loading ($expand)
- Pagination ($top, $skip)
- Ordering ($orderby`)
- Complex filtering with nested logic, navigation properties, lambda expressions, and function calls