How to connect to PostgreSQL from NodeJS

Question

I am trying to connect to a PostgreSQL database and make some queries. Though I have read a few threads here, the docs, and other blogs, I'm unable to make it works.

My function looks like this:

const postgreSQL = require('pg');

const config = {
  host: 'host',
  database: 'db',
  user: 'user',
  password: 'pass',
  port: 0000,
  max: 5, // max number of clients in the pool
  idleTimeoutMillis: 30000
};
let pool = new postgreSQL.Pool(config);

//.........other code.........

function CheckGeometryType(pool, tableName, column) {

  const schema = 'schema';
  let geometryValue = undefined;
  pool.connect(function(err, client, done) {
    client.query(`SELECT ST_GeometryType(${column})
                  AS geometryType FROM ${schema}.${tableName} LIMIT 1`);
    done(err);
    if (err) {
      console.log('Something went wrong. ' + err);
    }
    console.log("Result: " + queryResult.rows[0]);
    switch (queryResult) {
      // do things with result
    }
    done();
  });

  return geometryValue;
}

I've tried to do a console.log() inside the SWITCH, after/before done(), but it's not executed. I have printed all SQL queries formed on the string I pass to pool.query on pgAdmin and all works and return the expected values I want to get.

What am I doing wrong?

Thanks!

--- Edit ---

Thanks for the answers, but I'm still being unable to make it work. I've tried both methods but it isn't works. Looking at the Documentation I'm trying this method.

The code is:

let client = new Client(connectionString);
client.connect();
const query = client.query(new Query('SELECT ST_GeometryType($1) FROM $2.$3 LIMIT 1', [column, schema, tableName]));
console.log(JSON.stringify(query, null, 2)); //Debug purposes
query.on('row', (row) => {
    switch (row[0]) {
        case 'ST_LineString':
            geometryValue = 'LineString';
            break;
        case 'ST_MultiLineString':
            geometryValue = 'MultiLineString';
            break;
        case 'ST_Polygon':
            geometryValue = 'Polygon';
            break;
        case 'ST_MultiPolygon':
            geometryValue = 'MultiPolygon';
            break;
        case 'ST_Point':
            geometryValue = 'Point';
            break;
        case 'ST_MultiPoint':
            geometryValue = 'MultiPoint';
            break;
        default:
            break;
    }
});
query.on('end', (res) => {
    if(result.rows[0] === undefined){
        console.log('No data retrieved from DB');
    }
    client.end();
});
query.on('error', (res) => {
    console.log("Something went wrong: " + res);
});

Debugging, I can see that on query the values are stored fine:

{
  "text": "SELECT ST_GeometryType($1) FROM $2.$3 LIMIT 1",
  "values": [
    "column",
    "schema",
    "table"
  ],
  "portal": "",
  "_result": {
    "command": null,
    "rowCount": null,
    "oid": null,
    "rows": [],
    "fields": [],
    "_parsers": [],
    "RowCtor": null,
    "rowAsArray": false
  },
  "_results": {
    "command": null,
    "rowCount": null,
    "oid": null,
    "rows": [],
    "fields": [],
    "_parsers": [],
    "RowCtor": null,
    "rowAsArray": false
  },
  "isPreparedStatement": false,
  "_canceledDueToError": false,
  "_promise": null,
  "domain": null,
  "_events": {},
  "_eventsCount": 0
}

I've executed the query on pgAdmin an works fine, returning the desired result that I'm expecting but seems that trying to execute the query on the script doesn`t works.

What I'm missing? Thanks.

You call done(err) rigth after the query, all the rest of your code is out of reach. Besides client.query needs a callback function as second argument, i guess. Insude the callback you call done(null, data) — desoares
– desoares, Commented Apr 27, 2018 at 11:50

Rafał Praniuk · Accepted Answer · 2018-04-27 13:03:06Z

1

You can also use raw acces with "pg" package:

pg.connect(connectionString, function (err, client, done) {
  if (err) {
    return handleErrors(done, err, res);
  }
  return client.query("UPDATE tab SET v1=($1)  WHERE k1=($2)", [value, key], 
    function (err, result) {
     return reloadData(res, client, done, err);
  });
});

answered Apr 27, 2018 at 13:03

Rafał Praniuk

794 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MinionAttack Over a year ago

Hi, thanks for the answer but I'm still stuck. I've updated my answer.

Venkatesh · Accepted Answer · 2018-04-27 11:57:40Z

0

You can use sequelize for PostgreSQL. Its very good Object-Relational Mapping(ORM) in Node.js.

const sequelize = new Sequelize('database', 'username', 'password', { // gimme postgres, please! dialect: 'postgres' })

for more details check below link. http://docs.sequelizejs.com/manual/installation/usage.html

answered Apr 27, 2018 at 11:57

Venkatesh

3001 gold badge3 silver badges12 bronze badges

1 Comment

MinionAttack Over a year ago

Hi, thanks for the answer, but as project requirements I can't add more dependencies because we already have "pg". I've updated my answer.

MinionAttack · Accepted Answer · 2018-05-08 10:25:03Z

Well, finally I did achieve what I want to do. After read more the documentation and use the new methods (some questions had the old version, Node version < 7.x) I finally made it work. I'm posting the solution to help someone who may have the same problem building their own script.

This method uses the async/await form, so keep in mind that you will have to set all your functions to async and set await to the function that waits for the result inside of it, it's a chain from the function that connects with the DB to the top.

# Main file

#!/usr/bin/env node
'use strict';
const geometryTypeFinder = require("./modules/geometryTypeFinder");


readParametersFromConsole();

async function readParametersFromConsole() {
  ........ code ........
  
  
  processParameters(...params);
}


async function processParameters(...params) {
  ........ code ........
  
  transformation = await geometryTypeFinder.findGeometryType(transformation);

  ........ code ........

}

# Module file

'use strict';

const postgreSQL = require('pg');

module.exports = {
  findGeometryType: async function (transformation) {
    ........ code ........
    transformation.dataModel.entities = await FindGeometryType(...params);
    ........ code ........
  }
};

async function FindGeometryType(entities, geometricColumns) {
  ........ code ........

  for (let i = 0; i < numberOfEntities; i++) {
    ........ code ........
    for (let j = 0; j < numberOfColumns; j++) {
        let column = columns[j];
        let geometryType = await GetGeometryType(tableName, column);
        ........ now I can work with the value of geometryType ........
      }
  }
}

async function GetGeometryType(tableName, column) {

  const schema = 'schema';
  let geometryValue = undefined;
  const config = {
    user: 'user',
    password: 'pass',
    host: 'host',
    database: 'db',
    port: 1234,
  };

  let client = new postgreSQL.Client(config);
  try {
    client.connect();
    const result = await client.query(`SELECT ST_GeometryType(${column}) AS geo_type FROM ${schema}.${tableName} LIMIT 1`);
    if (result.rows.length === 0) {
      console.log('No data retrieved from DB');
    } else {
      const geoType = result.rows[0].geo_type; #<--- I get the result
      ........ code to process the value ........
    }
  } catch (error) {
    console.log(`Error retrieving database information: ${error.name} -> ${error.stack}`);
  } finally {
    client.end();
  }

  return geometryValue;
}

I don't know if this it's the best way to achieve what I want, but as I'm forced to use NodeJS and I'm learning by myself NodeJS, this is the best I could make it. Any suggestion will be well received.

Regards.

Collectives™ on Stack Overflow

How to connect to PostgreSQL from NodeJS

3 Answers 3

1 Comment

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Related