postgres-cg-lib (Postgres Library)

1. Introduction

The purpose of this library is to execute queries using the postgres database. The query can be a single operation or batch operations. The library used is postgres, and it is based in NodeJS.

2. Library Objects and Methods

The library can be installed from the npm page with the following commands:

npm i postgresql-cg-lib, npm install postgresql-cg-lib or yarn install postgresql-cg-lib

• 2.1. Methods

  The following method is available in the library:

  processQuery: This method executes single or batch queries.

  Method is asynchronous, then it uses async/await

  Before the transformation is executed, it is validated that the JSON to be processed is a valid JSON, only if it use content property, otherwise an exception will be thrown and the flow or process will be stopped.

  Before the method returns the information, it will be validated that the response is a valid JSON or valid JSON Array. If this is correct, the response will be returned, otherwise an exception will be thrown and the flow or process will stop.

• 2.2. Objects and properties

  The next are the objects and properties available in the library.

  • 2.2.1 Objects

    • objectLib: this object contains the required properties for the method processQuery
    • addProps: this object contains the optional properties for the method processQuery

  • 2.2.2 Properties

    {
        database: null,
        host: null,
        user: null,
        password: null,
        query: null
    }

    objectLib contains these properties:

    • The database property indicates the name of the database.
    • The host property indicates name of the host or IP to connect with the database.
    • The user property indicates the user that has grants to connect to the database.
    • The password property indicates the password of the user that has grants to connect to the database.
    • The query property indicates the SQL expression to be executed in the database (SELECT, INSERT, UPDATE, DELETE), this property is related with the optional property "content" for batch operations.

    {
        content: null,
        port: 5432
    }

    extraProps contains these properties.

    • The port property indicates the port to connect to the database, by default it is set to 5432.
    • The content property indicates the data that will be processed in batch operations, this data is going to replace que ? or variable names accordig with the possition and the query will be executed n times where n is the number of objects in the property times.This property can be arrays of array's, array of an object array, a single array (only values inside), an JSON object and an array of JSON objects.

3. Examples

In an implementatión of this library in an Open Integration Hub (OIH) based component the properties and data can be obtained from the msg and cfg attributes.

• Arguments:

1. The first argument is the message (msg) that will come from the OIH component, the property that contains the information in the data object.

2. The second parameter is the configuration (cfg) that will also come from the OIH component.

3. The third argument is only used to define if the library is used in test mode, by default it is false. It will be true if to test the method(s) from a OIH component without running this in a flow.

   3.1. processQuery

• Description: This method will execute a query in batch or single form.

• Object and Properties: To use this method, it is madatory to use the object called objectDb and, if required, the extraProps object to send and replace the default values.

• Examples:

  In this first example, the singleQuery method is used to execute a single query to insert one set of values:

  let properties = {
      database:'mydatabase',
      host:'myhost', //the IP address or the local machine name
      user:'myuser',
      password:'mypassword';
      query:'INSERT into users (name, age, email) VALUES ("Rishi", 18, "[email protected]");',
  };
  
  const _data = await processQuery({
      data: properties
  }, {}, true);
  console.log(_data)

  In this second example, the batchQuery method is used to execute multiple query at once to insert an array of values:

  let properties = {
     database:'mydatabase',
     host:'myhost', //the IP address or the local machine name
     user:'myuser',
     password:'mypassword';
     query:'INSERT into users (name, age, email) VALUES (["Rishi", 18, "[email protected]"], ["Ishi", 16, "[email protected]"], ["Joe", 20, "[email protected]"]);',
  };
  
  const _data = await processQuery({
     data: properties
  }, {}, true);
  console.log(_data)

  ---

  Example Results: For the first example, the result would be the following:

  {
  "result": "1 rows affected."
  }

  For the second example, the result would be the following:

  {
  "result": "3 rows affected."
  }