Skip to main content

Nodejs and PostgreSQL Tutorial with Example

Postgresql database connectivity in NodeJS

PostgreSQL is one of the popular open source [Object]- relational database, PostgreSQL supports json datatypes as well as you can define your own datatypes so its a good choice to use in your project. If you are using Nodejs as backend then you need PostgreSQL database driver to connect with nodejs. There are various driver available but most popular and well documented  is node-postgres [pg] so today we are going to learn how to use PostgreSQL with nodejs using stored procedure and connection pooling. Let’s start.

Prerequisite

  • You need to download postgresql and install on machine plus pgAdmin (postgresql management tool)
  • Nodejs and npm (node package manager)

Folder Structure

my package.json file.

Create table in Postgresql

Now go to pgAdmin and use postgresql database and create a dummy table Student.

Insert some dummy record into the table so later we will fetch through nodejs application.

so till now we have created a table and inserted some records . Now we are going to create our nodejs app using command

Installing nodejs postgresql driver

Install expressjs and postgresql nodejs driver [pg] in our nodejs application using command

this will install modules and write into our package.json file. Now we are going to use pg module to connect with database before that we have to create pg connection string. A pg database connection string made of with this format.

pg-connection-string

you can change your pg connection string as per your configuration.

here is my server.js file using pg module.

If you see i am using pg.connect method and passing connection string to get a connection, in callback. catch any error if there otherwise call postgresql query using client.query method, in this method you can also pass parameter to query. run server.js file and go to url http://localhost:4000/ , you will see student with id = 1

get-result

Stored Procedure in PostgreSQL

Create a simple stored procedure name GetAllStudent to fetch all student record and execute in pgAdmin

Now I will add one new endpoint /sp and call stored procedure with pg module in our server.js file

add this in server.js, run node application and check url http://localhost:4000/sp , you will see all student records in browser.

postgresql-stored-procedure-nodejs

Till here, you are able to use postgresql query and stored procedure through nodejs application but if you are running a web server where you are getting 100 request at a time then this code will open 100 connection to your postgresql database and slowly it will start throw no memory error because of so many connections open at a time, to overcome this situation use connection pool, this will create connection but will not close till some threshold and reuse for further requests. I will show how to use connection pooling in postgresql with nodejs.

Connection Pooling in PostgreSQL

create a new config object for connection pool like this

now we will call pg.pool method to create connection pool using

now add new endpoint /pool , and use pool variable to connect with database

now hit http://localost:4000/pool , you get all students records.
If you want to learn more about postgresql module you can refer documentation here.

Leave a Reply

Your email address will not be published. Required fields are marked *