node-pg-crud
    TypeScript icon, indicating that this package has built-in type declarations

    1.1.1 • Public • Published

    node-pg-crud

    Build Status Dependency Status

    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

    Install

    npm i node-pg-crud

    DownloadsWeekly Downloads

    13

    Version

    1.1.1

    License

    Apache-2.0

    Unpacked Size

    62.8 kB

    Total Files

    15

    Last publish

    Collaborators

    • howarded