# Save device data to PostgreSQL using the Rule Engine

In this article, we will simulate the temperature and humidity data, and publish these data to EMQ X Cloud via the MQTT protocol, and then we will use the EMQ X Cloud rule engine to store the data to PostgreSQL.

Before you start, you will need to complete the following:

  • A deployment (EMQ X Cluster) has been created on EMQ X Cloud.

  • For professional deployment users: Please complete the creation of a VPC Peering Connection first. All IPs mentioned below refer to the resource's intranet IP.

  • For basic deployment users: No VPC Peering Connection is required. All IPs mentioned below refer to the resource's public IP.

# PostgreSQL Configuration

  1. Install PostgreSQL 11

    docker run -d --restart=always --name postgres \
        -p 5432:5432 \
        -e POSTGRES_PASSWORD=public \
        -e POSTGRES_USER=root \
        postgres:11
    
    1
    2
    3
    4
    5
  2. New database

    docker exec -it postgres psql
    CREATE database emqx;
    \c emqx
    
    1
    2
    3
  3. New temperature and humidity table

    Use the following SQL statement to create temp_hum table. This table will be used to save the temperature and humidity data reported by devices.

    CREATE TABLE temp_hum (
    id SERIAL NOT NULL, 
    up_timestamp INTEGER, 
    client_id VARCHAR(32), 
    temp FLOAT, 
    hum FLOAT, 
    PRIMARY KEY (id)
    );
    
    1
    2
    3
    4
    5
    6
    7
    8
  4. Insert test data and view it

     Insert into temp_hum(up_timestamp, client_id, temp, hum) values (1603963414,'client_1',19.1,55); 
     select * from temp_hum;
    
    1
    2
    select * from temp_hum;
    
    1

# EMQ X Cloud Rule Engine Configuration

Select Rule Engine from the left menu bar to edit rule engine configuration.

  1. Create PostgreSQL Resource

    Click on Rule Engine on the left menu bar and click on + New button to create a new resource.

    create resource

    Then fill in the information regarding the PostgreSQL database you choose to store the data. You could always test if the database configuration you entered is correct before confirm to add the resource. If you get an error message after clicking the Test button, make sure to double-check the input information and retry.

    create resource

    If the configuration test is passed, click on confirm to finish creating resource.

  2. Create Rule

    Now we need to create a rule for the rule engine. Click on Rule Engine on the left menu bar and click on + New button to create a new Rule.

    In the following rule, we read the timestamp, client ID, payload via temp_hum/emqx topic.

    SELECT 
    
    timestamp as up_timestamp, clientid as client_id, payload.temp as temp, payload.hum as hum  
    
    FROM  
    
    "temp_hum/emqx"  
    
    1
    2
    3
    4
    5
    6
    7

    rule engine

  3. Rule Test

    You should always test the SQL ahead to make sure it's functioning as you expected. rule engine

  4. Add Action

    After inputting the SQL command, it's time to create the response actions.

    Towards the bottom of the page, in the Response Action section, click on the + Add action button.

    rule_action Fill in the SQL Template with sql command bellow

    insert into temp_hum(up_timestamp, client_id, temp, hum) values (${up_timestamp}, ${client_id}, ${temp}, ${hum})
    
    1
  5. Click on Confirm to finish creating a Rule. You could always come back to edit you rules and add more actions. rule list

  6. Check Rules Monitoring view monitor

# Test

  1. Use MQTT X (opens new window) to simulate publishing temperature and humidity data

    You need to replace broker.emqx.io with the deployment connection address you have created and add the client-side authentication information in the EMQ X Dashboard. MQTTX

  2. View stored results

    select * from temp_hum;
    
    1

    mysql