zar-mysql-master v2.0.3

MySql queries generator for node.js

Help you easy generate hard queries in function way. Use full power of mysql driver and give acces to all parts. All variables and column names are checked and used as placeholders. It's avoid SQL Injection.

In 2 version - All callbacks replaced by promises.

Depends:

Node.js driver for mysql >= 2.12.0

Chapters:

• Install
• Init
• Example
• Common Information
• Get generated sql string
• Get Result
  • Run
  • Get Count
  • cell
  • one
  • get
• Requests
  • Insert
  • Update
  • Select
    • .table()
    • .field()
    • .fields()
    • .distinct()
    • .from()
    • .count()
    • .page()
    • .order()
    • .group()
    • .Joins()
    • .condition()
    • .id()
    • .conditionType()
  • Delete

Install

npm i --save zar-mysql-master

Init

var DBconf = {
    host     : 'localhost',
    user     : 'root',
    password : '',
    database : 'test'
};

and all another options for MySql Driver

const Database = (require('zar-mysql-master'))(DBconf);

So now:

console.log(Database) = {
    db: * - MySql Connection. = mysqlDriver.createConnection,
    connect(): function - Promise function to check connections
    request(): function - Generate object of request.
}

If you need to check connection manually - you can:

Database.connect()
    .then(function(){})  // success
    .catch(function(){}) // error
    ;

But you don't need check connection or connect in each request to db. All queries request - do it automatically.

Example

var DBconf = {
    host     : 'localhost',
    user     : 'root',
    password : '',
    database : 'test'
};

const Database = (require('zar-mysql-master'))(DBconf);

const GetUsersForEmail = Database.request()
    .Select('Users')
    .fields(['id','name'])
    .condition('age',18,'>')
    .condition('email',null,'!')
    .group('email')
    .order('id','<')
    .page(3,40);

GetUsersForEmail.get().then((emailTo)=>{
    if(emailTo.lenght) {
        emailTo.map(user=>{sendEmailTo(user)})
    }
})

Sended sql will be:

SELECT `Users`.`id`, `Users`.`name` FROM `Users` WHERE (`Users`.`age` > 18) AND (`Users`.`email` IS NOT NULL) GROUP BY `Users`.`email` ORDER BY `Users`.`id` ASC LIMIT 80, 40

Common Information

Each request you need get new request instance

const MyRequest = Database.request();

So now you can do your request:

MyRequest.Select().table('MyTable')...

or

MyRequest.Select('MyTable')...

All request don't run while you not need it!!!!

Get generated sql string

const mySqlString = MyRequest.Select('myTable').getSql();

Get results

If you need only requset from string.

MyRequest.sql(`SQL String`[, `Array of values - for replace placeholders`]).then() - result promise

Run

MyRequest.Insert().run() - result promise

or

MyRequest.Update().run() - result promise

Get Count

return only count of query selection

MyRequest
    .Select('myTable')
    .getCount([field][,distinict])
    .then((COUNT)=>{
        console.log('Your Count:', COUNT)
    })

Cell

return only first cell of first row in query selection

MyRequest
    .Select('myTable')
    .field('name')
    .cell()
    .then((NAME)=>{
        console.log('Your Name:', NAME)
    })

One

Return Object - first row of query selection

MyRequest
    .Select('users')
    .id(21)
    .one()
    .then((USER)=>{
        console.log('USER ID = 21: ', USER)
    })

Get

Return all query selection.

Use only for select (In other variants use - run)

MyRequest
    .Select('users')
    .condition('age',18,'>')
    .get()
    .then((USERS)=>{
        console.log('USERS OLDER THEN 18: ', USERS)
    })

Requests

Insert

MyRequest
    .Insert('Users')
    .Data({name:'Mike',pass:'qwerty'})
    .duplicate({name:'New Name'})
    .run()
    .then((infoAboutInsertion)=>{
        console.log(infoAboutInsertion);
    });

Update

If need to update all - don't add any condition and use .allowAll()

About condition - read below in .condition()

MyRequest
    .Update('Users')
    .id(21) //or .allowAll() if need to update all
    //.condition('id',21) Any Conditions what avaiable for Select
    .Data({name:'Mike',pass:'qwerty'})
    .run()
    .then((infoAboutUpdate)=>{
        console.log(infoAboutUpdate);
    });

Select(table)

MyRequest
    .Select([`tableName`])

.table(tableName ,tableAs)

MyRequest
    .Select()
    .table('OriginalTableName','myCustumName')

.field(fieldName ,tableAs, fieldAs)

MyRequest
    .Select()
    .table('OriginalTableName','myCustumName')
    .field('age',null,'userAge')

.fields(fieldsArray ,tableAs, fieldAs)

MyRequest
    .Select()
    .table('OriginalTableName','myCustumName')
    .fields(['age','name','id'])

.distinct(status = true)

MyRequest
    .Select()
    .table('OriginalTableName','myCustumName')
    .distinct()

.from(fromInt)

MyRequest
    .Select()
    .table('OriginalTableName','myCustumName')
    .from(10)

