How to pass a Postgres Pool or Client through a socket

Viewed 150

So I've deployed my server to Heroku with the following:

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  ssl: {
    rejectUnauthorized: false
  }
});
const http = require('http').Server(app);
const io = require('socket.io')(http);

I have a function that sends a request to my Raspberry Pi (which is acting as a socket client in this case) asking it to run a scraping function and update a table in my Heroku Postgres database accordingly. The only problem is on the Pi, the client.query() function is not recognized...not sure why. Here's my function that sends the request to the Pi:

async function F1() {
  const client = await pool.connect();
  try {
    await client.query('BEGIN')
    //do some database updating here in some table updates
    
    let date = ("0" + terminationTime.getDate()).slice(-2);
    let month = ("0" + (terminationTime.getMonth() + 1)).slice(-2);
    let year = terminationTime.getFullYear();
    let hours = terminationTime.getHours();
    let minutes = terminationTime.getMinutes();
    let seconds = terminationTime.getSeconds();
    let timestamp = year + "-" + month + "-" + date + " " + hours + ":" + minutes + ":" + seconds
    let data = {
        pool: pool, //here I tried passing in the pool, also tried passing in client
        query: query,
        timestamp: timestamp
      }
      io.to(RPiMiniServerID).emit("requestToPi", data)
  } catch(err) {
    console.log(err);
  }
}

And here's my code on the Pi/Client side:

const io = require("socket.io-client");
const socket = io.connect("MY HEROKU APP URL HERE")
const { Pool, Client } = require("pg");

socket.on("requestToPi", (data) => sync function() {
  let client = await data.pool.connect()
  let query = data.query
  let timestamp = data.timestamp
  //a bunch more code and THEN
  await client.query('INSERT INTO table (columnnames) VALUES($1)', [value]); //this is the line with the error
}()); 

I've tried passing in the client directly, but that also doesn't work. Not really sure what to do here--any help is much appreciated!

1 Answers

Let me get this straight, you're trying to send the instance of the pg library object that was started on your heroku server to your Pi and continue operation from that 'pg' library object state?

The first rule of HTTP is that its stateless. I know its Socket io but it still relies on http and the data is recreated on the other end and its state is lost over the connection. So in a way, you're starting a new instance of the pg instance on the Pi and the one on the heroku server is not the same as the one on the Pi.

  await client.query('INSERT INTO table (columnnames) VALUES($1)', [value]); //this is the line with the error

That is because the data has completely lost its state. That means, you're not connected to database, you have not started the commit process yet, ect. You have a new instance of the pg library on your hand and everything you did on heroku server is completely different.

One solution to this problem is to use some sort of real time model data synchronization like backbone.io or other similar solutions out there.

Related