odata-sequelize

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

Completed Features

All planned features have been implemented!

Boolean Operators

• <input checked="" disabled="" type="checkbox"> AND
• <input checked="" disabled="" type="checkbox"> OR
• <input checked="" disabled="" type="checkbox"> NOT

Comparison Operators

• <input checked="" disabled="" type="checkbox"> Equal (eq)
• <input checked="" disabled="" type="checkbox"> Not Equal (ne)
• <input checked="" disabled="" type="checkbox"> Greater Than (gt)
• <input checked="" disabled="" type="checkbox"> Greater Than or Equal (ge)
• <input checked="" disabled="" type="checkbox"> Less Than (lt)
• <input checked="" disabled="" type="checkbox"> Less Than or Equal (le)

Functions

1. String Functions

2. substringof

3. <input checked="" disabled="" type="checkbox"> endswith
4. <input checked="" disabled="" type="checkbox"> startswith
5. <input checked="" disabled="" type="checkbox"> tolower
6. <input checked="" disabled="" type="checkbox"> toupper
7. <input checked="" disabled="" type="checkbox"> trim
8. <input checked="" disabled="" type="checkbox"> concat
9. <input checked="" disabled="" type="checkbox"> substring
10. <input checked="" disabled="" type="checkbox"> replace

11. indexof

12. Date Functions

13. day

14. <input checked="" disabled="" type="checkbox"> hour
15. <input checked="" disabled="" type="checkbox"> minute
16. <input checked="" disabled="" type="checkbox"> month
17. <input checked="" disabled="" type="checkbox"> second
18. <input checked="" disabled="" type="checkbox"> year

Advanced Features

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

Core OData Query Options

• <input checked="" disabled="" type="checkbox"> $filter - Complex filtering with all operators and functions
• <input checked="" disabled="" type="checkbox"> $select - Choose specific fields to return
• <input checked="" disabled="" type="checkbox"> $expand - Eager load related entities
• <input checked="" disabled="" type="checkbox"> $top - Limit number of results
• <input checked="" disabled="" type="checkbox"> $skip - Pagination offset
• <input checked="" disabled="" type="checkbox"> $orderby - Sorting with multiple fields

Development & Quality

• <input checked="" disabled="" type="checkbox"> Test (Jest) - Thanks to @remcohaszing
• <input checked="" disabled="" type="checkbox"> Lint & Prettier - Thanks to @remcohaszing
• <input checked="" disabled="" type="checkbox"> 86 comprehensive tests - Including complex integration scenarios
• <input checked="" disabled="" type="checkbox"> 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:

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:

• 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:

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