Skip to main content

postgresql@4.1.8

Functions

describeTable(tableName, [options], callback)
findValue([filter])
insert(table, record, [options], callback)
insertMany(table, records, [options], callback)
insertTable(tableName, columns, [options], callback)
modifyTable(tableName, columns, [options], callback)
sql(sqlQuery, [options], callback)
upsert(table, uuid, record, [options], callback)
upsertIf(logical, table, uuid, record, [options], callback)
upsertMany(table, uuid, data, [options], callback)

describeTable

describeTable(tableName, [options], callback) ⇒ Operation

List the columns of a table in a database.

ParamTypeDescription
tableNamestringThe name of the table to describe
[options]objectOptional options argument
[options.writeSql]booleanA boolean value that specifies whether to log the generated SQL statement. Defaults to false.
[options.execute]booleanA boolean value that specifies whether to execute the generated SQL statement. Defaults to false.
callbackfunction(Optional) callback function

Example

describeTable('clinic_visits')

findValue

findValue([filter]) ⇒ value

Fetch a uuid key given a condition

ParamTypeDescription
[filter]objectA filter object with the lookup table, a uuid and the condition
[filter.uuid]stringThe uuid value to search for in the specified relation.
[filter.relation]stringThe name of the relation to search for the uuid value.
[filter.where]objectAn object that contains key-value pairs to filter the search results.
[filter.operator]objectAn object that contains key-value pairs to specify the type of comparison to perform on the where clause.

Example

findValue({
uuid: 'id',
relation: 'users',
where: { first_name: 'Mamadou' },
operator: { first_name: 'like' }
})

insert

insert(table, record, [options], callback) ⇒ Operation

Insert a record

ParamTypeDescription
tablestringThe target table
recordobjectPayload data for the record as a JS object or function
[options]objectOptional options argument
[options.setNull]stringA string value that specifies the behavior for inserting null values.
[options.logValues]booleanA boolean value that specifies whether to log the inserted values to the console. Defaults to false.
[options.writeSql]booleanA boolean value that specifies whether to log the generated SQL statement. Defaults to false.
[options.execute]booleanA boolean value that specifies whether to execute the generated SQL statement. Defaults to false.
callbackfunction(Optional) callback function

Example

insert('users', { name: 'Elodie', id: 7 }, { setNull: "'NaN'", logValues: true });

insertMany

insertMany(table, records, [options], callback) ⇒ Operation

Insert many records, using the keys of the first as the column template

ParamTypeDescription
tablestringThe target table
recordsarrayAn array or a function that takes state and returns an array
[options]objectOptional options argument
[options.setNull]stringA string value that specifies the behavior for inserting null values.
[options.logValues]booleanA boolean value that specifies whether to log the inserted values to the console. Defaults to false.
[options.writeSql]booleanA boolean value that specifies whether to log the generated SQL statement. Defaults to false.
[options.execute]booleanA boolean value that specifies whether to execute the generated SQL statement. Defaults to false.
callbackfunction(Optional) callback function

Example

insertMany('users', state => state.data.recordArray, { setNull: "'undefined'", logValues: true });

insertTable

insertTable(tableName, columns, [options], callback) ⇒ Operation

Create a table in database when given an array of columns and a table_name.

ParamTypeDescription
tableNamestringThe name of the table to create
columnsarrayAn array of form columns
[options]objectOptional options argument
[options.writeSql]booleanA boolean value that specifies whether to log the generated SQL statement. Defaults to false.
[options.execute]booleanA boolean value that specifies whether to execute the generated SQL statement. Defaults to false.
callbackfunction(Optional) callback function

Example

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
})
));

modifyTable

modifyTable(tableName, columns, [options], callback) ⇒ Operation

Alter an existing table in the database.

