sql-pg-helper v3.2.1

The library provide smart helpers for standard operations integrated with PostgreSQL.

Initialization

require('sql-pg-helper')({ client })

:warning: If the client is restricted by using sql-pg-restrict it's needed to add the restricted sql tag to the initialization:

const sql = require('sql-pg-restrict')
const client = sql.restrict(originalClient)
require('sql-pg-helper')({ client, sql })

Examples

Select

The return value is result.rows of the pg result object.

All columns

const rows = await client.select(
  'users',
  { email: 'email', passwordhash: 'passwordhash' }
)

// text: SELECT "*" FROM "users" WHERE "email" = $1 AND "passwordhash" = $2
// parameters: ['email', 'passwordhash']

Only the list of the given columns

const rows = await client.select(
  'users',
  ['id', 'active'],
  { email: 'email', passwordhash: 'passwordhash' }
)

// text: SELECT "id", "active" FROM "users" WHERE "email" = $1 AND "passwordhash" = $2
// parameters: ['email', 'passwordhash']

Insert

Single row (rows is an object)

The return value is the SERIAL generated by inserting the new row located in result.rows[0][serialColumn = 'id'] of the pg result object.

const id = await client.insert(
  'users',
  { email: 'email', passwordhash: 'passwordhash' }
)

// text: INSERT INTO "users" ("email", "passwordhash") VALUES ($1, $2) RETURNING "id"
// parameters: ['email', 'passwordhash']

Multiple rows (rows is an array of objects)

The return value is an array of the SERIALs generated by inserting the new row located in result.rows[][serialColumn = 'id'] of the pg result object.

const ids = await client.insert(
  'users',
  [
    { email: 'emailA', passwordhash: 'passwordhashA' },
    { email: 'emailB', passwordhash: 'passwordhashB' },
    { email: 'emailC', passwordhash: 'passwordhashC' }
  ]
)

// text: INSERT INTO "users" ("email", "passwordhash") VALUES ($1, $2), ($3, $4), ($5, $6) RETURNING "id"
// parameters: ['emailA', 'passwordhashA', 'emailB', 'passwordhashB', 'emailC', 'passwordhashC']

The return value is also an array if the given rows array contains only one object. It only depends on the datatype of rows.

Returning another serial column

const example = await client.insert(
  'users',
  { email: 'email', passwordhash: 'passwordhash' },
  'example'
)

// text: INSERT INTO "users" ("email", "passwordhash") VALUES ($1, $2) RETURNING "example"
// parameters: ['email', 'passwordhash']

Update

The return value is the count of the rows affected by the update located in result.rowCount of the pg result object.

const rowCount = await client.update(
  'users',
  { email: 'new email', passwordhash: 'new passwordhash' },
  { email: 'old email', passwordhash: 'old passwordhash' }
)

// text: UPDATE "users" SET "email" = $1, "passwordhash" = $2 WHERE "email" = $3 AND "passwordhash" = $4
// parameters: ['new email', 'new passwordhash', 'old email', 'old passwordhash']

Delete

The return value is the count of the rows affected by the delete located in result.rowCount of the pg result object.

const rowCount = await client.delete(
  'users',
  { email: 'email', passwordhash: 'passwordhash' }
)

// text: DELETE FROM "users" WHERE "email" = $1 AND "passwordhash" = $2
// parameters: ['email', 'passwordhash']

Transaction

It's possible to wrap multiple queries into a transaction which will be started and at the end committed. If an error is thrown, the transaction will be rollbacked.

await client.transaction(async () => {
  const email = 'email'
  const id = await client.insert(
    'users',
    { email, passwordhash: 'passwordhash' }
  )
  await client.insert(
    'audits',
    { type: 'user registration', message: `user with e-mail "${email}" is registered with the ID ${id}` }
  )
})