Insert into postgres not working

Adaptors
1

Dear OpenFN community,
I have two issues with the postgresql adapter in a self hosted OpenFn application in a dockerized environment.

  1. The idea is to send a POST request with some .json formatted data and store whatever was in the input .json as an entry in a postgres sql table called “payload” using this command:
// Get started by adding operations from the API reference

fn( state => {
  insert('payload', {data: state.data }, {writeSql: true, setNull: "'NaN'", logValues: true });
    return state;
});

I get a notification that the job succeeded, no error in the console of the docker but the “payload” table is still empty. However the table “dataclips” shows the data as expected which should come directly from the OpenFN application.

  1. Further it does not seem to matter if I provide a API token or not in the request using postman and executes the job in both matters.

image
image2698×1266 463 KB

Log on the docker indicates it ran through

cmd:
openfn execute \
  -a @openfn/language-postgresql@latest=/app/priv/openfn/lib/node_modules/@openfn/language-postgresql-4.1.7 \
  -s /tmp/state-1694596536-1-1mwmiwk.json \
  --no-strict-output \
  -l info \
  -o /tmp/output-1694596536-1-v66c91.json \
  /tmp/expression-1694596536-1-1f7rpcu.js


[debug]
f5070 : [CLI] ℹ Versions:
f5070 :          ▸ node.js                        18.17.1
f5070 :          ▸ cli                            0.0.35
f5070 :          ▸ runtime                        0.0.21
f5070 :          ▸ compiler                       0.0.29
f5070 :          ▸ @openfn/language-postgresql    4.1.7
f5070 : [CLI] ✔ Loaded state from /tmp/state-1694596536-1-1mwmiwk.json
f5070 : [CLI] ℹ Added import statement for @openfn/language-postgresql
f5070 : [CLI] ℹ Added export * statement for @openfn/language-postgresql
f5070 : [CLI] ✔ Compiled job from /tmp/expression-1694596536-1-1f7rpcu.js
f5070 : [R/T] ℹ Resolved adaptor @openfn/language-postgresql to version 4.1.7
f5070 : [R/T] ✔ Operation 1 complete in 0ms
f5070 : [CLI] ✔ Writing output to /tmp/output-1694596536-1-v66c91.json
f5070 : [CLI] ✔ Done in 632ms! ✨
f5070 :
[debug] QUERY OK db=0.4ms idle=970.8ms
begin []
[debug] QUERY OK db=0.6ms
1 Like
2

hey @piische-tph , looks like that insert() is wrapped inside an fn block.

fn blocks are useful if you want to write some custom javascript and do stuff to your state object in ways that isn’t handled by regular operations. but here, it looks like you’re only trying to insert a record, so use that insert function without wrapping it up like that.

Your job should look like this:

insert(
  'payload',
  state.data,
  { writeSql: true, setNull: "'NaN'", logValues: true }
);

Or if you wanted to do some really fancy footwork (though this is definitely an anti-pattern!) you could break out into one of those custom fn blocks (think of this as an escape hatch) and then manually call the insert operation with state like this:

fn(state => {
  return insert('payload', {data: state.data }, {writeSql: true, setNull: "'NaN'", logValues: true })(state);
});

See how I return the operation when called with (state)? I go from insert(x) to return insert(x)(state);

But that would be bad :slight_smile: . You’d almost be replicating the OpenFn internals again in your job code. The “contract” that we make is to take each operation in a job expression and call it with state… so a normal job might look like this:

get(...);
get(...);
post(...);
fn(...);
insert(...);

Each of those 5 operations will return a function that can be called with state. You can run compile to see how that list of operations is turned into actual Javascript.

In essence, we take this like of operations and call each one with state, then pass the output of the first to the second, etc., etc. It’s a big reducer pipeline. You give us insert() and we call insert(...)(state) and then pass the result (as “state”) to the next item in the list of operations.

So… tl;dr: drop the fn(...) unless you’re specifically messing around with how you want OpenFn to execute your operations.

2 Likes
3

Thank you so much @taylordowns2000,

That solved my main issue, I used the insert function without the fn() as you mentioned it and now it works and stores the content in the provided table of the postgres database, thanks for the quick help!

As you suggested I kept it simple :smiley: But thanks for the insights in how I could achieve more complex scenarios.

Do you also have an idea on how I can set the webhook to be only accessible for a user by providing an API Token? Because now it seems that everyone with the correct webhook link can post to the endpoint or am I wrong?

1 Like
4

Webhook security is a feature currently only available in the OpenFn platform V1 (see docs), but it’s coming up on our roadmap for Lightning V2. You can track the issue here and we can follow up once delivered in the coming weeks!

2 Likes
5

Thanks @aleksa-krolls,

okay then I will subscribe / track the issue and implement it once it is available.