How to use PostgreSQL in Nodejs

I have written many articles on Node.js and AngularJs. In this article, I will demonstrate how to use PostgreSQL with Node.js and deploy on Heroku. I think you already smelled that this is one of starting article which will finally merged with Salesforce to create some cool application.

Assuming you already have some background knowledge on Node.js and Heroku, If not please visit previous articles of this blog or my Slideshare account. Using Angularjs is totally optional and you can use library like JQuery or plain JavaScript.

To connect PostgreSQL, we need “pg” module.

File -1 is code snippet of “package.json”. After creating this file, you will need to run command “npm install“. This command will install all the required dependencies needed for this application.

File 2, shows how different CRUD operations is performed in “pg” module of node.js. If you notice this file ; we are using “process.env.DATABASE_URL“. This variable will store Database string needed to connect PostgreSQL in heroku, this will be automatically available once you install “PostgreSQL” add-on in your Heroku application. If this variable is not available (like on local system) then we are using actual connection string of PostgreSQL installed on system. you will need to replace this connection string with your credentials.

If you want to create Table schema, you need to hit “/db/createTable” URL and if you want to delete table, hit “/db/dropTable” URL.

File 3 is Node.js starting file, which actually routes all requests. For code modularity, I have separated database operations related code in “File 2” and importing in this file.

File 4, is landing page which calls CRUD functions using ajax with the help of Angularjs.

File 5 – “App.json” which is important for “Heroku 1” button to work in Github. You can ignore this file. This file basically is used by Heroku to directly install this application on Heroku on single click and known as “Heroku Button“.

You can find complete code here on my Git repository and working demo here.

Please provide your feedback here, See you in next article.

Posted

November 17, 2014

Node.js, Web Technology

Jitendra

Tags:

AngularJs, Git, Heroku, NodeJs, pg, PostgreSQL

Comments

11 responses to “How to use PostgreSQL in Nodejs”

sohit

April 17, 2015

Hi,

Need your help, your demo is not working throwing error :

Application Error

I tried copying all code and made necesarry changes but getting the same error. can you please check whats the issue.

Loading…

Reply
1. Jitendra Zaa
  
  April 20, 2015
  
  Hi Sohit,
  Did you tried to fetch heroku log for errors ? What error you are getting ?
  
  Loading…
  
  Reply
  1. sohit
    
    April 22, 2015
    
    After series of test, I was able to actually find out the issue. I changed the port number and also I found out that in code I used var dbOperations which is case sensitive. but after changing the file name to which I was using, I was finally able to run the app at server. Thank so much for the excellent post to get introduced to node Js
    
    Loading…
    
    Reply
  2. Suresh
    
    May 6, 2018
    
    Hello Jitendra,
    
    Thanks for this wonderful article. I’m new to Node/expressJS.
    When try i to access external website (salesforce Marketing cloud) using index.html and App.js i’m not able to
    1) pass the http request from index.html to App.js 2) hence i have http request code in App.js. 3) i’m able to get the response from SFMC but not able to pass the same to index.html to parse and display.
    
    Index.html code:
    $scope.getAllRec = function(){
    $http ({
    method: ‘GET’,
    url: ‘/hub/v1/campaigns’,
    //url: ‘https://www.exacttargetapis.com/hub/v1/campaigns’,
    headers: { ‘Authorization’: ‘Bearer ‘abc’, ‘content-type’: ‘application/json’ }
    }). success(function(data, status)
    { console.log (‘success: str form for JSON: ‘ + data);
    alert(‘AngularJS success scope: ‘);
    $scope.dataset = JSON.parse(data);
    alert($scope.dataset);
    }). error(function(data, status)
    { console.log (‘error: str form for JSON: ‘ + data);
    alert(‘AngularJS error scope: ‘);
    $scope.dataset = data || “Request failed “;
    alert($scope.dataset);
    }); //end of error fun
    } //end of function
    
    App.js code:
    app.get(‘/hub/v1/campaigns’, function(req, res)
    {
    request ({
    uri: ‘https://www.exacttargetapis.com/hub/v1/campaigns’,
    headers: { ‘Authorization’: ‘Bearer 7AN0d9ntuWA9tYSHvbziJG6Q’, ‘content-type’: ‘application/json’}
    },
    function(error, response, body)
    {
    if (!error && res.statusCode === 200)
    {
    res.writeHead(200, {‘Content-Type’: ‘text/plain’});
    res.write(JSON.stringify( body, null, ” “));
    console.log(body);
    res.sendfile(‘views/index.html’);
    res.end();
    
    }
    else
    {
    res.json (error);
    }
    
    console.log(‘Original URL is: ‘+ req.originalUrl); // ‘/admin/new’
    console.log(‘Base URL is: ‘, req.baseUrl); // ‘/admin’
    console.log(‘Path is: ‘ + req.path);
    
    });
    
    //function replacer(key, value) {
    // return key.replaceAll(/\\/g,”);
    //}
    
    } );
    
    May be an example of how to access external site and pass the json response to html/view will help a lot get going.
    
    Regards,
    Suresh
    
    Loading…
    
    Reply
sohit

April 20, 2015

After making some changes like changing port number I was able to run the app locally correctly : but now after deploying to Heroku i get error as ;

Error: Cannot find module ‘./dbOperations.js’

I have removed .js extension still getting the same error. Please help how to solve this error.

Loading…

Reply
Thomas Q Brady

June 5, 2015

Shouldn’t the ‘pg’ module be in your package.json?

Loading…

Reply
1. Jitendra Zaa
  
  June 5, 2015
  
  Yeah, I installed manually, we need it in Package.json.
  
  https://github.com/JitendraZaa/node-postgres/tree/master/node_modules
  
  Loading…
  
  Reply
guest

June 28, 2015

You can save lots of time with pg-promise: https://github.com/vitaly-t/pg-promise

Loading…

Reply
Emre

January 6, 2016

Hi

Thanks for this great article. But, i could not run the passing datas from the html page to database. I test dbOperations with url get requests and it works properly. But, html apge does not call dbOperations. Do you have any suggestions? Should i change sometings in ‘index.html’?

Thanks again.

Loading…

Reply
Yatish Patil

September 10, 2019

Why we are using angular JS here ? Any alternative for that?

Loading…

Reply
Ayan Pramanik

May 18, 2020

when I click on the button ‘Add’ nothing is happening. On reload the page an error is thrown:

An error occurred in the application and your page could not be served. If you are the application owner, check your logs for details. You can do this from the Heroku CLI with the command

Following is the error log

2020-05-18T20:43:04.724323+00:00 app[web.1]:
2020-05-18T20:43:04.724420+00:00 app[web.1]: events.js:72
2020-05-18T20:43:04.724667+00:00 app[web.1]: throw er; // Unhandled ‘error’ event
2020-05-18T20:43:04.724691+00:00 app[web.1]: ^
2020-05-18T20:43:04.725969+00:00 app[web.1]: Error: connect ECONNREFUSED
2020-05-18T20:43:04.725970+00:00 app[web.1]: at errnoException (net.js:905:11)
2020-05-18T20:43:04.725972+00:00 app[web.1]: at Object.afterConnect [as oncomplete] (net.js:896:19)

Loading…

Reply