Skip to content

reb2020/5no-pg-builder

BranchesTags

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

27 Commits
src
src
 
 
test
test
 
 
typings
typings
 
 
.eslintrc.json
.eslintrc.json
 
 
.gitignore
.gitignore
 
 
README.md
README.md
 
 
index.js
index.js
 
 
package.json
package.json
 
 
tsconfig.json
tsconfig.json
 
 
yarn.lock
yarn.lock
 
 

Repository files navigation

5no LogoPG Builder

Simple query builder for PostgreSQL

Install

@5no/pg-builder requires Node version 8 or above.

npm install --save @5no/pg-builder

.env

DATABASE_URL=postgres://test:[email protected]:5432/testDB?ssl=false
DATABASE_QUERY_LOG=true

Manager.build

{
    table: "users",
    alias: "Testusers",
    schema: "custom",
    rowsHandler: fn(rows, method: 'select' | 'count' | 'update' | 'delete'),
}

Methods

async execute() // Return PG result
async rows() // Return rows of PG result
async result() // Return summary of count_rows, use only with count
async query() // Return raw query text

Examples

SELECT

const Manager = require('@5no/pg-builder')

const Users = await Manager.build({
    table: "users"
  }).select(["*"]).execute()

// "SELECT users.* FROM public.users AS users"    

const Users = await Manager.build({
    table: "users",
    alias: "Testusers"
  }).select(["*"]).execute()

// "SELECT Testusers.* FROM public.users AS Testusers"    

const Users = await Manager.build({
    table: "users",
    alias: "Testusers",
    schema: "custom"
  }).select(["*"])
  .execute()

// "SELECT Testusers.* FROM custom.users AS Testusers"    

const Users = await Manager.build({
    table: "users",
    alias: "Testusers",
    schema: "custom"
  }).select(["sum(email) AS sum_emails"])
  .execute()

// "SELECT sum(Testusers.email) AS sum_emails FROM custom.users AS Testusers"    

const Users = await Manager.build({
    table: "users",
    alias: "Testusers",
    schema: "custom"
  }).select(["email", "first_name", "last_name AS FN"])
    .where('status', '=', 'active')
    .whereIn('id', ['1', '2', '3'])
    .whereOr('email', '=', 'test')
    .whereOr('email', '=', 'test1')
    .execute()

// "SELECT Testusers.email, Testusers.first_name, Testusers.last_name AS FN FROM custom.users AS Testusers WHERE Testusers.status = $1 AND Testusers.id IN ($2,$3,$4) AND (Testusers.email = $5 OR Testusers.email = $6)"    

const Users = await Manager.build({
    table: "users",
    alias: "Testusers",
    schema: "custom"
  }).select(["email", "first_name", "last_name AS FN"])
    .whereOr('email', '=', 'test', 'email')
    .whereOr('email', '=', 'test1', 'email')
    .whereOr('last_name', '=', 'last_name1', 'last_name')
    .whereOr('last_name', '=', 'last_name2', 'last_name')
    .execute()

// "SELECT Testusers.email, Testusers.first_name, Testusers.last_name AS FN FROM custom.users AS Testusers WHERE (Testusers.email = $1 OR Testusers.email = $2) AND (Testusers.last_name = $3 OR Testusers.last_name = $4)"    

const Users = await Manager.build({
    table: "users",
    alias: "Testusers",
    schema: "custom"
  }).select(["email", "first_name", "last_name AS FN"])
    .distinct()
    .where('first_name', '=', 'first_name_test')
    .order('email', 'ASC')
    .order('first_name', 'DESC')
    .limit(10, 5)
    .execute()

// "SELECT DISTINCT Testusers.email, Testusers.first_name, Testusers.last_name AS FN FROM custom.users AS Testusers WHERE Testusers.first_name = $1 ORDER BY Testusers.email ASC, Testusers.first_name DESC LIMIT 10 OFFSET 5"    

const Users = await Manager.build({
    table: "users",
    alias: "Testusers",
    schema: "custom"
  }).select(["email"])
    .where('first_name', '=', 'first_name_test')
    .group(['email'])
    .order('email', 'ASC')
    .having('count(email)', '>', '1')
    .execute()

