modquery

A module to use for MySQL queries with respect to SQL.

npm install modquery
8 downloads in the last month

modQuery (v 0.9)

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, install

$ npm install modquery

And 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`   ;

Lazy Execute: Executes query in asynchronous manner and streams each row of result set. Best approach when large result set needs to be processed row by row.

modQuery.newModQuery()
    .from("beers") //
    .select(["name"]) //
    .filterBy("beers", "id").in([1,2,3,4]) //
    // .. anything else can be appended
    .lazyExecute(function (stream) {
        stream.on("result",function (db, row) {
            db.pause();
            // do something with the row e.g push it to redis
            process.nextTick(function () { // setImmediate() might be better
                db.resume();
            });
        }).on('end',function () {
                // all done proceed!
                // here a promise can return its value,
                // or proceed with the next() function
            }).on('error', function (db, err) {
                console.log(err);
            });
    });

I found a bug

  • First drink a beer,
  • then create an issue,
  • then pray & have faith.
npm loves you