NodeJs Skips Sqlite query

Post Reply
MY001
Newbie
Posts: 9
Joined: Mon Feb 15, 2021 9:47 pm

NodeJs Skips Sqlite query

Post by MY001 »

Hey everyone,

I am setting up a flow where i collect data and store it in a db file in localhost.

I don't have Database connect module; rather I am using manual way to connect to the database via nodeJs.

When I run the code outside Switch it works, it connects to the DB file and it injects the data I pass through the query but when I run real work for Switch, the job arrives to the entry point and nothing happens after that. I even set to send the job to single inside the query function to see if the function executes but not it does not.

is it possible that Switch blocks the possibility to work with sqlite?

Any idea?
freddyp
Advanced member
Posts: 1008
Joined: Thu Feb 09, 2012 3:53 pm

Re: NodeJs Skips Sqlite query

Post by freddyp »

It is definitely not a case of Switch blocking anything! This sounds a lot like the use of a callback versus a promise that is awaited.
viewtopic.php?f=26&t=3835&p=12034&hilit ... ses#p12034

And if you are interested in watching the webinar Laurent and I gave that is mentioned in that post:
https://learning.enfocus.com/course/view.php?id=321
MY001
Newbie
Posts: 9
Joined: Mon Feb 15, 2021 9:47 pm

Re: NodeJs Skips Sqlite query

Post by MY001 »

@freddyp

Thank you for your reply.

I don't think that's the case as I even tried to only have the database query function in the script in try/catch and nothing else and still not executing it.

Here is the code below (I used simple code from online)

Code: Select all

async function jobArrived(s,flowElement,job){

const sqlite3 = require('sqlite3').verbose();
let db = new sqlite3.Database('test.db');
let languages = ['C++', 'Python', 'Java', 'C#', 'Go'];

try {
      
      let placeholders = languages.map((language) => '(?)').join(',');
      let sql = 'INSERT INTO test(name) VALUES ' + placeholders;
      
      console.log(sql);
      
      db.run(sql, languages, function(err) {
        if (err) {
          return console.error(err.message);
        }
        console.log(`Rows inserted ${this.changes}`);
      });
      
      db.close();
     
    }catch(e){
     job.log(2, "error: " + e);
    }
I am not sure if something is wrong. When I run the code outside Switch it works fine.

PS: the db file is at same path as main.js
freddyp
Advanced member
Posts: 1008
Joined: Thu Feb 09, 2012 3:53 pm

Re: NodeJs Skips Sqlite query

Post by freddyp »

It is the case, you do use a callback function! The third parameter of db.run, starting at function(err) { up until the closing curly bracket before ); is a callback function.

If this is not clear have a look at the webinar or read about the differences between using callbacks and promises. And also google "javascript callback hell" if you need convincing that using promises is way easier than using callbacks.

The reason it works when you run your script in Node from the command line is that Node itself terminates at the end of the execution of the script and it nicely waits for all outstanding callbacks to be finalized before doing so. That why you see the result being logged in the callback function. In Switch your script is running in an environment that is constantly running multiple scripts and it stays alive; otherwise Node would have to be started for every single script which would have a detrimental effect on performance. It is not that Switch did not execute the database query, it is just that your script had already finished running when the callback was called.

There seem to be lots of other packages that support sqlite3, so I would recommend using another package. I would never even have considered trying that package because on the overview page of the search for sqlite3 on npmjs.com that package has "Asynchronous, non-blocking SQLite3 bindings" underneath the name. Asynchronous = callbacks = trouble. Always go for a package that mentions "synchronous" or "promise".
MY001
Newbie
Posts: 9
Joined: Mon Feb 15, 2021 9:47 pm

Re: NodeJs Skips Sqlite query

Post by MY001 »

@freddyp

Thank you for your explanation.

Changing the SQLite library to synchronous one solved the issue.
Post Reply