vitamin-query

A fluent SQL query builder for Node.js It provides support for:

• PostgreSQL (v9.5+)
• SQLite (v3.15.0+)
• MySQL (v5.7+)
• MSSQL (2012+)

Installing

$ npm install --save vitamin-query

Getting started

vitamin-query is composed of a set of useful expressions and helpers to build SQL queries easily

building queries

// import the query builder
import qb from 'vitamin-query'
 
// Select query
let query = qb('employees').select('count(*)').where('salary between ? and (?1 * 2)', 1500).toQuery('pg')
assert.equal(query.sql, 'select count(*) from employees where salary between $1 and ($2 * 2)')
assert.deepEqual(query.params, [ 1500, 1500 ])
 
// Compound query
let query = qb('t').where('a is null').union(qb('t').where('b is null')).toQuery('pg')
assert.equal(query.sql, 'select * from t where a is null union select * from t where b is null')
 
// Insert query
let fred = { name: "Fred", score: 30 }
let query = qb('players').insert(fred).returning('*').toQuery('mssql')
assert.equal(query.sql, 'insert into players (name, score) output inserted.* values (?, ?)')
assert.deepEqual(query.params, [ 'Fred', 30 ])
 
// Update query
let query = qb('books').update({ status: 'archived' }).where('publish_date <= ?', 2000).toQuery('mysql')
assert.equal(query.sql, 'update books set status = ? where publish_date <= ?')
assert.deepEqual(query.params, [ 'archived', 2000 ])
 
// Delete query
let query = qb('accounts').delete().where({ activated: false, deleted: true }).toQuery('sqlite')
assert.equal(query.sql, 'delete from accounts where activated = ? and deleted = ?')
assert.deepEqual(query.params, [ false, true ])

Custom compiler

If you may use a custom query compiler instead of the built-in ones, you can pass its instance to toQuery()

// in path/to/maria-compiler.js
import MysqlCompiler from 'vitamin-query/compiler/mysql'
 
class MariaCompiler extends MysqlCompiler { ... }
 
// later, you can use its instance with any query instance
import qb from 'vitamin-query'
 
let query = qb().select().from('foo').toQuery(new MariaCompiler({ /* options */ }))

API

For examples of usage, please refer to the tests

Expression helpers

These Helpers are functions that return Expression instances:

• alias(expr, name: string; ...columns: string[]): IAlias
• table(value: string | IExpression): ITable
• func(name: string, ...args): IFunction
• raw(expr: string, ...args): ILiteral
• values(...data: any[][]): IValues
• id(name: string): IIdentifier
• esc(value: string): ILiteral
• val(value): ILiteral
• desc(expr): IOrder
• asc(expr): IOrder

Function helpers

Helpers to emulate the SQL built-in functions

• substr | substring(expr, start: number, length?: number): IFunction
• replace(expr, pattern, replacement): IFunction
• strpos | position(str, substr): IFunction
• repeat(expr, count: number): IFunction
• right(expr, length: number): IFunction
• left(expr, length: number): IFunction
• round(expr, n: number): IFunction
• space(length: number): IFunction
• upper | ucase(expr): IFunction
• lower | lcase(expr): IFunction
• len | length(expr): IFunction
• today | curdate(): IFunction
• clock | curtime(): IFunction
• concat(...parts): IFunction
• now | datetime(): IFunction
• rand | random(): IFunction
• minute(expr): IFunction
• second(expr): IFunction
• ltrim(expr): IFunction
• rtrim(expr): IFunction
• month(expr): IFunction
• date(expr): IFunction
• time(expr): IFunction
• trim(expr): IFunction
• year(expr): IFunction
• hour(expr): IFunction
• abs(expr): IFunction
• day(expr): IFunction
• utc(): IFunction

Testing

$ npm test

Change log

• v1.0.0-alpha - TypeScript version of the library

  • Breaking changes and many API are unsupported
  • Supports
    • sql functions
    • Clonable expressions
    • Join precedences (#21)
    • common table expressions
    • order by nulls first or last
    • compound queries using unions
  • drop support for multiple tables in select queries

• v0.2.1 - Add support for common table expressions

  • Deprecate Query::toSQL() and add Query::build() instead
  • Add Support for named parameters within raw helper (issue #6)
  • Add Support for common table expressions using Query::with()
  • Minor fixes

• v0.2.0 - API breaking changes

  • Remove the operator argument from Criteria.where() (issue #4)
  • Lowcase all the helper functions
  • Use class mixin to keep the code DRY
  • Fix minor bugs and typos

• v0.1.2 - Add new datetime helpers

• v0.1.1 - Add helpers for SQL functions

  • Configure Travis CI
  • Update README.md
  • Add helpers for SQL functions
  • Fix minor bugs

• v0.1.0 - Intial release