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 // Select querylet query = assertassert // Compound querylet query = assert // Insert querylet fred = name: "Fred" score: 30 let query = assertassert // Update querylet query = assertassert // Delete querylet query = assertassert
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 ... // later, you can use its instance with any query instance let query =
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 addQuery::build()
instead - Add Support for named parameters within
raw
helper (issue #6) - Add Support for common table expressions using
Query::with()
- Minor fixes
- Deprecate
-
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
- Remove the operator argument from
-
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