2

I'm fetching emails from IMAP and want to store them in PostgreSQL via Hasura GraphQL API. As HTML and special characters can't be in the valid GraphQL query, I need to get rid of them. Remove or replace with something acceptable. That ends up with ugly and unformatted content in the DB. Googled but can't really find a good or at least near-perfect solution for getting the most readable (ideally lossless) content in PostgreSQL.

I have to create an admin panel where emails can be assigned to users and have other relations and different rights (eg. one role won't see email addresses and possible phone numbers and other contacts removed from the email content). But they can reply from the system. Everything is easy except the question of how to get the modified HTML email to the DB.

Maybe I shouldn't use GraphQL. Maybe not JavaScript. I can code in PHP, too if that's easier but I prefer JavaScript as I use N8N.io workflow and I can easily use JavaScript there. Option I was also considering is to use PostgreSQL compression but then I can not use the Directus.io admin panel (that is used) for viewing, editing and adding relations to the emails.

Kaspar L. Palgi
  • 1,332
  • 10
  • 22
  • 1
    You should be able to pass arbitrary content as GraphQL inputs using a JSON variables block. What characters do you think you need to escape? (ASCII space U+0020 has specific meaning in GraphQL as a token separator, but it also is perhaps 20% of your post; is it "special"?) Can you use GraphQL/HTML/SQL libraries that correctly handle the escaping for you? – David Maze Nov 26 '22 at 12:26
  • 1
    Thanks a lot! JSON was also an option I found but the Directus.io admin panel won't display me a nice WYSIWYG editor then. "GraphQL/HTML/SQL libraries" option I'm not sure as didn't Google out any to understand how they work. – Kaspar L. Palgi Nov 26 '22 at 14:09
  • 1
    OK, I think I understood you wrong, @DavidMaze and thought you are suggesting to store JSON in DB field. As I'm still struggling with this I re-read your message and understood that you suggest to pass the content via variables as a JSON. I now tried that and now it fails in a different way. It doesn't say "Invalid GRaphQL query" but now: "ERROR: Using variables failed. With error message: SyntaxError: Unexpected token in JSON at position 130". Here's the query and variables: https://github.com/service-host/vercel-test/tree/main/gql – Kaspar L. Palgi Jan 12 '23 at 10:31

1 Answers1

1

I can see from your variables.json that you have line breaks in your JSON. After removing line breaks the JSON becomes valid. JSON does not allow real line breaks so you must use \n instead. Because you have HTML and use <br> for line breaks you can just remove the real line breaks for example like this:

someText = someText.replace(/(\r\n|\n|\r)/gm, "");

More about removing line breaks with JavaScript here: https://www.textfixer.com/tutorials/javascript-line-breaks.php

You may copy your JSON to VSCode for example and you will see if your JSON is valid. In the code, you can also check with JavaScript if the string is valid a valid JSON.

Martin Sults
  • 79
  • 6
  • 1
    I'm a step ahead. Thanks a lot. I'm now successfully mutating half of my array and then getting JSON error again. I will check what else is wrong there later. Big step ahead and I know where and how to search for the problem now. – Kaspar L. Palgi Jan 12 '23 at 10:58