Multi insert inside a QueryFile

Question

I'm able to generate query for multi inserts or update thanks to pg-promise helpers but I was wondering if I could follow the advice of the author and put all queries outside of my javascript code (See here https://github.com/vitaly-t/pg-promise/wiki/SQL-Files and here : https://github.com/vitaly-t/pg-promise-demo).

When I use the insert helpers, the return query looks like :

INSERT INTO "education"("candidate_id","title","content","degree","school_name","start_date","still_in","end_date","picture_url") VALUES('6','My degree','Business bachelor','Bachelor +','USC','2018-05-15T02:00:00.000+02:00'::date,false,null::date,null),('6','Another degree','Engineering','Master degree','City University','2018-05-15T02:00:00.000+02:00'::date,false,null::date,null)

The idea is that I don't know how many inserts I want to do at the same time, so it has to be dynamic.

The following code doesn't work as I'm passing an array of object instead of an object :

db.none(`INSERT INTO "education"("candidate_id","title","content","degree","school_name","start_date","still_in","end_date","picture_url") 
      VALUES($<candidate_id>, $<title>, $<content>, $<degree>, $<school_name>, $<start_date>, $<still_in>, $<end_date>, $<picture_url>)`, data)

This code spreads the object but is still not correct to make a proper query :

db.none(`INSERT INTO "education"("candidate_id","title","content","degree","school_name","start_date","still_in","end_date","picture_url") 
      VALUES($1:list)`,
          [data])

Any idea ? Is it at least possible or in the case where I don't know how many records I want to insert in advance I have to call pgp.helpers everytime ?

Answer 1

You confuse static and dynamic SQL. SQL files are there for SQL queries that are mainly static, i.e. you still can inject dynamically a lot, but when most of the query is dynamic, there is no longer any point putting it into an SQL file.

And the helpers namespace is there for dynamic queries only. So you are asking about two separate things, to join things that do not need to be joined.