4.0.0 • Published 5 years ago

jm-ez-mysql v4.0.0

Weekly downloads
21
License
MIT
Repository
github
Last release
5 years ago

jm-ez-mysql

Overview

Easy MySQL Wrapper for NodeJS

Installation

npm install jm-ez-mysql --save

Examples

var My = require("jm-ez-mysql");

// Init DB Connection
My.init({
    host: 'localhost',
    user: 'root',
    password: 'root',
    database: 'psu',
});

// Select All
My.findAll("psu_project", ["id"], "1=1").then(function (r) {
    console.log(r)
})

// Select All with count
My.findAllWithCount("psu_project", "id", ["id"], "1=1", "LIMIT 0, 5").then(function (r) {
    console.log(r)
})

// Select First
My.first("psu_project", ["id"], "1=1 ").then(function (r) {
    console.log(r);
});

// Insert
My.insert("temp", {
    name: 'Jay'
}).then(function (result) {
    console.log(result.insertId)
})

// Insert Multiple Rows
My.insertMany("temp", [{
    name: 'Jay'
}, {
    name: 'Subham'
}]).then(function (result) {
    console.log(result.insertId)
})

// Update
My.update("temp", {
    name: 'Jayu'
}, "id = 2").then(function (result) {
    console.log(My.lQ);
})

// Update First
My.updateFirst("temp", {
    name: 'Jayu'
}, "id = 2").then(function (result) {
    console.log(My.lQ);
})

// Delete
My.delete("temp", "id = 6").then(function () {
    console.log(My.lQ);
})

// Pure MySQL Query
var id = "'4";
My.findRaw("select * from psu_project where id = " + My.escape(id))
    .then(function (results) {
        console.log('My query results', results);
        console.log('Lq', My.lQ);
    })
    .catch(function (err) {
        console.log(err);
    });

// Pure MySQL Query as Formatted String
var id = "'4";
My.query("select * from psu_project where id = ?", [id])
    .then(function (results) {
        console.log('My query results', results);
        console.log('Lq', My.lQ);
    })
    .catch(function (err) {
        console.log(err);
    });

// Select All using prepared statement
My.findAll("psu_project", ["id"], "id=?", [id]).then(function (r) {
    console.log(r)
})

// Select All with count using prepared statement
My.findAllWithCount("psu_project", "id", ["id"], "id=?", "LIMIT 0, 5", ["id"]).then(function (r) {
    console.log(r)
})

// Select First using prepared statement
My.first("psu_project", ["id"], "1=? ", [id]).then(function (r) {
    console.log(r);
});

// Update using prepared statement
My.update("temp", {
    name: 'Jayu'
}, "id = ?", [id]).then(function (result) {
    console.log(My.lQ);
})

// Update First using prepared statement
My.updateFirst("temp", {
    name: 'Jayu'
}, "id = ?", [id]).then(function (result) {
    console.log(My.lQ);
})

// Delete using prepared statement
My.delete("temp", "id = ?", [id]).then(function () {
    console.log(My.lQ);
})

//Select Query with where condition
const selectQuery = My.initQuery();
selectQuery.where('id', 1);
selectQuery.execute("table");

//Select Query with multiple where condition
const selectQuery = My.initQuery();
selectQuery.where('id', 1);
selectQuery.where('name', 'somename');
selectQuery.execute("table");


//Select Query with multiple(AND/OR) where condition
const selectQuery = My.initQuery();
selectQuery.where('id', 1);
selectQuery.orWhere('name', 'searchContent');
selectQuery.execute("table");
//query : SELECT  *  FROM table  WHERE id = 1   OR  name = 'searchContent';

//Select Query with multiple(AND/OR) with multple condition where condition
const selectQuery = My.initQuery();
selectQuery.where('(id = ? OR id = ?)', [1, 50]);
selectQuery.orWhere('name', 'searchContent');
selectQuery.execute("table");

//Another way to pass condition value
const selectQuery = My.initQuery();
newQuery.where('(id = ? OR id = ?)');
selectQuery.orWhere('name', 'searchContent');
selectQuery.execute("table", [1, 50]);
//query: SELECT  *  FROM table WHERE (id = 1 OR id = 50)  OR  name = 'searchContent';