ParamTypeDescription
tableNamestringThe name of the table to alter
columnsarrayAn array of form columns
[options]objectOptional options argument
[options.writeSql]booleanA boolean value that specifies whether to log the generated SQL statement. Defaults to false.
[options.execute]booleanA boolean value that specifies whether to execute the generated SQL statement. Defaults to false.
callbackfunction(Optional) callback function

Example

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

sql

sql(sqlQuery, [options], callback) ⇒ Operation

Execute an SQL statement

ParamTypeDescription
sqlQueryfunctiona function which takes state and returns a string of SQL.
[options]objectOptional options argument
[options.writeSql]booleanA boolean value that specifies whether to log the generated SQL statement. Defaults to false.
[options.execute]booleanA boolean value that specifies whether to execute the generated SQL statement. Defaults to false.
callbackfunction(Optional) callback function

Example

sql(state => `select(*) from ${state.data.tableName};`, { writeSql: true })

upsert

upsert(table, uuid, record, [options], callback) ⇒ Operation

Insert or update a record using ON CONFLICT UPDATE

ParamTypeDescription
tablestringThe target table
uuidstringThe uuid column to determine a matching/existing record
recordobjectPayload data for the record as a JS object or function
[options]objectOptional options argument
[options.setNull]stringA string value that specifies the behavior for inserting null values.
[options.writeSql]booleanA boolean value that specifies whether to log the generated SQL statement. Defaults to false.
[options.execute]booleanA boolean value that specifies whether to execute the generated SQL statement. Defaults to false.
[options.logValues]booleanA boolean value that specifies whether to log the inserted values to the console. Defaults to false.
callbackfunction(Optional) callback function

Example

upsert(
'users', // the DB table
'ON CONSTRAINT users_pkey', // a DB column with a unique constraint OR a CONSTRAINT NAME
{ name: 'Elodie', id: 7 },
{ setNull: ["''", "'undefined'"], writeSql:true, execute: true, logValues: true }
)

upsertIf

upsertIf(logical, table, uuid, record, [options], callback) ⇒ Operation

Insert or update a record based on a logical condition using ON CONFLICT UPDATE

ParamTypeDescription
logicalstringa data to check existing value for.
tablestringThe target table
uuidstringThe uuid column to determine a matching/existing record
recordobjectPayload data for the record as a JS object or function
[options]objectOptional options argument
[options.setNull]stringA string value that specifies the behavior for inserting null values.
[options.writeSql]booleanA boolean value that specifies whether to log the generated SQL statement. Defaults to false.
[options.execute]booleanA boolean value that specifies whether to execute the generated SQL statement. Defaults to false.
[options.logValues]booleanA boolean value that specifies whether to log the inserted values to the console. Defaults to false.
callbackfunction(Optional) callback function

Example

upsertIf(
dataValue('name'),
'users', // the DB table
'ON CONSTRAINT users_pkey', // a DB column with a unique constraint OR a CONSTRAINT NAME
{ name: 'Elodie', id: 7 },
{ writeSql:true, execute: true }
)

upsertMany

upsertMany(table, uuid, data, [options], callback) ⇒ Operation

Insert or update multiple records using ON CONFLICT UPDATE and excluded

ParamTypeDescription
tablestringThe target table
uuidstringThe uuid column to determine a matching/existing record
dataarrayAn array of objects or a function that returns an array
[options]objectOptional options argument
[options.setNull]stringA string value that specifies the behavior for inserting null values.
[options.writeSql]booleanA boolean value that specifies whether to log the generated SQL statement. Defaults to false.
[options.execute]booleanA boolean value that specifies whether to execute the generated SQL statement. Defaults to false.
[options.logValues]booleanA boolean value that specifies whether to log the inserted values to the console. Defaults to false.
callbackfunction(Optional) callback function

Example

upsertMany(
'users', // the DB table
'email', // a DB column with a unique constraint OR a CONSTRAINT NAME
[
{ name: 'one', email: 'one@openfn.org' },
{ name: 'two', email: 'two@openfn.org' },
]
{ logValues: true }
)