Database Monitoring and Alerting with n8n
Tags: tutorial, twilio, monitoring, postgres
Published: 2020-05-13 07:32:21

---
title: Database Monitoring and Alerting with n8n
published: true
date: 2020-05-06 13:01:22 UTC
tags: tutorial,twilio,monitoring,postgres
canonical_url: https://n8n.io/blog/database-monitoring-and-alerting-with-n8n/
---

In the past few months, I have been playing around with different kinds of IoT devices and sensors. I quite enjoy how these can be used to monitor different things like humidity, temperature, pressure among other things in the house. In this tutorial, I want to show you how you can monitor sensor readings in a database and send alerts when it crosses a threshold value using [n8n](https://n8n.io/) workflows.

![](https://cdn-images-1.medium.com/max/1024/1*vU8pW6v8lZj-1dTLzujZTg.png)<figcaption>Database monitoring and alerting workflow in n8n</figcaption>

We’ll be using [PostgreSQL](https://www.postgresql.org/) as the database and [Twilio](https://www.twilio.com/) for sending SMS alerts. We’ll divide the tutorial in two workflows (these workflows were built using n8n@0.110.0):

1. **Workflow 1:** This would be the data generator workflow, which will create a database record for us every minute. We will create this workflow to simulate sensor readings being ingested by a database. In case you already have an IoT device pushing data to a database, feel free to skip this step. Nonetheless, I’d recommend skimming through this section to check how to add the database credentials.
2. **Workflow 2:** This workflow will check for the threshold values every minute and in case of a value that crosses the specified threshold, it will trigger an alert with an SMS.

You can find information on how to set up and start n8n in the [documentation](https://docs.n8n.io/#/setup). Alternatively, you can also sign-up for n8n.cloud to get access to our hosted service.

## Workflow 1: Ingesting data in the database

In this workflow, we’ll use a [*Cron*](https://docs.n8n.io/nodes/n8n-nodes-base.cron/) node to trigger the workflow ever minute, a [*Set*](https://docs.n8n.io/nodes/n8n-nodes-base.set/) node to generate timestamp and a random value, and a [*Postgres*](https://docs.n8n.io/nodes/n8n-nodes-base.postgres/) node to insert the generated data to the database. If you haven’t already, you can find and install the database from their [website](https://www.postgresql.org/download/).

For this tutorial, create a table called `n8n` and use this SQL statement to create a table:

```
CREATE TABLE n8n (id SERIAL, sensor_id VARCHAR, value INT, time_stamp TIMESTAMP, notification BOOLEAN);
```

I have also submitted [Workflow 1](https://n8n.io/workflows/356) on n8n.io, in case you’d like to skim through this workflow. Please note that you’ll still need to configure a couple of things like your Postgres credentials.

### 1. Cron node: Trigger the workflow every minute

In the Node Editor view, add a new node by clicking on the '***+***' button on the top right of the Editor UI. Select the _Cron_ node under the ***Triggers*** section. Click on the ***Add Cron Time*** button and select ‘Every Minute’ as the _Mode_. Here’s a video of me following the steps above.

{% youtube p4W82EExLe4 %}

### 2. Set node: Generate timestamp and random value

Add a new node by clicking on the '***+***' button and select the *Set* node. Click on ‘Add Value’, under the ***Values to Set*** section, and select ‘String’. Enter `sensor_id` in the ***Name*** field and `humidity01` for the ***Value*** field. To add a new value, click on ‘Add Value’ and select ‘String’. Name this as `value`. Click on the gears symbol next to the ***Value*** field and select ***Add Expression***. Paste the following expression `{{Math.ceil(Math.random() * 100);}}`. This will create a random value between 1 to 100. We will also create a time uptime value.

Click on ‘Add Value’ and select ‘String’. In the ***Name*** field enter `time_stamp`. Click on the gears symbol next to the Value field and select ***Add Expression***. Paste the following expression `{{new Date().toISOString()}}`. This will return the timestamp of when the data was generated.

Click on ‘Add Value’ and select ‘Boolean’. Enter `notification` in the ***Name*** field. Set the value to `false`.

Here’s a video of me following the steps mentioned above.

{% youtube MWaZz3MXoIQ %}

### 3. Postgres node: Insert data to the database

Add a new node by clicking on the '***+***' button and selecting the _Postgres_ node. Create new credentials by clicking on _Create New_ under the ***Credentials*** section. Enter the details for your database here and click on the ***Save*** button.

Now, enter `n8n` as the name of the ***Table***. Enter `sensor_id, value, time_stamp, notification` in the _Columns_ field. Here, I skipped the `id` as it is a SERIAL datatype and would be generated by the database. Click on the ***Execute Node*** button and the record will be inserted to your database.

Here’s a video of me following the steps mentioned above.

{% youtube BRoIr1VzHCw %}

> **Note:** Don’t forget to save and activate the workflow before moving on to the next workflow. Once you have done that, every minute a new record will be inserted into the database

### Workflow 2: Monitoring the database and sending alerts

In this workflow, we’ll use a *Cron* node to trigger the workflow every minute, a *Postgres* node to fetch the threshold values, and a [*Twilio*](https://docs.n8n.io/nodes/n8n-nodes-base.twilio/) node to send us an SMS about them. Finally, we’ll use a *Set* and *Postgres* node to mark the records which we have already been alerted about.

I have also submitted [Workflow 2](https://n8n.io/workflows/357) on n8n.io, in case you’d like to skim through this workflow. Please note that you’ll still need to configure a couple of things like your Postgres and Twilio credentials as well as the settings for the *Twilio* node.

### 1. Cron node: Trigger the workflow every minute

You can create the *Cron* node following the same steps as you did in _Workflow 1, Step 1_.

### 2. Postgres node: Get all the records with the outlier values

Create a *Postgres* node following the same steps as _Workflow 1, Step 3,_ and select the same credentials that you entered there. Select ‘Execute Query’ for the ***Operation*** field and enter the following SQL query:

```
SELECT * FROM n8n WHERE value > 70 AND notification = false;
```

This query returns the records where the value is greater than 70 (feel free to change this to something else) and the notification is marked as _false_. Click on the ***Execute Node*** button. If you have the other workflow running or an active IoT device sending data into the database, you’ll likely have records as the result.

Here’s a video of me following the steps above.

{% youtube 8J05pt3ngU4 %}

> **Note:** You might want to turn off the other workflow or device at some point otherwise you ‘might’ end up using all your Twilio free credits during the first run _😉_

### 3. Twilio node: Send an SMS alert about the threshold value

First of all, you’ll have to create a [Twilio](https://www.twilio.com/) account. You can create a free trial account and once you have done that, go to the [console](https://www.twilio.com/console) and create a _Trial Number_ for yourself. On that page, you can also acquire the _Account Sid_ and _Auth Token_ that you’ll need for the credentials.

Once you have done that, add a *Twilio* node in n8n. Create new credentials for the Twilio API and enter the details. Now, enter the trial number that you created in the ***From*** field and your number (the one that you verified your Twilio account with) in the ***To*** field. In the message field, we want to include dynamic pieces of information. To do that click on the gears icon next to the ***Message*** field and select _Add Expression_. I entered the following in that field:

```
🚨 The Sensor ({{$node["Postgres"].json["sensor_id"]}}) showed a reading of {{$node["Postgres"].json["value"]}}.
```

> **Note:** In case you have a large number of threshold values, I would recommend waiting on the execution, as you might end up using all your free credits.

Once you are done, click on the ***Execute Node*** button and it’ll send an SMS alert to you. I got three alerts here since I had three threshold values in my records.

Here’s what I got:

![](https://cdn-images-1.medium.com/max/713/1*lxSaA7noBgrTPZyH9SaRWw.jpeg)<figcaption>Alert SMS sent using Twilio</figcaption>

### 4. Set node: Set the notification to true

Add a new node by clicking on the '***+***' button and selecting the _Set_ node. Open the Node Editor and click on ***Keep Only Set*** toggle. We do this because *Twilio* node overwrites data and we want to pass along the data that is generated only by this node.

Under the ***Values to Set*** section, click on the ***Add Value*** button and select ‘Number’. Enter `id` as the ***Name***. Since the ***Value*** is a dynamic piece of information, click on _Add Expression_ and select the id in the _Variable Selector_ by clicking on:

`Nodes > Postgres > Output Data > JSON > id`

Now, click on ***Add Value*** again and select ‘Boolean’. Enter `notification` as the ***Name*** and click on the ***Value*** toggle. Click on the ***Execute Node*** button and you’ll see that it will set the value of the notification to _true_ for all the corresponding ids. Once we make this update in the database with the help of the next node, the SMS alerts will not go through twice for any given record.

Here’s a video of me following the steps mentioned above.

{% youtube 83OaA7AqWkw %}

### 5. Postgres node: Make update to the notification in the database

Add a new *Postgres* node, select the Credentials and select ‘Update’ as the ***Operation***. In the Table field, enter `n8n` and enter `notification` in the ***Columns*** field.

Click on the ***Execute Node*** button and this will make an update to the notification value in the database. Here’s a video of me following the steps mentioned above.

{% youtube 7csi5KAOCu4 %}

## Conclusion

Today we created a workflow to continuously monitor a database and alert us about an abnormal reading by sending an SMS. You can also use other databases such as MongoDB or MySQL. You can even use other Postgres compatible databases like [CrateDB](https://crate.io/) using the Postgres node. I am curious to learn what kind of workflows you’ll create using these or other nodes. I’d love to check them out, please consider [sharing](https://n8n.io/workflows) those workflows with the community.

In case you’ve run into an issue while following the tutorial, feel free to reach out to me on [Twitter](https://twitter.com/tanay1337) or ask for help on our [forum](https://community.n8n.io/) 💙

> This [post](https://n8n.io/blog/database-monitoring-and-alerting-with-n8n/) originally appeared on n8n.io [blog](https://n8n.io/blog).