Is there a neater way of creating this payload?


I am creating table in a database, originally it was one long line ,
I wanted to make it clearer to understand as I do not like one long line of text.
I have made a function node with this, it works but is there a better and neater way
As I have lots of commas and pluses ?

msg.payload = 
	"CREATE TABLE sensors (" +
	"serial_no	 SERIAL PRIMARY KEY," +
	"device      TEXT not null," +
	"device_data JSON not null );"

return msg;

You could use a template string, as sql statement can be on multiple lines

msg.payload = `CREATE TABLE sensors (
  serial_no   SERIAL PRIMARY KEY,
  device      TEXT not null,
  device_data JSON not null 

The quotes are backticks

I believe that will generate a mulit-line string with Return characters in it.

@questuk you can do this if you prefer it

msg.payload = "CREATE TABLE sensors ( \
  serial_no   SERIAL PRIMARY KEY,  \
  device      TEXT not null, \
  device_data JSON not null );"

Edit: I see that @E1cid acknowledged that and says that an SQL statement can include embedded return characters. He may well be correct.

Hi, E1cid

Yes that works ... thank you and colin, I thought it should be something like that.
I did have that exact layout of text, but it would fail.

I was using ' ' and not `` very subtle but makes or breaks code :slightly_smiling_face:

Thank you both very much for your help, that code will be added to my " cheat sheet " for future reference.

I like to use the template node for blocks of text like this -- any text you put into the multi-line editor will be added to msg.payload with the newlines intact. And since SQL statements can contain newlines, this should just work without using any javascript code.


Please note that this is NOT the ui_template node... and if you want to insert incoming msg values into your text, it also support moustache (sic ;*) syntax like {{payload.tablename}}.

1 Like

Thanks shrickus

That is even better, no back ticks to worry about, I am going to use mustache :slightly_smiling_face:

And you can set the Syntax Highlight dropdown to SQL :sunglasses:


I for one support all rodent facial hair


True - what you see is what you get... but if you do use "mustache" syntax to insert data into that DDL string, don't forget to use the triple braces i.e. {{{payload.tablename}}} to stop any potential html character encoding!

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.