How to integrate PostgreSQL database with Nodejs

Question

I want to write api (RESTful) in Nodejs to GET data from PostgreSQL. How do I connect to PostgreSQL database from Nodejs

Answer 1

You will probably want to use an NPM package. This will provide a client that allows interaction with your PostgreSQL database. The most common is pg. You can get this package in your project by using the command-line from your root directory: npm install pg -S. This adds the package as a dependency to your project.

Refer to the Github repo for this package if you want many useful code examples. Here's a really simple one of my own:

// Your Express GET Route`
router.get('/api/data', (request, response) => {
    var pg = require('pg');
    var pgURL = 'postgres://linktoyourdatabase';

    // connect to the database
    pg.connect(pgURL, (error, client, done) => {
        // query the database
        client.query('SELECT *', (error, result) => {
             // close connection
             done();
             // return the data requested
             response.json({ data: result });
        }
    }
};

You will probably want to decompose the code and separate the GET route from the database call. Make sure you also handle errors and validate data.

If you want a more comprehensive tutorial, I found this one by Michael Herman helpful.