// "SELECT Testusers.email FROM custom.users AS Testusers WHERE Testusers.first_name = $1 GROUP BY Testusers.email HAVING count(Testusers.email) > $2 ORDER BY Testusers.email ASC"   

const Users = await Manager.build({
    table: "users",
    alias: "Testusers",
    schema: "custom"
  }).select(["email"])
    .where('first_name', '=', 'first_name_test')
    .whereBetween('created_at', '2019-10-12', '2019-11-12')
    .group(['email'])
    .order('email', 'ASC')
    .having('count(email)', '>', '1')
    .execute()

// "SELECT Testusers.email FROM custom.users AS Testusers WHERE Testusers.first_name = $1 AND Testusers.created_at BETWEEN $2 AND $3 GROUP BY Testusers.email HAVING count(Testusers.email) > $4 ORDER BY Testusers.email ASC"   

const Users = await Manager.build({
    table: "users",
    alias: "Testusers",
    schema: "custom"
  }).select(["email"])
    .where('first_name', '=', 'first_name_test')
    .whereNotBetween('created_at', '2019-10-12', '2019-11-12')
    .group(['email'])
    .order('email', 'ASC')
    .having('count(email)', '>', '1')
    .execute()

// "SELECT Testusers.email FROM custom.users AS Testusers WHERE Testusers.first_name = $1 AND Testusers.created_at NOT BETWEEN $2 AND $3 GROUP BY Testusers.email HAVING count(Testusers.email) > $4 ORDER BY Testusers.email ASC"   

const Users = await Manager.build({
    table: "users",
    alias: "Testusers",
    schema: "custom"
  }).count("email")
    .distinct()
    .execute()

//SELECT COUNT(DISTINCT TestUser.email) AS count_rows FROM custom.users AS TestUser

SELECT WITH JOIN

const SelectQueryInfo = await Manager.build({
    table: "users_info",
    schema: "custom"
  }).select()
    .where('status', '=', 't')
    .order('created_at', 'DESC')

const SelectQueryAddress = await Manager.build({
    table: "users_address",
    schema: "custom"
  }).select(["*"])
    .where('number', '=', '100')

const Users = await Manager.build({
    table: "users",
    alias: "Testusers",
    schema: "custom"
  }).select(["email", "first_name", "last_name AS FN"])
    .innerJoin(SelectQueryInfo, 'id', 'users_id')
    .leftJoin(SelectQueryAddress, 'id', 'users_id')
    .where('first_name', '=', 'first_name_test')
    .whereIsNull('last_name')
    .execute()

//SELECT Testusers.email, Testusers.first_name, Testusers.last_name AS FN, users_address.* FROM custom.users AS Testusers INNER JOIN custom.users_info AS users_info ON Testusers.id = users_info.users_id LEFT JOIN custom.users_address AS users_address ON Testusers.id = users_address.users_id WHERE Testusers.first_name = $1 AND Testusers.last_name IS NULL AND users_info.status = $2 AND users_address.number = $3 ORDER BY users_info.created_at DESC 


const Users = await Manager.build({
    table: "users",
    alias: "Testusers",
    schema: "custom"
  }).select(["email", "first_name", "last_name AS FN"])
    .innerJoin(SelectQueryInfo, 'id', 'users_id')
    .leftJoin(SelectQueryAddress, 'id', 'users_id')
    .where('id', '=', {
      builder: SelectQueryInfo,
      field: 'users_id'
    })
    .execute()

//SELECT Testusers.email, Testusers.first_name, Testusers.last_name AS FN, users_address.* FROM custom.users AS Testusers INNER JOIN custom.users_info AS users_info ON Testusers.id = users_info.users_id LEFT JOIN custom.users_address AS users_address ON Testusers.id = users_address.users_id WHERE Testusers.id = users_info.users_id AND users_info.status = $1 AND users_address.number = $2 ORDER BY users_info.created_at DESC

INSERT

const data = {
  email: '[email protected]',
  first_name: 'Test',
  last_name: null
}

const Users = await Manager.build({
    table: "users",
    schema: "custom"
  }).insert(data)
  .returning()
  .execute()

//INSERT INTO custom.user AS user (email, first_name, last_name) VALUES ($1, $2, NULL) RETURNING user.*