//Select custom fields
const selectQuery = My.initQuery();
selectQuery.select('name, id, data'); // argument can be String|Array , Default is *
selectQuery.execute("table", [1, 50]);
//Query: SELECT name, id, data FROM table  WHERE 1=1

//Use joins : 

// Ex1. Left Join
const selectQuery = My.initQuery();
selectQuery.leftJoin("table2 as t2", "t2.t1ID = t1.id "); 
selectQuery.execute("table as t1");

// Query: SELECT  *  FROM table1 as t1 LEFT  JOIN table2 as t2 ON t2.t1ID = t1.id  WHERE 1=1;

// Ex2. Left and Right Join
const selectQuery = My.initQuery();
selectQuery.leftJoin("table2 as t2", "t2.t1ID = t1.id "); 
selectQuery.rightJoin("table3 as t3", "t3.t1ID = t1.id "); 
selectQuery.execute("table as t1");
//Query: SELECT  *  FROM table1 as t1 LEFT  JOIN table2 as t2 ON t2.t1ID = t1.id  RIGHT  JOIN table3 as t3 ON t3.t1ID = t1.id  WHERE 1=1;

// Ex3. Left Join with Condition
const selectQuery = My.initQuery();
selectQuery.leftJoin("table2 as t2", "t2.t1ID = t1.id AND t2.name = ? "); 
selectQuery.execute("table as t1", ['somename']);
//query: SELECT  *  FROM table as t1 LEFT  JOIN table2 as t2 ON t2.t1ID = t1.id AND t2.name = 'somename'  WHERE 1=1; 

// Ex4. Inner Join

const selectQuery = My.initQuery();
selectQuery.innerJoin("table2 as t2", "t2.t1ID = t1.id");
selectQuery.execute("table as t1");
// Query: SELECT  *  FROM table1 as t1 INNER  JOIN table2 as t2 ON t2.t1ID = t1.id  WHERE 1=1;

// Ex5.
const selectQuery = My.initQuery();
selectQuery.join("table2 as t2", "t2.t1ID = t1.id", "left"); // third argument will join type ex. (left, right, right outer, ...), default is inner
selectQuery.execute("table as t1");



//Use query as count
const selectQuery = My.initQuery();
selectQuery.select('name, id, data'); // argument can be String|Array , Default is *
selectQuery.where('(id = ? OR id = ?)', [1, 50]);
selectQuery.orWhere('name', 'searchContent');
selectQuery.execute("table", [], true); // If third argument will be true than last condition will remains and used for further process, like for count.
//Qeury: SELECT name, id, data FROM table  WHERE (id = 1 OR id = 50)  OR  name = 'searchContent'

selectQuery.count(); // argument can be any field name, default is id 
selectQuery.execute("table");
//Qeury: SELECT COUNT(table.id) as count  FROM table  WHERE (id = 1 OR id = 50) OR  name = 'searchContent'


//Use order by
const selectQuery = My.initQuery();
selectQuery.orderBy('id', 'ASC');
selectQuery.orderBy('name', 'DESC');
selectQuery.execute("table");
//Query: SELECT  *  FROM table  WHERE 1=1 ORDER BY id ASC, name DESC

//Use group by
const selectQuery = My.initQuery();
selectQuery.groupBy('id'); //
selectQuery.execute("table");
//Query:SELECT  *  FROM table  WHERE 1=1 GROUP BY id 

//Use Limit 
const selectQuery = My.initQuery();
selectQuery.skip(2); // records want to skip
selectQuery.limit(10);
selectQuery.execute("table");
//Query:SELECT  *  FROM table  WHERE 1=1 LIMIT 2, 10


// Get Last fired Query
console.log(My.lQ);

License

The MIT License (MIT)

4.0.0

5 years ago

3.0.1

6 years ago

2.5.0

6 years ago

2.3.0

7 years ago

2.2.0

7 years ago

2.1.0

7 years ago

2.0.0

7 years ago

1.0.4

7 years ago

1.0.3

9 years ago

1.0.2

9 years ago

1.0.1

10 years ago

1.0.0

10 years ago

0.0.8

10 years ago

0.0.7

10 years ago

0.0.6

10 years ago

0.0.5

10 years ago

0.0.4

10 years ago

0.0.3

10 years ago

0.0.2

10 years ago

0.0.1

10 years ago