CODEWITHSUNDEEP
javascriptnode.jsasynchronousnode-postgres
Damian Toczek
Damian Toczek

Reputation: 73

How do I make my own CRUD module using node-postgres?

When making an ajax call to the NodeJS server (not localhost), it doesn't return anything. When making the ajax call, NodeJS shows the results inside my NodeJS console (ssh). NodeJS crashes 10-15s after the ajax call with an error.

I've tried to use Pool but I don't understand it.

frontend

"use strict";
var btn = document.getElementById('button');

function ajax(){
  let req = new XMLHttpRequest();
  req.onreadystatechange = function(){
    if( this.readyState === 4 && this.status === 200){
      let data = JSON.parse( this.responseText );
      console.log(data);
    }
  };
  req.open("GET", "/ajax/", true);
  req.send();
};

btn.addEventListener("click", (e)=>{
  e.preventDefault();
  ajax();
});

backend (nodejs)

const {Client} = require('pg');

const db = new Client({
  user: "x",
  password: "x",
  host: "x",
  port: 123,
  database: "abc",
  ssl: true
});

module.exports = {
  async select( sql ){
    try{
      await db.connect();
      console.log("Connected to DB");
      const result = await db.query( sql );
      console.table(result.rows); // <-- Shows the data i want to send back.
      return JSON.stringify(result.rows);
    }

    catch(ex){
      console.log("We messed up! " + ex);
    }

    finally{
      await db.end();
      console.log("DB connection closed");
    }
  }
};

I expect the ajax call to return data from the postgres database. The same data i see inside my nodeJS console.

Error: Client has already been connected. You cannot reuse a client.
events.js:177
      throw er; // Unhandled 'error' event
      ^

Error: This socket has been ended by the other party
    at TLSSocket.writeAfterFIN [as write] (net.js:407:14)
    at Connection.end 
 code: 'EPIPE'

Upvotes: 1

Views: 532

Answers (1)

yqlim
yqlim

Reputation: 7098

To use Client from the pg module, you must create a new Client instance for every database query because each Client represents different users and connections. So, you should treat them as a one-time use instance:

const {Client} = require('pg');
const dbConn = {
  user: "x",
  password: "x",
  host: "x",
  port: 123,
  database: "abc",
  ssl: true
};

module.exports = {
  async select( sql ){
    const db = new Client(dbConn); // <-- Create new Client for every call

    try{
      await db.connect();
      console.log("Connected to DB");
      const result = await db.query( sql );
      console.table(result.rows); // <-- Shows the data i want to send back.
      return JSON.stringify(result.rows);
    }

    catch(ex){
      console.log("We messed up! " + ex);
    }

    finally{
      await db.end();
      console.log("DB connection closed");
    }
  }
};

In your case, also as recommended in the documentation, you would want a Pool. It is basically the same as Client, except now the Pool will manage the clients for you internally.

From your perspective, it is actually not much different with Client, except it is much simpler.

const {Pool} = require('pg');

// Notice here
const db = new Pool({
  user: "x",
  password: "x",
  host: "x",
  port: 123,
  database: "abc",
  ssl: true
});

module.exports = {
  async select( sql ){
    try
      const result = await pool.query( sql ); // <-- Notice here
      console.table(result.rows); // <-- Shows the data i want to send back.
      return JSON.stringify(result.rows);
    }

    catch(ex){
      console.log("We messed up! " + ex);
    }

    /* No need to release client as Pool does it for you internally */
  }
};

Upvotes: 2

Related Questions