query_builder

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

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

Introduction

This is a small mysql query builder using the nodejs 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 );

OR

var dbconn_default = {
    host : 'host',
    user : 'user',
    pass : 'password',
    dbase : 'database_schema'
};
 
var qb = new query_builder();
 
qb.add( dbconn_default );

Connect in other database

var dbconn1 = {
    host : 'host',
    user : 'user',
    pass : 'password',
    dbase : 'other_database_schema',
    pool_name : 'DBCONN1'
};
 
qb.add( dbconn1 );
 
qb.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.