my-query-builder

1.1.0 • Public • Published

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: 'webilix@gmail.com',
    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', 'webilix@gmail.com', '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

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

Package Sidebar

Install

npm i my-query-builder

Weekly Downloads

1

Version

1.1.0

License

MIT

Unpacked Size

45.1 kB

Total Files

38

Last publish

Collaborators

  • webilix