pglink-lite
    TypeScript icon, indicating that this package has built-in type declarations

    0.5.5 • Public • Published

    pglink-lite

    A library for Postgresql to use ORM on NodeJS with GraphQL

    Via Janden Ma

    MIT LICENCE

    This library is built for who uses GraphQL on NodeJS, you can use model to operate data.

    Version Change Logs

    • Build20190812 : Prepared version
    • Build20190819 : Beta version
    • Build20190826 : Fixed bugs.
    • Build20190829 : Added multi insert and update functions.
    • Build20190917 :
      • MultiUpdateWithConditions bug fixes.
      • Supported all update functions in model to set the fields, which need to update to current time automatically, when updated.
    • Build20190919 : Supports set global parameter globalAutoSetTimeFields
    • Build20190924 :
      • Changed tableName and returnTableName in Transaction to alias and returnWithAlias
      • Added Execute function in dataAccess to run a single query
      • Bug fixes
      • Optimized something
    • Build20191022 : Update README.
    • Build20191111 : Vast changes.
    • Build20191112 :
      • Added the ability to force flatten results.
      • Added the ability to return a single record.
    • Build20191114 : Corrected pg package dependency.
    • Build20191120 :
      • Added connectionTimeoutMillis and idleTimeoutMillis parameters.
      • Added DataAccess.Disconnect() function (beta).
    • Build20191219 : findByPk function supports multiple primary keys.
    • Build20200107 : Resolved async issue for autoSetTimeFields
    • Build20200116 : Added ssl
    • Build20200120 : Bug fixes.
    • Build20200212 : Performance improvements.
    • Build20200218 : Bug fixes.
    • Build20200409 : Bug fixes and performance improvements.
    • Build20200421 : Added customize transaction functions
      • BeginTransaction
      • CommitTransaction
      • RollbackTransaction
    • Build20200423 : Bug fixes
    • Build20200427 : Optimized client connections in Transaction
    • Build20200514 : Corrected sortBy option for build querying sql
    • Build20200824 : Bug fixes
    • Build20200917 : Corrected declaration and signature of parameters to Transaction
    • Build20201010 : Upgraded pg to support PostgreSQL 13
    • Build20201027 : Try to resolve ssl connections failed issue
    • Build20201029 : Added preserveClient for leaving the client open for further operations in the transaction
    • Build20210110 : Throw error if no valid value to insert or update.
    • Build20210619 : Added safeguard in deleteByConditions

    Installation

    • npm

      npm i pglink-lite --save
    • yarn

      yarn add pglink-lite --save

    Quick Example

    • Instance (core/pglink.js)

      // core/pgsqlize.js
      const { PgLink } = require('pglink-lite')
      
      const pglink = new PgLink({
        host: 'http://192.168.1.100',
        port: 5432,
        userName: 'root',
        password: '123456',
        database: 'test',
        connectionTimeoutMillis: 0,
        idleTimeoutMillis: 60000,
        globalAutoSetTimeFields: ['updatedAt'],
        ssl: true
      })
      
      module.exports.default = pglink
    • Model (models/users.js)

      // models/users.js
      const pglink = require('../core/pglink')
      
      class UserModel extends pglink.Model {
        constructor() {
          super({ tableName: 'users', pkName: 'userId'})
        }
      }
      
      module.exports.default = UserModel
    • Schema (schemas/users.js)

      // schemas/users.js
      // you need to install something for gql first, we use apollo-server here
      const { gql } = require('apollo-server')
      
      const typeDefs = gql`
        type User {
          userId: ID!
          userName: String
          status: Boolean
        }
      
      	input UserInsertInput {
          userName!: String
      	}
      
      	input UserEditInput {
          userId!: ID
          userName: String
          status: Boolean
      	}
      
      	type Query {
      		getUserById(userId: ID!): User
      	}
      
      	type Mutation	{
      		insertUser(user: UserInsertInput): User
      		editUser(user: UserEditInput): User
      	}
      `
      module.exports.default = typeDefs
    • Resolver (resolvers/users.js)

      // resolvers/users.js
      const UserModel = require('../models/users.js')
      
      const getUserById = async (_, args) => {
        const inst = new UserModel()
        const res = await inst.findByPk({ pk: args.userId })
        return res
      }
      
      const insertUser = async (_, args) => {
        const inst = new UserModel()
        const res = await inst.insertOne({ params: args.user })
        return res
      }
      
      const editUser = async (_, args) => {
        const inst = new UserModel()
        const res = await inst.updateByPk({ params: args.user })
        return res
      }
      
      module.exports = {
        getUserById,
        insertUser,
        editUser
      }

    Usage

    • Import library package

      import { PgLink } from 'pglink-lite'
      // or
      import PgLink from 'pglink-lite'
      // or
      const { PgLink } = require('pglink-lite')
      // or
      const PgLink = require('pglink-lite')
    • Instantiate PgLink

      export const pglink = new PgLink({
        host: 'http://192.168.1.100',
        port: 5432,
        userName: 'root',
        password: '123456',
        database: 'test'
      })
      • Props: object

        Key Type Introduction Default value
        host string Postgresql server host "localhost"
        port number Postgresql server port 5432
        userName string Postgresql server user name "postgres"
        password string Postgresql server password ""(empty)
        database string Postgresql database name "postgres"
        connectionMax number Postgresql database max connection 10
        connectionTimeoutMillis number Number of milliseconds to wait before timing out when connecting a new client, by default this is 0 which means no timeout 0
        idleTimeoutMillis number Number of milliseconds a client must sit idle in the pool and not be checked out, before it is disconnected from the backend and discarded, default is 10000 (10 seconds) - set to 0 to disable auto-disconnection of idle clients 10000
        globalAutoSetTimeFields Array<string> To define fields that should be automatically updated with a current timestamp []
        ssl boolean To connect to pg using ssl false
    • Inherit and declare model

      // example
      class xxxModel extends pglink.Model {
        constructor(params) {
          super({
            tableName: 'users',
            pkName: 'No',
            enumMapping: {
              sex: { MALE: 1, FAMALE: 0 },
              role: { STUDENT: 1, TEACHER: 2 }
            }
          })
        }
        // if you need rewrite inner funtions or add some functions, write here
      }
      • constructor props : object
      Key Type Introduction Required
      tableName string the data table in postgresql you need to operate true
      pkName string the name of primary key in the data table, default id false
      enumMapping object to defined the key and value, key should be included in the fields, e.g. {role: {ADMIN: 0, USER: 1}} false
      • inner properties or functions

        1. dataAccess

          • Introduction

            A data table operator (CRUD)

          • see the details below

        2. findAll

          • Introduction

            A function for querying all rows from one table

          • Parameters

            { options: object, default { sortBy: undefined, limit: undefined, offset: undefined }

            preserveClient: boolean

            callback: function }

          • Returns

            (Promise) All rows data or error

        3. findByPk

          • Introduction

            A function for querying by primary key

          • Parameters

            {

            pkValue: string | number | object

            • if multiple primary keys, should use object, e.g. {id: 1, cid: 2}

            selectFields: string, default *

            callback: function

            preserveClient: boolean

            }

          • Returns

            (Promise) One row data or error

        4. findByConditions

          • Introduction

            A function for querying by conditions

          • Parameters

            { whereClause: string. (e.g. ' name = "Tim" ')

            selectFields: string, default *

            options: object, default { sortBy: undefined, limit: undefined, offset: undefined }

            preserveClient: boolean

            callback: function }

          • Returns

            (Promise) Some rows data or error

        5. InsertOne

          • Introduction

            A function for inserting one row to a table

          • Parameters

            {

            params: object. (data from resolver)

            callback: function

            client: object

            preserveClient: boolean

            }

          • Returns

            (Promise) Inserted row data or errors

        6. multiInsert

          • Introduction

            A function for inserting multi rows to a table

          • Parameters

            {

            items: Array<object>. (data from resolver)

            forceFlat?: boolean (whether or not to force results into a single array)

            callback: function

            client: object

            preserveClient: boolean

            }

          • Returns

            (Promise) Inserted rows data or errors

        7. updateByPk

          • Introduction

            A function for updating by primary key

          • Parameters

            { params: object. (data from resolver, have to include pkName and pkValue)

            autoSetTimeFields: Those fields need to set time automatically, should be included in items

            callback: function

            client: object

            preserveClient: boolean }

          • Returns

            (Promise) updated row data or errors

        8. updateByConditions

          • Introduction

            A function for updating by conditions

          • Parameters

            { params: object. (data from resolver)

            whereClause: string. (e.g. ' name = "Tim" ')

            autoSetTimeFields: Those fields need to set time automatically, should be included in items

            callback: function

            client: object

            preserveClient: boolean }

          • Returns

            (Promise) updated rows data or errors

        9. multiUpdateWithConditions

          • Introduction

            A function for multi updating by conditions

          • Parameters

            { items: Array<object>. (data from resolver)

            whereClause: string with replacements. (e.g. ' company = $1 ')

            replacementFields: Array<string> (e.g. ['company'])

            autoSetTimeFields: Those fields need to set time automatically, should be included in items

            forceFlat?: boolean (if true, forces results into a single array)

            callback: function

            client: object

            preserveClient: boolean }

          • Returns

            (Promise) Updated rows data or errors

        10. deleteByConditions

          • Introduction

            A function for deleting by conditions

          • Parameters

            { whereClause: string. (e.g. ' "companyId" = 1001 ')

            returnSingleRecord?: boolean (if true, returns just one record)

            callback: function

            client: object

            preserveClient: boolean }

          • Returns

            (Promise) Deleted rows data or errors

        11. encodeFromEnum

          • Introduction

            A function for encoding the enum to integer value

          • Parameters

            data: object | Array. (input data)

          • Returns

            (Object) Same structure of input data, with encoded enum

        12. decodeToEnum

          • Introduction

            A function for decoding the enum from integer value

          • Parameters

            data: object | Array. (output data)

          • Returns

            (Object) Same structure of output data, with decoded enum

      • dataAccess functions

        1. Execute

          • Introduction

            to run a single and simple sql

          • Parameters:

            • sql: string
          • Returns

            reponse from database

          • Example:

              const sqlStatement = `SQL STATEMENT GOES HERE`
              const res = await this.dataAccess.Execute(sqlStatement);
        2. Transaction

          • Introduction

            core function with transaction

          • Parameters:

             args: {
                params: Array<{
                sql: string
                replacements?: Array<any>
                alias?: string // to distinguish responses
                }>
              client?: object
              returnWithAlias?: boolean, // if true, return res with alias
              returnSingleRecord?: boolean,
              forceFlat?: boolean,
              preserveClient?: boolean // Skips committing the operation, leaving the client open for further operations in the transaction
             },
             transaction: Function // callback function or Transaction
          • Returns

            reponse from database

        3. GenerateInsertSQL

          • Introduction

            generate insert sql object

          • Parameters

            params: object, //data from resolver, includes inserted fields and values
            tableName: string //name of inserted table
          • Returns

            {
              sql: string
              replacement: Array<any>
              tableName: string
            }
        4. GenerateMultiInsertSQL

          • Introduction

            generate bulk insert sql object

          • Parameters

            insertFields: Array<string>,
            params: object, //data from resolver, includes inserted fields and values
            tableName: string //name of inserted table
          • Returns

            {
              sql: string
              replacement: Array<any>
              tableName: string
            }
        5. GenerateUpdateSQL

          • Introduction

            generate update sql object

          • Parameters

            {
            	/** an object includes the fields and values you want to update */
              params: object
              /** the name of table */
              tableName: string
              /** e.g. "employeeId" = '123' */
              whereClause?: string
              /** the name of primary key, default 'id' */
              pkName?: string
              /** those fields need to set time automatically, default value is from globalAutoSetTimeFields. We will check whether fields included in the table, if not, skip */
              autoSetTimeFields?: Array<string>
            }
          • Returns

            {
              sql: string
              replacement: Array<any>
              tableName: string
            }
        6. InsertExecutor

          • Introduction

            execute insert sql

          • Parameters

            params: object, //data from resolver, includes inserted fields and values
            tableName: string, //name of inserted table
            callback?: function, //function to run before committing the transaction
            client?: object //the pg client to be used for the transaction
            preserveClient?: boolean // skips comitting the client if true
          • Returns

            response from database

        7. MultiInsertToOneTableExecutor

          • Introduction

            execute insert sqls to one table

          • Parameters

            insertFields: Array<string>,
            params: object, //data from resolver, includes inserted fields and values
            tableName: string, //name of inserted table
            callback?: function, //function to run before committing the transaction
            client?: object //the pg client to be used for the transaction
            preserveClient?: boolean // skips comitting the client if true
          • Returns

            response from database

        8. MultiInsertExecutor

          • Introduction

            execute insert sqls to deferent tables

          • Parameters

            Array<
            {
              params: object, //data from resolver, includes inserted fields and values
              tableName: string //name of inserted table
            }>,
            forceFlat?: boolean, //if true, forces results into one array
            callback?: function, //function to run before committing the transaction
            client?: object //the pg client to be used for the transaction
            preserveClient?: boolean // skips comitting the client if true
          • Returns

            response from database

        9. UpdateByPkExecutor

          • Introduction

            execute update sql by primary key

          • Parameters

            params: object, //data from resolver, includes updated fields and values
            tableName: string, //name of inserted table
            pkName?: string, //the name of primary key
            autoSetTimeFields?: Array<string> ,//those fields need to set time automatically
            callback?: function, //function to run before committing the transaction
            client?: object //the pg client to be used for the transaction
            preserveClient?: boolean // skips comitting the client if true
          • Returns

            response from database

        10. UpdateExecutor

          • Introduction

            execute update sql by conditions

          • Parameters

            params: object, //data from resolver, includes updated fields and values
            tableName: string, //name of inserted table
            whereClause?: string, //e.g. "employeeId" = '123'
            autoSetTimeFields?: Array<string>, //those fields need to set time automatically
            callback?: function, //function to run before committing the transaction
            client?: object //the pg client to be used for the transaction
            preserveClient?: boolean // skips comitting the client if true
          • Returns

            response from database

        11. MultiUpdateExecutor

          • Introduction

            execute bulk update sqls by conditions

          • Parameters

            Array<
              {
              params: object, //data from resolver, includes updated fields and values
                tableName: string, //name of inserted table
                whereClause?: string //e.g. "employeeId" = '123'
                pkName: string, //the name of primary key
                autoSetTimeFields?: Array<string>, //those fields need to set time automatically
                client?: object //the pg client to be used for the transaction
              }>,
              forceFlat?: boolean //if true, forces results into a single array
              callback?: function //function to run before committing the transaction
              preserveClient?: boolean // skips comitting the client if true
          • Returns

            response from database

        12. DeleteExecutor

          • Introduction

            execute delete sql by conditions

          • Parameters

            tableName: string, //name of inserted table
            whereClause?: string, //e.g. "employeeId" = '123'
            returnSingleRecord?: boolean,//if true, returns one record instead of array
            callback?: function, //function to run before committing the transaction
            client?: object //the pg client to be used for the transaction
            preserveClient?: boolean // skips comitting the client if true
          • Returns

          response from database

        13. SingleQueryExecutor

          • Introduction

            execute query sql

          • Parameters

            {
               /** the name of table */
             	tableName: string
               /** e.g. "employeeId" = '123' */
               whereClause: string
               /** the fields what you want to select, default * */
               selectFields?: string
               /** the field name for sorting, e.g.: [{field: 'id', sequence:'DESC'}] */
               sortBy?: Array<{ field: String; sequence?: 'ASC' | 'DESC' }>
               /** to limit the count of rows you want to query */
               limit?: number
               /** how many rows you want to skip */
               offset?: number,
              /** if true, return a single record instead of an array */
               returnSingleRecord?: boolean,
               client?: object //the pg client to be used for the transaction
               preserveClient?: boolean // skips comitting the client if true
            }
          • Returns

            response from database

        14. Disconnect [Beta Function]

          • Introduction

            It will drain the pool of all active clients, disconnect them, and shut down any internal timers in the pool. It is common to call this at the end of a script using the pool or when your process is attempting to shut down cleanly.

    Install

    npm i pglink-lite

    DownloadsWeekly Downloads

    103

    Version

    0.5.5

    License

    MIT

    Unpacked Size

    98 kB

    Total Files

    22

    Last publish

    Collaborators

    • jandenma
    • jaketompkins