@axolo/sequelize-query

Sequelize Query

Generate Sequelize querying options by program, querystring, http or curl.

Convert Sequelize operators Aliases to Sequelize operators Symbol.

Alias	Symbol
$or	[Sequelize.Op.or]
$and	[Sequelize.Op.and]
$ne	[Sequelize.Op.ne]
$between	[Sequelize.Op.between]
...	...

install

npm install @axolo/sequelize-query --save

test

npm run test

use by program see in test

API

WARNING: only support version >= 0.1.0

sequelizeQuery(query, params = {})

parameters

Name	Type	Required	Description
query	Object	true	query (where with alias) for convert
params.Sequelize	Object		Sequelize, default to builtin
params.options	Object		Sequelize querying options
params.keys	Object		omit values of keys in where
params.excludeOps	Array		omit Sequelize Op alias

default to options

{
  offset: 0,
  limit: 1000,
}

default to keys

{
  attributes: 'attributes',
  include: 'include',
  where: 'where',
  order: 'order',
  offset: 'offset',
  limit: 'limit',
}

default to excludeOps

[]

return

Type	Description
Object	Sequelize querying options with operators Symbol

usage

A example of Egg.js at /app/controller/user.js by RESTful style router.

controller

'use strict';

const qs = require('qs');
const sequelizeQuery = require('@axolo/sequelize-query');
const Controller = require('egg').Controller;

class SequelizeQueryController extends Controller {
  async index() {
    const { app, ctx } = this;
    const { querystring } = ctx.request;
    const query = qs.parse(querystring);
    const options = sequelizeQuery(query, {
      Sequelize: app.Sequelize,
      logging: console.log,
      distinct: true,
      subQuery: false,
    });
    const user = await ctx.model.User.findAll(options);
    ctx.body = user;
  }

  async create() {
    const { app, ctx } = this;
    const { body } = ctx.request;
    const options = sequelizeQuery(body, {
      Sequelize: app.Sequelize,
      logging: console.log,
    });
    const user = await ctx.model.User.findAll(options);
    ctx.body = user;
  }
}

module.exports = SequelizeQueryController;

request

GET

/user?where={"username":{"$like":"%25ming%25"}}&limit=5&order=[["createdAt","desc"],["updatedAt","asc"]]

MUST encodeURIComponent querystring by url

POST

curl -X POST '/user' \
-H 'Content-Type: application/json' \
-d '{
  "where": { "username": { "$like": "%ming%" } },
  "order": [[ "createdAt", "desc" ], ["updatedAt", "asc" ]],
  "limit": 5
}'

response

[
  {
    "id": "e54160d0-ffa3-11e9-961a-013b0b64d1f2",
    "username": "yueming",
    "password": "password",
    "status": 0,
    "createdAt": "2019-11-05T08:11:40.000Z",
    "updatedAt": "2019-11-05T08:11:40.000Z",
    "deletedAt": null
  }
]

SQL

SQL from Sequelize

SELECT `id`, `username`, `password`, `status`, `createdAt`, `updatedAt`, `deletedAt`
FROM `user` AS `user`
WHERE (`user`.`deletedAt` IS NULL AND `user`.`username` LIKE '%ming%')
ORDER BY `user`.`createdAt` DESC, `user`.`updatedAt` ASC
LIMIT 0, 5;