Using upsert in postgres with a json file in nodejs

Question

I have a large json file of data which I want to put into my database. Some of the object are duplicates, so I want to update the data in case the row is already in the database. Here is my code:

const FILE_PATH = path.join(__dirname, "../../files/apps.json");

const columns = [
          "name",
          "description",
          "ext_id"
        ];

const myFile = fs.readFileSync(FILE_PATH, { encoding: "utf-8" });
const appData = await models.sequelize.query(
          `
        INSERT INTO data (${columns.join(", ")})
        SELECT ${columns.join(", ")}
        FROM (:path)
        ON CONFLICT (ext_id)
          DO UPDATE SET
            ${columns.map(col => `${col} = EXCLUDED.${col}`).join(", ")}
        RETURNING ext_id;
        `,
          { replacements: { path: FILE_PATH } }
        );

As you can see, I want to read the file directly and put it into the database. I use a mapper called sequelize, but use a raw query in this case. My immediate problem is that I get this error:

syntax error at or near "'/home/blub/filePath'"

I don't really know how I should specify the path. I tried to parse it in directly, but then the program complained about the /. Any help here? In addition, I am also not sure whether the query is syntactically correct.

Answer 1

Here is a solution using CTE of postgres.

Versions:

• "sequelize": "^5.21.3"
• postgres:9.6

apps.json:

[
    {
        "name": "app-a",
        "description": "app a desc",
        "ext_id": 1
    },
    {
        "name": "app-b",
        "description": "app b desc",
        "ext_id": 2
    },
    {
        "name": "app-c",
        "description": "app c desc",
        "ext_id": 3
    }
]

index.ts:

import { sequelize } from '../../db';
import { Model, DataTypes, QueryTypes } from 'sequelize';
import fs from 'fs';
import path from 'path';

class Data extends Model {}
Data.init(
  {
    name: DataTypes.STRING,
    description: DataTypes.STRING,
    ext_id: {
      type: DataTypes.INTEGER,
      unique: true,
    },
  },
  { sequelize, tableName: 'data' },
);

(async function test() {
  try {
    await sequelize.sync({ force: true });
    const FILE_PATH = path.join(__dirname, './apps.json');
    const columns = ['name', 'description', 'ext_id'];
    const myFile = fs.readFileSync(FILE_PATH, { encoding: 'utf-8' });
    const appData = await sequelize.query(
      `
        with app_json(doc) as (
            values ('${myFile}'::json)
        )
        insert into data (${columns.join(', ')})
        select ${columns.join(', ')}
        from app_json l
            cross join lateral json_populate_recordset(null::data, doc) as p
        on conflict (ext_id) do update 
            set ${columns.map((col) => `${col} = EXCLUDED.${col}`).join(', ')}
        returning ext_id;
    `,
      { type: QueryTypes.INSERT },
    );
    console.log(appData);
  } catch (error) {
    console.log(error);
  } finally {
    await sequelize.close();
  }
})();

The execution result:

Executing (default): DROP TABLE IF EXISTS "data" CASCADE;
Executing (default): DROP TABLE IF EXISTS "data" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "data" ("id"   SERIAL , "name" VARCHAR(255), "description" VARCHAR(255), "ext_id" INTEGER UNIQUE, PRIMARY KEY ("id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'data' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): with app_json(doc) as (
            values ('[
    {
        "name": "app-a",
        "description": "app a desc",
        "ext_id": 1
    },
    {
        "name": "app-b",
        "description": "app b desc",
        "ext_id": 2
    },
    {
        "name": "app-c",
        "description": "app c desc",
        "ext_id": 3
    }
]'::json)
        )
        insert into data (name, description, ext_id)
        select name, description, ext_id
        from app_json l
            cross join lateral json_populate_recordset(null::data, doc) as p
        on conflict (ext_id) do update 
            set name = EXCLUDED.name, description = EXCLUDED.description, ext_id = EXCLUDED.ext_id
        returning ext_id;
[ [ { ext_id: 1 }, { ext_id: 2 }, { ext_id: 3 } ], 3 ]

Check the data records in the database:

id  name    description ext_id
1   app-a   app a desc  1
2   app-b   app b desc  2
3   app-c   app c desc  3