Learn about our RFC process, Open RFC meetings & more.Join in the discussion! »

@wesselkuipers/odata-sequelize

1.0.15 • Public • Published

odata-sequelize

Note: This is a temporary fork until this PR has been merged.

License: MIT Build Status

NPM

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

  • 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
  1. Date Functions
  • day
  • hour
  • minute
  • month
  • second
  • year

Others

  • Test (Jest) - Thanks to @remcohaszing
  • Lint & Prettier - Thanks to @remcohaszing
  • Complex query with precedence
  • top
  • select
  • filter
  • skip
  • expand
  • query children tables

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

Install

npm i @wesselkuipers/odata-sequelize

DownloadsWeekly Downloads

966

Version

1.0.15

License

MIT

Unpacked Size

37 kB

Total Files

11

Last publish

Collaborators

  • avatar