node-pg-crud

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

Installation

1$ npm install node-pg-crud

Usage

1const 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

1const CRUDModel = new CRUDBuilder(
2    POOL, // Pool or Client instance from 'pg' library
3    MODEL_NAME, // Name of CRUDModel instance (typically the name of the table)
4    TABLE_NAME, // Name of table in PostgreSQL database
5    DEFAULT_SELECT_QUERY, // Default query to be used when querying data if no custom query is specified
6    DEFAULT_SELECT_WHERE_QUERY, // Default filter to be used when querying data if no custom where clause is specified
7    TABLE_KEY // Optional key to set when aliasing main referenced table, eg. 'select * from users u' where 'u' is the table key
8).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:

1{
2   total, // total amount of results for specific query
3   page, // current page
4   pageSize, // max number of items to be returned in data; can be 'all' or a number
5   results, // number of items returned in data
6   pages, // amount of pages given query
7   data: [ // results
8      {id: ..., ...},
9      {},
10      ...
11   ]
12}

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:

1{ 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:

1{ 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:

1[{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

1const CRUDBuilder = require('node-pg-crud').default
2const { buildValuesEntries, buildUpdateEntries } = require('node-pg-crud')
3
4const TABLES = require('../tables')
5const { pool } = require('../../loaders/postgresql')
6
7const MODEL_NAME = 'User'
8const TABLE_NAME = TABLES.USERS
9const TABLE_KEY = 'u'
10
11const DEFAULT_SELECT_QUERY = `
12${TABLE_KEY}.id,
13${TABLE_KEY}.first_name,
14${TABLE_KEY}.last_name,
15${TABLE_KEY}.email
16from ${TABLE_NAME} ${TABLE_KEY}
17`
18const DEFAULT_SELECT_WHERE_QUERY = `where ${TABLE_KEY}.id = $1 limit 1`
19
20// create instance of PG CRUD Model
21const CRUD = new CRUDBuilder(pool, MODEL_NAME, TABLE_NAME, DEFAULT_SELECT_QUERY, DEFAULT_SELECT_WHERE_QUERY, TABLE_KEY).build()
22
23const get = (query = {}, pagination = {}) => {
24    // 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
25    const searchFields = [ // single and concatenated columns to search through with search parameter
26        `${TABLE_KEY}.first_name || ' ' || ${TABLE_KEY}.last_name`,
27        `${TABLE_KEY}.email`
28    ]
29    return CRUD.get(query, pagination, searchFields, DEFAULT_SELECT_QUERY)
30}
31
32const getById = id => CRUD.getById(id, DEFAULT_SELECT_QUERY, DEFAULT_SELECT_WHERE_QUERY)
33
34const insert = ({ first_name, last_name, email }) => {
35    const values = [first_name, last_name, email]
36    const valuesText = buildValuesEntries(values)
37    const queryText = `insert into ${TABLE_NAME} (first_name, last_name, email) VALUES (${valuesText}) returning id`
38
39    return CRUD.insert(queryText, values)
40}
41
42const update = async (id, { first_name, last_name, email }) => {
43    const updateParams = {
44        first_name,
45        last_name,
46        email
47    }
48
49    const { updateSetQueryText, updateValues } = buildUpdateEntries(updateParams)
50    if (!updateSetQueryText) throw Error({
51        id: `${MODEL_NAME.toLowerCase()}.update.error.no.input`,
52        message: `Failed to update ${MODEL_NAME}. No update values found.`,
53    })
54
55    const values = [id, ...updateValues]
56    const queryText = `update ${TABLE_NAME} ${updateSetQueryText} where id = $1`
57
58    return CRUD.update(id, queryText, values)
59}
60
61const remove = id => {
62    const values = [id]
63    const queryText = `delete from ${TABLE_NAME} where id = $1`
64
65    return CRUD.remove(id, queryText, values)
66}
67
68module.exports = {
69    get,
70    getById,
71    insert,
72    update,
73    remove
74}

Route

1const express = require('express')
2const httpStatus = require('http-status-codes')
3const { UserModel } = require('../../models')
4const { validate, validateRules } = require('./validator')
5
6const router = express.Router()
7
8router.get('/', validateRules('getUsers'), validate, async (req, res) => {
9    const {search, filter} = req.query
10    const {page, limit, sort} = req.query
11
12    try {
13        const result = await UserModel.get({ search, filter }, { page, limit, sort })
14        res.send(result)
15    } catch (error) {
16        // log error
17        return res.status(httpStatus.SERVICE_UNAVAILABLE).send({ error: error.message })
18    }
19})
20
21router.get('/:id', validateRules('getUserById'), validate, async (req, res) => {
22    const {id} = req.params
23
24    try {
25        const result = await UserModel.getById(id)
26        res.send(result)
27    } catch (error) {
28        // log error
29        return res.status(httpStatus.SERVICE_UNAVAILABLE).send({ error: error.message })
30    }
31})
32
33router.post('/', validateRules('createUser'), async (req, res) => {
34    const params = req.body
35
36    try {
37        const result = await UserModel.insert(params)
38        res.send(result)
39    } catch (error) {
40        // log error
41        return res.status(httpStatus.SERVICE_UNAVAILABLE).send({ error: error.message })
42    }
43})
44
45router.put('/:id', validateRules('updateUser'), async (req, res) => {
46    const { id } = req.params
47    const params = req.body
48
49    try {
50        const result = await UserModel.update(id, params)
51        res.send(result)
52    } catch (error) {
53        // log error
54        return res.status(httpStatus.SERVICE_UNAVAILABLE).send({ error: error.message })
55    }
56})
57
58router.delete('/:id', validateRules('deleteUser'), async (req, res) => {
59    const { id } = req.params
60
61    try {
62        const result = await UserModel.remove(id)
63        res.status(httpStatus.NO_CONTENT).send()
64    } catch (error) {
65        // log error
66        return res.status(httpStatus.SERVICE_UNAVAILABLE).send({ error: error.message })
67    }
68})
69
70module.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

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