pg-helper

English | Simplified Chinese

Lightweight Node-Postgres build query assistant.

Since Node-postgres queries with ordinal parameters ($1, $2, etc), variables need to be in a clear order, which can be cumbersome if too many parameters are used. Pg-helper makes it easier, faster, and safer to build SQL.

Install

  yarn add @ckpack/pg-helper
  npm install @ckpack/pg-helper
Copy the code

Featrues

You do not need to pay attention to parameter sequence query

The template parameter {params} in SQL will be replaced with the corresponding value of the key in the object parameter. You can use {} template parameter anywhere, and it will be replaced with $n when executed

The two types of query are equivalent

// in node-postgres
pg.query(`SELECT * FROM ${tablename} WHERE field1 = $1 AND field2 = $2`, [field1, field2]);

// in pg-helper 
pgHelper.runSql(`SELECT * FROM ${tablename} WHERE field1 = {field1} AND field2 = {field2}`, {field1, field2});
// The following is still supported
pgHelper.runSql(`SELECT * FROM ${tablename} WHERE field1 = $1 AND field2 = $2`, [field1, field2])
Copy the code

Convenient single table query

insert

/**
SQL: INSERT INTO "public"."users"  (  "user", "email"  )  VALUES  ( $1, $2 ) ,  ( $3, $4 )  ;
values: ["jack","jack@test.com","chen","chen@test.com"]
**/
const result = await pgHelper.insert([{
  user: 'jack'.email: '[email protected]'}, {user: 'chen'.email: '[email protected]'}, {tableName: 'users'});Copy the code

delete

/**
SQL: DELETE FROM "public"."users"
     where  (  "username" ={username}  and "id" >0  or  (  "email" ={email}  )   )   ;
values: {"username":"jack","email":"demo@test.com"}
**/
const result = await pgHelper.delete({
  username: 'jack'.email: '[email protected]'
}, {
  where: {
    username: '={username}'.id: '> 0'.or: {
      email: '={email}'}},tableName: 'users'});Copy the code

update

/**
SQL: UPDATE "public"."users"
    SET  "email" = {email} 
     where  (  "username" ={username}  )   ;
values: {"username":"jack","email":"jack@test.com"}
**/
const result = await pgHelper.update({
  username: 'jack'.email: '[email protected]'
}, {
  update: ['email'].tableName: 'users'.where: {
    username: '={username}'}});Copy the code

select

/**
SQL: SELECT  * 
    FROM "public"."users"
    where  (  "username" ={username}  and "id" >0  or  (  "email" ={email}  )   )     ;
values: {"username":"jack","email":"demo@test.com"}
**/
const result = await pgHelper.select({
  username: 'jack'.email: '[email protected]'
}, {
  where: {
    username: '={username}'.id: '> 0'.or: {
      email: '={email}'}},tableName: 'users'});Copy the code

Simplify the operation of transactions

await pgHelper.runTSql([
    {
       sql: `DELETE FROM "public"."users" where ( "username" ={username} and "id" >0 or ( "email" ={email} ) )`.params: {"username":"jack"."email":"[email protected]"}, {},sql: `UPDATE "public"."users" SET "email" = {email} where ( "username" ={username} ) `.params: {"username":"jack"."email":"[email protected]"}}]);//OR

let transaction;
try {
  transaction = await pgHelper.getTransaction();
 
  await pgHelper.update({
    username: 'jack'.email: '[email protected]'
  }, {
    update: ['email'].tableName: 'users'.where: {
      username: '={username}'}});await pgHelper.delete({
    username: 'jack'}, {where: {
      username: '={username}',},tableName: 'users'}); transaction.commit(); }catch (error) {
  transaction.rollback();
}
Copy the code

Automatic disconnection and reconnection

API

PgHelper Class

new PgHelper(config, options)

config Object – same as pg.Pool
options
- options.autoHump Boolean– ifautoHumpThe names of fields returned for true are formatted as humps
- options.returning Boolean– ifreturningThe result returned with true contains updated, inserted, and modified data
- options.logger Object– Used to modify the default loginfo,errorTwo functions

const {PgHelper} = require('@ckpack/pg-helper');  
const pgHelper = new PgHelper({
    host,
    user,
    password,
    database,
    port: 5432.max: 20.idleTimeoutMillis: 30000.connectionTimeoutMillis: 2000}, {autoHump: true.logger: console.returning: true});Copy the code

pgHelper.insert(params, options)

Function

params

params Array<Object>– Insert table data, whereObjectThe key of the table must correspond to the fields in the table
options
- options.autoHump Boolean– ifautoHumpThe names of fields returned for true are formatted as humps
- options.tableNameString– the name of the table
- options.schemaNameString– Table name; default:public
- options.returning Boolean | Array– ifreturningIf true, the result will contain the inserted data, or if it is an array, the fields contained in the array are returned

return

same as pg.queries

pgHelper.delete(params, options)

Function

params

Params Object – template parameter, where the key of Object must correspond to the value of {params} in the SQL template
options
- Options. AutoHump Boolean – If autoHump is true, the name of the returned field will be formatted as a hump
- Options. tableNameString – Table name
- Options. schemaNameString – Table name; default: public
- Options. returning Boolean | Array – The result will include deleted data if RETURNING is true, or columns in returning Array
- Options. whereObject – Builds where SQL. You can nest and, or
```
{
	id: '> 10'.type: '={type}'.or: {id:'= any({ids})'.name: '={name}'}}// sql
//where (id > 0 and type={type} or (id = any({ids} or name ={name} ) )
Copy the code
```

return

same as pg.queries

pgHelper.update(params, options)

Function

params

Params Object – template parameter, where the key of Object must correspond to the value of {params} in the SQL template
options
- Options. AutoHump Boolean – If autoHump is true, the name of the returned field will be formatted as a hump
- Options. tableNameString – Table name
- Options. schemaNameString – Table name; default: public
- Returning Boolean | Array – The result will contain updated data if RETURNING IS true
- Options. whereObject – Builds where SQL. You can nest and, or
- Options. UpdateArray | Object – fields need to be updated
```
['name'.'type']
//name = {name},type={type}

{ name: 'name'.type: 'myType'}
//name = {name},type={myType}
 
['name', { field: 'type'.updated_at: 'now()'}]
// name = {name},updated_at=now()

{ 'name'.updated_at: sqlUtils.literalSql('now()')}// name = {name}, updated_at = now()  
Copy the code
```

return

same as pg.queries

pgHelper.select(params, options)

Function

params

Params Object – template parameter, where the key of Object must correspond to the value of {params} in the SQL template
options
- Options. AutoHump Boolean – If autoHump is true, the name of the returned field will be formatted as a hump
- Options. tableNameString – Table name
- Options. schemaNameString – Table name; default: public
- Options. whereObject – Builds where SQL
- options.limit int – limit number
- options.offset int – offset number
- Options. count Boolean – Whether to return the number of rows to query
- Options. include array – Array of returned fields default*
- Options. Order array – Build orderSQL
```
  ['id'['type'.'desc'], ['name'.'asc']]

  // order by id, type desc, name asc
Copy the code
```

return

same as pg.queries

pgHelper.runSql(sqlTem, obj, options)

Function

params

SqlTem String – The SQL to execute
Obj Object – Template parameter, where the key of Object must correspond to the value of {params} in the SQL template

options Object

Options. AutoHump Boolean – If autoHump is true, the name of the returned field will be formatted as a hump
Returning Boolean – Returns updated, inserted, and modified data if RETURNING is true

Options.transaction client-pghelper.getTransaction () Returns the value

let transaction;
try {
  transaction = await pgHelper.getTransaction();
  await pgHelper.runSql('select now()', {
    transaction,
  });
  await pgHelper.runSql('select power({a}, {b})', { a: 2.b: 4}, {
    transaction,
  });
  transaction.commit();

} catch (error) {
  transaction.rollback();
}
Copy the code

return

same as pg.queries

pgHelper.getTransaction()

Function

Get a transaction Client

pgHelper.runTSql(sqlTemps)

Function

Automatically rollback a transaction

params

sqlTemps Array<object>

[{sql: 'select power({a}, {b})'.params: { a: 2.b: 4}}, {sql: 'any sql'.params: '<any params>'}]Copy the code

return

same as pg.queries

pgHelper.commit()

Function

Commit a transaction

pgHelper.rollback()

Function

Rollback a transaction

sqlUtils

Functions used internally to construct SQL

sqlUtils.literalSql(str)

params

str String– It is useful to construct some special SQL that will not be returned as a key in the template

/** SQL: UPDATE "public"."users" SET "email" = {username}||'email.com' , "updated_at" = now() where ( "username" ={username} ) ; values: {"username":"jack"} **/
const {sqlUtils} = require('@ckpack/pg-helper');

const result = await pgHelper.update({
  username: 'jack'}, {update: {
    email: sqlUtils.literalSql("{username}||'email.com'"),
    updated_at: sqlUtils.literalSql('now()')},tableName: 'users'.where: {
    username: '={username}'}});Copy the code

Share a node SQL constructor

pg-helper

Install

Featrues

API

new PgHelper(config, options)

pgHelper.insert(params, options)

params

return

pgHelper.delete(params, options)

params

return

pgHelper.update(params, options)

params

return

pgHelper.select(params, options)

params

return

pgHelper.runSql(sqlTem, obj, options)

params

return

pgHelper.getTransaction()

pgHelper.runTSql(sqlTemps)

params

return

pgHelper.commit()

pgHelper.rollback()

sqlUtils

sqlUtils.literalSql(str)

params

sqlUtils.updateSql(update)

sqlUtils.insertSql(rows)

sqlUtils.orderSql(order)

sqlUtils.includeSql(include)

sqlUtils.whereSql(where)

sqlUtils.returningSql(returning)

sqlUtils.fieldsSql(fields)

sqlUtils.limitOffsetSql(option)

sqlUtils.sqlTemplate(str, obj)

sqlUtils.rowsUnderline2hump(rows)

Related Posts

Learning Java the strongest book list recommended, with learning methods

XML+time+OO

GitHub: Novel Coronavirus Epidemic Prevention information collection platform