Using the Postgres Query node

I’m trying to get up a simple Hackeet to access a Postgres database. Right now I just want to do a successful query that pulls all the lines from a database called ‘nocodb’ which currently has 3 lines in the table called ‘Tailr’.

I can see in the output in the terminal that the SQL query I’m using (below) returns one database line at a time, as it prints 3 times separately from the node ‘Results’.

SELECT * FROM "Tailr";

So I figured I need to add a step to build a JSON array of results made from each successful Postgres query result. The workflow is still just running to the end after the first Postgres result and not outputting the other two results, or concatenating them into a JSON object. I’m guessing I’m missing a loop or a mapping or something… Help please @Thierry ?

When a request is made to a Hackeet server, the answer returned is the one returned by the first “Output” node requested.
In your example, it is the “returnAllItems” output that will return a response as soon as it is activated, i.e. as soon as a record is found in the database.
If you want an array that contains all the records found, you must first create it, then return it as in the following example.
In this example, the activation orders of the nodes are numbered.
Results aggregation

This is so cool, thanks Thierry! The examples you give are so well made and helpful.

One question:

So there are two branches coming off the DB loop simulator in your example. How do you know in Hackeet that step 1. will repeatedly go to step 2. and 3. and only when it’s finished it goes to step 4. ?

The way it looks to me, it could conceivably go:
Step 1. runs, which triggers simultaneously step 2. and step 4. every time it runs.

How does it know to only trigger Step 4. at the end of the loop, while at the same time it also knows to trigger Step 2. every time?

Here’s some screenshots for everyone else’s benefit :

In Hackeet, when a node receives an event, it is activated and can then eventually produce one or more events on each of the output ports (if it contains any).
In the case of the Postgresql node, an event will be produced on the Results output port for each record found. In the example I sent you, the algorithm is as follows:
1/ The “EmulateDB” node receives an event from the “Search” node via the “search” port.
2/ Upon receiving the event, it runs and simulates the behavior of a call to a database that returns 3 records. For each record, it creates an event on the output port “loop”.
3/ For each event coming from “EmulateDB” via the “loop” port, the nodes “2.Create JSON” then “3.Add Item” are then executed.
4/ When “EmulateDB” has finished its treatment, the events coming from “search” continue to be propagated in the diagram, here just to “4.Finalize”.
It is in this last node that the final response returned to the user is updated.

That’s awesome, it works!! Thanks @Thierry :partying_face: So if I’ve understood it right, as long as the exit port of the looping node doesn’t directly connect to the Output, it’ll keep looping until that node’s results exit port has been exhausted and then move on to the other exit port that leads to the Output node?

One question which I think I’ve already asked you before but other people will wonder the same thing (especially for working with Bubble or Noodl APIs):

The returned data from Hackeet has the ‘content’ field with the array of objects found in my Postgres, but it’s been formatted as a JSON-safe string, so it needs to be unescaped in the front-end editor. This can be done in Noodl I think, but not in Bubble, and it unnecessarily complicates things sometimes especially with these no-code editors.

I know you showed me a way before that I can format the ‘content’ field that’s returned from the API call so it shows up as raw JSON and not an escaped string. Can you please explain it to me again?

Example of what I’m receiving right now in Postman:

"body": "",
        "content": "[{\n\"Level\":\"2,1\",\n\"Title\":\"Perfect\"\n}\n,\n{\n\"Level\":\"4,2,3\",\n\"Title\":\"qsdqsd\"\n}\n,\n{\n\"Level\":\"1\",\n\"Title\":\"Lesson 5\"\n}\n]",
        "ws_secret": ""

By default, when a Hackeet server returns data through an endpoint, that data is returned with additional information that lets the end user know what output was triggered, what type of information is being conveyed, etc. If you want to return only the raw data, you must specify a “content” field on the returned information that will contain it, as well as a “content_type” that indicates the type of data returned. Consider the “4.Finalize” node in the previous example.

1 Like

Awesome, thanks Thierry!!

I’ve got a brand new problem. After our last discussion I made the following Node just before my Postgres query to generate an SQL query depending on a condition, like this:

@query = if(request.lessonId!=0,"SELECT * FROM \"Courses\" WHERE id =" + request.lessonId + ";", "SELECT * FROM \"Courses\";")

Here’s how the debugger looks:

Here’s the Postgres node:

When I paste the error into chatGPT it says the error shows that {query} is evaluating with quotes around it, so the SQL query has quotes around it and is throwing an error.

It’s true that when I copy and paste the same @query SQL query from the debugger directly into the Query field of the Postgres node, it works great.

Any ideas?

Hi Richard,
Could you show me the properties of the Postgres node ?

Hey Thierry! It’s in the second screenshot, all the properties are visible.

Sorry, I usually set up my Hackeet studio with the properties window on the right and I missed this one.
I looked and there is a bug in the way the queries are built. This will be fixed in the next version, tomorrow or the day after I think.

1 Like

hero GIF

I have deployed a new version of the plugin available for download. Open Hackeet, go to the settings tab and select the Postgresql component to install. This should do the trick

Hey Thierry. I did that, but now the query that’s passed returns no results. I’ve tested the same query directly in my Postgres database and it works. I also tried replacing {query} with the direct SQL query ‘SELECT * FROM “Courses”;’ in the Postgres node, still zero results and no errors in the debugger. Check screenshot:

Hi Richard!
Is it possible to share your Hackeet application, even with fake credentials, so I can have a look at it?
You can send it at if you want.

1 Like

So it was indeed the filter between the Postgres query node and the JSON Fast Parse node. I took out the filter and it worked! It’s weird because the filter worked before. Maybe the outputs of the Postgres node changed since you updated it? ‘results.published’ was outputting a boolean before the Postgres update, and ‘results.published == “Yes”’ worked ok. Why did we make that condition in the first place??

EDIT: I’m a dumbass, I figured it out. I put the published condition in place because my database has some ‘in progress’ entries that I don’t want to show in the API results. I forgot I had set all the lines to published = false! Oops. I changed a few lines to ‘true’ and it worked fine. My bad! All is well :sweat_smile:

1 Like

It’s good to know that everything works fine. By the way, as mentioned earlier, I think it’s better to filter the data you want to retrieve directly in your SQL query rather than retrieving all the data and filtering it using Hackeet. This will work for sure, but in the latter case you may increase unnecessary network traffic and have a longer response time.

1 Like