my-query-builder
MySQL query builder
For more information and samples please check wiki
Installation
npm install --save my-query-builder
Usage
const myQueryBuilder = ;
Specify default table name
myQueryBuilder;
SELECT query
const query = myQueryBuilder ; // SELECT * FROM `users`
const query = myQueryBuilder ; // ELECT `name`, `email` AS `emailAddress`, CONCAT(`name`, ' ', `family`) AS `fullName` FROM `users`
const query = myQueryBuilder ; // SELECT `ID`, `name` FROM `users` WHERE ((`status` = 'A') AND (`name` LIKE '%a%'))
const query = myQueryBuilder ; // SELECT `users`.`ID`, `users`.`name`, `projects`.`ID` AS `projectID`, `projects`.`name` AS `projectName` FROM `users` INNER JOIN `projects` ON (`projects`.`userID` = `users`.`ID`)
const query = myQueryBuilder ; // SELECT `name`, COUNT(*) AS `count` FROM `users` GROUP BY `name`
const query = myQueryBuilder ; // SELECT `ID`, `name`, `email` AS `emailAddress` FROM `users` HAVING ((`ID` >= 1000) AND (`emailAddress` LIKE '%@gmail.com'))
const query = myQueryBuilder ; // SELECT `ID`, `name` FROM `users` ORDER BY `name` ASC, `ID` DESC
const query = myQueryBuilder ;// SELECT `ID`, `name` FROM `users` LIMIT 100, 10
INSERT query
const user = name: 'Ali' family: 'Amirnezhad' bday: '1979-06-03' email: 'webilix@gmail.com' register: 'last-login': null; const query = myQueryBuilder ; // INSERT INTO `users` (`name`, `family`, `bday`, `email`, `register`, `last-login`) VALUES ('Ali', 'Amirnezhad', '1979-06-03', 'webilix@gmail.com', '2019-07-21 01:23:45', NULL)
UPDATE query
const user = bio: 'Senior Full Stack Web Developer' 'last-login': ;const query = myQueryBuilder ; // UPDATE `users` SET `bio` = 'Senior Full Stack Web Developer', `last-login` = '2019-07-21 01:12:45' WHERE ((`ID` = 1)) LIMIT 1
DELETE query
const query = myQueryBuilder ; // DELETE FROM `users` WHERE ((`ID` = 1)) LIMIT 1
Field Values and Functions
Type | INSERT | WHERE | ||
---|---|---|---|---|
Sample | Result | Sample | Result | |
String | field: 'value' | `field` = 'value' | {field: 'value'} | (`field` = 'value') |
Number | field: 1 | `field` = 1 | {field: 1} | (`field` = 1) |
NULL | field: null | `field` = NULL | {field: null} | ISNULL(field) |
Date | field: new Date() | `field` = '1979-06-03 01:23:45' | {field: new Date()} | (`field` = '1979-06-03 01:23:45') |
Date: $NOW | field: '$NOW' | `field` = '1979-06-03 01:23:45' | {field: '$NOW'} | (`field` = '1979-06-03 01:23:45') |
Date: $DATE | field: {$DATE: new Date()} | `field` = '1979-06-03' | {field: {$DATE: new Date()}} | (`field` = '1979-06-03') |
Date: $TIME | field: {$TIME: new Date()} | `field` = '01:23:45' | {field: {$TIME: new Date()}} | (`field` = '01:23:45') |
Date: $YEAR | field: {$YEAR: new Date()} | `field` = 1979 | {field: {$YEAR: new Date()}} | (`field` = 1979) |
Date: $TIMESTAMP | field: {$TIMESTAMP: new Date()} | `field` = 297221025 | {field: {$TIMESTAMP: new Date()}} | (`field` = 297221025) |
Array | field: [1, 2] | `field` = '{\"0\":1,\"1\":2}' | {field: [1, 2]} | (`field` = '{\"0\":1,\"1\":2}') |
Object | field: {a: 'b',c: 'd'} | `field` = '{\"a\":\"b\",\"c\":\"d\"}' | {field: {a: 'b',c: 'd'}} | (`field` = '{\"a\":\"b\",\"c\":\"d\"}') |
SELECT functions
- DISTINCT
- SUM
- AVG
- MAX
- MIN
- COUNT
- ASCII
- BIN
- OCT
- ORD
- HEX
- UNHEX
- BIT_LENGTH
- TO_BASE64
- FROM_BASE64
- DATE
- TIME
- YEAR
- MONTH
- MONTHNAME
- WEEK
- DAYOFYEAR
- DAYOFMONTH
- DAYOFWEEK
- HOUR
- MINUTE
- SECOND
- UNIX_TIMESTAMP
- LENGTH
- LCASE
- LOWER
- LTRIM
- RTRIM
- SPACE
- TRIM
- UCASE
- UPPER
- REVERSE
- CONCAT
- FORMAT
- INSTR
- LEFT
- REPLACE
- LPAD
- RPAD
- SUBSTR
- SUBSTRING
- DATE_FORMAT
WHERE / HAVING conditions
- : (equal)
- NOT
- EQ (equal with value function)
- NE (not equal)
- BETWEEN
- IN
- LIKE
- LLIKE (like at the end of string)
- RLIKE (like at the beginning of string)
- LT (less than)
- LTE (less than or equal)
- GT (greater than)
- GTE (greater than or equal)
Tests
git clone https://github.com/webilix/my-query-builder.gitnpm installnpm test