Is it possible to publish a message to Google Pub/Sub whenever a data is inserted or updated in Google Cloud SQL?

Question

I'm new to Google Cloud SQL and Pub/Sub. I couldn't find documentation anywhere about this. But another question's accepted and upvoted answer seems to say it is possible to publish a Pub/Sub message whenever there is an insert happen to the database. Excerpt from that answer:

2 - The ideal solution would be to create the Pub/Sub topic and publish to it when you insert new data to the database.

But since my question is a different one, thus I asked a new question here.

Background: I'm using a combination of Google Cloud SQL, Firestore and Realtime Database for my app for its own unique strengths.

What I want to do is to be able to write into Firestore and Realtime databases once an insert is successful in Google Cloud SQL. According to the answer above, this is the steps I should do:

1. The app calls a Cloud Function to insert a data into Google Cloud SQL database (PostgreSQL). Note: The Postgres tables has some important constraints and triggers Postgres functions, thats why we want to start here.
2. When the insert is successful I want Google Cloud SQL to publish a message to Pub/Sub.
3. Then there is another Cloud Function that subscribes to the Pub/Sub topic. This function will write into Firestore / Realtime Database accordingly.

I got steps #1 & #3 all figured out. The solution I'm looking for is for step #2.

Broglie

Answer 1

The answer in the other question is simply suggesting that your code do both of the following:

1. Write to Cloud SQL.
2. If the write is successful, send a message to a pubsub topic.

There isn't anything that will automate or simplify either of these tasks. There are no triggers for Cloud Functions that will respond to writes to Cloud SQL. You write code for task 1, then write the code for task 2. Both of these things should be straightforward and covered in product documentation. I suggest making an attempt at both (separately), and posting again with the code you have that isn't working the way you expect.

If you need to get started with pubsub, there are SDKs for pretty much every major server platform, and the documentation for sending a message is here.

Orchidaceous

Thanks for the answer Doug. So there is no way that Google Cloud SQL sends message to Pub/Sub right ? – Broglie

No, that is not a capability of Cloud SQL. The documentation says "server-level triggers" are unavailable. – Orchidaceous

Answer 2

As mentioned in the comments, you could now use https://cloud.google.com/datastream

---

Original answer:

While Google Cloud SQL doesn't manage triggers automatically, you can create a trigger in Postgres:

CREATE OR REPLACE FUNCTION notify_new_record() RETURNS TRIGGER AS $$
    BEGIN
       PERFORM pg_notify('on_new_record', row_to_json(NEW)::text);
       RETURN NULL;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER on_insert
AFTER INSERT ON your_table
FOR EACH ROW EXECUTE FUNCTION notify_new_record();

Then, in your client, listen to that event:

import pg from 'pg'
const client = new pg.Client()
client.connect()
client.query('LISTEN on_new_record') // same as arg to pg_notify
client.on('notification', msg => {
  console.log(msg.channel) // on_new_record
  console.log(msg.payload) // {"id":"...",...}
  // ... do stuff
})

In the listener, you can either push to pubsub or cloud tasks, or, alternatively, write to firebase/firestore directly (or whatever you need to do).

Source: https://edernegrete.medium.com/psql-event-triggers-in-node-js-ec27a0ba9baa

You could also check out Supabase which now supports triggering cloud functions (in beta) after a row has been created/updated/deleted (essentially does the code above but you get a nice UI to configure it).

Jacobina

Hi stackoverflow.com/users/384349/petr-bela, what code have you used in the above section for the client to listen? Is that on nodejs? Also, could you please let me know whether this connection could be made from Google Cloud Function and use that to trigger/publish to a pub/sub topic? – Declass

Yes, Node.js. Because you need to listen to potential events 24/7, it needs to run on a persistent server. You could, however, trigger a cloud function from there to handle each individual event. – Jacobina

Found a better solution, cloud.google.com/datastream this works seamless while we want to get changes from CloudSQL into Cloud Storage and then we can use Pub/sub along with Dataflow to do any processing. Details on how we could implement it is available in: cloud.google.com/datastream/docs/… – Declass