5no-pg-builder 中文文档教程
PG Builder
PostgreSQL 的简单查询生成器
此包已移至 @5no/pg-builder
Install
@5no/pg-构建器需要 Node 版本 8 或更高版本。
npm install --save @5no/pg-builder
.env
DATABASE_URL=postgres://test:123123@127.0.0.1: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
选择
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: 'test@test.com',
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: 'test1@test.com',
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: 'test@test.a.a',
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
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=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: 'test@test.com',
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: 'test1@test.com',
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: 'test@test.a.a',
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