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)

There should definitely be an error. Are you using a promise-supporting router like express-promise-router? You need that for promises to produce errors properly. (If this is the issue, there should also be an unhandled promise rejection warning on your console.) — Ry-
– Ry- ♦, Commented Apr 28, 2020 at 14:27
@Ry- so i need to install it first with npm install express-promise-router? I added my package.json dependencies for clarity. — Munchkin
– Munchkin, Commented Apr 29, 2020 at 13:57
You can’t stop at installing it; you have to use it, too. Read the package’s documentation (README). — Ry-
– Ry- ♦, Commented Apr 29, 2020 at 17:16
@Ry- it does not seem to help :/ I tried adding the code as mentioned in the updated question — Munchkin
– Munchkin, Commented Apr 30, 2020 at 14:39

Danizavtz · Accepted Answer · 2020-05-11 20:54:54Z

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)
    }
})

That part with creation of a table before inserting comes from the official documentation..
gave you the bounty because you were the closest to an actual answer. Please edit your answer to contain it and I will accept it as an answer.

Wazted · Accepted Answer · 2020-05-04 14:37:31Z

-1

Make sure that your client is correctly connected to your Postgres DB, try to add a client.connect() and basic query with console log (outside your router).

https://node-postgres.com/features/connecting

Do your tables are correctly created meaning taht you are connected to DB ?

Is the NodeJS and the DB on your computer or in docker ?

And do the request give you any response code ?

answered May 4, 2020 at 14:37

Wazted

1011 silver badge10 bronze badges

8 Comments

Munchkin Over a year ago

Node.js and Postgres is running on metal. My tables were not created. I get no output at all

Wazted Over a year ago

Ok then verify you are using the correct user/password or hostname, you can also try the postgres url. ( postgresql://[user[:password]@][netloc][:port][/dbname] ).

Munchkin Over a year ago

So the code is just await postgres.url(postgresql://[user[:password]@][netloc][:port][/dbname]);?

Wazted Over a year ago

No, in your new Client, you can add connectionString : with your url correctly formed , it replace all user/hostname/port/user/passwd fields, both can work if infos are correct ones.

Munchkin Over a year ago

managed to get this printed out: [ { id: '8ff4d60a-82e7-45e0-a13a-3d9a438cf418', data: { case: 'it', section: 'ua' } } ] - this is my JSONB

|

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related