Language Pack for building expressions and operations to run PostgreSQL queries.
{
"host": "some-host-url.compute-1.amazonaws.com",
"port": "5432",
"database": "wouldntyouliketoknow",
"user": "me",
"password": "noway",
"ssl": true,
"allowSelfSignedCert": true
}
Execute an sql query. An option can be added to either log the query or not and either execute the query or no. The options can be omitted as well.
sql(
state =>
`INSERT INTO untitled_table (name, the_geom) VALUES ('` +
dataValue('form.first_name')(state) +
`', ST_SetSRID(ST_Point(` +
dataValue('lat')(state) +
`, ` +
dataValue('long')(state) +
`),4326))`,
{ writeSql: true, execute: true }
);
This helper function allows to build a specific query where sql
would not be
best suited. It returns a single value and not a promise. An example of usage
would be in building a mapping object with a value from a lookup table.
alterState(async state => {
const user = {
id: 1,
name: 'Mamadou',
user_id: await findValue({
uuid: 'id',
relation: 'users',
where: { first_name: 'Mama%' },
operator: { first_name: 'like' }, // operator is optional. "=" is used by default.
})(state),
};
return upsert(...)(state);
});
This functions is used to insert a single record in postgres database. An option
writeSql
allows to log the generated sql query. The setNull
option allows to
replace the specified elements with NULL
.
insert(
'users',
{
email: 'antony@gmail.com',
first_name: 'Antony',
inserted_at: '2020-08-27 00:00:00',
updated_at: '2020-08-27 00:00:00',
},
{ setNull: ["''", "'undefined'"], writeSql: true, logValues: true }
);
Insert or Update if matched. An option, execute
, allows to either run the
generated query or no.
upsert(
'users',
'email',
{
email: 'luca@openfn.org',
first_name: 'Luca',
inserted_at: '2010-01-01 00:00:00',
updated_at: '2010-01-01 00:00:00',
},
{
setNull: ["'NaN'", "'undefined'"],
writeSql: false,
execute: true,
logValues: true,
}
);
This function will upsert a record only if the logical given is true. In this
case we check if dataValue('name')
exists.
upsertIf(dataValue('name'), 'users', 'ON CONSTRAINT users_pkey', {
name: 'Elodie',
id: 7,
});
This function allows the insert of a set of records inside a table all at once.
insertMany(
'users',
state =>
state.data.people.map(s => {
return {
first_name: ['Luca', 'Mohamed', 'Elodie'],
inserted_at: '2020-01-01 00:00:00',
updated_at: '2020-01-01 00:00:00',
};
}),
{ logValues: true }
);
This function allows the upsert of a set of records inside a table all at once.
upsertMany('users', 'ON CONSTRAINT users_pkey', state =>
state.data.people.map(s => {
return {
first_name: ['Luca', 'Mohamed', 'Elodie'],
inserted_at: '2020-01-01 00:00:00',
updated_at: '2020-01-01 00:00:00',
};
})
);
This function is used to fetch the list of columns of a given table in the database.
describeTable('users', { writeSql: true, execute: false });
This function allows to create a table in a database from a given array of
columns. The key identity
can be use for a column to auto-generate a value.
insertTable('users', state =>
state.data.map(column => ({
name: column.name,
type: column.type,
required: true, // optional
unique: false, // optional - set to true for unique constraint
}))
);
This function allows to add new columns to a table. Beware of the fact that you cannot add new columns with names that already exist in the table.
modifyTable(
'users',
state =>
state.data.map(newColumn => ({
name: newColumn.name,
type: newColumn.type,
required: true, // optional
unique: false, // optional - set to true for unique constraint
})),
{ writeSql: false, execute: true }
);
Clone the adaptors monorepo. Follow the
Getting Started
guide inside to get set up.
Run tests using pnpm run test
or pnpm run test:watch
Build the project using pnpm build
.
To just build the docs run pnpm build docs