Nefariously Programmed Mecha

    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

    Install

    npm i my-query-builder

    DownloadsWeekly Downloads

    1

    Version

    1.1.0

    License

    MIT

    Unpacked Size

    45.1 kB

    Total Files

    38

    Last publish

    Collaborators

    • webilix