Check arrays for data

v2.4.1

Bump version 2.4.1

Uniqueness constraint option

 insertTable('table_name', state => state.data.map(
   column => ({
      name: column.name,
      type: column.type,
      required: true, // optional
      unique: false, // optional - to be set to true for unique constraint
    })
 ));

Options for all helpers

Implemented an extensible, optional, final argument for each helper function which is an options object.

{
  writeSql: true, // write prepared sql to state.queries[...] (defaults to false)
  execute: false  // execute the prepared sql (defaults to true)
}

Make inline helpers public

Added the public tag to inline jsDocs for consumption on OpenFn.org

Database configuration functions

Added 3 new functions:

/**
 * List the columns of a table in a database.
 * @example
 * describeTable('table_name')
 * @constructor
 * @param {string} table - The name of the table to describe
 * @returns {Operation}
 */

/**
 * Create a table in database when given a form definition and a table_name.
 * @example
 * insertTable('table_name', state => state.data.koboColumns)
 * @constructor
 * @param {string} table - The new table to create
 * @param {function} records - An array of form columns
 * @returns {Operation}
 */

/**
 * Alter an existing table in the database.
 * @example
 * modifyTable('table_name', state => state.data.koboColumns)
 * @constructor
 * @param {string} table - The name of the table to alter
 * @param {function} records - An array of form columns
 * @returns {Operation}
 */

Updated docs and readme

Inline docs and readme updated, all 4 helpers (insert, upsert, insertMany, upsertMany) provide accurate inline docs for the platform UI

upsertMany(...)

/**
 * Insert or update multiple records using ON CONFLICT UPDATE and excluded
 * @example

 upsert(
  table, // the DB table
  uuid, // a DB column with a unique constraint OR a CONSTRAINT NAME
  state => state.data.records,
  {}
 )

 * @constructor
 * @param {string} table - The target table
 * @param {string} uuid - The uuid column to determine a matching/existing record
 * @param {object} records - A function that takes state and returns an array of records
 * @param {object} options - Optional options argument
 * @returns {Operation}
 */

Safer query building with pg-format

This release uses a more standard implementation for building value sets in insertMany.

upsert() allows constraint names

This release doesn't change the API, but respects the use of ON CONSTRAINT blah_pk instead of column_id for the uuid target in our upsert() function.

Node PG version 8.x

This release sets the default to blocking self-signed certificates and changes the output on multi-string selects: Result from multi-statement text queries such as SELECT 1; SELECT 2; are now returned as an array of results instead of a single result with 1 array containing rows from both queries.

The adaptor is now more secure and running on up-to-date dependencies. To allow self signed certificates, please pass the allowSelfSignedCerts option into your configuration:

{
  "configuration": {
     "user": "postgres",
     "ssl": true,
     "allowSelfSignedCert": true,
     "port": 5432,
     "password": "shhhhhhhhh",
     "host": "localhost",
     "database": "openfn_dev"
  },
}

See the pg changelog here: https://github.com/brianc/node-postgres/blob/master/CHANGELOG.md#700