{"_id":"@bekzod/sequelizeqp","name":"@bekzod/sequelizeqp","dist-tags":{"latest":"1.1.2"},"versions":{"1.1.2":{"name":"@bekzod/sequelizeqp","version":"1.1.2","description":"Small library to convert express query params (or objects in general) into a syntax recognised by the Sequelize library.","main":"index.js","scripts":{"test":"cd tests && mocha"},"keywords":["pagination","parser","sorting","query","express","sequelize","query-builder","query-parser"],"author":{"name":"Stuart Harrison"},"license":"GNU GPLv3","devDependencies":{"mocha":"^10.0.0","sqlite3":"^5.0.8"},"dependencies":{"sequelize":"^6.21.2"},"repository":{"type":"git","url":"git+https://github.com/stuartaharrison/sequelize-query-parser.git"},"bugs":{"url":"https://github.com/stuartaharrison/sequelize-query-parser/issues"},"homepage":"https://github.com/stuartaharrison/sequelize-query-parser#readme","_id":"@bekzod/sequelizeqp@1.1.2","gitHead":"364c7f738d0b6c35962d3781dbf1163ab415b390","_nodeVersion":"20.11.0","_npmVersion":"10.2.4","dist":{"integrity":"sha512-qH8eIBgR1YF3r85eMaSai31A6SLAuK3D5EUpOMQcJnhCfEkSxzWi/4mO2xhxr4mNK01Vaoo0I+whWyF9Tmd28g==","shasum":"e1041d188f96370f81b732403552ddcc201f7414","tarball":"https://registry.npmjs.org/@bekzod/sequelizeqp/-/sequelizeqp-1.1.2.tgz","fileCount":7,"unpackedSize":59031,"signatures":[{"keyid":"SHA256:jl3bwswu80PjjokCgh0o2w5c2U4LhQAE57gj9cz1kzA","sig":"MEUCIBnwy0/CFbRUFbxOwEEsTUa3j6UFmOpFV5zwPD2aUIkrAiEAi/7ylpp9il5NLunB6FIHA0fm1DyvOPnfLKRTN7yiO7w="}]},"_npmUser":{"name":"bekzod","email":"bekzod@me.com"},"directories":{},"maintainers":[{"name":"bekzod","email":"bekzod@me.com"}],"_npmOperationalInternal":{"host":"s3://npm-registry-packages","tmp":"tmp/sequelizeqp_1.1.2_1706769275517_0.09805295238218692"},"_hasShrinkwrap":false}},"time":{"created":"2024-02-01T06:34:35.419Z","1.1.2":"2024-02-01T06:34:35.658Z","modified":"2024-02-01T06:34:35.936Z"},"maintainers":[{"name":"bekzod","email":"bekzod@me.com"}],"description":"Small library to convert express query params (or objects in general) into a syntax recognised by the Sequelize library.","homepage":"https://github.com/stuartaharrison/sequelize-query-parser#readme","keywords":["pagination","parser","sorting","query","express","sequelize","query-builder","query-parser"],"repository":{"type":"git","url":"git+https://github.com/stuartaharrison/sequelize-query-parser.git"},"author":{"name":"Stuart Harrison"},"bugs":{"url":"https://github.com/stuartaharrison/sequelize-query-parser/issues"},"license":"GNU GPLv3","readme":"# Sequelize Qquery Parser\n\nAccept query parameters in express or similar libraries and convert them into syntax easily recognised by the [Sequelize Library](https://sequelize.org/). Useful for when building quick and simple API's for accepting varied user queries and searches.\n\n## Features\n\n* Handles basic operations to build queries from query parameters.\n* Basic pagination support to tag on `offset` and `limit` to your queries.\n* Basic & Multiple-Column Sorting support. Query and sort your results by 1-or-more columns.\n* Parses string input to integers, floats, booleans and dates.\n* Accepts non-string values (though from express it will likely come through as a string type anyway).\n* Allows specified DATETIME fields to be compared on the DATE-part only.\n* Blacklisting.\n* Aliasing.\n* Custom handlers for specific column/query-key names.\n\n### Operations\n\n| operation                 | query string         | query object |\n|---------------------------|----------------------|--------------|\n| equal                     | `?foo=bar`           | `{ foo: \"bar\" }` |\n| unequal                   | `?foo=!bar`          | `{ foo: { [Op.ne]: \"bar\" } }` |\n| between                   | `?age=\\|20\\|30`      | `{ age: { [Op.gte]: 20, [Op.lte]: 30 }` \n| is null                   | `?foo=`              | `{ foo: { [Op.is]: null } }` |\n| is not null               | `?foo=!`             | `{ foo: { [Op.not]: null } }` |\n| greater than              | `?foo=>10`           | `{ foo: { [Op.gt]: 10 } }` |\n| greater than or equal to  | `?foo=>=10`          | `{ foo: { [Op.gte]: 10 } }` |\n| less than                 | `?foo=<10`           | `{ foo: { [Op.lt]: 10 } }` |\n| less than or equal to     | `?foo=<=10`          | `{ foo: { [Op.lte]: 10 } }` |\n| starts with               | `?foo=^bar`          | `{ foo: { [Op.startsWith]: \"bar\" } }` |\n| ends with                 | `?foo=$bar`          | `{ foo: { [Op.endsWith]: \"bar\" } }` |\n| contains                  | `?foo=~bar`          | `{ foo: { [Op.like]: '%bar%' } }` |\n| in array                  | `?foo=$inbar\\|baz`   | `{ foo: { [Op.in]: ['bar', 'baz'] }}` |\n| not in array              | `?foo=$nin!bar\\|baz` | `{ foo: { [Op.notIn]: ['bar', 'baz'] }}` |\n\n_You can see the configuration section to see how the operations can be configured_\n\n_**Note:** I use `[Op.gte]` & `[Op.lte]` instead of `[Op.between]` to handle dates and other types better. I will investigate if the latter is a better option._\n\n### Aliasing & Blacklisting\nBy setting a column/property value into the Blacklist, you are telling the parser to simply ignore the key-value pair and not include it in the final output. With Aliasing, you can convert a specific incoming key-value pair to match the name of a known column. For example, converting incoming `personsAge=12` to `age=12`. Both of these can be configured in the initialise options.\n\n```javascript\nconst sequelizeDateQS = new SequelizeQS({\n    blacklist: ['createdAt'],\n    alias: {\n        'personsAge': 'age'\n    }\n});\n```\n\n### Date Only\nYou can now easily (as of v1.1.0) have specific Date-type columns converted to compare on the Date only instead of including the Timestamp. DATEONLY fields in Sqlite are still working as they did before though!\n\nTo prevent breaking changes from prior versions and to also not rely on guess working with the code, you will need to specify what columns in your Sequelize model are actual Date columns you want to be handled in this way. You must also enable this feature by setting the `dateOnlyCompare` property in the options to `true`.\n\n```javascript\nconst sequelizeDateQS = new SequelizeQS({\n    dateOnlyCompare: true,\n    dateFields: ['createdAt', 'updatedAt', 'lastLogin']\n});\n```\n\nNow when we compare on the `lastLogin` column, our query can come out like;\n\n```mysql\nSELECT count(*) AS `count` FROM `customers` AS `customers` WHERE date(`lastLogin`) LIKE '2021-%';\n```\n\nBy default, `dateOnlyCompare` will be `false` and the initial array value for `dateFields` will contain the original timestamp columns that sequelize typically adds to your models by default (createdAt & updatedAt).\n\n### Custom Handlers\nA big improvement over the initial version is the ability to have the parser handle specific columns/query string properties in a specific way. This is configured through the initial options when setting up the parser.\n\nPlease note, that the value your custom function must return is one that would be interpreted by the Sequelize library. You can see an example below for more details;\n\n```javascript\nconst { Op } = require('sequelize');\n\nconst minimumAgeHandler = (column, value, options) => {\n    return {\n        'age': {\n            [Op.gte]: value\n        }\n    }\n};\n\nconst sequelizeDateQS = new SequelizeQS({\n    customHandlers: {\n        minAge: minimumAgeHandler\n    }\n});\n```\n\n### Pagination\n\nThis library contains some basics for parsing pagination and setting default values. There is checks in place to ensure no negative pages are set and also that not *too many* records are pulled down. The `default maximum page size is 100` but this can be configured in the options. You can also omit `page` and `limit` from the parameters and no paging will take place. Though, setting 1 or both will have an effect or enabling pagination.\n\n| Param name | Default Value |\n|------------|---------------|\n| page | 1 |\n| limit | 25 |\n\n_You can see the configuration section to see how to adjust defaults for parameters_\n\n### Sorting\n\nYou can also configure your options to sort the results of your query by 1 or more columns.\n\n| operation                  | query string         | query object |\n|----------------------------|----------------------|--------------|\n| single column in asc       | `?sort=name`         | `order: [ [ 'name', 'ASC' ] ]` |\n| single column in desc      | `?sort=!name`        | `order: [ [ 'name', 'DESC' ] ]` |\n| multiple columns           | `?sort=age\\|name`    | `order: [ [ 'age', 'ASC' ], [ 'name', 'ASC' ] ]` |\n| multiple columns with desc | `?sort=age\\|!name`   | `order: [ [ 'age', 'ASC' ], [ 'name', 'DESC' ] ]` |\n\n## Install\n\n> Please not that you will need [Sequelize](https://www.npmjs.com/package/sequelize) for this to work.\n\n```\nnpm i --save sequelize\nnpm i --save sequelizeqp\n```\n\n## How to Use\n\n```javascript\nconst SequelizeQS = require('sequelizeqp');\nconst sequelizeParser = SequelizeQS();\n```\n\n### Configuration Options\n\nYou can pass additional configuration options into the constructor for the parser. This will change how the parser operations. **As of Version 1.1, this have been a little more fleshed out. However, This is currently pretty experimental and does not have all the options available. You should be cautious with the order of operations otherwise you might get some unintended results!**\n\n| property               | decription                                                                   | default |\n|------------------------|------------------------------------------------------------------------------|--------------|\n| ops                    | The available operations.                                                    | `['$in', '$nin', '$', '!', '\\|', '^', '~', '>=', '>', '<=', '<']` |\n| alias | The alias matching for properties and db columns. | `{ }` |\n| blacklist | The list of columns that should be ignored and not compared on | `[ ]` |\n| customHandlers | List of available custom handlers for specific columns | `{ }` |\n| dateOnlyCompare | Tells the parser to convert recognised `datetime fields` to `date-only` for comparisons | `false` |\n| dateFields | List of date columns in your model that are datetime type fields | `['createdAt', 'updatedAt']` |\n| defaultPaginationLimit | Limit to default too when no limit is set or maximum size has been exceeded. | `25` |\n| maximumPageSize        | The maximum page size allowed.                  | `100` |\n\n\n### Parse\n\n`fetch me the first page of 25 where the age of the customer is between 20 & 30 and the order total is more or equal to £100`\n\n```javascript\nvar parser = SequelizeQS();\nvar query = parser.parse({\n    page: 1,\n    limit: 25,\n    age: '|20|30',\n    orderTotal: '>=100'\n});\n\n// if using express:\n// var query = parser.parse(req.query);\n\nawait models.customers.findAll(query);\n```\n\n## Why build this Library?\n\nThere is a lot of questions out there that point to a libary like this being desired. However, equally there is calls that a library like this should not be required and to \"put in the work\" for each of your queries. Recently, I've found myself building small API's in express with MongoDB that included a react front-end in a CRM style front-end/web-application. There is a great small library for processing parameters into a mongoose query [here](https://www.npmjs.com/package/mongo-querystring) that I drew a lot of inspiration from. I found myself working with MySQL and Sequelize and could not find a library to do the queries I needed, so to save time now and in the future, I built this library for myself. Sharing is caring, so I want to make this library publicly available for anyone else who is seeking a solution like this.\n\n## Future of the Library (Roadmap)\n\nThis library was primarily built for myself when building multiple small API's and finding myself duplicating the code for querying & pagination. I do have plans to extend some of the features to include more complex AND & OR operations, though this might take time for me to complete. Feel free to fork and submit a PR if you want to contribute to these features.\n\nSome features that I have thought about but not necessarily implemented include;\n\n* complex queries with AND & OR operators.\n* ~~white & blacklist for specific parameter names.~~\n* ~~custom functions that execute when a specific parameter is included.~~\n* advanced sorting by functions (e.g. ordering by max(age))\n* ~~DATE-only comparison.~~\n\n## Collaborators\n\nCollaborating in a significant way will get you added to the collaborators list with higher access to make commits/changes.\n\n* Stuart Harrison - [@stuartaharrison](https://www.stuart-harrison.com/)\n\n## Licence\n\nThis project is licenced under the [GNU GPLv3](https://raw.githubusercontent.com/stuartaharrison/sequelize-query-parser/main/LICENSE) licence. Primarily to enable full-oper source and contribution but to keep closed sources from being generated.","readmeFilename":"README.md"}