modquery
v0.9.0
Published
A module to use for MySQL queries with respect to SQL.
Downloads
3
Readme
modQuery (v 0.5)
A module to use for MySQL queries with respect to SQL.
What is about
modQuery is a module that aims to bridge your JS code with MySQL with a respect to both SQL & Javascript. It's purpose is to wrap your SQL with extra functionality that is often needed. It's fun.
Why to use it
Because..
- You like using pure SQL but not have a unmaintainable JS code.
- You don't want flood your database with unnecessary connections & re-connections.
- You want to wrap your your Query with objects & callbacks that fit better to your JS logic.
- Your model in your JS code doesn't mirror exactly the Database Schema.
- You want a better SQL control.
- You want to have fun & you like beer.
What to have in mind
- It is not a persistence framework.
- It currently supports MySQL only.
- It can be used in production but with caution.
- Don't use it if you don't like beer.
Getting Started
As usual, include the module
var modQ = new require("modquery");
Initiate it.
var modQuery = new modQ({
dbArgs: {
host : "localhost",
user : "bartender",
password : "beerIsAwesome",
database : "MyPub"
}
});
Cheers!
// create a massive Query
modQuery.newModQuery()// TIP: use newModQuery() to void collisions & have better connection pooling
.from("beers") //
.select(["name"]) //
.filterBy("beers", "id").in([1,2,3,4]) //
.addParallel() // parallel execution & result fetch
//-------------------------- test regex
.from("beers") //
.select(['id', 'name']) //
.filterBy("beers", "name").regex("'^[a-d]'") //
.addParallel() // parallel execution & result fetch
//-------------------------- test equals
.from("beers") //
.select(['id', 'name']) //
.filterBy("beers", "id").equals(1) //
.addParallel() // parallel execution & result fetch
//-------------------------- test notEquals
.from("beers") //
.select(['id', 'name']) //
.filterBy("beers", "id").notEquals(1) //
.limit(10, 20)
.addParallel() // parallel execution & result fetch
//-------------------------- test contains
.from("beers") //
.select(['id', 'name']) //
.filterBy("beers", "name").contains("old") //
.limit(1, 10)
.addParallel() // parallel execution & result fetch
//-------------------------- test greaterThan
.from("beers") //
.select(['id', 'name']) //
.filterBy("beers", "abv").greaterThan(5) //
.limit(1, 10)
.addParallel() // parallel execution & result fetch
//-------------------------- Build and execute all queries in parallel
.execute(function(rows, err, sql) {
if(err){
// if error, show what the heck was executed
console.log(sql);
console.log(err);
}
console.log(rows);
});
Insert Rows:
modQuery.newModQuery()
.insertInto("beers")
.insert("name", "test Beer")
.insert("abv", 5.7)
.addParallel() // other row
.insertInto("beers")
.insert("name", "best Beer")
.insert("abv", 9)
.execute(function(rows, err, sql) {
if(err){
// if error, show what the heck was executed
console.log(sql);
console.log(err);
}
console.log(sql);
});
compiles to
INSERT INTO `beers` SET name = 'test Beer', abv = 5.7 ;
INSERT INTO `beers` SET name = 'best Beer', abv = 9 ;
A more Advanced
modQuery.newModQuery() //
.insertInto("beers_mirror") //
.from("beers") //
.select(["id", "name", "abv"]) //
.filterBy("beers", "id").in([1,2,3,4]) //
.limit(1, 10) //
.onDuplicate() //
.set("name","fooBar") //
.execute(function(rows, err, sql) {
if(err){
// if error, show what the heck was executed
console.log(sql);
console.log(err);
}
console.log(sql);
});
compiles to
INSERT INTO `beers_mirror`
SELECT id, name, abv FROM `beers`
WHERE ( `beers`.`id` IN (1,2,3,4) )
LIMIT 1,10
ON DUPLICATE KEY UPDATE
name = 'fooBar';
Update rows
modQuery.newModQuery() //
.update("beers_mirror") //
.set("name","fooBar1") //
.filterBy("beers_mirror", "id").in([1,2]) //
.execute(function(rows, err, sql) {
if(err){
// if error, show what the heck was executed
console.log(sql);
console.log(err);
}
console.log(sql);
});
compiles to
UPDATE `beers_mirror`
SET name = 'fooBar1'
WHERE ( `beers_mirror`.`id` IN (1,2) );
You can even enqueue heterogeneous Queries and execute them in pipe, callback will then be called for each one of them.
modQuery.newModQuery() //
.insertInto("beers_mirror") //
.from("beers") //
.select(["id", "name", "abv"]) //
.filterBy("beers", "id").in([1,2,3,4,5,6]) //
.limit(0, 10) //
.onDuplicate() //
.set("name","fooBar") //
.inAddition() // enQueue query
.update("beers_mirror") //
.set("name","fooBar1") //
.filterBy("beers_mirror", "id").in([1,2]) //
.inAddition() // enQueue query
.from("beers_mirror")// a SELECT * query
// Execute all as they have been piped
.execute(function(rows, err, sql) {
if(err){
// if error, show what the heck was executed
console.log(sql);
console.log(err);
}
console.log(sql);
});
compiles to
INSERT INTO `beers_mirror`
SELECT id, name, abv
FROM `beers`
WHERE ( `beers`.`id` IN (1,2,3,4,5,6) )
LIMIT 0,10
ON DUPLICATE KEY UPDATE
name = 'fooBar';
UPDATE `beers_mirror`
SET name = 'fooBar1'
WHERE ( `beers_mirror`.`id` IN (1, 2) );
SELECT * FROM `beers_mirror` ;
I found a bug
- First drink a beer,
- then create an issue,
- then pray & have faith.