const Users = await Manager.build({
    table: "users",
    schema: "custom"
  }).insert(data)
  .onConflict(['email'])
  .doNothing()
  .returning()
  .execute()

//INSERT INTO custom.user AS user (email, first_name, last_name) VALUES ($1, $2, NULL) ON CONFLICT (email) DO NOTHING RETURNING user.*


const Users = await Manager.build({
    table: "users",
    schema: "custom"
  }).insert(data)
  .onConflict(['email'])
  .doUpdate(['email', 'first_name', 'last_name'])
  .returning()
  .execute()

//INSERT INTO custom.user AS user (email, first_name, last_name) VALUES ($1, $2, NULL) ON CONFLICT (email) DO UPDATE SET email = $2, first_name = $3, last_name = NULL WHERE user.email = $2 RETURNING user.*

UPDATE

const data = {
  email: '[email protected]',
  first_name: 'Test1',
  last_name: null
}

const Users = await Manager.build({
    table: "users",
    schema: "custom"
  }).update(data)
  .where("id", "=", "123")
  .execute()

//UPDATE custom.users AS users SET email = $1, first_name = $2, last_name = NULL WHERE users.id = $3

UPDATE WITH JOIN

const data = {
    email: '[email protected]',
    first_name: {
      builder: SelectQueryInfo,
      field: 'users_id',
    }
}

const SelectQuery = Manager.build({
      table: "user",
      alias: "TestUser",
      schema: "custom"
  }).update(data)
    .innerJoin(SelectQueryInfo, 'id', 'users_id')
    .where("id", "=", "123")
    .returning(['email'])
    .execute()

//UPDATE custom.user AS TestUser SET email = $1, first_name = users_info.users_id FROM custom.users_info AS users_info WHERE TestUser.id = $2 AND TestUser.id = users_info.users_id RETURNING TestUser.email

DELETE

const Users = await Manager.build({
    table: "users",
    schema: "custom"
  }).delete()
  .where("id", "=", "123")
  .execute()
  
//DELETE FROM custom.users AS users WHERE users.id = $1

DELETE WITH JOIN

const Users = await Manager.build({
    table: "users",
    schema: "custom"
  }).delete()
  .join(SelectQueryInfo, 'id', 'users_id')
  .where("id", "=", "123")
  .execute()
  
//DELETE FROM custom.users AS users USING custom.users_info AS users_info WHERE users.id = $1 AND TestUser.id = users_info.users_id

TRANSACTION

await Manager.begin()

await Manager.build({
    table: "users",
    schema: "custom"
  }).delete()
  .where("id", "=", "1233")
  .execute()

await Manager.build({
    table: "users",
    schema: "custom"
  }).delete()
  .where("id", "=", "1234")
  .execute()  

await Manager.commit() // or await Manager.rollback()

RAW QUERY

const Manager = require('@5no/pg-builder')

const SelectQueryInfo = await Manager.query("SELECT Testusers.email, Testusers.first_name, Testusers.last_name AS FN, users_address.* FROM custom.users AS Testusers INNER JOIN custom.users_info AS users_info ON Testusers.id = users_info.users_id LEFT JOIN custom.users_address AS users_address ON Testusers.id = users_address.users_id WHERE Testusers.first_name = $1 AND users_info.status = $2 AND users_address.number = $3 ORDER BY users_info.created_at DESC", [
  "one",
  "two",
  "three"
])

//SELECT Testusers.email, Testusers.first_name, Testusers.last_name AS FN, users_address.* FROM custom.users AS Testusers INNER JOIN custom.users_info AS users_info ON Testusers.id = users_info.users_id LEFT JOIN custom.users_address AS users_address ON Testusers.id = users_address.users_id WHERE Testusers.first_name = $1 AND users_info.status = $2 AND users_address.number = $3 ORDER BY users_info.created_at DESC

License

MIT Licensed, Copyright (c) 2018 Aleksandr Sokol

About

Simple query builder for PostgreSQL

Resources

Readme
Activity

Stars

1 star

Watchers

3 watching

Forks

1 fork
Report repository

Releases

2 tags

Packages

No packages published

Languages