SQL Where Builder

A library to build SQL WHERE statements from object.

Install

npm install sql-where-builder

Usage

const sqlWhereBuilder = require('sql-where-builder')

sqlWhereBuilder({ test: 1, name: 'package' })
// {
//   statement: '`test` = ? AND `name` = ?',
//   parameters: [1, 'package']
// }

Run tests

npm run test

Examples

Basic Example

const sqlWhereBuilder = require('sql-where-builder')

var where = sqlWhereBuilder({ test: 1, name: 'package' })
// {
//   statement: '`test` = ? AND `name` = ?',
//   parameters: [1, 'package']
// }

Or Queries

sqlWhereBuilder({ $or: { status: 'completed', dispatched: true }})
// {
//   statement: '`status` = ? OR `dispatched` = ?',
//   parameters: ['completed', true]
// }

Other comparasions

This requires object with type key as a value.

sqlWhereBuilder({
    status: {
        type: 'neq',
        value: 'completed'
    }
})
// {
//   statement: '`status` != ?',
//   parameters: ['completed']
// }

Available types

| type | result (needed variables) | Notes | |---|---|---| | eq | = value | Used by default if object value is string not object | | neq | != value | | | gt | > value | | | gte | >= value | | | lt | < value | | | lte | <= value | | | in | IN(value) | value must be array. Used by default if value is array not object | | nin | NOT IN(value) | value must be array | | between | BEWTEEN from AND to | | | like | LIKE value | | | nlike | NOT LIKE value | | | null | IS NULL | | | notnull | IS NOT NULL | |

sqlWhereBuilder({
    price: {
        type: 'between',
        from: 10,
        to: 100
    }
})
// {
//   statement: '`price` BETWEEN ? AND ?',
//   parameters: [10, 100]
// }

Options

Alias

There is an option to prepend all object keys with defined alias. Keys that have a dot already in the name won't get alias

sqlWhereBuilder({
  status: 1,
  'otherAlias.test': 'other'
}, {
  alias: 'alias',
})
// {
//   statement: '`alias`.`price` = ? AND `otherAlias`.`test` = ?',
//   parameters: [1, 'other']
// }