PostgreSQL query builder for Node

A PostgreSQL query builder for NodeJS.

Installation

npm install @evershop/postgres-query-builder

Usage guide

It implements async/await.

Simple select

const {select} = require('@evershop/postgres-query-builder')

const products = await select("*")
.from("product")
.where("product_id", ">", 1)
.execute(pool);

More complex where

const {select} = require('@evershop/postgres-query-builder')

const products = await select("*")
.from("product")
.where("product_id", ">", 1)
.and("sku", "LIKE", "sku")
.execute(pool);

Event more complex where

const {select} = require('@evershop/postgres-query-builder')

const query = select("*").from("product");
query.where("product_id", ">", 1).and("sku", "LIKE", "sku");
query.orWhere("price", ">", 100);

const products = await query.execute(pool);

Join table

const {select} = require('@evershop/postgres-query-builder')

const query = select("*").from("product");
query.leftJoin('price').on('product.`product_id`', '=', 'price.`product_id`');
query.where("product_id", ">", 1).and("sku", "LIKE", "sku");
query.andWhere("price", ">", 100);

const products = await query.execute(pool);

Insert&update

```javascript
const {insert} = require('@evershop/postgres-query-builder')

const query = insert("user")
.given({name: "David", email: "[email protected]", "phone": "123456", status: 1, notExistedColumn: "This will not be a part of the query"});
await query.execute(pool);

const {update} = require('@evershop/postgres-query-builder')

const query = update("user")
.given({name: "David", email: "[email protected]", "phone": "123456", status: 1, notExistedColumn: "This will not be a part of query"})
.where("user_id", "=", 1);
await query.execute(pool);

Working with transaction

const { Pool } = require('pg');
const {insert, getConnection, startTransaction, commit, rollback} = require('@evershop/postgres-query-builder');

const pool = new Pool(connectionSetting);

// Create a connection from the pool
const connection = await getConnection(pool);

// Start a transaction
await startTransaction(connection);
try {
  await insert("user")
        .given({name: "David", email: "[email protected]", "phone": "123456", status: 1, notExistedColumn: "This will not be a part of the query"})
        .execute(connection);
  await commit(connection);
} catch(e) {
  await rollback(connection);
}

Security

All user provided data will be escaped.