Esphome Custom Output to send data to Postgresql

ESPHome
1

I’m trying to replace a sensor I had. It takes some data from an I2C chip and sends it to a postgres table as well as Home Assistant. It was all custom Arduino code, but I want to migrate it over to ESPHome to make it easier to maintain (over the air updates, etc). I figured I could use the custom output to send the sensor value to postgres. I think I have it setup correctly (squashed all my errors in the compilation), but now I’m getting messages like

undefined reference to `_ZN12PGconnection5closeEv’

Any idea what I’m doing wrong? Code is as follows:

YAML File:

esphome:
  name: test
  includes:
    - postgresql.h

esp8266:
  board: esp01_1m

# Enable logging
logger:

# Enable Home Assistant API
api:

ota:
  password: "497eb82db5ba18c292c6b7e7b98f483d"

wifi:
  ssid: !secret wifi_ssid
  password: !secret wifi_password

  # Enable fallback hotspot (captive portal) in case wifi connection fails
  ap:
    ssid: "Test Fallback Hotspot"
    password: "D2OSUL8Av3He"

captive_portal:
    

external_components:
  - source: SimplePgSQL

output:
- platform: custom
  type: float
  lambda: |-
    auto ph_to_postgres = new Save_To_Postgres(IPAddress(X,X,X,X), "username", "password", "db", "schema", "table", "PH");
    App.register_component(ph_to_postgres);
    return {ph_to_postgres};

  outputs:
    id: ph_to_postgres

postgresql.h:

#include <SimplePgSQL.h>
#include "esphome.h"
using namespace esphome;

class Save_To_Postgres : public Component, public FloatOutput {
 public:
  Save_To_Postgres(IPAddress,std::string, std::string, std::string, std::string, std::string, std::string);
  IPAddress host;
  std::string user;
  std::string password;
  std::string dbname;
  std::string schema;
  std::string table;
  std::string sensor;

  void setup() override {
    // This will be called by App.setup()
  }

  void write_state(float state) override {
    WiFiClient client;
    char buffer[10240];
    PGconnection conn(&client, 0, 10240, buffer);
    int ignore;
    ignore = conn.setDbLogin(host, user.c_str(), password.c_str(), dbname.c_str(), "utf8");
    while (conn.status() != CONNECTION_OK) {
      delay(1000);
    }
    std::string sql;
    sql = "insert into " + schema + "." + table + " (sensor, value) values ('" + sensor + "', ";
    sql += state;
    sql += ");";

    int returned_value;
    returned_value = conn.execute(sql.c_str());
    conn.close();
  }
};

Save_To_Postgres::Save_To_Postgres(IPAddress ip, std::string username, std::string pass, std::string db, std::string schemaname, std::string tablename, std::string sensorname) {
  host = ip;
  user = username;
  password = pass;
  dbname = db;
  schema = schemaname;
  table = tablename;
  sensor = sensorname;
};

The SimplePgSQL directory in the config directory is a clone of this git repo:
https://github.com/ethanak/SimplePgSQL

2

Dear ebolam.
I was able to solve a similar problem for the am2320 sensor. I wrote a Custom Sensor Component in which I receive data from the sensor on ESPHome and write it to postgresql.

YAML-config for ESPHome

includes:
    - GarageDB.h
  libraries:
    - SPI
    - Wire
    - adafruit/Adafruit BusIO @ 1.9.6
    - Adafruit Unified Sensor
    - "AM2320"
sensor:
  - platform: custom
    lambda: |-
      auto garageDB = new GarageDB(IPAddress(X,X,X,X), "user_db", "pass_db", port_db, "name_db", "name_table");
      App.register_component(garageDB);
      return {garageDB->temperature_sensor, garageDB->humidity_sensor, garageDB->result};
    sensors:
    - name: "garageTemperature"
      device_class: temperature
      unit_of_measurement: '°C'
      accuracy_decimals: 2
    - name: "garageHumidity"
      device_class: humidity
      unit_of_measurement: '%'
      accuracy_decimals: 2
    - name: "garageDBResult"
      accuracy_decimals: 0

And Garage.h

#include "esphome.h"
#include <SimplePgSQL.h>
#include <Adafruit_AM2320.h>

WiFiClient pgClient;
char buffer[1024];
PGconnection pgConn(&pgClient, 0, 1024, buffer);

Adafruit_AM2320 am2320 = Adafruit_AM2320();

class GarageDB : public PollingComponent {
    public:
        IPAddress pg_host;
        String pg_user;
        String pg_password;
        int pg_port;
        String db_name;
        String db_table;

        Sensor *result = new Sensor();
        Sensor *temperature_sensor = new Sensor();
        Sensor *humidity_sensor = new Sensor();

    GarageDB(IPAddress _pg_host, String _pg_user, String _pg_password, int _pg_port, String _db_name, String _db_table) : PollingComponent(60000) {
        pg_host = _pg_host;
        pg_user = _pg_user;
        pg_password = _pg_password;
        pg_port = _pg_port;
        db_name = _db_name;
        db_table = _db_table;
    }

    float get_setup_priority() const override { return esphome::setup_priority::DATA; }

    void setup() override {
        am2320.begin();
     }

    void update() override {

        float temp = am2320.readTemperature();
        float humi = am2320.readHumidity();

        temperature_sensor->publish_state(temp);
        humidity_sensor->publish_state(humi);

        pgConn.setDbLogin(pg_host, pg_user.c_str(), pg_password.c_str(), db_name.c_str(), "utf8", pg_port);
        int rc = pgConn.status();
        int rcc = 0;
        while(rc == CONNECTION_AWAITING_RESPONSE && rcc < 1000) {
            rc = pgConn.status();
            delay(1);
            rcc++;
        }
//return debug info
        if (rc == CONNECTION_OK) {
            result->publish_state(0.0F);
        } else if (rc == CONNECTION_AUTH_OK) {
            result->publish_state(1.0F);
        } else if (rc == CONNECTION_AWAITING_RESPONSE) {
            result->publish_state(2.0F);
        } else if (rc == CONNECTION_NEEDED) {
            result->publish_state(3.0F);
        } else if (rc == CONNECTION_BAD) {
            result->publish_state(4.0F);
        }

        if (rc == CONNECTION_OK || rc == CONNECTION_AUTH_OK) {

            String str = "";
            str = "INSERT INTO <table_name> (data, sensor_id) VALUES (";
            str += temp;
            str += ", <sensor_id>); INSERT INTO <table_name> (data, sensor_id) VALUES (";
            str += humi;
            str += ", <sensor_id>);";

            const char *sql = str.c_str();
            pgConn.execute(sql);
        }
        pgConn.close();
    }
};
3

Hello,
tell me how you added the SimplePgSQL library
(src/postgresql.h:2:10: fatal error: SimplePgSQL.h: No such file or directory)
Where is this src directory? I’ve already added it in various ways

4

I finally added the library but there is another problem :wink:
libraries:
- file:///config/esphome/components/SimplePgSQL