Create useful automations with plain Javascript 🧑‍💻
Tags: javascript, learn, automation
Published: 2022-12-01 09:52:24

---
title: Create useful automations with plain Javascript 🧑‍💻
published: true
description: How we create incremental automation for greater efficiency
tags: javascript, learn, automation
cover_image: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/f8lj9qsugul51m3qlxhw.png
# Use a ratio of 100:42 for best results.
# published_at: 2022-11-23 10:15 +0000
---

## Are you more automatic or manual?

*Hi all 👋,
Continuing my contribution to the developer community as app maker, today I propose to discuss how to effectively automate tasks with NodeJS.*

So... How many repetitive manual actions do you do each day? **Probably a lot!** 😐

And why...? Because **automating your scattered actions is long and difficult**. Don't have time to build and automate a complete process? Neither do I!

In fact, it's probably easier than you think. If writing automation is giving you a headache, it means you're probably not using the right tool.

An easy way is: **Incremental Automation**.
*Write your process down in a note and start coding parts of your tasks. Thus, you can alternate between manual and automated actions.*

I don't know about you, but personally **I'm more comfortable learning by testing and prototyping**. For me, the best way to achieve this was to use **a Javascript playground using Markdown.** That's why I recommend you <a href="https://znote.io" target="_blank">give it a try to my app Znote</a> to capitalize your work, otherwise no problem use your favorite IDE.

## Choose a use case 
Let's now see with a concrete example how to proceed to build a ready-to-use automation. The best way to do this is to start solving your own problems! So let me expose our first use case.

When I have new registered users, I had to regularly
- connect to my remote database with SSH,
- manually extract the users with an SQL query,
- copy the results locally and compare with the existing users.
- Once the new users were identified,
- I sent a welcome email to each new member to get their feedback.

<img alt="SSH - mail new users diagram" src="https://dev-to-uploads.s3.amazonaws.com/uploads/articles/f8lj9qsugul51m3qlxhw.png" />


### Make an SQL query on a remote database
1 - Make an SQL query
```js
async function getSQL(sqlQuery, host, port, user, password, database) {
 const { Sequelize } = require('sequelize');
 const sequelize = new Sequelize(user, password, database, {
    dialect: "mysql"/* one of 'mysql' | 'mariadb' | 'postgres' | 'mssql' */,
    host: host,
    port: port
 });

 sequelize.authenticate();
 const [results, metadata] = await sequelize.query(sqlQuery);

 sequelize.close()
 return results;
}
```

2 - Make an SQL query with SSH
```js
async function getRemoteSQL(sqlQuery) {
  const { spawn } = require('child_process');

  // connect to server (don't forget to copy your ssh-key on the remote server)
  const ssh = spawn('ssh', [
   '-L', '3307:localhost:3306',
   'user@XXX.ovh.net',
  ]);
  await sleep(4000); // wait connection
  // make SQL query (replace with your credentials)
  const data = await getSQL(sqlQuery, "localhost", 3307, "USER", "PASSWORD", "DATABASE");
  // quit
  ssh.kill();
  return data;
}
```

You can now just do anywhere in my notes an SQL query against your remote database
```js
const users = await getRemoteSQL("select purchase_email from license;");
printJSON(users)
```

### Identify new users
Then, we want to identify all new users since the query was last run.
```js
function getNewUsers(incomingData, filename, key) {
  const fs = require('fs');
  let refData = [];
  try {
    refData = JSON.parse(fs.readFileSync(filename, 'utf8'))
      .map(element => key(element));
  } catch (err){}

  const delta = [];
   incomingData.forEach(obj => {
    if (refData.indexOf(key(obj))===-1) {
      delta.push(obj);
    }
  });
  printJSON(delta);
  return delta;
}
```

### Send mail
Finally, we can send a mail to each new user. With Google, you need to generate a dedicated credential: https://support.google.com/accounts/answer/185833
```js
function sendMail(recipients) {
  const nodemailer = require('nodemailer');

  const transporter = nodemailer.createTransport({
    service: 'gmail',
    auth: {
      user: 'XX@gmail.com', // your gmail
      pass: 'XXX', // app credential here
   },
  });
  const result = await transporter.sendMail({
   from: '"Anthony" <XX@gmail.com>', // sender address
    to: recipients, // list of receivers (,)
    subject: "Just to know if everything is fine 😊", // Subject line
    text: "Hi 👋, Being very concerned about the quality of Znote app, I thank you for any feedback you could give me, allowing me to improve my software!",   // plain text body
    html: "Being very concerned about the <b>quality</b> of Znote app, I thank you for any feedback you could give me, allowing me to improve my software!",   // html body
  });
  printJSON(result);
}
```

### Plug everything in 🔌
It remains to describe our process by calling the different functions.
```js
// get all users in remote database
const allUsers = await getRemoteSQL("select purchase_email from license;");
// show new users since last execution time
const newUsers = getNewUsers(allUsers, '/Users/alagrede/Desktop/users.json', it => it.purchase_email);
// write all users in file
fs.writeFileSync('/Users/alagrede/Desktop/users.json', JSON.stringify(allUsers));
// send mail
sendMail(newUsers.map(e=>e.purchase_email));
```

## Tada!!
<img alt="Gmail screenshot" src="https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rwgz5pkp4jwmiow6040d.png" />


## To summarize
You now have a bunch of reusable functions and an automated process. Since functions can be reused on other notes, you can **capitalize on your existing work** and continue to create additional processes. Then **export your script, ready to run it in a cron**.

👉  I hope this example will inspire you! **Did you find this article useful?** If so, I have plenty of other examples to share with you.


## Go further
I will continue to add some examples when you need them.

Meanwhile, start turning your daily tasks into something easy and repeatable and your job will be more fun. 🌈️

You can check my blog 👉 <a target="blank" href="https://blog.znote.io/">here</a>

If you want try Znote, it's 👉 **[here](https://znote.io)** (I will be happy to take any comments)

<a href="https://znote.io/"><img alt="znote screenshot" src="https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0vd0z208x9747nbmoprh.png" /></a>