Querry Builder v 1.2.3

INFO

This tool is a query builder for nodeJs and pg-pool (postgre).
This also allows to make a pool to connect the database.

REQUIREMENTS AND DEPENDENCIES

This module needs nodeJs 8.x available at: https://nodejs.org

And it requires these two modules:

• pg [ npm pg ]
• pg-pool [ npm pg-pool ]

Those two modules are made sire Brianc, you can visit all of his work at npm-brianc

make sure to launch $ npm install.
If it does not work use $ npm install pg-pool pg --save.

FEATURES

The module allows to make queries:

• SELECT
• FROM
• INNER JOIN
• INSERT
• UPDATE
• DELETE
• subQuery "EXISTS", "IN", "NOT IN"
• GROUP BY
• HAVING

The module include the:

• idleTimeoutMillis
• connectionTimeoutMillis

using the existing pg-pool object

GET STARTED

To make it simple I use express and body-parser to write my APIs but the usage is the same. You can launch

$ npm install express body-parser

Or you can add these two more dependencies to add in your package.json:

"dependencies": {
   "body-parser": "^1.18.2",
   "express": "^4.16.0"
}

and run

$ npm install 

Here there are a few example of usage, do not hesitate to read full documentation

// simple express app launching ...
const express = require('express');
let app = express();
  
// import the QueryBuilder class
const QueryBuilder = require('pg-pool-query-builder');
  
// Make a pool 
let pool = new QueryBuilder({
    dbName:process.env.dbName,
    dbUser: process.env.dbUser,
    dbHost: process.env.dbHost,
    dbPort: process.env.dbPort,
    dbPassword: process.env.dbPassword
});
  
// GET synchronously person API
app.get('/person/sync', async (req, res)=>{

    let data = await
        pool.select("*")
            .from("person")
            .where("firstname")
            .in("toto")
            .syncExecute();

    res.status(200).json(data.rows);
});

// GET person API
app.get('/person', (req, res)=>{

    pool.select("*")
        .from("person")
        .where({
            "firstname": 'toto',
            "age": [25, 26]
        })
        .execute()
        .then( data => res.status(200).json(data.rows) )
        .catch( err => res.status(500).send(`ouch! error: ${err}`));
});

// POST person API
app.get('/address', async (req, res)=>{

    let data = await
        pool.select("*")
            .from("person")
            .innerJoin({person: 'address', address: 'id'})
            .where("firstname")
            .in("toto")
            .syncExecute();

    res.status(200).json(data.rows);
});

//GET person with subQuery
app.get('/personSubQuery', (req, res)=>{

    pool.select("*")
        .from("person")
        .where("firstname")
        .startSubQuery("IN")
            .select("firstname")
            .from("person")
        .endSubQuery()
        .execute()
        .then( data => res.status(200).json(data.rows) )
        .catch( err => res.status(500).send(`ouch! error: ${err}`));
});

//GET COUNT(id) person
app.get('/personCountId', (req, res)=>{

    pool.maxConnectionTimeout(0.1)
        .select(["firstname", "lastname", "COUNT (id) "])
        .from("person")
        .groupBy(["firstname", "lastname"])
        .having(" COUNT(id) <= 10")
        .execute()
        .then( data => res.status(200).json(data.rows) )
        .catch( err => res.status(500).send(`ouch! error: ${err}`));
});

// POST person API
app.post('/person', async (req, res)=>{
    let person = req.body.person;
    let columns = Object.keys(person);
    let values = Object.values(person);

    let data = await
        pool.insert("person", columns)
            .values(values)
            .syncExecute();

    res.status(200).json(data);
});

// PUT person API
app.put('/person', async (req, res)=>{
    let person = req.body.person;
    let id = person.id;
    delete person.id;

    let data =
        pool.update("person")
            .set(person)
            .where("id")
            .equal(id)
            .syncExecute()
            .catch( err => res.status(500).json(err) );

    res.status(200).json(data);
});

// DELETE person API
app.delete('/person', async (req, res)=>{
    let person = req.body.person;

    let data =
        pool.delete()
            .from("person")
            .where("id")
            .equal(person.id)
            .syncExecute()
            .catch( err => res.status(500).json(err) );

    res.status(200).json(data);
});
  
// start listening
app.listen(process.env.port);

DOCUMENTATION

N.B.: my server is actually down, my apologies, I will restore it soon. but if you want to have the doc, you can:

• run

$ npm run doc

from /node_modules/pg-pool-query-builder

• then, go to /node_modules/pg-pool-query-builder/doc/index.html instead ;) I will make a real domain name later

BY THE WAY

Special thanks to brianc.

I'll make a greater "get started" when I serve documentation By myself.

An EcmaScript 5 exist in the folder, I did not test it, i just made it with babel without caring of it by now. If it is asked I'll make a npm module for its ES5 version.

I think you can add issues at gitIssues

LICENSE

GNU