Nodejs MySQL Query Builder Wiki

A small lib that builds query using mysql and nodejs

Status: Alpha

Brought to you by: sarahseguenza

Home

Authors:

Nodejs MySQL Query Builder

A small lib that builds query using mysql and nodejs

credits to https://github.com/felixge/node-mysql for mysql nodejs module

Introduction
Database Configuration and Connecting to your Database
Connection Options
Queries
Select Statement
Update Statement
Delete Statement
Insert Statement
Native Query
Set Field Clause
Build a Condition Clause
Build a Join Clause
View the Generated Query

Introduction

This is a small mysql query builder using the nojejs mysql driver by felixge.

Sample Usage:

var query_builder = require('query_builder');

var dbconn_default = {
    host : 'host',
    user : 'user',
    pass : 'password',
    dbase : 'database_schema'
};

var qb = new query_builder( dbconn_default );

var select_details = {
    table : 'table_name',
    fields : [ 'field1', 'field2', ... ],
    conditions : {
        field1 : 'value1',
        field2 : 'value2'
    }
};

qb.select( select_details, function( err, rows ){
    if( err )
        console.log( err );
    else
        console.log( rows );
});

By this, we can generate a query something like this:

SELECT `field1`, `field2` FROM `table_name` WHERE `field1` = 'value1' AND `field2` = 'value2'

Database Configuration and Connecting to your Database

This module used the poolcluster functionality that provides multiple host connection.

Below is how you connect to your default database schema.

var dbconn_default = {
    host : 'host',
    user : 'user',
    pass : 'password',
    dbase : 'database_schema'
};

var default_schema = new query_builder( dbconn_default );

Connect in other database

var dbconn1 = {
    host : 'host',
    user : 'user',
    pass : 'password',
    dbase : 'other_database_schema',
    pool_name : 'DBCONN1'
};

var new = new query_builder( dbconn1 );

new.select( 
    {
        table : 'table_name_in_another_database',
        fields : [ 'field1', 'field2', ... ],
        conditions : {
            field1 : 'value1',
            field2 : 'value2'
        },
        pool_name : 'DBCONN1'
    }, 
    function( err, rows ){
        if( err )
            console.log( err );
        else
            console.log( rows );
    }
);

Connection Options

Configuration to establish connection are as follows:

host : Hostname of the database.
user : The username used to connect to the database.
pass : The password used to connect to the database.
dbase : The name of the database you want to connect to.
pool_name : Connection name identifier ( use when using multiple connections ).

Queries

To submit a query, use the following functions.

qb.select( query_details_here, callback );
qb.update( query_details_here, callback );
qb.delete( query_details_here, callback );
qb.insert( query_details_here, callback );
qb.native_query( query_here, callback );

This will automatically generate, escape and execute a query.

Select Statement

Options for Select Statement

table : The table name that you want to select. Required.
fields : An array of table fields that you want to show.
conditions : This is for WHERE clause. Please see Build a Condition Parameters.
joins : This is for JOIN portion of your query. Please see Build a Join Parameters.
order : Set an ORDER BY clause.
group : Permits you to create a GROUP BY clause.
limit : Limit the number of rows you would like returned by the query.
start_row : Set a result offset. Commonly used on pagination functionality.
count : Determine the number of rows in a particular table. A boolean type. Set to false by default.
count_fields : Field set with COUNT option. Sample is COUNT( users.id ) as X. This will overwrite the fields option.
show_query : View the generated query. Please see View the Generated Query.

    qb.select(
        {
            table : 'table',
            fields : [ 'field1', 'field2' ],
            conditions : {
                field1 : 'value1',
                field2 : 'value2'
            },
            order : 'field_name ASC',
            group : 'field_name, ...',
            limit : 10,
            start_row : 1,
            show_query : true
        },
        function( err, row ){
            // callback
            // err : an error code, msg
                // { [Error: ER_NO_SUCH_TABLE: Table 'database_schema.table' doesn't exist]
                //  code: 'ER_NO_SUCH_TABLE',
                //  errno: 1146,
                //  sqlState: '42S02',
                //  index: 0 }
            // row : contain results of the query
                // [{
                //  field1 : value1,
                //  field2 : value2
                //  }]
        }
    );

