This library is intended to take an OData query string as a parameter and transform it on a sequelize-compliant query.
- Navigation Properties: Filter on related entity properties (
Customer/CompanyName eq 'Acme') - Lambda Expressions: Query child tables with
any/alloperators (Orders/any(o: o/Amount gt 100)) - Complex Mixed Logic: Advanced AND/OR combinations with deep parentheses nesting
- Smart Include Merging: Automatic merging of
$expandand navigation filters - 0 deps: Now the library has no dependencies!
- Node.JS
- NPM
- Sequelize.JS
Simply run a npm command to install it in your project:
npm install odata-sequelize
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.
All planned features have been implemented!
- AND
- OR
- NOT
- Equal (eq)
- Not Equal (ne)
- Greater Than (gt)
- Greater Than or Equal (ge)
- Less Than (lt)
- Less Than or Equal (le)
- String Functions
- substringof
- endswith
- startswith
- tolower
- toupper
- trim
- concat
- substring
- replace
- indexof
- Date Functions
- day
- hour
- minute
- month
- second
- year
- $expand - Eager loading associations with nested support
- Lambda expressions -
any/alloperators for child table queries - Navigation properties - Filter on related entity properties (e.g.,
Customer/CompanyName) - Mixed logical operators - Complex AND/OR combinations with parentheses
- Function integration - Functions combined with navigation and lambda expressions
- Include merging - Smart merging of $expand and navigation filters
- Precedence handling - Proper parentheses and operator precedence support
- $filter - Complex filtering with all operators and functions
- $select - Choose specific fields to return
- $expand - Eager load related entities
- $top - Limit number of results
- $skip - Pagination offset
- $orderby - Sorting with multiple fields
- Test (Jest) - Thanks to @remcohaszing
- Lint & Prettier - Thanks to @remcohaszing
- 86 comprehensive tests - Including complex integration scenarios
- 76% code coverage - High-quality test coverage
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:
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
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...
{
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
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...
{
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
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...
{
where: {
[Op.and]: [
{
Foo: {
[Op.eq]: "Test"
}
},
{
Date: {
[Op.gt]: new Date("2012-09-27T21:12:59")
}
}
]
}
}4) startswith function
var parseOData = require("odata-sequelize");
var sequelize = require("sequelize");
var query = parseOData("$filter=startswith('lorem', Foo) and Bar eq 'Test'", sequelize);query becomes...
{
where: {
[Op.and]: [
{
Foo: {
[Op.like]: "lorem%"
}
},
{
Bar: {
[Op.eq]: "Test"
}
}
]
}
}5) substringof function
var parseOData = require("odata-sequelize");
var sequelize = require("sequelize");
var query = parseOData("$filter=substringof('lorem', Foo) and Bar eq 'Test'", sequelize);query becomes...
{
where: {
[Op.and]: [
{
Foo: {
[Op.like]: "%lorem%"
}
},
{
Bar: {
[Op.eq]: "Test"
}
}
]
}
}6) startswith function
var parseOData = require("odata-sequelize");
var sequelize = require("sequelize");
var query = parseOData("$filter=startswith('Foo', Name) and Bar eq 'Test'", sequelize);query becomes...
{
where: {
[Op.and]: [
{
Name: {
[Op.like]: "Foo%"
}
},
{
Bar: {
[Op.eq]: "Test"
}
}
]
}
}7) trim function
var parseOData = require("odata-sequelize");
var sequelize = require("sequelize");
var query = parseOData("$filter=trim(Name) eq 'Foo' and Bar eq 'Test'", sequelize);query becomes...
{
where: {
[Op.and]: [
{
Name: {
comparator: [Op.eq],
logic: "Foo",
attribute: {
fn: "trim",
args: [
{
col: "Name"
}
]
}
}
},
{
Bar: {
[Op.eq]: "Test"
}
}
]
}
}8) tolower function
var parseOData = require("odata-sequelize");
var sequelize = require("sequelize");
var query = parseOData("$filter=tolower(Name) eq 'foobaz' and Name eq 'bar'", sequelize);query becomes...
{
where: {
[Op.and]: [
{
Name: {
comparator: [Op.eq],
logic: "foobaz",
attribute: {
fn: "lower",
args: [
{
col: "Name"
}
]
}
}
},
{
Bar: {
[Op.eq]: "Test"
}
}
]
}
}9) toupper function
var parseOData = require("odata-sequelize");
var sequelize = require("sequelize");
var query = parseOData("$filter=toupper(Name) eq 'FOOBAZ' and Name eq 'bar'", sequelize);query becomes...
{
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.
var parseOData = require("odata-sequelize");
var sequelize = require("sequelize");
var query = parseOData("$filter=year(StartDate) gt 2017", sequelize);becomes...
{
where: {
{
StartDate: {
comparator: [Op.gt],
logic: 2017,
attribute: {
fn: "year",
args: [
{
col: "StartDate"
}
]
}
}
}
}
}11) $expand for eager loading associations
var parseOData = require("odata-sequelize");
var sequelize = require("sequelize");
var query = parseOData("$expand=Orders", sequelize);becomes...
{
include: [
{
association: "Orders"
}
]
}12) Multiple $expand
var parseOData = require("odata-sequelize");
var sequelize = require("sequelize");
var query = parseOData("$expand=Orders,Customer", sequelize);becomes...
{
include: [
{
association: "Orders"
},
{
association: "Customer"
}
]
}13) Nested $expand
var parseOData = require("odata-sequelize");
var sequelize = require("sequelize");
var query = parseOData("$expand=Orders/OrderItems", sequelize);becomes...
{
include: [
{
association: "Orders",
include: [
{
association: "OrderItems"
}
]
}
]
}14) Complex query with $expand
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...
{
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.
// 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:
{
include: [
{
association: "Orders",
where: {
Amount: {
[Op.gt]: 100
}
},
required: true // 'any' uses INNER JOIN, 'all' uses different logic
}
]
}Key Features of Lambda Expressions:
anyoperator: Returns parent records if at least one child matches the condition (usesrequired: truefor INNER JOIN)alloperator: 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
includestructures withwhereclauses
16) Navigation Properties - Filtering on Related Entity Properties
You can filter parent entities based on properties of related entities using navigation syntax:
var parseOData = require("odata-sequelize");
var sequelize = require("sequelize");
var query = parseOData("$filter=Customer/CompanyName eq 'Acme Corp'", sequelize);becomes...
{
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:
var query = parseOData(
"$filter=Customer/Country ne 'USA'&$expand=Customer",
sequelize
);becomes...
{
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:
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...
{
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:
var query = parseOData(
"$filter=tolower(CompanyName) eq 'acme corp' and Customer/Country ne 'USA'&$expand=Customer&$orderby=OrderDate desc",
sequelize
);becomes...
{
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:
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