odata-sequelize

Objective

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

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 first parsed by node-odata-parser and then the resulting object is recursively iterated to build a new object that is compliant to sequelize's standard.

Roadmap

WIP

• Test (Mocha)
• Query in children tables
• $expand

Boolean Operators

• AND
• OR
• NOT

Comparison Operators

• Equal (eq)
• Not Equal (ne)
• Greater Than (gt)
• Greater Than or Equal (ge)
• Less Than (lt)
• Less Than or Equal (le)

Functions

1. String Functions

• substringof
• endswith
• startswith
• tolower
• toupper
• trim
• concat
• substring
• replace
• indexof

2. Date Functions

• day
• hour
• minute
• month
• second
• year

Others

• Complex query with precedence
• top
• select
• filter
• skip
• expand
• query children tables
• test (Mocha)

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"
                        }
                    ]
                }
            }
        }
    }
}