Insert into JSON objects postgres using Node without forloop for every db.query

Question

I have an array of objects like with the same setup as the one below. I wanna insert this array of objects into postgres table that looks like this:[![Table setup][1]][1] I have tried to make a function below but it returns error when inserting UNGDOMSKOLE because this is a string so it doesn't understand the space so it crashes on the second input value. How can I make it understand it is a string?

{
  '@type': 'SensorSystem',
  id: 'SN47230',
  name: 'ÅKRA UNGDOMSSKOLE',
  shortName: 'Åkra ',
  country: 'Norge',
  countryCode: 'NO',
  geometry: {
    '@type': 'Point',
    coordinates: [ 5.1963, 59.2555 ],
    nearest: false
  },
  masl: 18,
  validFrom: '2013-10-29T00:00:00.000Z',
  county: 'ROGALAND',
  countyId: 11,
  municipality: 'KARM�?Y',
  municipalityId: 1149,
  stationHolders: [ 'KARM�?Y KOMMUNE' ],
  externalIds: [ '506131077' ],
  wigosId: '0-578-0-47230'
}

Error code:

error: syntax error at or near "UNGDOMSSKOLE"

What I have tried so far:

let sqlinsert= data.data.map((source)=>{
          if (source.geometry) {
            if(!source.masl){
              source.masl=0
          }
          let Point = `POINT(${source.geometry.coordinates[0]} ${source.geometry.coordinates[1]})`;
          return `(${source.id}, ${source.name}, ${source.shortName},${source.country},${source.countryCode},${source.masl},${source.geometry.coordinates[0]},${source.geometry.coordinates[1]},${Point},${source.validFrom},${source.county},${source.countyId},${source.municipality},${source.municipalityId})`
          }
        })
const result = await db.query("INSERT INTO sources(source_id,name,shortName,country,countryCode,masl,long,lat,geog,valid_from,county,countyId,municipality,municipalityId) values"+sqlinsert[0])

A second problem I have with this is that inserting

POINT(59.2555 5.1963)

Gives a syntax error at 5.1963 [1]: https://i.sstatic.net/4RSkq.png

Answer 1

The main problem with your query as written is that you are adding raw, unescaped values into your VALUES records. You can use escapeLiteral on your db client to ensure that these values are properly escaped which will solve the syntax errors you are getting:

    const data = [
      {
        "@type": "SensorSystem",
        id: "SN47230",
        name: "ÅKRA UNGDOMSSKOLE",
        shortName: "Åkra ",
        country: "Norge",
        countryCode: "NO",
        geometry: {
          "@type": "Point",
          coordinates: [5.1963, 59.2555],
          nearest: false,
        },
        masl: 18,
        validFrom: "2013-10-29T00:00:00.000Z",
        county: "ROGALAND",
        countyId: 11,
        municipality: "KARM�?Y",
        municipalityId: 1149,
        stationHolders: ["KARM�?Y KOMMUNE"],
        externalIds: ["506131077"],
        wigosId: "0-578-0-47230",
      },
    ].map((source) => {
      const {
        id,
        name,
        shortName,
        country,
        countryCode,
        masl,
        geometry: {
          // the coordinates in your source data appear to be in y,x instead of
          // x,y. Treating them as x,y results in the point being located
          // in the Indian Ocean while y,x is somewhere in Norway.
          coordinates: [lat, long],
        },
        validFrom,
        county,
        countyId,
        municipality,
        municipalityId,
      } = source;

      return [
        id,
        name,
        shortName,
        country,
        countryCode,
        masl || 0,
        long,
        lat,
        `POINT( ${long} ${lat} )`,
        validFrom,
        county,
        countyId,
        municipality,
        municipalityId,
      ];
    });

    const headers = [
      "source_id",
      "name",
      "shortname",
      "country",
      "countrycode",
      "masl",
      "long",
      "lat",
      "geog",
      "valid_from",
      "county",
      "countyid",
      "municipality",
      "municipalityid",
    ];

    const sourceValStr = data
      .map((sourceRecords, rowIndex) => {
        return sourceRecords
          .map((value, colIndex) => {
            if (typeof value === "string") {
              // safely escape string values
              return dbClient.escapeLiteral(value);
            }
            if (
              typeof value === "number" ||
              typeof value === "boolean" ||
              typeof value === "bigint"
            ) {
              return value;
            }

            if (value === undefined || value === null) {
              return "null";
            }

            throw new Error(
              `non-simple value: ${JSON.stringify(value)} for ${
                headers[colIndex]
              } at row ${rowIndex}`
            );
          })
          .join(",");
      })
      .map((value) => `(${value})`)
      .join(",");

    const sourceInsert = `INSERT INTO sources(${headers.join(
      ","
    )}) VALUES ${sourceValStr};`;
    await dbClient.query(sourceInsert);

A much more efficient and scalable way to insert the rows is to use the pg-copy-streams library in conjunction with a CSV library like csv-stringify which will bulk insert using a COPY FROM stream:

import { from as copyFrom } from "pg-copy-streams";
import { stringify } from "csv-stringify";
// ...
const copyStmt = `COPY sources(${headers.join(
      ","
    )}) FROM STDIN (FORMAT CSV)`;
await new Promise<void>((resolve, reject) => {
      const copyStream = dbClient.query(copyFrom(copyStmt));
      const stringifiedStream = stringify(data, {
        header: false,
        encoding: "utf-8",
        delimiter: ",",
        quote: "\"",
      });
      stringifiedStream
        .on("error", (err) => {
          reject(err);
        })
        .on("end", () => resolve());
      stringifiedStream.pipe(copyStream);
    });

On my low-end laptop, this approach takes about 39 seconds to insert a million rows with no database optimizations.