my-query-builder

MySQL query builder

For more information and samples please check wiki

Installation

npm install --save my-query-builder

---

Usage

const myQueryBuilder = require('my-query-builder');

Specify default table name

myQueryBuilder.TABLE('users');

SELECT query

const query = myQueryBuilder
    .SELECT()
    .from('users')
    .get();

// SELECT * FROM `users`

const query = myQueryBuilder
    .SELECT('name', { email: 'emailAddress' }, { $CONCAT: { name: 'fullName' }, $ARGS: ['$ ', 'family'] })
    .from('users')
    .get();

// ELECT `name`, `email` AS `emailAddress`, CONCAT(`name`, ' ', `family`) AS `fullName` FROM `users`

const query = myQueryBuilder
    .SELECT('ID', 'name')
    .from('users')
    .where({ status: 'A' }, { name: { $LIKE: 'a' } })
    .get();

// SELECT `ID`, `name` FROM `users` WHERE ((`status` = 'A') AND (`name` LIKE '%a%'))

const query = myQueryBuilder
    .SELECT('users.ID', 'users.name', { 'projects.ID': 'projectID' }, { 'projects.name': 'projectName' })
    .from('users')
    .join({ $INNER: { projects: { userID: 'users.ID' } } })
    .get();

// 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: { '*': 'count' } })
    .from('users')
    .group('name')
    .get();

// SELECT `name`, COUNT(*) AS `count` FROM `users` GROUP BY `name`

const query = myQueryBuilder
    .SELECT('ID', 'name', { email: 'emailAddress' })
    .from('users')
    .having({ ID: { $GTE: 1000 } }, { emailAddress: { $LLIKE: '@gmail.com' } })
    .get();

// 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({ name: '$ASC' }, { ID: '$DESC' })
    .get();

// SELECT `ID`, `name` FROM `users` ORDER BY `name` ASC, `ID` DESC

const query = myQueryBuilder
    .SELECT('ID', 'name')
    .from('users')
    .skip(100)
    .limit(10)
    .get();
// SELECT `ID`, `name` FROM `users` LIMIT 100, 10

INSERT query

const user = {
    name: 'Ali',
    family: 'Amirnezhad',
    bday: '1979-06-03',
    email: '[email protected]',
    register: new Date(),
    'last-login': null
};

const query = myQueryBuilder
    .INSERT(user)
    .into('users')
    .get();

// INSERT INTO `users` (`name`, `family`, `bday`, `email`, `register`, `last-login`) VALUES ('Ali', 'Amirnezhad', '1979-06-03', '[email protected]', '2019-07-21 01:23:45', NULL)

UPDATE query

const user = {
    bio: 'Senior Full Stack Web Developer',
    'last-login': new Date()
};
const query = myQueryBuilder
    .UPDATE(user)
    .table('users')
    .where({ ID: 1 })
    .unique()
    .get();

// 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 })
    .unique()
    .get();

// 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.git
npm install
npm test