PG Builder
Simple query builder for PostgreSQL
This package has moved to @5no/pg-builder
Install
@5no/pg-builder requires Node version 8 or above.
npm install --save @5no/pg-builder
.env
DATABASE_URL=postgres://test:123123@127.0.0.1:5432/testDB?ssl=falseDATABASE_QUERY_LOG=true
Manager.build
table: "users" alias: "Testusers" schema: "custom" rowsHandler:
Methods
async // Return PG resultasync // Return rows of PG resultasync // Return summary of count_rows, use only with countasync // Return raw query text
Examples
SELECT
const Manager = const Users = await Manager // "SELECT users.* FROM public.users AS users" const Users = await Manager // "SELECT Testusers.* FROM public.users AS Testusers" const Users = await Manager // "SELECT Testusers.* FROM custom.users AS Testusers" const Users = await Manager // "SELECT sum(Testusers.email) AS sum_emails FROM custom.users AS Testusers" const Users = await Manager // "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 // "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 // "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 // "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 // "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 // "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 //SELECT COUNT(DISTINCT TestUser.email) AS count_rows FROM custom.users AS TestUser
SELECT WITH JOIN
const SelectQueryInfo = await Manager const SelectQueryAddress = await Manager const Users = await Manager //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 //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: 'test@test.com' first_name: 'Test' last_name: null const Users = await Manager //INSERT INTO custom.user AS user (email, first_name, last_name) VALUES ($1, $2, NULL) RETURNING user.* const Users = await Manager //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 //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: 'test1@test.com' first_name: 'Test1' last_name: null const Users = await Manager //UPDATE custom.users AS users SET email = $1, first_name = $2, last_name = NULL WHERE users.id = $3
UPDATE WITH JOIN
const data = email: 'test@test.a.a' first_name: builder: SelectQueryInfo field: 'users_id' const SelectQuery = Manager //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 //DELETE FROM custom.users AS users WHERE users.id = $1
DELETE WITH JOIN
const Users = await Manager //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 await Manager await Manager await Manager // or await Manager.rollback()
RAW QUERY
const Manager = const SelectQueryInfo = await Manager //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