Accessing mysql db in Custom Action on Login / Post Login trigger

[be0z]

Ready to post? First, try searching for your answer.
Hello,

I am in the process of migrating our old Rules to the new(ish) Actions.
I have hit a roadblock in accessing and updating our db with some values from the event object.

In our old Hooks and Rules we would use the mysql npm package to achieve this. But in Custom Actions this does not seem to work.

Specifically the function:

function(error, results, fields){
      console.log(results);
  }

In the query call, does not seem to work and anything inside here will not be executed. Very problematic if one is trying to access results.lastInsertId for example.

Am i doing anything wrong? I have been through rewriting this a a Promise and with awaits, and a combination of these. Triple checked our connection values. And tried using the mysql2 package. (Both the mysql@2.18.1 and mysql2@latest are listed as supported in the “can i require” listing )

Any insight would be appreciated. Cheers!

exports.onExecutePostLogin = async (event, api) => {

  const mysql = require('mysql');
  
  const connection = mysql.createConnection({
    host: 'ourHost',
    user: 'ourUser',
    password: 'ourPass',
    database: 'ourDB'
  });
  
  connection.connect();
  console.log("foobar") //This works
  var query = "select * from db_users";
  connection.query(query, function(error, results, fields){
      console.log(results); //This does not
  });
};

mysql added as dependency.
query is not actual query but just an example.

[nik.baleca]

Hi @be0z

You appear to be setting up the connection correctly and I am not quite sure why it does not seem to be querying it.

You could try to put a console.log(connection) to see if the action is able to make the connection to your DB and that none of the provided parameters are incorrect.

Also, could you try to check for errors when you try to execute the query as in the example below?

connection.connect(function(err) {
  if (err) throw err;
  con.query("SELECT * FROM db_users", function (err, result, fields) {
    if (err) throw err;
    console.log(result);
  });
});

Also, you have mentioned that you have tried using promises as well, could you confirm that you already tried implementing something similar to this:

const mysql = require('mysql2/promise');
async function connectToDatabase() {
  try {
    const connection = await mysql.createConnection({
      host: 'localhost',
      user: 'your_username',
      password: 'your_password',
      database: 'your_database'
    });

    // Execute a simple query
    const [rows, fields] = await connection.execute('SELECT * FROM users');
    console.log('Query results:', rows);

    await connection.end();
  } catch (error) {
    console.error('Database connection failed:', error);
  }
}

Kind Regards,
Nik

[be0z]

YES!

The last example with promises worked. Funny, as i recall that I have tried that exact snippet from the mysql2 docs. I must have missed something.

Only other feedback I can give is that require('mysql2/promise'); gives a red line in the Actions code editor. Even though mysql2@3.12.0 is added to dependencies.

Thank you for the assistance!

[nik.baleca]

Hi @be0z

Glad to know the solution provided above was helpful!

The dependency might throw an error in the Action code since you are trying to use a subdirectory of the dependency, if you just use mysql2 for the dependency that should do the trick as well.

If you have any other questions, feel free to leave a reply or post on the community!

Kind Regards,
Nik