How do I properly save this JSONB into PostgreSQL using node-postgres (pg)?

Question

So I have information coming in the GET URL, which needs to get passed into JSON and then saved (aggregated with increasing IDs to be correct) in the PostgreSQL DBMS. I wrote the following code, which seems to not save anything with no errors:

// Pg initialization
const { Client } = require('pg')
client = new Client({
    host: 'localhost',
    user: 'postgres',
    password: 'passwordhere',
    database: 'dbnamehere',
});

const createTableText = `
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE TEMP TABLE IF NOT EXISTS cases (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  data JSONB
);
`

app.get('/test', async function (req, res) {
  data = req.query.data;
  console.log(data);
  res.status(200).send(data);
// create our temp table
await client.query(createTableText)
//const newUser = { email: '[email protected]' }
// create a new case
await client.query('INSERT INTO cases(data) VALUES($1)', [data])
const { rows } = await client.query('SELECT * FROM cases')
console.log(rows)
  res.end();
});

My package.json dependencies:

"dependencies": {
    "express": "^4.17.1",
    "mongoose": "^5.9.9",
    "pg": "^8.0.3"
  },
  "devDependencies": {}

UPDATE

I have this error handling code at the end of the file:

 // Prints out more detailed errors
  if(process.env.NODE_ENV !== 'production') {
    process.once('uncaughtException', function(err) {
      console.error('FATAL: Uncaught exception.');
      console.error(err.stack||err);
      setTimeout(function(){
        process.exit(1);
      }, 100);
    });
  }

I also tried installing npm install express-promise-router and adding the following code but no errors were printed:

var router = require('express-promise-router')();
router.use('/test', function (req, res) {
    return Promise.reject();
})

UPDATE2 This code without closing it prints out the JSONB, not how do I save it?:

const connectionString=urlhere;
const pool = new Pool({
    connectionString: connectionString,
  })

  const client = new Client({
    connectionString: connectionString,
  })
  client.connect()

UPDATE3:

I removed the asynchronous code and made it synchronous. I get the following error messages now:

(node:10860) UnhandledPromiseRejectionWarning: Error: Connection terminated
    at Connection.<anonymous> (/path/here/node_mo
dules/pg/lib/client.js:275:34)
    at Object.onceWrapper (events.js:299:28)
    at Connection.emit (events.js:215:7)
    at Socket.<anonymous> (/path/here/node_module
s/pg/lib/connection.js:73:10)
    at Socket.emit (events.js:210:5)
    at TCP.<anonymous> (net.js:659:12)
(node:10860) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This
 error originated either by throwing inside of an async function without a catch
 block, or by rejecting a promise which was not handled with .catch(). (rejectio
n id: 1)
(node:10860) [DEP0018] DeprecationWarning: Unhandled promise rejections are depr
ecated. In the future, promise rejections that are not handled will terminate th
e Node.js process with a non-zero exit code.
(node:10860) UnhandledPromiseRejectionWarning: Error: Connection terminated
    at Connection.<anonymous> (/path/here/client.js:275:34)
    at Object.onceWrapper (events.js:299:28)
    at Connection.emit (events.js:215:7)
    at Socket.<anonymous> (/path/here/node_module
s/pg/lib/connection.js:73:10)
    at Socket.emit (events.js:210:5)
    at TCP.<anonymous> (net.js:659:12)
(node:10860) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This
 error originated either by throwing inside of an async function without a catch
 block, or by rejecting a promise which was not handled with .catch(). (rejectio
n id: 2)
(node:10860) UnhandledPromiseRejectionWarning: Error: Connection terminated
    at Connection.<anonymous> (/path/here/node_mo
dules/pg/lib/client.js:275:34)
    at Object.onceWrapper (events.js:299:28)
    at Connection.emit (events.js:215:7)
    at Socket.<anonymous> (/path/here/node_module
s/pg/lib/connection.js:73:10)
    at Socket.emit (events.js:210:5)
    at TCP.<anonymous> (net.js:659:12)
(node:10860) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This
 error originated either by throwing inside of an async function without a catch
 block, or by rejecting a promise which was not handled with .catch(). (rejectio
n id: 3)

Answer 1

I have a project generated with express-generator folder structure. But it follows the node pg suggested project structure.

.
├── _bin
|   ├── www
├── _db
|   ├── index.js
├── _server
|   ├── index.js
├── _sql
|   ├── create.sql
|   ├── insert.sql
|   └── drop.sql
├──.env
├── package.json
└── app.js

db/index.js

const { Pool } = require('pg')
const pool = new Pool()
module.exports = {
  query: (text, params, callback) => {
    return pool.query(text, params, callback)
  },
}

In file above the credentials are loaded by .env file. But you can as in your question.

server/index.js

// notice here I'm requiring my database adapter file
// and not requiring node-postgres directly
const db = require('../db')
app.get('/:id', (req, res, next) => {
  db.query('SELECT * FROM users WHERE id = $1', [req.params.id], (err, res) => {
    if (err) {
      return next(err)
    }
    res.send(res.rows[0])
  })
})

Now in sql folder you should put your DDL data.

sql/create.sql

CREATE TABLE employee(
  employee_id SERIAL PRIMARY KEY ,
  login VARCHAR(20) NOT NULL,
  password VARCHAR(512)
);

here you can rollup the commands just using the psql or you can create a wrapper to run DDL with javascript and use it in your package.json

If you would like to do a wrapper in javascript

sql/create.js

const fs = require('fs'),
pg = require('../db/index'),
sql = fs.readFileSync(__dirname + '/../sql/create.sql').toString();

pg.query(sql, (err) => {
    if (err) {
        throw err;
    }
})

Now in your package.json in the scripts section you can put a new line:

package.json

{
  ...
  "name": "myawesomeproject",
  "version": "1.0.0",
  "description": "My wonderful description",
  "main": "app.js",
  "scripts": {
    "start": "node ./bin/www",
    "createdb": "node ./sql/create.js"
  },
  ...
}

Now you can use npm run createdb.

So answering the real question here in your /test route should be:

app.get('/test', async function (req, res) {
  try {
    const { rows } = await client.query('SELECT * FROM cases')
    res.status(200).send(rows);
  } catch {
    res.status(500).send("Error. Detail: "+e)
  }    
});
//should insert data with post method, whole body is your current data.
app.post('/adddata', async (req, res) => {
    const data = req.body;
    console.log(data);
    try {
        await client.query('INSERT INTO cases(data) VALUES($1)', [data])
        res.status(201).send('Data inserted');
    } catch (e) {
        res.status(500).send("Error. Detail: "+e)
    }
})