Update Statement

Options for Update Statement

table : The table name where you want to update a particular record. Required.
details : This is where we set the fields and its new values. Please see build Set Fields Parameters.
conditions : This is for WHERE clause. Please see Build a Condition Parameters.
show_query : View the generated query. Please see View the Generated Query.

    qb.update(
        {
            table : 'table',
            details : {
                field3 : 'new_value',
                ...    : '...'
            },
            conditions : {
                field1 : 'value1',
                field2 : 'value2'
            },
            show_query : true
        },
        function( err, result, number_of_changed_rows ){
            // callback
            // err : an error code, msg
                // { [Error: ER_NO_SUCH_TABLE: Table 'database_schema.table' doesn't exist]
                //  code: 'ER_NO_SUCH_TABLE',
                //  errno: 1146,
                //  sqlState: '42S02',
                //  index: 0 }
            // result : contain results of the query
            // number of changed rows : The number of changed rows when executing the update statement
        }
    );

Delete Statement

Options for Delete Statement

table : The table name where you want to delete a particular record. Required.
conditions : This is for WHERE clause. Please see Build a Condition Parameters.
show_query : View the generated query. Please see View the Generated Query.

    qb.delete(
        {
            table : 'table',
            conditions : {
                field1 : 'value1',
                field2 : 'value2'
            },
            show_query : true
        },
        function( err, result, number_of_affected_rows ){
            // callback
            // err : an error code, msg
                // { [Error: ER_NO_SUCH_TABLE: Table 'database_schema.table' doesn't exist]
                //  code: 'ER_NO_SUCH_TABLE',
                //  errno: 1146,
                //  sqlState: '42S02',
                //  index: 0 }
            // result : contain results of the query
            // number of affected rows : The number of deleted rows when executing the delete statement
        }
    );

Insert Statement

Options for Insert Statement

table : The table name where you want to insert a particular record. Required.
details : This is where we set the fields and its values. Please see build Set Fields Parameters.
show_query : View the generated query. Please see View the Generated Query.

    qb.insert(
        {
            table : 'table',
            details : {
                field1 : 'value1',
                field2 : 'value2'
            }
        },
        function( err, result, inserted_id ){
            // callback
            // err : an error code, msg
                // { [Error: ER_NO_SUCH_TABLE: Table 'database_schema.table' doesn't exist]
                //  code: 'ER_NO_SUCH_TABLE',
                //  errno: 1146,
                //  sqlState: '42S02',
                //  index: 0 }
            // result : contain results of the query
            // inserted id : The generated primary id of the newly created row
        }
    );

Native Query

Options for executing a query

query : Your query statement to execute.
pool_name : Use this if you have multiple connections. Optional.

    qb.native_query( { query : 'SELECT * FROM users' }, function( err, result ){
        console.log( result );
    });

Set Field Clause

This is for select and insert queries.

To build on what fields to insert and/or update, please create an object of field names and values

{ field1 : 'value', field2 : 'value' }

This will generate

UPDATE `table` SET `field1` = 'value', `field2` = 'value';
INSERT INTO `table` SET `field1` = 'value', `field2` = 'value';

Build a Condition Clause

This is where we build the WHERE clause.

For simple condition, this will generate an AND operator by default

    {
        field1 : 'value',
        field2 : 'value'
    }

    `field1` = 'value' AND `field2` = 'value'

Joined by OR

    {
        or : {
            field1 : 'value',
            field2 : 'value'
        }
    }

    `field1` = 'value' OR `field2` = 'value'

IN clauses

    {
        field1 : {
            field1 : 'value',
            field2 : 'value'
        }
    }

    `field1` IN( 'value', 'value' )

NOT clauses

    1. {
        not : {
            field1 : 'value',
            field2 : 'value'
        }
    }

    2. {
        'field1 !=' : 'value'
    }

    1. `field1` != 'value' AND `field2` != 'value'

    2. `field1` != 'value'

