sqlim

Basic Db Queries for mysql

Installation

npm install sqlim

Usage

Connect Mysql

var sqlim=require('sqlim');
sqlim.dataBase.host='localhost';
sqlim.dataBase.user='root';
sqlim.dataBase.pass='';
sqlim.dataBase.name='';
sqlim.connect();

Available Functions

• select
• from
• where
• where_or
• where_like
• where_like_or
• join
• join_left
• join_right
• getSql
• orderby
• groupby
• clear
• get
• insert
• update
• delete

select

sqlim.select('field').select('field1 as f').select('sum(field) as total');
// SQL: select field,field1 as f,sum(field) as total

from

sqlim.from('table').from('table1 t1');
// SQL: from table,table t1

where

sqlim.where('field',1).where('field2','test');
// SQL: where field=1 and field2='test'

where_or

sqlim.where('field',1).where_or('field2','test');
// SQL: where field=1 or field2='test'

where_like

sqlim.where_like('field','%t1%').where_like('field2','test');
// SQL: where field like '%t1%' and field2 like 'test'

where_like_or

sqlim.where_like('field','%t1%').where_like_or('field2','test');
// SQL: where field like '%t1%' or field2 like 'test'

join

sqlim.join('tablename t','t.ID=m.FIELD');
// SQL: join tablename t on t.ID=m.FIELD

join_left

sqlim.join_left('tablename t','t.ID=m.FIELD');
// SQL: left join tablename t on t.ID=m.FIELD

join_right

sqlim.join_right('tablename t','t.ID=m.FIELD');
// SQL: right join tablename t on t.ID=m.FIELD

getSql

sqlim.select('ID,NAME,USERNAME')
       .select('EMAIL')
       .where('ID',2)
       .where('ISADMIN',1)
       .from('users');
var sql=sqlim.getSql();
// OUTPUT sql: select ID,NAME,USERNAME,EMAIL from users where ID=2 and ISADMIN=2

orderby

sqlim.orderby('ID').orderby('DATE','DESC');
// SQL: order by ID asc,DATE desc

groupby

sqlim.groupby('YEAR(datefield)').groupby('STOCKNAME');
// SQL: group by YEAR(datefield),STOCKNAME

clear

Reset Sql

sqlim.clear();
// SQL: ""

get

Sql Results

var cb=function(err, results, fields){
console.log(results);
}
sqlim.get('tablename',cb);
// Running SQL: select * from tablename;
// OUTPUT: [{ID:1,FIELDNAME:'VALUE',ISTEST:'OK'}]

sqlim.select('ID,FIELDNAME').where('ISTEST','OK').get('tablename',cb);
// Running SQL: select ID,FIELDNAME from tablename;
// OUTPUT: [{ID:1,FIELDNAME:VALUE}]


sqlim.select('ID,FIELDNAME').where('ISTEST','OK').from('tablename').get(cb);
// Running SQL: select ID,FIELDNAME from tablename;
// OUTPUT: [{ID:1,FIELDNAME:VALUE}]

insert

var cb=function(err, result) {
    if (error) throw error;
    console.log(result.insertId);// last insert id
}
var data={
NAME:'test',
SURNAME:'test'
};
sqlim.insert('tablename',data,cb);
// SQL: "insert into tablename set NAME='test',SURNAME='test'"

update

var cb=function(err, result) {
    if (error) throw error;
    console.log(result);
}
var data={
NAME:'test',
SURNAME:'test'
};
sqlim.update('tablename',data,cb);
// SQL: "update tablename set NAME='test',SURNAME='test'"

sqlim.where('ID',17).update('tablename',data,cb);
// SQL: "update tablename set NAME='test',SURNAME='test' where ID=17"

delete

var cb=function(err, result) {
    if (error) throw error;
    console.log(result.affectedRows);
}
sqlim.delete('tablename',cb);
// SQL: "delete from tablename"

sqlim.where('ID',17).delete('tablename',cb);
// SQL: "delete from tablename where ID=17"