How to create a command for sql?

Question

PostgreSQL

Hello, I have an object

let obj={
id:1,
name: "someName",
lastName: "someLastName",
}

And I want to fill in the database with all its fields of the
base type

CREATE TABLE myTable (id BIGINT NOT NULL PRIMARY KEY, firstName VARCHAR(50) UNIQUE, lastName VARCHAR(50));

To create a sql command
, I had to do this

let query = "";
          query += "INSERT INTO";
          query += " " + obj.__TABLE_NAME;
          query += "(";
          for (field in obj) {
            query += field + ",";
          }
          query += ") " + "VALUES" + " (";
          for (field in obj) {
            query += obj[field] + ",";
          }
          query += ")";
          query += "ON CONFLICT (name)";
          query += "DO UPDATE SET ";
          for (field in obj) {
            query += field + " = " + "EXCLUDED." + field + " ";
          }
          query = ";";

But for some reason it reminds me of g-code, are there other approaches...

Answer 1

You can create a function in the database that accepts json:

CREATE TABLE my_table (id BIGINT NOT NULL PRIMARY KEY, first_name VARCHAR(50) UNIQUE, last_name VARCHAR(50));

CREATE FUNCTION add_obj (p_obj json)
RETURNS int
LANGUAGE plpgsql
AS $$
DECLARE
  p_id int := p_obj ->> 'id';
  p_name TEXT := p_obj ->> 'name';
  p_last_name TEXT := p_obj ->> 'lastName';
BEGIN 
  INSERT INTO my_table (
    id, first_name, last_name
  ) VALUES (
    p_id, p_name, p_last_name
  ) RETURNING id;
END;
$$;

Then take a low-level library:
npm i pg
And use it to pull the function:

import pkg from 'pg';
const { Pool } = pkg;

export class PostgresDriver {
  constructor(config) {
    this.pool = new Pool(config);
    this.pool.on('error', err => {
      console.error('StoreDB Postgres error', err);
    });
  }

  async getPoolClient() {
    try {
      return await this.pool.connect();
    } catch (e) {
      throw new Error('StoreDB -> poolConnect() ->', e.message);
    }
  }

  async addObject(obj) {
    const json = JSON.stringify(obj);
    const sql = `CALL add_obj('${json}');`;
    try {
      const client = await this.getPoolClient();
      const rs = await client.query(sql);
      client.release();
      return rs;
    } catch (e) {
      throw new Error(`PostgresDriver -> ${sql}`);
    }
  }
}

But I would recommend renaming id to ext_id and adding a secure serial primary key to the table.