LIKE clause, by default, this will joined by AND

    {
        'field1 LIKE' : '%value%',
        'field2 NOT LIKE' : 'value%'
    }

    `field1` LIKE '%value%' AND `field2` NOT LIKE 'value%'

OR LIKE clause

LIKE clause, by default, this will joined by AND

    or : {
        'field1 LIKE' : '%value%',
        'field2 NOT LIKE' : 'value%'
    }

    `field1` LIKE '%value%' OR `field2` NOT LIKE 'value%'

Nested clause

    and : {
        and : {
            key1 : 'value',
            key2 : 'value'
        },
        or : {
            key3 : 'value',
            key4 : 'value'
        },
        not : {
            key5 : 'value',
            key6 : 'value'
        }
    }

    ( ( `key1` = 'value' and `key2` = 'value' ) and ( `key3` = 'value' or `key4` = 'value' ) and ( `key5` != 'value' AND `key6` != 'value' ) )

Other operators

    {
        'field1 !=' : 1,
        'field2 <=' : 1,
        'field3 >=' : 1
    }

    field1 != 1 AND field2 <= 1 AND field3 >= 1

Summary of Condition clause

    {
        field1 : 'value',
        field2 : 'value',
        'field1 !=' : 'value',
        'field1 LIKE' : '%value%',
        'field2 NOT LIKE' : 'value%',
        'field1 !=' : 1,
        'field2 <=' : 1,
        'field3 >=' : 1
        or : {
            field1 : 'value',
            field2 : 'value',
            'field1 LIKE' : '%value%',
            'field2 NOT LIKE' : 'value%'
        },
        and : {
            field1 : 'value',
            field2 : 'value'
        },
        field3 : {
            key1 : 'value',
            key2 : 'value'
        },
        not : {
            field1 : 'value',
            field2 : 'value'
        },
        and : {
            and : {
                key1 : 'value',
                key2 : 'value'
            },
            or : {
                key3 : 'value',
                key4 : 'value'
            },
            not : {
                key5 : 'value',
                key6 : 'value'
            }
        }
    }

    SELECT      * 
    FROM        `table` 
    WHERE       `field1` = 'value' 
                AND 
                `field2` = 'value' 
                AND 
                field1 != 1 
                AND 
                field1 LIKE '%value%' 
                AND 
                field2 NOT LIKE 'value%' 
                AND 
                field2 <= 1 
                AND 
                field3 >= 1 
                AND 
                ( `field1` = 'value' or `field2` = 'value' or field1 LIKE '%value%' or field2 NOT LIKE 'value%' ) 
                AND 
                ( ( `key1` = 'value' and `key2` = 'value' ) and ( `key3` = 'value' or `key4` = 'value' ) and ( `key5` != 'value' AND `key6` != 'value' ) ) 
                AND 
                `field3` IN ( 'value', 'value' )
                AND 
                ( `field1` != 'value' AND `field2` != 'value' )
    ;

Build a Join Clause

Permits you to write the JOIN portion of your query.

Options are as follows:

joins : An object of table details to join.
type : Optional and this is JOIN by default. Join types are left, right and inner.
ON clause : This is where the ON condition resides. Similar to Condition parameter format.

    qb.select(
        {
            table : 'users',
            joins : {
                'user_types' : {
                    type : 'left',
                    'users.user_type_id !=' : 'user_types.user_type_id'
                },
                'another table to join' : {
                    'field' : 'field'
                }
            }
        },
        function( err, rows ){
            console.log( rows );
        }
    );

    SELECT * FROM `users` LEFT OUTER JOIN `user_types` ON  users.user_type_id != user_types.user_type_id

View the Generated Query

This will log the generated query using console.log().

Setting is as simple as assigning a boolean value to show_query. This is set false by default.

Project Members:

sarah seguenza (admin)

Nodejs MySQL Query Builder Wiki

A small lib that builds query using mysql and nodejs

Home

Nodejs MySQL Query Builder

Table of Contents

Introduction

Database Configuration and Connecting to your Database

Connection Options

Queries

Select Statement

Update Statement

Delete Statement

Insert Statement

Native Query

Set Field Clause

Build a Condition Clause

Build a Join Clause

View the Generated Query

Project Members: