node-pg-crud

Lightweight easy-to-use PostgreSQL CRUD handlers + utilities built. node-postgres is required.

Installation

$ npm install node-pg-crud

Usage

const CRUDBuilder = require('node-pg-crud')

CRUDBuilder

The CRUDBuilder object exposes a builder method to create a PostgreSQL Model, CRUDModel to be used to call typical CRUD Methods (get, getById, getByQuery, insert, put, delete).

CRUDBuilder.setLevel(limit: number | 'all')

Sets the default limit for the number of results when the CRUDModel.get() method is called.

CRUDBuilder.build()

Returns CRUDModel Type.

CRUDModel

const CRUDModel = new CRUDBuilder(
    POOL, // Pool or Client instance from 'pg' library
    MODEL_NAME, // Name of CRUDModel instance (typically the name of the table)
    TABLE_NAME, // Name of table in PostgreSQL database
    DEFAULT_SELECT_QUERY, // Default query to be used when querying data if no custom query is specified
    DEFAULT_SELECT_WHERE_QUERY, // Default filter to be used when querying data if no custom where clause is specified
    TABLE_KEY // Optional key to set when aliasing main referenced table, eg. 'select * from users u' where 'u' is the table key
).build()

CRUDModel.get(query: {search, customSearch, filter}, pagination: {page, limit, sort}, searchFields, selectQueryText)

Returns Promise for a dataset matching the query requested with the following result structure.

Example:

{
   total, // total amount of results for specific query
   page, // current page
   pageSize, // max number of items to be returned in data; can be 'all' or a number
   results, // number of items returned in data
   pages, // amount of pages given query
   data: [ // results
      {id: ..., ...},
      {},
      ...
   ]
}

query.search: String

The search parameter(s).

query.customSearch: String

A custom search query which is passed directly to the database.

query.filter: Object

Search filter options to be combined with the other filter options, and the search query where applicable.

Example:

{ status: 'active', enabled: true }

pagination.page: Integer

The requested page.

pagination.sort: Object

The different attributes which can be used to sort the results.

Example:

{ id: 'asc', first_name: 'desc' }

searchFields: [String]

Field names used to define what the search value is used to search through.

selectQueryText: String

Used to define what is being queried and to also define the structure with which the data is returned for the result's objects.

CRUDModel.getById(id, selectQueryText, whereQueryText)

Returns Promise for a single object returned from the database.

id: String | Integer

Object ID being referenced.

selectQueryText: String

Used to define what is being queried and to also define the structure with which the data is returned for the result's objects.

whereQueryText: String

Used to define a custom where clause.

CRUDModel.getByQuery(queryData, selectQueryText, returnAll)

Returns Promise for a single or all matching objects from the table based on a constructed query.

queryData: [Any]

Used to define the keys and variables being used to query.

Example:

[{key: 'name', value: nameVariable}, {status: true, value: statusVariable}]

selectQueryText: String

Used to define what is being queried and to also define the structure with which the data is returned for the result's objects.

returnAll: Boolean

Used to define whether the data returned is a single option or multiple.

CRUDModel.insert(queryText, values)

Returns Promise for the object that was inserted.

queryText: String

Defines the structure with which the data is inserted.

values: [Any]

Defines the values for the object to be inserted.

CRUDModel.update(id, queryText, values)

Returns Promise for the updated object.

id: String | Integer

Object ID being referenced.

queryText: String

Defines the query text for the data being updated.

values: [Any]

Defines the values for the object to be updated.

CRUDModel.remove(id, queryText, values)

Returns Promise for the updated object.

id: String | Integer

Object ID being referenced.

queryText: String

Defines the query text for the data being removed.

values: [Any]

Defines the values for the object to be removed.

Examples

Model

const CRUDBuilder = require('node-pg-crud').default
const { buildValuesEntries, buildUpdateEntries } = require('node-pg-crud')

const TABLES = require('../tables')
const { pool } = require('../../loaders/postgresql')

const MODEL_NAME = 'User'
const TABLE_NAME = TABLES.USERS
const TABLE_KEY = 'u'

const DEFAULT_SELECT_QUERY = `
${TABLE_KEY}.id,
${TABLE_KEY}.first_name,
${TABLE_KEY}.last_name,
${TABLE_KEY}.email
from ${TABLE_NAME} ${TABLE_KEY}
`
const DEFAULT_SELECT_WHERE_QUERY = `where ${TABLE_KEY}.id = $1 limit 1`

// create instance of PG CRUD Model
const CRUD = new CRUDBuilder(pool, MODEL_NAME, TABLE_NAME, DEFAULT_SELECT_QUERY, DEFAULT_SELECT_WHERE_QUERY, TABLE_KEY).build()