.count(getCountInt)

MyRequest
    .Select()
    .table('OriginalTableName','myCustumName')
    .count(50)

.page(currentPage,inPage)

Alias for .from() and .count() - Auto set data from page

MyRequest
    .Select()
    .table('OriginalTableName','myCustumName')
    .page(2,30)

.order(column ,order = 'DESC', tableAs)

order = (ASC | DESC | > = 'DESC' | < = 'ASC')

MyRequest
    .Select()
    .table('OriginalTableName','myCustumName')
    .order('age','>')

.group(column ,tableAs)

MyRequest
    .Select()
    .table('OriginalTableName','myCustumName')
    .group('name')
    .group('age')

Joins

innerJoin (table,joinParams ,joinTableAs)

leftJoin (table,joinParams ,joinTableAs)

rightJoin (table,joinParams ,joinTableAs)

MyRequest
    .Select()
    .table('OriginalTableName','myCustumName')
    .fields(['id','name','age'],'myCustumName')
    .fields(['type'],'typeAs')
    .leftJoin('type','id','typeAs') // LEFT JOIN `type` AS `typeAs` USING(`id`)
    .leftJoin('type',['some condition','and some condition'],'typeAs') // LEFT JOIN `type` AS `typeAs` ON((some condition) AND (and some condition))

.condition()

.condition(column , value, type, tableAs)

Your own condition

MyRequest
    .Select()
    .condition('`SomeColumn` = 21 OR 1 + 1')

Check NULL

MyRequest
    .Select()
    .condition('someColumn',NULL)      //= `someColumn` IS NULL
    .condition('someColumn',NULL,'!')  //= `someColumn` IS NOT NULL

value is Array

MyRequest
    .Select()
    .condition('someColumn',[1,2,3,4])            //= `someColumn` IN(1,2,3,4)
    .condition('someColumn',[1,2,3,4],'!')        //= `someColumn` NOT IN(1,2,3,4)
    .condition('someColumn',[10,20],'BETWEEN')    //= `someColumn` BETWEEN 10 AND 20
    .condition('someColumn',[10,20[,...]],'OR')         //= `someColumn` = 10 OR `someColumn` = 20
    .condition('someColumn',[10,20[,...]],'||')         //= `someColumn` = 10 OR `someColumn` = 20

value is other Sql Query (not compiled)

const Administrators = Database.request()
                        .Select('types')
                        .field('id')
                        .condition('type','admin');


Database.request()
    .Select()
    .condition('someColumn',Administrators)            //= `someColumn` IN(*SQL FROM ADMINISTRATOPS*)
    .condition('someColumn',Administrators,'in')       //= `someColumn` IN(*SQL FROM ADMINISTRATOPS*)
    .condition('someColumn',Administrators,'!in')      //= `someColumn` NOT IN(*SQL FROM ADMINISTRATOPS*)
    .condition('someColumn',Administrators,'=')        //= `someColumn` = (*SQL FROM ADMINISTRATOPS*)
    .condition('someColumn',Administrators,'!=')       //= `someColumn` != (*SQL FROM ADMINISTRATOPS*)
    .condition('someColumn',Administrators,'>')        //= `someColumn` > (*SQL FROM ADMINISTRATOPS*)
    .condition('someColumn',Administrators,'<')        //= `someColumn` < (*SQL FROM ADMINISTRATOPS*)

value is Primitive

MyRequest
    .Select()
    .condition('someColumn',55)            //= `someColumn` = 55
    .condition('someColumn',55,'=')        //= `someColumn` = 55
    .condition('someColumn',55,'!=')       //= `someColumn` != 55
    .condition('someColumn',55,'!')        //= `someColumn` != 55

    .condition('someColumn',55,'like')     //= `someColumn` LIKE 55
    .condition('someColumn',55,'%')        //= `someColumn` LIKE &55&
    .condition('someColumn',55,'%_')       //= `someColumn` LIKE &55
    .condition('someColumn',55,'_%')       //= `someColumn` LIKE 55&

    .condition('someColumn',55, '>')       //= `someColumn` > 55
    .condition('someColumn',55, '<')       //= `someColumn` < 55

.id(idInt)

Alias for .condition('id',INT)

MyRequest
    .Select()
    .table('OriginalTableName','myCustumName')
    .id(21)

MyRequest
    .Update('OriginalTableName')
    .id(21)
    .Data({})

MyRequest
    .Delete('OriginalTableName')
    .id(21)

.conditionType(type = AND)

MyRequest
    .Select()
    .table('OriginalTableName','myCustumName')
    .condition('name','Mike')
    .condition('name','Mi','_%')
    .conditionType('OR')

Delete

If need to remove all - don't add any condition and use .allowAll()

About condition - read in .condition()

MyRequest
    .Delete('Users')
    .id(21) //or .allowAll() if need to update all
    //.condition('id',21) Any Conditions what avaiable for Select
    .run()
    .then((infoAboutDelete)=>{
        console.log(infoAboutDelete);
    });

Author : Mike (myvzar) LinkedIn