const get = (query = {}, pagination = {}) => {
    // use search & filter to create WHERE clause; search to do a text search across multiple columns, filter expects a where clause on a particular column
    const searchFields = [ // single and concatenated columns to search through with search parameter
        `${TABLE_KEY}.first_name || ' ' || ${TABLE_KEY}.last_name`,
        `${TABLE_KEY}.email`
    ]
    return CRUD.get(query, pagination, searchFields, DEFAULT_SELECT_QUERY)
}

const getById = id => CRUD.getById(id, DEFAULT_SELECT_QUERY, DEFAULT_SELECT_WHERE_QUERY)

const insert = ({ first_name, last_name, email }) => {
    const values = [first_name, last_name, email]
    const valuesText = buildValuesEntries(values)
    const queryText = `insert into ${TABLE_NAME} (first_name, last_name, email) VALUES (${valuesText}) returning id`

    return CRUD.insert(queryText, values)
}

const update = async (id, { first_name, last_name, email }) => {
    const updateParams = {
        first_name,
        last_name,
        email
    }

    const { updateSetQueryText, updateValues } = buildUpdateEntries(updateParams)
    if (!updateSetQueryText) throw Error({
        id: `${MODEL_NAME.toLowerCase()}.update.error.no.input`,
        message: `Failed to update ${MODEL_NAME}. No update values found.`,
    })

    const values = [id, ...updateValues]
    const queryText = `update ${TABLE_NAME} ${updateSetQueryText} where id = $1`

    return CRUD.update(id, queryText, values)
}

const remove = id => {
    const values = [id]
    const queryText = `delete from ${TABLE_NAME} where id = $1`

    return CRUD.remove(id, queryText, values)
}

module.exports = {
    get,
    getById,
    insert,
    update,
    remove
}

Route

const express = require('express')
const httpStatus = require('http-status-codes')
const { UserModel } = require('../../models')
const { validate, validateRules } = require('./validator')

const router = express.Router()

router.get('/', validateRules('getUsers'), validate, async (req, res) => {
    const {search, filter} = req.query
    const {page, limit, sort} = req.query

    try {
        const result = await UserModel.get({ search, filter }, { page, limit, sort })
        res.send(result)
    } catch (error) {
        // log error
        return res.status(httpStatus.SERVICE_UNAVAILABLE).send({ error: error.message })
    }
})

router.get('/:id', validateRules('getUserById'), validate, async (req, res) => {
    const {id} = req.params

    try {
        const result = await UserModel.getById(id)
        res.send(result)
    } catch (error) {
        // log error
        return res.status(httpStatus.SERVICE_UNAVAILABLE).send({ error: error.message })
    }
})

router.post('/', validateRules('createUser'), async (req, res) => {
    const params = req.body

    try {
        const result = await UserModel.insert(params)
        res.send(result)
    } catch (error) {
        // log error
        return res.status(httpStatus.SERVICE_UNAVAILABLE).send({ error: error.message })
    }
})

router.put('/:id', validateRules('updateUser'), async (req, res) => {
    const { id } = req.params
    const params = req.body

    try {
        const result = await UserModel.update(id, params)
        res.send(result)
    } catch (error) {
        // log error
        return res.status(httpStatus.SERVICE_UNAVAILABLE).send({ error: error.message })
    }
})

router.delete('/:id', validateRules('deleteUser'), async (req, res) => {
    const { id } = req.params

    try {
        const result = await UserModel.remove(id)
        res.status(httpStatus.NO_CONTENT).send()
    } catch (error) {
        // log error
        return res.status(httpStatus.SERVICE_UNAVAILABLE).send({ error: error.message })
    }
})

module.exports = router

Running Locally

1. git clone https://github.com/howard-e/node-pg-crud.git

Build

1. cd node-pg-crud
2. npm install
3. npm run build

Example Project

1. cd example/scrips
2. Run ./db-populate-local.sh to populate a PostgreSQL Database. (This script assumes a PostgreSQL database is running locally on PORT: 5432, with the username: admin, password: Passw0rd1 and a database called database)
3. cd ..
4. Create a .env file with the structure shown in the .env.example file. POSTGRES_CONNECTION_STRING MUST BE SET.
5. npm install
6. npm start
7. The application should now be running locally on PORT 4040 by default. This can be adjusted by overwriting the PORT variable in the .env file.

Why Use node-pg-crud?

Because it's easy to use.

License

Apache 2.0

TODO

• [x] Provide Usage Instructions
• [x] Provide Documentation
• [x] Provide Example Project
• [x] Provide Example Project Documentation
• [x] Provide "Why Use This?" Section
• [ ] Add Tests